kbd_2.pdf

KBD2

JęzykSQLwOracle

SQLijegowykonywanie

RodzajezdańjęzykaSQL

Komercyjnebazydanych2

JęzykSQLwOracle

doc.drinż.TomaszTraczyk

DQL ( DataQueryLanguage )–zapytania SELECT ,służącedo

pozyskiwaniadanychzbazy

DML ( DataManipulationLanguage )–dodawanie,usuwanie

orazmodyﬁkacjadanych

DDL ( DataDeﬁnitionLanguage )–tworzenieimodyﬁkacja

strukturdanychorazdoadministrowaniabaządanych

InstytutAutomatykiiInformatykiStosowanej

PolitechnikaWarszawska

Styczeń2010

T.Traczyk

KBD2–JęzykSQLwOracle

2/40

KBD2

JęzykSQLwOracle

SQLijegowykonywanie

KBD2

JęzykSQLwOracle

SQLijegowykonywanie

ŚrodowiskawykonywaniajęzykaSQL

Obsługabłędów

◮ Środowiskakonwersacyjne

◮ konsoleSQL(SQL*Plus,SQLDeveloper)

◮ programyadministracyjne(EnterpriseManager)

◮ programydograﬁcznegotworzeniazapytań(np. OracleDataQuery)

◮ Językiprogramowania

◮ zanurzonySQL( embeddedSQL )

◮ OCI( OracleCallInterface )

◮ ODBC

◮ JDBC

◮ Narzędziaczwartejgeneracji(OracleDeveloper,HTMLDBczyli

ApplicationExpress)

◮ Programypowszechnegoużytku(przezODBC)np. MicrosoftO … ce

zMicrosoftQuery

◮ ObsługabłędówwSQL

◮ sygnalizowanekomunikatem

◮ obsłużone:

◮ błędyskładniowe–instrukcjaniejestwykonywana

◮ błędywykonania–efektybłędnejinstrukcjiautomatycznie

wycofywane

◮ transakcjajestkontynuowana

◮ Komunikatyobłędach

◮ trójliterowykodśrodowiska

◮ numerbłędu

◮ komunikatobłędzie(wjęzykuNLS)

◮ Propagacjainformacjiobłędzie

◮ programktóryspowodowałbłądmożeprzechwycićinformację

(wyjątek)

◮ nieprzechwyconainformacjaobłędzieukazujesięnaekranie

operatora

T.Traczyk

KBD2–JęzykSQLwOracle

3/40

T.Traczyk

KBD2–JęzykSQLwOracle

4/40

KBD2

JęzykSQLwOracle

SpecyﬁcznecechyjęzykaSQLwOracle

KBD2

JęzykSQLwOracle

SpecyﬁcznecechyjęzykaSQLwOracle

Przykładowastrukturadanych

DQL 1 –specyﬁcznecechyOracle

WYKLADOWCY

#Idwykladowcy

• Nazwisko

• Imiona

◦ Tytul

◦ Datazatrudnienia

◦ Idprzelozonego(fk)

◮ Szczegółyskładni

◮ słowo AS niedozwolneprzedaliasamitabel

◮ słowo AS nieobowiązkoweprzedaliasamikolumn

◮ aliasykolumnniedozwolonewklauzulach WHERE , GROUP BY i HAVING

◮ dostępneoperatorymnogościowe: UNION [ALL] , MINUS , INTERSECT

◮ Tabelazaślepka DUAL ,np. SELECT SYSDATE FROM DUAL;

PRZEDMIOTY

#Idprzedmiotu

• Nazwa

• Idwykladowcy(fk)

◦ Liczbagodzin

1 DataQueryLanguage

T.Traczyk

KBD2–JęzykSQLwOracle

5/40

T.Traczyk

KBD2–JęzykSQLwOracle

6/40

KBD2

JęzykSQLwOracle

SpecyﬁcznecechyjęzykaSQLwOracle

KBD2

JęzykSQLwOracle

SpecyﬁcznecechyjęzykaSQLwOracle

ZłączeniawgSQL’99

Podzapytaniewklauzuli FROM

◮ CROSS JOIN –produktkartezjański

◮ LEFT|RIGHT|FULL OUTER JOIN –złączeniazewnętrzne

◮ JOIN ... ON –złączeniezjawnympodaniemwarunkuzłączenia

wklauzuli ON

◮ NATURAL JOIN –złączenienaturalnezautomatycznymdoborem

kolumn

◮ wgzgodnościnazwkolumn

◮ nieconiebezpieczne!

◮ JOIN ... USING –złączenierównościowezewskazaniemkolumn

◮ wymieniasięnazwykolumn(zgodnewobutabelach)

◮ nakolumnyzklauzuli USING niemożnapowoływaćsięzużyciem

aliasutabeli

SELECT w.id_wykladowcy, nazwisko, obciazenie,

liczba_przedmiotow

alias w , by uniknąć niejednoznacz

ności

FROM wykladowcy w,

(SELECT id_wykladowcy, SUM(liczba_godzin) AS obciazenie

FROM przedmioty

GROUP BY id_wykladowcy podzapytanie oblicza obciążenia

godzinowe

) o, o jest aliasem nadanym wynikowi

podzapytania

(SELECT id_wykladowcy, COUNT( ) AS liczba_przedmiotow

FROM przedmioty

GROUP BY id_wykladowcy

podzapytanieobliczaliczbęprowa

dzonychprzedmiotów

) l

WHERE w.id_wykladowcy = o.id_wykladowcy warunki złączeń tabeli z wynikami

podzapytań

AND w.id_wykladowcy = l.id_wykladowcy

ORDER BY nazwisko;

T.Traczyk

KBD2–JęzykSQLwOracle

7/40

T.Traczyk

KBD2–JęzykSQLwOracle

8/40

KBD2

JęzykSQLwOracle

SpecyﬁcznecechyjęzykaSQLwOracle

KBD2

JęzykSQLwOracle

SpecyﬁcznecechyjęzykaSQLwOracle

Podzapytaniewklauzuli SELECT

Zapytaniehierarchiczne

Podzapytaniewklauzuli SELECT musizwracaćwartośćskalarną–udaje

wyrażenie.

SELECT LPAD(’ ’,2 (LEVEL−1)) || nazwisko as Nazwisko,

id_wykladowcy, id_przelozonego LEVEL –poziomzagłębieniawhie

rarchii

FROM wykladowcy

CONNECT BY PRIOR id_wykladowcy = id_przelozonego

CONNECT BY – związek między

elementami w hierarchii, PRIOR –

kierunekprzeszukiwania

START WITH id_przelozonego IS NULL START WITH –punktstartowy

ORDER SIBLINGS BY nazwisko;

Przykład[21]

specjalna klauzula do sortowania

wramach„gałęzi”

T.Traczyk

KBD2–JęzykSQLwOracle

9/40

T.Traczyk

KBD2–JęzykSQLwOracle

10/40

KBD2

JęzykSQLwOracle

SpecyﬁcznecechyjęzykaSQLwOracle

KBD2

JęzykSQLwOracle

SpecyﬁcznecechyjęzykaSQLwOracle

WyrażeniawSQL

Pseudokolumny

◮ Budowa

◮ stałe

◮ nazwykolumnipseudokolumn

◮ operatory

◮ wywołaniafunkcjiwbudowanych

◮ wywołaniafunkcjiużytkownika(wywoływanafunkcjaniemoże

modyﬁkowaćdanychanizmiennychpakietów)

◮ Stałe

◮ znakowe–wpojedynczychapostrofach

◮ czasidata–takjakznakoweoileformatzgodnyzustawionymdla

sesjialbojawnakonwersja: TO DATE(’1997-10-24’,

’YYYY-MM-DD’)

◮ numeryczne–notacjazwykłalub„naukowa”

◮ NULL

◮ Używanepodobniejakkolumny

◮ Określonejedyniewzapytaniach!

ROWID Podajewewnętrznyidentyﬁkatorwiersza

ROWNUM Podaje numer kolejny pobranego wiersza

(aleprzedsortowaniem)

LEVEL Podajepoziomzagłębieniawhierarchii,do

stępnatylkowzapytaniuhierarchicznym

nazwa sekwencji.NEXTVAL Podajenowąwartośćzsekwencji

nazwa sekwencji.CURRVAL Podajeostatniąwartośćzwróconąprzezse

kwencję

T.Traczyk

KBD2–JęzykSQLwOracle

11/40

T.Traczyk

KBD2–JęzykSQLwOracle

12/40

KBD2

JęzykSQLwOracle

SpecyﬁcznecechyjęzykaSQLwOracle

KBD2

JęzykSQLwOracle

SpecyﬁcznecechyjęzykaSQLwOracle

NULL

◮ Stała NULL

◮ Wyrażeniezoperandemowartości NULL całejestrówne NULL ,wyjątki:

◮ NULL AND fałsz–dajefałsz, NULL OR prawda–dajeprawdę

◮ tekst|| NULL –daje tekst

◮ NULL jestignorowanyprzezfunkcjegrupujące

◮ Pustynapistypu VARCHAR2 ,czyli ’’ ,jestrównoznacznyz NULL

◮ Testowaniewartościsensownejedynieoperatorami IS NULL lub IS NOT NULL

◮ Wzapytaniuwarunekowartości NULL jestuważanyzaniespełniony

◮ Wwięzachkontrolnychwarunekowartości NULL jestuważanyzaspełniony

◮ Dozastępowaniawartości NULL służąwyrażenia

NVL(wartość wejściowa, wartość zastępcza)

NVL2(wartość wejściowa, wart jeśli nie null, wart jeśli null)

COALESCE(wartość 1, wartość 2, ...) –zwracapierwsząwartość

nienullową

◮ Wyrażeniezwracające NULL jeślioperandysąrówne:

NULLIF(wartość 1, wartość 2)

Operatory

Arytmetyczne + - * / Operacjearytmetyczne

Logiczne NOT, AND, OR Operacjelogiczne

Znakowe || Konkatenacja

Dataiczas + - Przesunięcie daty/czasu: dla typu

DATE 1oznaczajednądobę

- Odejmowanie dat/czasu: dla typu

DATE różnica w dobach (i ich ułam

kach)

Porównania = Równość

!=^=<> Brakrówności

< > <= >= Nierówności

LIKE Porównanie wartości znakowej

zwzorcem(znakispecjalne % i )

BETWEENaANDb Przynależnośćdoprzedziału

IN ( lista wartości) Przynależnośćdozbioruwartości

IS NULL, IS NOT NULL Testbrakuwartości

T.Traczyk

KBD2–JęzykSQLwOracle

13/40

T.Traczyk

KBD2–JęzykSQLwOracle

14/40

KBD2

JęzykSQLwOracle

SpecyﬁcznecechyjęzykaSQLwOracle

KBD2

JęzykSQLwOracle

SpecyﬁcznecechyjęzykaSQLwOracle

Konwersje

Wzorceformatów–wybór

◮ KonwersjewSQLsąwzasadzierobioneautomatycznie

◮ Funkcjekonwersji TO typ ,np. TO CHAR , TO DATE , TO NUMBER

◮ Formatdlaczasuidatyorazliczb

◮ domyślny–ustawianydlasesji

◮ wartośćpoczątkowabranazustawieńNLSnastacjiklienta

◮ jawniepodany,np.: TO CHAR(SYSDATE,’YYYY-MM-DD HH24:MI:SS’)

Liczby 9 cyfra

0 cyfra, z uzupełnieniem liczby zerem wiodącym lub

końcowym

D znakdziesiętny(zależnyodNLS)

G separatorgrup,np. tysięcy(zależnyodNLS)

Dataiczas YYYY rok(4cyfry)

MM miesiąccyframi

MONTH miesiącsłownie(wjęzykuNLS)

DD dzieńmiesiąca

DAY dzieńtygodniasłownie(wjęzykuNLS)

HH godzina(112)

HH24 godzina(024)

MI minuty

SS sekundy

RR rok(2cyfry)ze„zgadywaniem”wieku(Y2K)

T.Traczyk

KBD2–JęzykSQLwOracle

15/40

T.Traczyk

KBD2–JęzykSQLwOracle

16/40

KBD2

JęzykSQLwOracle

SpecyﬁcznecechyjęzykaSQLwOracle

KBD2

JęzykSQLwOracle

SpecyﬁcznecechyjęzykaSQLwOracle

Funkcjewbudowane(wybór)

Funkcjewbudowane(wybór),c.d.

Arytmetyczne ABS , EXP , LN , Typowefunkcjearytmetyczne

LOG , POWER,

SIGN , ROUND , TRUNC ,

MOD , SIN ,

COS , TAN , SQRT

Dataiczas SYSDATE Zwracabieżącyczasidatę

ROUND Zaokrągleniedatyiczasudookreślo

nejprecyzji(miesiąca,kwartałuitp.)

TRUNC Obcięcie daty i czasu do określonej

precyzji

NEXT DAY Znalezienie daty najbliższego poda

negodniatygodnia

Znakowe ASCII Zamianaznakunakodliczbowy

LENGTH Zwracadługośćnapisuwznakach

CHR Zamianakoduliczbowegonaznak

LOWER , UPPER Zmiana liter na małe/wielkie, działa prawidłowo na

znakachnarodowych

INITCAP Zmienia pierwsze litery wyrazów na wielkie, pozo

stałenamałe

LPAD , RPAD Dodaje podaną liczbę powtórzeń wzorca po le

wej/prawejstronienapisu

LTRIM , RTRIM Usuwa określone znaki (np. spacje) z lewej/prawej

stronynapisu

INSTR Badawystąpieniewzorcawnapisie

SUBSTR Zwracafragmentnapisu

REPLACE Zamieniawzorzecznalezionywnapisienainny

Konwersja TO CHAR, TO DATE,

TO NUMBER Konwersjadoodpowiedniegotypu

ROWIDTOCHAR,

CHARTOROWID Konwersjaadresówwierszy

HEXTORAW, RAWTOHEX Konwersjadanychbinarnych

T.Traczyk

KBD2–JęzykSQLwOracle

17/40

T.Traczyk

KBD2–JęzykSQLwOracle

18/40

KBD2

JęzykSQLwOracle

Małoznaneelementyzapytań

KBD2

JęzykSQLwOracle

Małoznaneelementyzapytań

WyrażenieDECODE

DECODE( wyrażenie , klucz1 , wynik1 [, klucz2 , wy

nik2 ...] [, wartość _ domyślna ])

Przykłady:

WyrażenieCASE

SELECT DECODE(tytul, ’prof.’, ’W.P.’, ’dr’, ’Pan’,

’Ob.’) wybieraodpowiednizwrot

|| ’ ’ || imiona || ’ ’ || INITCAP(nazwisko) AS

naglowek

nazwiskojestzamienianezwielkich

liternazwykleprzyjętąformę

◮ Porównywaniewyrażeniazlistąwartości:

CASE wyrażenie

WHEN klucz1 THEN wynik1

[WHEN klucz2 THEN wynik2 ...]

[ELSE wartość _ domyślna ]

◮ Sprawdzaniewarunków(zwracającychwartośćlogiczną):

CASE WHEN warunek1 THEN wynik1

[WHEN warunek2 THEN wynik2 ...]

[ELSE wartość _ domyślna ]

FROM wykladowcy;

SELECT id_przedmiotu,

DECODE( SIGN(liczba_godzin−2.1), −1, ’niewielkie’, 1, ’znaczne’) || ’

obciążenie’ AS obciazenie

FROM przedmioty;

użyto funkcji SIGN do porównania

dwóchliczb

T.Traczyk

KBD2–JęzykSQLwOracle

19/40

T.Traczyk

KBD2–JęzykSQLwOracle

20/40

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: