BAZY DANYCH
- J ZYK ZAPYTA SQL
- PROGRAMOWANIE POCEDURALNE PL/SQL
1
SQL  PL/SQL
SPIS TRE CI:
SPIS TRE CI:............................................................................................................................................................. 2
1. TYPY DANYCH............................................................................................................................................... 4
1.1. S ................................................................................................................................... 4
1.2. T ...................................................................................................................................... 4
1.3. K .................................................................................................................................... 5
1.4. W ........................................................................................................................................ 5
2. STRUKTURY DANYCH W MODELU RELACYJNYM............................................................................ 7
3. ROZKAZY J ZYKA SQL.............................................................................................................................. 8
4. SKA
ADNIA ROZKAZÓW J ZYKA SQL.................................................................................................... 9
4.1. D ............................................................................................................................ 9
4.2. R CREATE TABLE......................................................................................................................... 9
4.3. R DROP ......................................................................................................................................... 10
4.4. R INSERT...................................................................................................................................... 11
4.5. R DELETE..................................................................................................................................... 12
4.6. R CREATE SEQUENCE............................................................................................................... 12
4.7. R SELECT ..................................................................................................................................... 14
4.8. R UPDATE .................................................................................................................................... 15
4.9. R RENAME ................................................................................................................................... 16
4.10. R ALTER TABLE ......................................................................................................................... 16
4.11. R CREATE INDEX ....................................................................................................................... 17
4.12. R CREATE VIEW......................................................................................................................... 18
4.13. R COMMIT ................................................................................................................................... 19
4.14. R ROLLBACK .............................................................................................................................. 19
4.15. R SAVEPOINT.............................................................................................................................. 20
4.16. R SET TRANSACTION................................................................................................................ 20
5. OPERACJE RELACYJNE ........................................................................................................................... 21
5.1. S .................................................................................................................................................. 21
5.2. P ................................................................................................................................................ 21
5.3. P ................................................................................................................................................... 22
5.4. P .............................................................................................................................................. 22
5.5. O ......................................................................................................................... 23
5.6. G ............................................................................................................................................ 23
5.7. K SELECT............................................................................................ 24
5.8. P ......................................................................................................................................... 24
5.9. W ( )........................................................................................................................... 25
5.10. T ............................................................................................................................................. 26
6. NORMALIZACJA RELACJI....................................................................................................................... 27
6.1. C ................................................................................................................................. 27
6.2. P .................................................................................................................. 27
6.3. D ............................................................................................................................ 28
6.4. D ...................................................................................................................... 30
6.5. T .................................................................................................................... 32
7. CZWARTA POSTAĆ NORMALNA ........................................................................................................... 32
7.1. P ........................................................................................................................ 33
7.2. P ....................................................................................................................................... 33
8. WARUNKI I WYRA ENIA ......................................................................................................................... 34
8.1. W ............................................................................................................................ 34
8.2. O .................................................................................................................... 34
8.3. O ............................................................................................................................. 34
Opis..................................................................................................................................................................... 34
8.4. O ........................................................................................................................ 34
8.5. O .............................................................................................................................. 35
8.6. O ..................................................................................................................... 36
8.7. W .............................................................................................................................................. 36
8.8. W .................................................................................................................................................. 37
9. STANDARDOWE FUNKCJE J ZYKA SQL ............................................................................................ 39
9.1. F ............................................................................................................................. 39
9.2. F ................................................................................................................................... 39
9.3. F ................................................................................................................................... 40
9.4. F ................................................................................................................................. 41
9.5. F ................................................................................................................. 42
9.6. I ........................................................................................................................................... 43
9.7. F ........................................................................................................................ 43
9.8. F ........................................................................................................................... 43
9.9. F ........................................................................................................................................... 44
10. PROGRAMOWANIE PROCEDURANE - PL/SQL................................................................................... 46
10.1. W ....................................................................................................................................... 46
10.2. S ................................................................................................................................... 46
10.3. P .............................................................................................................................. 47
10.4. K .................................................................................................................................................. 47
10.5. R .................................................................................................................................................. 48
10.6. O .................................................................................................................................... 49
11. ROZKAZY J ZYKA PL/SQL...................................................................................................................... 53
11.1. R OPEN ......................................................................................................................................... 53
11.2. R CLOSE ....................................................................................................................................... 53
11.3. R FETCH ....................................................................................................................................... 53
11.4. R SELECT ... INTO....................................................................................................................... 54
11.5. R IF ................................................................................................................................................ 54
11.6. R LOOP ......................................................................................................................................... 55
11.7. R EXIT........................................................................................................................................... 56
11.8. R GOTO......................................................................................................................................... 56
3
1. TYPY DANYCH
1.1. Spis typów danych
Typ Opis
Ci g znaków o zmiennej długo ci nie wi kszej ni podany rozmiar. Dla tego typu
char(size) maksymalny rozmiar mo e wynosić 255. W przypadku nie podania rozmiaru domy lnie
przyjmowana jest warto ć 1
character Synonim do char
W aktualnej wersji ORACLE'a jest to synonim do char, konieczne jest jednak podanie
varchar(size) rozmiaru. W przyszłych wersjach zakłada si , e char b dzie ci giem znaków o stałej
długo ci, natomiast varchar o zmiennej.
Poprawne daty z zakresu 1 stycznia 4712 p.n.e. do 31 grudnia 4712 n.e. Domy lny
date
format wprowadzania to" DD-MON-YY np.: '01-JAN-89'
Ci g znaków o zmiennej długo ci nie wi kszej ni 65535 znaków. Mo na zdefiniować
long
tylko jedn kolumn typu long w jednej tabeli.
long varchar synonim do long
Ci g bajtów o podanej długo ci. Specyfikacja rozmiaru jest konieczna. Rozmiar
raw(size) maksymalny dla tego typu to 255. Warto ci do pól tego typu s wstawiane jako ci gi
znaków w notacji szesnastkowej.
Ci g bajtów o zmiennej długo ci. Pozostałe własno ci jak dla typu long. Warto ci do
long raw
pól tego typu s wstawiane jako ci gi znaków w notacji szesnastkowej.
Unikalna warto ć identyfikuj ca wiersz. Podany typ jest pseudotypem, tzn. kolumna
tego typu nie mo e być utworzona w tabeli i nie jest w niej przechowywana, ale
rowid obliczana na podstawie informacji o fizycznym poło eniu wiersza na dysku, w pliku itp.
Warto ć typu rowid mo e być przekonwertowana do typu znakowego za pomoc funkcji
ROWIDTOCHAR.
Typ numeryczny. Jego warto ci mog si zmieniać w zakresie od 1.0 * 10 do 9.99 *
number 10 . Mo liwe jest ograniczenie podanego zakresu przez specyfikacj precyzji i skali w
sposób opisany poni ej.
1.2. Typ numeryczny
Typ numeryczny jest u ywany do przechowywania liczb zarówno zmienno jak i stałoprzecinkowych. Dla kolumn
numerycznych typ mo na wyspecyfikować na jeden z trzech sposobów:
- number
- number (precyzja)
- number (precyzja, skala)
Precyzja okre la całkowit liczb cyfr znacz cych i mo e si zmieniać od 1 do 38. Skala okre la liczb cyfr po
prawej stronie kropki dziesi tnej i mo e si zmieniać w zakresie od -84 do 127.
W momencie definiowania kolumny numerycznej dobrym zwyczajem jest podawanie zarówno precyzji jak i skali,
poniewa wymusza to automatyczn kontrol wprowadzanych warto ci, a wi c zwi ksza szanse na zachowanie
spójno ci bazy danych. Je li warto ć przekracza maksymaln precyzj , to generowany jest bł d. Je li warto ć
przekracza skal , to jest zaokr glana. Je li skala jest ujemna, to warto ć jest zaokr glana do podanej liczby miejsc po
lewej stronie kropki dziesi tnej; np. specyfikacja (10, -2) oznacza zaokr glenie do setek. Czasami specyfikuje si
równie skal wi ksz ni precyzj . Oznacza to wtedy, e wprowadzane liczby musz mieć po kropce dziesi tnej tak
liczb zer jaka jest ró nica mi dzy skal a precyzj ; np: number(4, 5) b dzie wymagał jednego zera po kropce
dziesi tnej.
Liczby mo na równie zapisywać w formacie zmiennoprzecinkowym. Składa si ona wtedy z ułamka dziesi tnego,
bezpo rednio po którym znajduje si litera E i wykładnik pot gi liczby 10 przez jaki trzeba pomno yć ten ułamek. Dla
przykładu zapis 9.87E-2 oznacza 9.87 * 10 .
4
Inne systemy baz danych posiadaj kilka ró nych typów numerycznych, które w ORACLE'u implementowane s
jako number. Mo liwe jest stosowanie nazw tych typów. Nazwy te oraz sposób implementacji za pomoc typu number
przedstawia tabela:
Specyfikacja Typ Precyzja Skala
number number 38 null
number(*) number 38 null
number(*, s) number 38 s
number(p) number p 0
number(p,s) number p s
decimal number 38 0
decimal(*) number 38 0
decimal(*, s) number 38 s
decimal(p) number p 0
decimal(p, s) number p s
integer number 38 0
smallint number 38 0
float number 38 null
float(*) number 38 null
float(b) number b null
real number 63 binary (18 decimal) null
double precision number 38 null
1.3. Konwersje typów
W systemie zarz dzania baz danych mo liwe s konwersje danych jednego typu do danych innego typu. Dane te
musz spełniać pewne warunki, aby konwersja taka była mo liwa, np. chc c przekonwertować ci g znaków do liczby,
ci g ten powinien składać si z cyfr. Poni sza tabela przedstawia funkcje słu ce do wykonywania konwersji pomi dzy
poszczególnymi typami:
Do typu
Z typu char number date
char zb dna TO_NUMBER TO_DATE
number TO_CHAR zb dna TO_DATE
date TO_CHAR niemo liwa zb dna
1.4. Warto ci puste
Pola tabeli mog przyjmować warto ci puste, pod warunkiem, e nie zostało to zabronione przez projektanta bazy
danych. Warto ć pusta (NULL) nie jest równa warto ci 0 i w wyniku obliczenia dowolnego wyra enia, którego
argumentem jest NULL otrzymuje si równie warto ć pust (NULL).
Funkcja NVL pozwala dokonać konwersji warto ci aktualnej (do niej samej) lub warto ci pustej do warto ci
domy lnej. Działanie funkcji NVL ilustruje przykład:
NVL(COMM, 0) zwróci warto ć COMM, jesli nie jest to warto ć pusta lub 0 je li COMM ma warto ć NULL.
Wi kszo ć funkcji grupuj cych ignoruje warto ć NULL. Np. zapytanie, którego zadaniem jest obliczenie redniej z
pi ciu nast puj cych warto ci: 1000, NULL, NULL, NULL i 2000 zwróci 1500 poniewa (1000 + 2000)/2 = 1500.
Jedyne operatory porównania, które mo na u yć do warto ci pustej to IS NULL i IS NOT NULL. Je li zostanie
u yty jakikolwiek inny operator porównania do warto ci pustej, to wynik jest nieokre lony. Poniewa NULL
5
reprezentuje brak warto ci, wi c nie mo e on być równy ani nierówny jakiejkolwiek innej warto ci, równie innemu
NULL.
ORACLE traktuje warunki, których wynik jest nieznany jako fałszywe. Tak wi c warunek COMM = NULL jest
nieznany, w zwi zku z czym rozkaz SELECT z takim warunkiem nie zwróci nigdy adnego wiersza. Jednak w takiej
sytuacji ORACLE nie zgłosi informacji o wyst pieniu bł du.
6
2. STRUKTURY DANYCH W MODELU RELACYJNYM
Podstawow struktur danych jest relacja b d ca podzbiorem iloczynu kartezja skiego dwóch wybranych zbiorów
reprezentuj cych dopuszczalne warto ci. W bazach danych relacja przedstawiana jest w postaci tabeli. Relacja jest
zbiorem krotek posiadaj cych tak sam struktur , lecz ró ne warto ci. Ka da krotka odpowiada jednemu wierszowi
tablicy. Ka da krotka posiada co najmniej jeden atrybut odpowiadaj cy pojedynczej kolumnie tablicy. Ka da relacja
(tablica) posiada nast puj ce własno ci:
- krotki (wiersze) s unikalne
- atrybuty (kolumny) s unikalne
- kolejno ć krotek (wierszy) nie ma znaczenia
- kolejno ć atrybutów (kolumn) nie ma znaczenia
- warto ci atrybutów (pól) s atomowe
Przykładow tabel wraz z jej elementami przedstawia rysunek:
Dokumentacja systemów zarz dzania bazami danych posługuje si najcz ciej terminologi tabela, wiersz i
kolumna, a nie terminologi relacyjn . Wynika to z tego, e operacje na relacjach s opisywane za pomoc
matematycznych operacji na zbiorach i relacjach, które s cisłe, ale trudno zrozumiałe dla przeci tnego u ytkownika.
Natomiast posługiwanie si tabelami, wierszami i kolumnami jest mniej formalne i cisłe, ale bardziej przejrzyste. W
dalszej cz ci tego wykładu b dzie b dzie stosowana zarówno jedna jak i druga terminologia.
Tabela mo e reprezentować:
- zbiór encji wraz z atrybutami
- zbiór powi za pomi dzy encjami wraz z ich atrybutami
- zbiór encji wraz z atrybutami i ich powi zania z innymi encjami (wraz z atrybutami)
Ka dy wiersz w tabeli reprezentuje pojedyncz encj , powi zanie lub encj wraz z powi zaniami. W tabeli nie
powinny powtarzać si dwa identyczne wiersze - zabezpieczenie przed tym powtórzeniem jest realizowane poprzez
pola kluczowe. Wiersze w odró nieniu od kolumn s dynamiczne - działanie bazy danych polega na dopisywaniu,
modyfikacji i usuwaniu wierszy. W raz utworzonej tabeli rzadko dopisuje si lub kasuje kolumny - poniewa ka da z
kolumn reprezentuje pewn własno ć modelowanej rzeczywisto ci.
W przypadku projektowania tabeli w bazie danych nale y stosować si do nast puj cych wskazówek:
- U ywaj nazw opisowych do nazwania kolumn tabeli. Kolumny nie powinny mieć znaczenia ukrytego, ani
reprezentować kilku atrybutów (zło onych w pojedyncz warto ć).
- B d konsekwentny w stosowaniu liczby pojedynczej lub mnogiej przy nazywaniu tabeli.
- Twórz tylko te kolumny, które s niezb dne do opisania modelowanej encji lub powi zania - tabele z mniejsz
ilo ci kolumn s Å‚atwiejsze w u yciu.
- Utwórz kolumn pól kluczowych dla ka dej tabeli.
- Unikaj powtarzania informacji w bazie danych (normalizacja).
7
3. ROZKAZY J ZYKA SQL
Poni sza tabela zawiera spis podstawowych rozkazów j zyka SQL wraz z krótkim opisem. Operacje relacyjne b d
wyja nione dokładniej w dalszej cz ci wykładu.
Rozkaz Typ Opis
Dodaje kolumn do tabeli, redefiniuje kolumn w istniej cej tabeli lub
ALTER TABLE DDL
redefiniuje ilo ć miejsca zarezerwowan dla danych
CREATE INDEX DDL Tworzy indeks dla tabeli
Tworzy obiekt słu cy do generowania kolejnych liczb - sekwencj .
CREATE SEQUENCE DDL Sekwencji mo na u yć do generowania unikalnych identyfikatorów w
tabelach
Tworzy tabel i definiuje jej kolumny oraz alokacj przestrzeni dla
CREATE TABLE DDL
danych
Definiuje widok dla jednej lub wi kszej ilo ci tabel lub innych
CREATE VIEW DDL
widoków
DELETE DML Usuwa wszystkie lub wyró nione wiersze z tabeli
DROP obiekt DDL Usuwa indeks, sekwencje, tablic , widok lub inny obiekt
INSERT DML Dodaje nowy wiersz (lub wiersze) do tabeli lub widoku
RENAME DDL Zmienia nazw tabeli, widoku lub innego obiektu
Wykonuje zapytanie. Wybiera wiersze i kolumny z jednej lub kilku
SELECT DML
tabel
UPDATE DML Zmienia dane w tabeli
COMMIT DML Ko czy transakcj i na stałe zapisuje zmiany
ROLLBACK DML Wycofuje zmiany od pocz tku transakcji lub zaznaczonego punktu.
Zaznacza punkt, do którego mo liwe jest wykonanie rozkazu
SAVEPOINT DML
ROLLBACK
SET TRANSACTION DDL Zaznacza aktualn transakcj jako read-only (tylko do odczytu).
8
4. SKA
ADNIA ROZKAZÓW J ZYKA SQL
4.1. Definicje podstawowe
- Identyfikator (nazwa) - ci g liter, cyfr i znaków podkre lenia rozpoczynaj cy si liter lub znakiem
podkre lenia. Ró ne systemy baz danych umo liwiaj stosowanie innych znaków wewn trz identyfikatorów
(np. znak '$', lub '!'). Stosowanie tych znaków nie jest jednak zalecane ze wzgl du na pó niejsze problemy
zwi zane z przeno no ci napisanych w ten sposób aplikacji.
- SÅ‚owa zarezerwowane - identyfikatory zastrze one posiadaj ce specjalne znaczenie w j zyku SQL. Spis
wszystkich słów zarezerwowanych w j zyku SQL przez twórców ORACLE'a przedstawia tabela:
access add all alter and any
as asc audit between by char
check cluster column comment compress connect
create current date dba decimal default
delete desc distinct drop else exclusive
exists file float for from grant
graphic group having identified if immediate
in increment index install initial insert
integer intersect into is level like
lock long maxextents minus mode modify
noaudit nocompress not nowait null number
of offline on online option or
order pctfree prior privileges public raw
rename resource revoke row rowid rownum
rows select session set share size
smallint start successful synonym sysdate table
then to trigger uid union unique
update user validate values varchar vargraphic
view whenever where with
- Liczby - mog być całkowite lub rzeczywiste. Liczba calkowita nie posiada kropki dziesi tnej. W systemie
ORACLE liczby mo na zapisywać w formacie zwykłym lub wykładniczym. Format wykładniczy składa si z
liczby oraz wykładnika liczby 10, przez który nale y pomno yć t liczb oddzielonego liter 'e' lub 'E'.
Przykłady:
7E2 = 7 * 10
25e-03 = 25 * 10
Dodatkowo w systemie ORACLE liczb całkowit mo na zako czyć liter 'K' lub liter 'M'. Litera 'K' oznacza, e
cała liczba ma być pomno ona przez 1024 (1 KB), natomiast litera 'M', e liczb nale y pomno yć przez 1048576 (1
MB). Przykłady:
256K = 256 * 1024
1M = 1 * 1048576
- Rozkazy j zyka SQL ko cz si rednikiem
4.2. Rozkaz CREATE TABLE
Rozkaz CREATE TABLE słu y do tworzenia struktury tabeli (bez danych) i posiada dodatkowe opcje
umo liwiaj ce:
9
- okre lenie sposobu alokacji przestrzeni do przechowywania danych
- okre lenie rozmiaru tabeli
- przydzielenie tabeli do okre lonego klastra
- załadowanie danych b d cych wynikiem podanego zapytania, do tabeli
Rozkaz CREATE TABLE posiada nast puj c składni :
CREATE TABLE [user.]table
( {column_element | table_constraint}
[, {column_element | table_constraint} ] ... )
[ PCTFREE n ] [ PCTUSED n ]
[ INITTRANS n ] [ MAXTRANS n ]
[ TABLESPACE tablespace ]
[ STORAGE storage ]
[ CLUSTER cluster (column [, column] ...) ]
[ AS query ]
Parametry:
- user - wła ciciel tabeli, je li nie zostanie podany, to wła cicielem staje si osoba tworz ca tabel . Tabele
dla innych u ytkowników mo e tworzyć tylko administrator systemu zarz dzania baz danych (DBA)
- table - nazwa tabeli, powinna być prawidłowym identyfikatorem. Wszystkie obiekty danego u ytkownika
powinny mieć unikalne nazwy
- column_element - definiuje kolumn i opcjonalne ograniczenia na warto ci w tej kolumnie. Tabela musi
zawierać co najmniej jedn kolumn (jak to wynika ze skladni)
- table_constraints - okre la ograniczenia jakie musi spełniać cała tabela
- tablespace - okre la obszar, w którym nale y umie cić tabel
- storage - okre la przyszły sposób alokacji pami ci
- cluster - okre la klaster (którego wła cicielem musi być wła ciciel tabeli), do którego nale y przydzielić
tabel
- query - jest poprawnym zapytaniem takim samym jak zdefiniowane w rozkazie SELECT. Je li podane jest
zapytanie, to mo na podać tylko nazwy kolumn - typy i rozmiary s kopiowane z odpowiednich kolumn
okre lonych w zapytaniu. Mo liwe jest równie pomini cie nazw kolumn, ale tylko wtedy, gdy nazwy te s
unikalne i dobrze zdefiniowane w zapytaniu. Liczba wyspecyfikowanych kolumn musi być taka sama jak
liczba kolumn w zapytaniu.
Przykłady:
&5($7( 7$%/( SUDFRZQLF\
QUBSUDFRZQLND 180%(5 127 18// 35,0$5< .(<
LPLH &+$5 127 18// &+(&. LPLH 833(5 LPLH
QD]ZLVNR &+$5 127 18//
&+(&. QD]ZLVNR 833(5 QD]ZLVNR
QUBZ\G]LDOX 180%(5 127 18//
4.3. Rozkaz DROP
Rozkaz drop słu y do kasowania obiektów ró nego rodzaju. Ogólna postać tego rozkazu jest nast puj ca:
'523 REMHFWBW\SH >XVHU @REMHFW
Poni ej przedstawione s ró ne postacie rozkazu drop słu ce do kasowania poszczególnych typów obiektów:
- '523 &/867(5 >XVHU @FOXVWHU >,1&/8',1* 7$%/(6@ - kasowanie klastra. W
przypadku podania klauzuli INCLUDING TABLES zostan skasowane wszystkie tabele przydzielone
uprzednio do kasowanego klastra. Je li klauzula INCLUDING TABLES nie zostanie podana, to przed
10
skasowaniem klastra musz być skasowane wszystkie nale ce do niego tabele. Jest to zabezpieczenie
przed omyłkowym skasowaniem klastra zawieraj cego tabele, które s potrzebne.
- '523 >38%/,&@ '$7$%$6( /,1. OLQN - usuwanie poł czenia. Je li poł czenie jest
publiczne to skasować je mo e tylko administrator (DBA).
- '523 ,1'(; >XVHU @LQGH[ - kasowanie indeksu.
- '523 >38%/,&@ 52//%$&. 6(*0(17 VHJPHQW - kasowanie segmentu wycofywania
(rollback). Mo na usun ć tylko te segmenty wycofywania, które nie s u ywane w danym momencie.
Kasowanie segmentów wycofywania mo e wykonywać tylko administrator bazy danych.
- '523 6(48(1&( >XVHU @VHTXHQFH - kasowanie sekwencji.
- '523 >38%/,&@ 6<121<0 >XVHU @V\QRQ\P - usuwanie synonimu. Synonim publiczny
mo e zostać usuni ty tylko przez administratora (DBA). Poszczególni u ytkownicy mog usuwać tylko te
segmenty, których s wła cicielami.
- '523 7$%/( >XVHU @WDEOH - usuwanie tabeli. W momencie usuni cia tabeli automatycznie
kasowane s skojarzone z ni indeksy zarówno utworzone przez wła ciciela tabeli jak i przez innych
u ytkowników. Widoki i synonimy wskazuj ce na tabel nie s kasowane automatycznie, ale staj si
nieprawidłowe.
- '523 7$%/(63$&( WDEOHVSDFH >,1&/8',1* &217(176@ - usuwanie obszaru
danych. Rozkaz ten mo e być wykonany tylko przez administratora (DBA). W przypadku podania klauzuli
INCLUDING CONTENTS obszar danych zostanie skasowany nawet wtedy, gdy zawiera dane. Je li
klauzula INCLUDING CONTENTS nie została podana, a obszar zawiera dane, to nie zostanie skasowany.
- '523 9,(: >XVHU @YLHZ - usuwanie widoku. Po usuni ciu widoku, inne widoki lub synonimy,
które odwoływały si do widoku skasowanego, nie zostan skasowane, ale staj si nieprawidłowe.
4.4. Rozkaz INSERT
Rozkaz insert dodaje nowe wiersze do tabeli lub do tabel przynale cych do widoku. Aby dodać wiersze do tabeli
nale y być wła cicielem tabeli, administratorem (DBA) lub posiadać uprawnienia dopisywania do tej tabeli.
Składnia rozkazu:
,16(57 ,172 >XVHU @WDEOH > FROXPQ > FROXPQ@ @
^ 9$/8(6 YDOXH > YDOXH@ _ TXHU\ `
Parametry:
- user - nazwa wła ciciela tabeli
- table - nazwa tabeli, do której dopisywane s wiersze
- column - nazwa kolumny wewn trz tabeli lub widoku
- value - pojedyncza warto ć odpowiadaj ca odpowiedniej pozycji na li cie kolumn. Warto ć mo e być
dowolnym wyra eniem. Je li wprowadzana warto ć nie jest równa NULL to musi być zgodna z typem
warto ci kolumny, do której zostanie dopisana.
- query - prawidłowy rozkaz SELECT, który zwraca tak ilo ć warto ci jak podana w li cie okre laj cej
kolumny. Zapytanie nie mo e mieć klauzuli ORDER FOR ani FOR UPDATE.
Opis:
Rozkaz INSERT u yty z klauzul VALUES zawsze dodaje dokładnie jeden wiersz. Do pól wyspecyfikowanych w
li cie kolumn (lub do wszystkich kolumn) wstawiane s podane warto ci. Kolumny nie wyspecyfikowane na li cie
kolumn przyjmuj warto ci puste NULL (w zwi zlu z tym nie mog być uprzednio zadeklarowane jako NOT NULL).
Je li u yje si rozkazu SELECT zamiast klauzuli VALUES, to mo liwe jest dodanie wi kszej ilo ci wierszy
(wszystkich zwróconych przez zapytanie). Po wykonaniu zapytania kolumny b d ce jego rezultatem s dopasowywane
i wpisywane do kolumn podanych na li cie kolumn (lub do wszystkich kolumn, je li ich nie wyspecyfikowano).
Zapytanie mo e odwoływać si równie do tabeli, do której dopisywane s wiersze.
W przypadku, gdy lista kolumn nie jest podana, to warto ci s dopasowywane do poszczególnych kolumn na
podstawie ich wewn trznego porz dku. Porz dek ten nie musi być taki sam jak kolejno ć kolumn przy tworzeniu tabeli.
aden wiersz nie zostanie dopisany, je li zapytanie nie zwróci adnych wierszy.
Przykłady:
11
,16(57 ,172 SUDFRZQLF\ 9$/8(6
-$1 .2:$/6.,
,16(57 ,172 NVLD]NL W\WXO DXWRU PLHMVFH
6(/(&7 3DQ 7DGHXV] DXWRUBQU PLHMVFHBQU
)520 DXWRU]\ PLHMVFD
:+(5( QD]ZLVNR 0LFNLHZLF] $1'
PLHMVFH OHZD SROND
4.5. Rozkaz DELETE
Rozkaz DELETE słu y do usuwania wierszy z tabeli.
Składnia:
'(/(7( >)520@ >XVHU @WDEOH >DOLDV@ >:+(5( FRQGLWLRQ@
Parametry:
- user - nazwa u ytkownika
- table - nazwa tabeli lub widoku, z którego nale y usun ć wiersze
- alias - nazwa aliasu odnosz cego si do tabeli, który jest u ywany w rozkazie DELETE z powi zanymi
zapytaniami
- condition - warunek jaki musz spełniać wiersze, które nale y usun ć. Warunek ten mo e odwoływać si
do tabeli, na której przeprowadza si operacj i zawierać powi zane z nim zapytania. Konieczne jest
jednak, by warunek, dla ka dego z wiersza podanej tabeli, był obliczany do warto ci TRUE lub FALSE.
Opis:
Cała przestrze zwolniona przez skasowane wiersze i elementy indeksów jest zatrzymywana przez t tabel i
indeks.
Przykłady:
Skasowanie wszystkich wierszy w tabeli pracownicy:
'(/(7( )520 SUDFRZQLF\
Skasowanie wszystkich wierszy zawieraj cych ksi ki, których autor oznaczony jest numerem 2:
'(/(7( )520 NVLD]NL :+(5( DXWRU
4.6. Rozkaz CREATE SEQUENCE
Tworzy obiekt (nazywany sekwencj ), za pomoc którego wielu u ytkowników mo e generować unikalne liczby
całkowite. Sekwencj mog być u yte do generacji kluczy pierwotnych w sposób automatyczny. Do utworzenia
sekwencji konieczne s przynajmniej uprawnienia RESOURCE w conajmniej jednej przestrzeni tabel.
Składnia:
&5($7( 6(48(1&( >XVHU @VHTXHQFH
>,1&5(0(17 %< Q@
>67$57 :,7+ Q@
>0$;9$/8( Q _ 120$;9$/8(@
>0,19$/8( Q _ 120,19$/8(@
>&<&/( _ 12&<&/(@
>&$&+( Q _ 12&$&+(@
>25'(5 _ 1225'(5@
Parametry:
x user - nazwa u ytkownika
x sequence - nazwa tworzonej sekwencji, musi być poprawnym identyfikatorem i być unikalna w obr bie
danego u ytkownika.
x INCREMENT BY - okre la ró nic mi dzy kolejno generowanymi liczbami. Je li liczba ta jest jest
ujemna, to b d generowane liczby w porz dku malej cym, w przeciwnym wypadku - w porz dku
rosn cym. Domy lnie przyjmowana jest warto ć 1. Dozwolona jest ka da liczba ró na od 0.
12
x START WITH - pierwsza liczba, która powinna być wygenerowana przez sekwencj . Domy ln warto ci
jest MINVALUE dla sekwencji rosn cych i MAXVALUE dla sekwencji malej cych. Utworzona
sekwencja nie jest zainicjalizowana i pierwsz warto ć otrzymuje si po jednokrotnym odczytaniu
pseudokolumny NEXTVAL.
x MINVALUE - okre la minimaln warto ć jak mo e wygenerować sekwencja. Domy lnie dla sekwencji
rosn cych jest to 1, natomiast dla malej cych warto ć ta wynosi -10e27 + 1. Podanie NOMINVALUE
powoduje, e sekwencja nie b dzie sprawdzać warto ci minimalnej.
x MAXVALUE - okre lenie maksymalne warto ci, jak mo e wygenerować sekwencja. Warto ciami
domy lnymi s -1 i 10e27 - 1 odpowiednio dla sekwencji malej cej i rosn cej. Wyspecyfikowanie
NOMAXVALUE powoduje, e sekwencja nie b dzie sprawdzać warto ci maksymalnej.
x CYCLE, NOCYCLE - domy ln warto ci jest NOCYCLE, które powoduje, e adne dodatkowe numery
nie zostan wygenerowane po osi gni ciu ko ca sekwencji. W tym wypadku ka da próba generacji
kolejnego numeru spowoduje zgłoszenie bł du. W przypadku podania klauzuli CYCLE po osi gni ciu
warto ci maksymalnej sekwencja powróci do warto ci minimalnej (dla sekwencji rosn cych) lub po
osi gni ciu warto ci minimalnej powróci do maksymalnej (dla sekwencji malej cych) rozpoczynaj c
kolejny cykl generacji numerów.
x CACHE, NOCACHE - klauzula CACHE wł cza wykonywanie pre-alokacji numerów sekwencji i
przechowywanie ich w pami ci, co skutkuje zwi kszeniem szybko ci generacji kolejnych liczb. Klazula
NOCACHE wył cza t mo liwo ć. Domy lnie przyjmowane jest CACHE 20. Warto ć podana w CACHE
musi być mniejsza ni MAXVALUE - MINVALUE.
x ORDER, NOORDER - klauzula ORDER gwarantuje, e kolejne liczby b d generowane w porz dku
jakim otrzymane zostały przez system polecenia ich generacji. Klauzula NOORDER wył cz t własno ć.
Kolejno ć generacji numerów w sekwencji jest wa na w aplikacjach, w których wa na jest kolejno ć
(czasowa) wykonywanych operacji. Zwykle nie jest ona wa na w aplikacjach, które wykorzystuj
sekwencje tylko do generacji kluczy pierwotnych.
Opis:
Sekwencje mog być u ywane do generacji kluczy pierwotnych dla jednej tabeli lub wielu tabel i wielu
u ytkowników. Aby mieć dost p do sekwencji, której wła cicielem jest inny u ytkownik, nale y mieć uprawnienia
SELECT do tej sekwencji. Sekwencja mo e posiadać synonim.
Numery w sekwencjach s generowane niezale nie od tabel, dlatego mog być u ywane jako liczby unikalne dla
kilku ró nych tabel i u ytkowników. Jest jednak mo liwe, e niektóre numery z sekwencji zostan pomini te, poniewa
zostały one wygenerowane i u yte w transakcji, która nast pnie została wycofana. Dodatkowo jeden u ytkownik mo e
nie zdawać sobie sprawy, e inni u ytkownicy korzystaj z tej samej sekwencji (co równie skutkuje pomini ciem
numerów dla tego u ytkownika).
Dost p do sekwencji zapewniaj dwie pseudokolumny: NEXTVAL i CURRVAL. Pseudokolumna NEXTVAL jest
u ywana do generacji nast pnej warto ci z podanej sekwencji. Składnia jest nast puj ca:
VHTXHQFH 1(;79$/
gdzie sequence jest nazw sekwencji.
Pseudokolumna CURRVAL pozwala na odczytanie aktualnej warto ci sekwencji. Aby mo liwe było u ycie
CURRVAL konieczne jest wcze niejsze u ycie NEXTVAL w aktualnej sesji dla danej sekwencji. Składnia tego
rozkazu jest nast puj ca:
VHTXHQFH &8559$/
gdzie sequence jest nazw sekwencji.
Pseudokolumny NEXTVAL i CURRVAL mog być u ywane w:
x w klauzuli SELECT i rozkazie SELECT (z wyj tkiem widoków)
x li cie warto ci rozkazu INSERT
x wyra eniu SET w rozkazie UPDATE
Pseudokolumn NEXTVAL i CURRVAL nie mo na u ywać w:
podzapytaniach
wli cie select dla widoków
x ze słowem kluczowym DISTINCT
x z klauzulami ORDER BY, GROUP BY i HAVING w rozkazie SELECT
13
x z operatorem ustawienia (UNION, INTERSECT, MINUS)
Przykłady:
&5($7( 6(48(1&( HVHT ,1&5(0(17 %<
,16(57 ,172 SUDFRZQLF\
9$/8(6 HVHT 1(;79$/ -DQ .RZDOVNL
4.7. Rozkaz SELECT
Rozkaz SELECT słu y do wy wietlania wierszy i kolumn z jednej lub kilku tabel. Mo e być u ywany jako osobny
rozkaz lub (z pewnymi ograniczeniami) jako zapytanie lub podzapytanie w innych poleceniach. Aby odczytać dane z
okre lonej tabeli trzeba być jej wła cicielem, mieć uprawnienia SELECT dla tej tabeli lub być administratorem bazy
(DBA).
Składnia:
6(/(&7 >$// _ ',67,1&7@
^ _ WDEOH _ H[SU >FBDOLDV@ `
> ^ WDEOH _ H[SU >FBDOLDV@ ` @
)520 >XVHU @WDEOH >WBDOLDV@
> >XVHU @WDEOH >WBDOLDV@@
> :+(5( FRQGLWLRQ @
> &211(&7 %< FRQGLWLRQ >67$57 :,7+ FRQGLWLRQ@ @
> *5283 %< H[SU > ([SU@ >+$9,1* FRQGLWLRQ@ @
> ^81,21 _ ,17(56(&7 _ 0,186` 6(/(&7 @
> 25'(5 %< ^H[SU _ SRVLWLRQ` >$6& _ '(6&@
> ^H[SU _ SRVLWLRQ` >$6& _ '(6&@@ @
> )25 83'$7( 2) FROXPQ > FROXPQ@ >12:$,7@ @
Parametry:
ALL - ustawiane domy lnie, oznacza, e wszystkie wiersze, które spełniaj warunki rozkazu SELECT powinny
zostać pokazane.
DISTINCT - okre la, e wiersze powtarzaj ce si powinny zostać usuni te przed zwróceniem ich na zewn trz. Dwa
wiersze traktuje si jako równe je li wszystkie warto ci dla ka dej z kolumn zwracanych rozkazem SELECT s sobie
równe.
* - oznacza, e wszystkie kolumny ze wszystkich wymienionych tabel powinny zostać pokazane.
table.* - oznacza, e wszystkie kolumny z podanej tabeli powinny zostać pokazane
expr - wyra enie, zostanie opisane w dalszej cz ci wykładu
c_alias - jest inn nazw dla kolumny (aliasem) i powoduje, e nazwa ta zostanie u yta jako nagłówek kolumny
podczas wy wietlania. W aden sposób nie jest zmieniana rzeczywista nazwa kolumny. Aliasy kolumn nie mog być
u ywane w dowolnym miejscu zapytania.
[user.]table - okre la które tabele i widoki nale y pokazać. Je li u ytkownik nie jest podany to domy lnie
przyjmowany jest u ytkownik aktualny (wykonuj cy rozkaz SELECT).
t_alias - pozwala okre lić inn nazw dla tabeli w celu obliczenia zapytania. Najcz ciej jest u ywane w
zapytaniach powi zanych. W tym wypadku inne odwołania do tabeli wewn trz zapytania musz posługiwać si
wyspecyfikowanym aliasem.
14
condition - warunek, jaki musz spełniać wiersze, aby zostały zwrócone przez zapytanie. Warunki zostan opisane
dikładniej w dalszej cz ci wykładu.
position - identyfikuje kolumn bazuj c na jej tymczasowym poło eniu w rozkazie SELECT, a nie na nazwie.
ASC, DESC - okre la, e zwracane wiersze powinny być posortowane w kolejno ci rosn cej lub malej cej
(odpowiednio).
column - nazwa kolumny nale ca do jednej z tabel podanych w klauzuli FROM.
NOWAIT - okre la, e ORACLE powinien zwrócić sterowanie do u ytkownika, zamiast czekać na mo liwo ć
zablokowania wiersza, który został uprzednio zablokowany przez innego u ytkownika.
Opis:
U ycie nazwy tabeli przed nazw kolumny i nazwy u ytkownika przed nazw tabeli jest najcz ciej opcjonalne, to
jednak dobrym zwyczajem jest podawanie nazw w pełni kwalifikowanych z dwóch powodów:
x je li dwie tabele maj kolumny o tej samej nazwie, to nie wiadomo, która powinna być u yta w rozkazie
SELECT
x ORACLE wykonuje znacznie mniej oblicze , je li nazwy te s podane i nie trzeba ich szukać.
x Pozostałe operacje wykonywane przez rozkaz SELECT zostan opisane w dalszej cz ci wykładu.
Przykłady:
6(/(&7 LPLH QD]ZLVNR )520 SUDFRZQLF\
6(/(&7 W\WXO DXWRU]\ LPLH DXWRU]\ QD]ZLVNR
PLHMVFD PLHMVFH
)520 NVLD]NL DXWRU]\ PLHMVFD
:+(5( NVLD]NL DXWRU DXWRU]\ DXWRUBQU $1'
NVLD]NL PLHMVFH PLHMVFD PLHMVFHBQU
4.8. Rozkaz UPDATE
Rozkaz UPDATE słu y do zmiany danych zapisanych w tabeli. Warunkiem wykonania tego polecenia jest bycie
wła cicielem tabeli, administratorem (DBA) lub posiadanie uprawnie UPDATE dla tej tabeli.
Składnia:
83'$7( >XVHU @WDEOH >DOLDV@
6(7 FROXPQ H[SU > FROXPQ H[SU@
> :+(5( FRQGLWLRQ @
lub
83'$7( >XVHU @WDEOH >DOLDV@
6(7 FROXPQ > FROXPQ@ TXHU\
> FROXPQ > FROXPQ@ TXHU\ @
> :+(5( FRQGLWLRQ @
Parametry:
x user - nazwa wła ciciela tabeli.
x table - nazwa istniej cej tabeli.
x alias - dodatkowa nazwa u ywana do dost pu do tabeli w pozostałych klauzulach rozkazu.
x column - kolumna wewn trz tabeli. Nawiasy nie s potrzebne je li lista kolumn zawiera tylko jedn
kolumn .
x expr - wyra enie - zostanie opisane w dalszej cz ci wykładu
x query - rozkaz SELECT bez klauzul ORDER BY i FOR UPDATE, cz sto skorelowany ze zmienian
tabel .
x condition - poprawny warunek. Warunek musi zwracać warto ć TRUE lub FALSE. Warunki b d opisane
w dalszeuj cz ci wykładu
x Opis:
15
Klauzula SET okre la, które kolumny zostan zmienione i jakie nowe warto ci maj być w nich zapisane. Klauzula
WHERE okre la warunki jakie musz spełniać wiersze, w których nale y wymienić warto ci podanych wcze niej
kolumn. Je li klauzula WHERE nie jest podana, to zmieniane s wszystkie wiersze w tabeli.
Rozkaz UPDATE dla ka dego wiersza, który spełnia warunki klauzuli WHERE oblicza warto ci wyra e
znajduj cych si po prawej stronie operatora '=' i przypisuje te warto ci do pola okre lanego przez nazw kolumny z
lewej strony.
Je li klauzula SET posiada podzapytanie, to musi ono zwrócić dokładnie jeden wiersz dla ka dego ze zmienianych
wierszy. Ka da warto ć jest przypisywana zgodnie z kolejno ci na li cie kolumn. Je li zapytanie (w przypadku
klauzuli postaci SET value = query) nie zwróci wierszy to odpowiednie pola s ustawiane na NULL.
Zapytanie mo e odwoływać si do zmienianej tabeli. Jest ono obliczane oddzielnie dla ka dego zmienianego
wiersza a nie dla całego rozkazu UPDATE.
Przykłady:
83'$7( SUDFRZQLF\
6(7 QUBZ\G]LDáX
:+(5( QUBZ\G]LDáX
4.9. Rozkaz RENAME
Rozkaz RENAME zmienia nazw tabeli, widoku lub synonimu. Zmiany mo e dokonać wła ciciel tabeli, widoku
lub synonimu.
Składnia:
5(1$0( ROG 72 QHZ
Parametry:
ROG DNWXDOQD QD]ZD WDEHOL ZLGRNX OXE V\QRQLPX
QHZ GDQD QD]ZD WDEHOL ZLGRNX OXE V\QRQLPX
Opis:
Wszystkie pozwolenia, które posiadał obiekt o starej nazwie, przechodz na obiekt o nowej nazwie. Za pomoc tego
rozkazu nie mo na zmieniać nazw kolumn. Zmiana nazwy kolumny mo e być dokonana za pomoc trzech rozkazów:
CREATE TABLE, DROP TABLE i RENAME w nast puj cy sposób:
&5($7( 7$%/( WHPSRUDU\ QHZBFROXPQBQDPH
$6 6(/(&7 ROGBFROXPQBQDPH )520 WDEOH
'523 7$%/( WDEOH
5(1$0( WHPSRUDU\ 72 WDEOH
Przykłady:
5(1$0( Z\G]LDO\ 72 MHGQRVWNL
4.10. Rozkaz ALTER TABLE
Rozkaz słu cy do zmieniania tabeli. Wykonuje nast puj ce operacje:
x dodaje kolumny i warunki
x modyfikuje definicje kolumn jak typy i warunki
x usuwa warunki
x modyfikuje przyszły sposób alokacji przestrzeni
x zapisuje, e operacja BACKUP została wykonana dla tej tabeli.
Aby wykonać t operacj trzeba być wła cicielem tabeli, mieć uprawnienia ALTER dla tabeli lub być
administratorem (DBA).
Składnia:
$/7(5 7$%/( >XVHU @WDEOH
>$'' ^FROXPQBHOHPHQW _ WDEOHBFRQVWUDLQW`
> ^FROXPQBHOHPHQW _ WDEOHBFRQVWUDLQW`@ @
>02',)< FROXPQBHOHPHQW > FROXPQBHOHPHQW@ @
16
>'523 &21675$,17 FRQVWUDLQW@
>3&7)5(( LQWHJHU@ >3&786(' LQWHJHU@
>,1,775$16 LQWHJHU@ >0$;75$16 LQWHJHU@
>6725$*( VWRUDJH@
>%$&.83@
Parametry:
x [user.]table - wła ciciel i tabela, któr trzeba zmienić. Je li nazwa u ytkownika nie jest podana, to
domy lnie przyjmowany jest u ytkownik, który wywołał rozkaz.
x ADD/MODIFY column_element - dodaje lub modyfikuje definicj kolumny, ograniczenia kolumny lub
warto ci domy lne okre lonej kolumny.
x ADD table_constraint - dodaje ograniczenia na warto ci w tabeli.
x DROP constraint - usuwa podan kolumn lub ograniczenie.
x BACKUP - zmienia zawarto ć słownika danych (Data Dictionary) tak, e zostaje zapisana informacja o
wykonaniu backup'u tabeli, który nast pił w czasie wykonywania rozkazu ALTER TABLE.
Opis:
Je li u yta zostanie klauzula ADD w celu dodania nowej kolumny do istniej cej tabeli, to warto ć ka dego pola w
tej kolumnie b dzie równa NULL. W zwi zku z tym mo liwe jest dodanie kolumny z warunkiem NOT NULL tylko do
kolumn, które nie maj wierszy.
Klauzula MODIFY mo e zostać u yta do zmiany nast puj cych atrybutów kolmny:
x rozmiar
x typ danych
x NOT NULL
Zmiana typu lub zmniejszenie rozmiaru mo liwe jest tylko wtedy, gdy wszystkie warto ci w kolumnie s równe
NULL. Mo liwe jest nało enie ograniczenia NOT NULL na istniej c kolumn tylko wtedy, gdy nie zawiera ona
warto ci pustych. Je li zmieniany jest rozmiar kolumny zadeklarowanej jako NOT NULL i w klauzuli MODIFY nie
poda si NOT NULL, to kolumna nadal pozostaje z warunkiem NOT NULL.
W przypadku widoków z zapytaniem wybieraj cym wszystkie kolumny tabeli (SELECT * FROM ...), widok mo e
nie pracować poprawnie, je li do tabeli, z któr jest powi zany została dodana nowa kolumna.
3U]\NáDG\
$/7(5 7$%/( SUDFRZQLF\
$'' SODFD 180%(5
$/7(5 7$%/( SUDFRZQLF\
02',)< SODFD 180%(5
4.11. Rozkaz CREATE INDEX
Rozkaz tworzy nowy indeks dla tabeli lub klastra. Indeks zapewnia bezpo redni dost p do wierszy w tabeli w celu
zredukowania czasu wykonywania operacji. Indeks zawiera informacj o ka dej warto ci, która jest zapisana w
indeksowanej kolumnie. Indeks mo e utworzyć wła ciciel tabeli, u ytkownik posiadaj cy uprawnienia INDEX dla
danej tabeli lub administrator (DBA).
6NáDGQLD
&5($7( >81,48(@ ,1'(; LQGH[ 21
^WDEOH FROXPQ >$6&_'(6&@> FROXPQ >$6&_'(6&@@ _
&/867(5 FOXVWHU`
>,1,775$16 Q@ >0$;75$16 Q@
>7$%/(63$&( WDEOHVSDFH@
>6725$*( VWRUDJH@
>3&7)5(( Q@
>126257@
3DUDPHWU\
x UNIQUE - zakłada, e tabela nie ma nigdzie dwóch wierszy zawieraj cych te same warto ci we wszystkich
indeksowanych kolumnach. W aktualnej wersji ORACLE'a je li indeks typu UNIQUE nie zostanie
utworzony dla tabeli, to tabela mo e zawierać powtarzaj ce si wiersze.
17
x indeks - nazwa tworzonego indeksu. Nazwa ta musi być inna od ka dego innego obiektu bazy danych
danego u ytkownika.
x table - nazwa istniej cej tabeli, dla której tworzy si indeks.
x column - nazwa kolumny w tabeli.
x ASC, DESC - zostały dodane w systemie ORACLE w celu zapewnienia kompatybilno ci z systemem DB2,
ale zawsze s tworzone w porz dku rosn cym.
x CLUSTER cluster - okre la klaster, dla którego tworzony jest indeks
x NOSORT - wskazuje ORACLE'owi, e wiersze przechowywane w bazie s ju posortowane, w zwi zku z
czym nie jest konieczne sortowanie podczas tworzenia indeksu.
Opis:
Indeksy s tworzone w celu przyspieszenia operacji:
x dost pu do danych w posortowanych według kolumn indeksowanych
x wyszukiwania wierszy, zawieraj cych dane z indeksowanych kolumn.
Nale y jednak zwrócić uwag , e indeks spowalnia wstawianie, usuwanie i zmiany warto ci w indeksowanych
kolumnach, poniewa jego zawarto ć musi ulec zmianie w momencie zmiany zawarto ci tabeli.
Do jednego indeksu wstawionych mo e być co najwy ej 16 kolumn. Jeden element indeksu jest konkatenacj
warto ci tych kolumn w poszczególnych wierszach. W momencie wyszukiwania mo e być u yty cały element indeksu
lub pewna jego cz ć pocz tkowa. Dlatego kolejno ć kolumn w indeksie jest wa na. Je li wi c indeks zostanie
utworzony na podstawie trzech kolumn A, B, C w takiej kolejno ci, to zostanie on u yty do wyszukiwania konkatenacji
kolumn A, B, C, kolumn A i B lub tylko kolumny A. Nie b dzie natomiast u ywany w przypadku wyszukiwania
poł czenia kolumn B i C lub pojedynczej kolumny B lub C.
Mo liwe jest utworzenie dowolnej ilo ci indeksów dla jednej lub kilku tabel. Nale y jednak pami tać, e oprócz
spowolnienia operacji modyfikacji tabeli, indeksy zajmuj równie do ć du ilo ć miejsca na dysku.
Przykłady:
&5($7( ,1'(; LBSUDFBLPLH 21 SUDFRZQLF\ LPLH
4.12. Rozkaz CREATE VIEW
Rozkaz słu cy do tworzenia widoku, czyli logicznej tabeli bazuj cej na jednej lub wielu tabelach. Utworzyć widok
mo e wła ciciel tabel, u ytkownik posiadaj cy do nich co najmniej uprawnienia SELECT lub administrator.
Składnia:
&5($7( 9,(: >XVHU @YLHZ > DOLDV > DOLDV@ @
$6 TXHU\
> :,7+ &+(&. 237,21 >&21675$,17 FRQVWUDLQW@ @
Parametry:
x user - wła ciciel tworzonego widoku
x view - nazwa tworzonego widoku
x query - identyfikuje kolumny i wiersze tabel, na których bazuje widok. Zapytanie mo e być dowolnym
poprawnym rozkazem SELECT nie zawieraj cym kluzul ORDER BY ani FOR UPDATE.
WITH CHECK OPTION - informuje, e wstawienia i zmiany wykonywane poprzez widok, s niedozwolone je li
spowoduj wygenerowanie wierszy, które b d niedost pne dla widoku. Klauzula WITH CHECK OPTION mo e być
u yta w widoku bazuj cym na innym widoku.
CONSTRAINT - nazwa doł czona do warunku WITH CHECK OPTION.
Opis:
Widok jest logicznym oknem dla jednej lub kilku tabel. Widok ma nast puj ce wła ciwo ci:
x widok nie przechowuje danych - jest on przeznaczony do pokazywania danych zawartych w innych
tabelach.
18
x widok mo e być u yty w rozkazie SQL w dowolnym miejscu, w którym mo liwe jest u ycie tabeli z
zastrze eniem, e mo na wykonywać selekcj z widoku tylko wtedy, gdy zapytanie na którym bazuje
widok zawiera:
poł czenie
klauzule GROUP BY, CONNECT BY lub START WITH
klauzul DISTINCT, pseudokolumny lub wyra enia na li cie kolumn
Mo liwa jest zmiana danych zawartych w widoku, który posiada pseudokolumny lub wyra enia dot d dopóki
rozkaz UPDATE nie odwołuje si do pseudokolumny lub wyra enia.
Widoki s u ywane do:
x utworzenia dodatkowego poziomu zabezpieczenia tabeli poprzez ograniczenie dost pu do okre lonych
kolumn lub wierszy tabeli bazowej
x ukrycia zło ono ci danych - na przykład widok mo e być u yty do operacji na wielu tabelach tak, by
wydawało si , e operacje wykonywane s na jednej tabeli.
x pokazywania danych z innej perspektywy - dla przykładu widok mo e zostać u yty do zmiany nazwy
kolumny bez zmiany rzeczywistych danych zapisanych w tabeli.
x zapewnienia poziomu integralno ci.
Przykłady:
&5($7( 9,(: ELEO
$6 6(/(&7 NVLD]NL W\WXO DXWRU]\ LPLH
DXWRU]\ QD]ZLVNR PLHMVFD PLHMVFH
)520 NVLD]NL DXWRU]\ PLHMVFD
:+(5( NVLD]NL DXWRU DXWRU]\ DXWRUBQU
$1' NVLD]NL PLHMVFH PLHMVFD PLHMVFHBQU
:,7+ &+(&. 237,21 &21675$,17 FKNRSW
4.13. Rozkaz COMMIT
Składnia:
&200,7 >:25.@
Opis:
Rozkaz COMMIT i COMMIT WORK wykonuj t sam operacj polegaj c na zako czeniu aktualnej transakcji i
stałym zapisaniu wszystkich dokonanych zmian w bazie danych.
4.14. Rozkaz ROLLBACK
Składnia:
52//%$&. > :25. @ >72 > 6$9(32,17 @ VDYHSRLQW @
Parametry:
x WORK - opcjonalne, wprowadzone tylko dla kompatybilno ci ze standardem ANSI
x SAVEPOINT - opcjonalne, nie zmienia działania rozkazu ROLLBACK
x savepoint - nazwa punktu zaznaczonego podczas wykonywania aktualnej transakcji.
Opis:
Rozkaz ROLLBACK wycofuje wszystkie zmiany a do podanego punktu (w przypadku klauzuli TO) lub poczatku
transakcji (bez klauzuli TO).
Przykłady:
52//%$&.
52//%$&. 72 6$9(32,17 63
19
4.15. Rozkaz SAVEPOINT
Składnia:
6$9(32,17 VDYHSRLQW
Parametry:
x savepoint - nazwa punktu w aktualnej transakcji zaznaczanego przez wykonywany rozkaz
Opis:
Rozkaz SAVEPOINT jest u ywany w poł czeniu z ROLLBACK do wycofywania fragmentów wykonywanej
transakcji. Nazwy punktów musz być unikalne w jednej transakcji. Systemy zarz dzania bazami danych wprowadzaj
najcz ciej ograniczenia na liczb punktów, które mo na zaznaczyć w jednej transakcji.
Przykłady:
83'$7( SUDFRZQLF\
6(7 SODFDBSRGVWDZRZD
:+(5( QD]ZLVNR .RZDOVNL
6$9(32,17 .RZBSODF
83'$7( SUDFRZQLF\
6(7 SODFDBSRGVWDZRZD
:+(5( QD]ZLVNR 1RZDN
6$9(32,17 1RZBSODF
6(/(&7 680 SODFDBSRGVWDZRZD )520 SUDFRZQLF\
52//%$&. 72 6$9(32,17 .RZBSODF
83'$7( SUDFRZQLF\
6(7 SODFDBSRGVWDZRZD
:+(5( QD]ZLVNR 1RZDN
&200,7
4.16. Rozkaz SET TRANSACTION
Składnia:
6(7 75$16$&7,21 ^ 5($' 21/< `
Parametry:
x READ ONLY - klauzula, która musi wyst pić
Opis:
Rozkaz informuje system, e wykonywana transakcja b dzie składać si tylko z zapyta . Nie jest mo liwe u ywanie
w takiej transakcji rozkazów INSERT, UPDATE lub DELETE. Rozkaz SET TRANSACTION musi wyst pić jako
pierwszy w transakcji, w przeciwnym razie zgłoszony zostanie bł d.
20
5. OPERACJE RELACYJNE
5.1. Selekcja
Operacja selekcji umo liwia pobranie krotek (wierszy) spełniaj cych okre lony warunek. Operacja ta nazywana jest
równie podzbiorem poziomym.
Wj zyku SQL wykonanie selekcji umo liwia rozkaz SELECT z klauzul WHERE. Przykładowo polecenie:
6(/(&7 )520 RVRE\
spowoduje wybranie wszystkich krotek (wierszy) z relacji (tabeli) ludzie.
W celu pobrania wierszy, dla których pole w kolumnie 'Wykształcenie' jest równe 'SO' ( rednie ogólne) nale y
napisać:
6(/(&7 )520 RVRE\
:+(5( :\NV]WDáFHQLH 62
Warunki selekcji mog być zło one. Przykładowo, aby wybrać wszystkie osoby, które maj wykształcenie rednie
( rednie techniczne - ST lub rednie ogólne - SO) mo na odpowiednie warunki poł czyć spójnikiem logicznym OR,
czyli zapisać w nast puj cy sposób:
6(/(&7 )520 RVRE\
:+(5( :\NV]WDáFHQLH 67 25 :\NV]WDáFHQLH 62
Budowa wyra e i warunków zostanie opisana dokładniej w dalszej cz ci wykładu.
5.2. Projekcja
Projekcja umo liwia pobranie warto ci wybranych atrybutów, wymienionych po słowie kluczowym SELECT z
wszystkich krotek relacji. Operacja ta jest nazywana tak e podzbiorem pionowym.
21
Przykładow operacj projekcji pokazan na rysunku mo na wykonać za pomoc nast puj cego rozkazu SELECT:
6(/(&7 3HVHO :\NV]WDáFHQLH )520 RVRE\
Operacje selekcji i projekcji mog być ł czone w jednym rozkazie SELECT. I tak chc c otrzymać kolumny
zawieraj ce Pesel i Nazwisko osób maj cych rednie wykształcenie nale y napisać:
6(/(&7 3HVHO 1D]ZLVNR )520 RVRE\
:+(5( :\NV]WDáFHQLH 67 25 :\NV]WDáFHQLH 62
5.3. Produkt
Produkt (iloczyn kartezja ski) jest operacj teorii zbiorów. Operacja ta umo liwia ł czenie dwóch lub wi cej relacji
w taki sposób, e ka da krotka pierwszej relacji, jest ł czona z ka d krotk drugiej relacji. W przypadku wi kszej
ilo ci relacji, operacja ta jest wykonywana, na pierwszych dwóch, a nast pnie na otrzymanym wyniku i relacji
nast pnej, a do wyczerpania wszystkich argumentów. Przykładowe wykonanie iloczynu kartezja skiego przedstawia
rysunek.
Znajdowanie iloczynu kartezja skiego dwóch relacji (tabel) jest równie wykonywane przez rozkaz SELECT.
Przedstawion na rysunku operacj mo na wykonać za pomoc nast puj cego rozkazu:
6(/(&7 )520 5 5
Operacja znajdowania iloczynu kartezja skiego mo e być ł czona zarówno z operacj selekcji, jak równie
projekcji lub oboma równocze nie.
5.4. Poł czenie
Operacja ta polega na ł czeniu krotek dwóch lub wi cej relacji z zastosowaniem okre lonego warunku ł czenia.
Wynikiem poł czenia jest podzbiór produktu relacji.
Operacj pokazan na rysunku mo na wykonać nast puj cym poleceniem SELECT.
6(/(&7 LPLH QD]ZLVNR W\WXO
)520 DXWRU]\ NVLD]NL
22
:+(5( DXWRU]\ QD]ZLVNR 0LFNLHZLF] DQG
DXWRU]\ QU NVLD]NL DXWRU
5.5. Operacje mnogo ciowe
Operacje mnogo ciowe s operacjami teorii zbiorów. W j zyku SQL operacje te mo emy stosować do relacji
(tabel), zarówno istniej cych w systemie, jak równie b d cych wynikiem działania innych rozkazów.
1. Unia
Unia pozwala na zsumowanie zbiorów krotek dwóch lub wi cej relacji (bez powtórze - zgodnie z teori mnogo ci).
Warunkiem poprawno ci tej operacji jest zgodno ć liczby i typów atrybutów (kolumn) sumowanych relacji. Przykład
przedstawiony poni ej sumuje zbiory pracowników i wła cicieli okrojone do imienia i nazwiska (za pomoc projekcji),
w celu uzyskania informacji o wszystkich ludziach powi zanych z firm :
6(/(&7 LPLH QD]ZLVNR )520 SUDFRZQLF\
81,21
6(/(&7 LPLH QD]ZLVNR )520 ZODVFLFLHOH
2. Przekrój
Przekrój pozwala znale ć iloczyn dwóch lub wi cej zbiorów krotek tzn. takich, które wyst puj zarówno w jednej
jak i w drugiej relacji. Podobnie jak w przypadku unii, warunkiem poprawno ci tej operacji jest zgodno ć liczby i
typów atrybutów relacji bazowych.
Poni szy przykład znajduje wszystkie nazwiska (np. stosunek pracy, powi zania rodzinne), które wyst puj
zarówno w relacji pracownicy jak i w relacji wła ciciele.
6(/(&7 QD]ZLVNR )520 SUDFRZQLF\
,17(56(&7
6(/(&7 QD]ZLVNR )520 ZODVFLFLHOH
3. Ró nica
Operacja obliczania ró nicy dwóch relacji polega na znalezieniu wszystkich krotek, które wyst puj w pierwszej
relacji, ale nie wyst puj w drugiej.
Przykład znajduje wszystkie osoby, które s współwła cielami spółki, ale nie s w niej zatrudnieni:
6(/(&7 LPLH QD]ZLVNR )520 ZODVFLFLHOH
0,186
6(/(&7 LPLH QD]ZLVNR )520 SUDFRZQLF\
5.6. Grupowanie
Klauzule GROUP BY i HAVING wyst puj ce w rozkazie SELECT pozwalaj dzielić relacj wynikow na grupy,
wybierać niektóre z tych grup i na ka dej z nich z osobna wykonywać pewne (dozwolone przez system) operacje.
Operacje te działaj na wszystkich wierszach wchodz cych w skład grupy. Na samym ko cu zwracana jest tylko
zbiorcza informacja o wybranych grupach (nie zwraca si wszystkich wierszy wchodz cych w skład grupy).
Klauzula GROUP BY słu y do dzielenia krotek relacji na mniejsze grupy. Sposób takiego podziału ilustruje
przykład:
6(/(&7 VWDQRZLVNR DYJ SODFDBSRGVWDZRZD
)520 SUDFRZQLF\
*5283 %< VWDQRZLVNR
Istnieje mo liwo ć odrzucenia pewnych krotek przed podziałem na grupy. Dokonuje si tego za pmoc klauzuli
WHERE:
6(/(&7 VWDQRZLVNR DYJ SODFDBSRGVWDZRZD
)520 SUDFRZQLF\
:+(5( VWDQRZLVNR .,(52:&$
*5283 %< VWDQRZLVNR
23
Dzielenie na grupy mo e być zagnie d ane, co umo liwia wydzielanie podgrup w uprzednio znalezionych
podgrupach. W przykładzie poni ej wszyscy pracownicy s dzieleni na wydziały, w których pracuj , a w ramach
ka dego wydziały grupowani według stanowiska:
6(/(&7 Z\G]LDO VWDQRZLVNR DYJ SODFDBSRGVWDZRZD
)520 SUDFRZQLF\
*5283 %< QUBZ\G]LDOX VWDQRZLVNR
Klauzula HAVING ogranicza wy wietlanie grup do tych, które spełniaj okre lony warunek. Chc c wy wietlić
tylko te grupy, w których płaca podstawowa przynajmniej jednego pracownika jest wi ksza ni 3 000 nale y
zastosować nast puj ce zapytanie:
6(/(&7 VWDQRZLVNR PD[ SODFDBSRGVWDZRZD
)520 SUDFRZQLF\
*5283 %< VWDQRZLVNR
+$9,1* PD[ SODFDBSRGVWDZRZD !
5.7. Kolejno ć klauzul w rozkazie SELECT
Klauzule maj ce wpływ na realizacj rozkazu SELECT uwzgl dniane s w nast puj cej kolejno ci:
x 6(/(&7 L :+(5(
x *5283 %<
x +$9,1*
x 25'(5 %<
5.8. Podzapytania
Zapytania j zyka SQL mog być zagnie d ane, tzn. wynik jednego zapytania mo e być u yty np. jako warunek
selekcji innego zapytania. Podzapytania mo na podzielić na dwa rodzaje:
x podzapytania proste (nazywane po prostu podzapytaniem) - podzapytanie jest wykonywane przed
wykonaniem zapytania głównego;
x podzapytania skorelowane - podzapytanie jest wykonywane dla ka dej krotki podzapytania głównego.
Najcz ciej podzapytania u ywane s w klauzuli WHERE rozkazu SELECT. Je li wiadomo, e wynikiem
podzapytania b dzie pojedyncza warto ć, to warto ć t mo na u yć bezpo rednio w warunku klauzuli WHERE w
nast puj cy sposób:
6(/(&7 QD]ZLVNR
)520 SUDFRZQLF\
:+(5( SODFDBSRGVWDZRZD
6(/(&7 PLQ SODFDBSRGVWDZRZD
)520 SUDFRZQLF\
Powy szy przykład znajduje nazwiska pracowników zarabiaj cych najmniej.
Je li jednak w wynikiem podzapytania b dzie kilka warto ci, to konieczne jest u ycie operatora IN w zapytaniu
głównym. Na przykład, w celu znalezienia pracowników zarabiaj cych najmniej w swoich grupach, nale y posłu yć si
nast puj cycm rozkazem:
6(/(&7 QD]ZLVNR QUBZ\G]LDOX
)520 SUDFRZQLF\
:+(5( SODFDBSRGVWDZRZD ,1
6(/(&7 PLQ SODFDBSRGVWDZRZD
)520 SUDFRZQLF\
*5283 %< QUBZ\G]LDOX
Liczba warto ci i ich typy musz być zgodne z list znajduj c si po prawej stronie operatora IN.
24
W podzapytaniach stosować mo na równie operatory ANY i ALL. Operator ANYpozwala sprawdzić, czy chocia
jeden element z listy spełnia podany warunek. Operator ALL umo liwia sprawdzenie czy warunek jest spełniony dla
wszystkich elementów listy. Składnia operatorów ANY i ALL oraz przykłady u ycia zostan podane w dalszej cz ci
wykładu.
Podzapytania mo na umieszczać równie w klauzuli HAVING. Poni szy przykład wy wietla zespoły, w których
rednie zarobki s wi ksze ni w zespole 30:
6(/(&7 QUBZ\G]LDOX DYJ SODFDBSRGVWDZRZD
)520 SUDFRZQLF\
*5283 %< QUBZ\G]LDOX
+$9,1* DYJ SODFDBSRGVWDZRZD !
6(/(&7 DYJ SODFDBSRGVWDZRZD
)520 SUDFRZQLF\
:+(5( QUBZ\G]LDOX
Zapytania mog być zagnie d ane na dowoln ilo ć poziomów. Przy zagnie dzaniu zapyta nale y jednak pami tać
o:
x w podzapytaniach nie mo e wyst pować klauzula ORDER BY (mo e ona wyst pić jako ostatnia tylko w
zapytaniu głównym);
x zapytania zagnie d one s wykonywane w kolejno ci od najbardziej zagnie d onego do zapytania
głównego, chyba, e mamy do czynienia z podzapytaniem skorelowanym.
Podzapytanie skorelowane jest zapytaniem zagnie d onym, które jest wykonywane dla ka dej krotki analizowanej
przez zapytanie zewn trzne. Podstawowa własno ć podzapytania skorelowanego polega na tym, e operuje ono na
informacji przekazanej przez zapytanie główne.
Nast puj cy przykład pokazuje u ycie podzapytania skorelowanego w celu znalezienia pracowników
zarabbiaj cych wi cej ni wynosi rednia płaca w ich działach:
6(/(&7 LPLH QD]ZLVNR SODFDBSRGVWDZRZD QUBZ\G]LDOX
)520 SUDFRZQLF\ SUDFRZQLN
:+(5( SODFDBSRGVWDZRZD !
6(/(&7 DYJ SODFDBSRGVWDZRZD
)520 SUDFRZQLF\
:+(5( QUBZ\G]LDOX SUDFRZQLN QUBZ\G]LDOX
W podzapytaniach skorelowanych, w przeciwie stwie do podzapyta prostych, wyst puje odwołanie do atrybutu
krotki analizowanej aktualnie przez zapytanie zewn trzne. Je li wi c zapytania skorelowane operuj na tej samej
relacji, to konieczne jest u ycie aliasu w celu odwołania si do atrybutu krotki analizowanej przez zapytanie główne (w
tym wypadku jest to realizowane za pomoc aliasu "pracownik").
5.9. Widoki (perspektywy)
Widoki s traktowane przez system zarz dzaniaa baz danych podobnie jak tabele m. in. posiadaj kolumny i
wiersze słu ce do przechowywania informacji. Widok nie posiada jednak własnych danych. Wszystkie dane,
udost pniane przez widok s danymi zawartymi w jednej lub kilku tabelach (albo widokach). Widoki stosuje si w celu:
x ograniczenia dost pu do tabel w bazie danych
x uproszczenia zapyta kierowanych do systemu
x zapewnienia niezale no ci danych wewn trz aplikacji
Ze wzgl du na ilo ć tabel, na których zdefiniowany został widok, widoki mo na podzielić na:
x proste
x zło one
25
Widok definiuje si przy pomocy polecenia SELECT, które jest zapami tywane przez system i wykonywane
automatycznie w momencie otrzymania dania dost pu do danych zawartych w widoku.
Widok prosty udost pnia dane tylko z jednej tabeli i w jego definicji nie stosuje si polece j zyka SQL ani te
grupowania wierszy.
Widok zło ony udost pnia dane zawarte w kilku tabelach i mo e zawierać operacje relacyjne oraz grupowanie
wierszy. Cen płacon za mo liwo ć zdefiniowania widoku zło onego jest najcz ciej brak mo liwo ci zapisu danych
w tym widoku (ale nie we wszystkich systemach).
Do tworzenia widoków słu y opisany wcze niej rozkaz CREATE VIEW. Poni szy przykład pokazuje tworzenie
widoku na bazie tabeli pracownicy udost pniaj cego tylko imie i nazwisko pracownika (bez mo liwo ci dost pu do
płacy czy numeru wydziału):
&5($7( 9,(: SHUVRQDOLD
$6
6(/(&7 LPLH QD]ZLVNR
)520 SUDFRZQLF\
Tworzenie widoku zło onego przedstawia przykład podobny do znajduj cego si w opisie rozkau CREATE VIEW:
&5($7( 9,(: ELEO
$6 6(/(&7 NVLD]NL W\WXO DXWRU]\ LPLH
DXWRU]\ QD]ZLVNR PLHMVFD PLHMVFH
)520 NVLD]NL DXWRU]\ PLHMVFD
:+(5( NVLD]NL DXWRU DXWRU]\ DXWRUBQU
$1' NVLD]NL PLHMVFH PLHMVFD PLHMVFHBQU
5.10. Transakcje
Transakcje s wykonywane za pomoc dwóch rozkazów: COMMIT i ROLLBACK. Pierwszy z tych rozkazów jest
u ywany do zapisywania w bazie wszystkich dokonanych zmian, drugi do wycofywania zmian uprzednio
wprowadzonych. Przykładem transakcji jest dokonywanie przelewu pomi dzy jednym bankiem a drugim. Operacja ta
wymaga wykonania dwóch rozkazów UPDATE - zmniejszenia stanu konta w banku dokonuj cym przelewu i
zwi kszenia odpowiedniego stanu konta w banku otrzymuj cym przelew. Z charakteru operacji przelewu wynika, e
musz być wykonane oba rozkazy albo aden. Je li bowiem wykonano by tylko jeden z nich to pieni dze albo by
"znikły" albo si "rozmno yły".
Rozkaz COMMIT słu y do zapisywania na stałe wykonanych uprzednio operacji. Przed wykonaniem tego rozkazu
adne zmiany w bazie danych nie s widoczne dla innych u ytkowników. W podanym uprzednio przykładzie po
wykonaniu dwóch rozkazów UPDATE, nale y wykonać COMMIT w celu trwałego zapisania dokonanych zmian. Od
tego momentu zmienione stany kont b d widoczne dla innych u ytkowników.
Rozkaz ROLLBACK jest odwrotno ci COMMIT. Jest to równie rozkaz ko cz cy transakcj , jednak powoduje
wycofanie wszystkich zmian w bazie od poprzedniego rozkazu COMMIT lub ROLLBACK.
Rozkaz SAVEPOINT pozwala na zaznaczenie i nazwanie pewnego punktu wewn trz transakcji. W ten sposób za
pomoc rozkazu ROLLBACK TO mo liwe jest cz ciowe wycofanie transakcji (tzn. do podanego punktu wewn trz
aktualnie wykonywanej transakcji). Wykonanie rozkazu ROLLBACK TO do okre lonego punktu powoduje
skasowanie wszystkich zaznacze poni ej. Wykonanie ROLLBACK lub COMMIT powoduje skasowanie wszystkich
uprzednio zaznaczonych punktów.
Jest dobrym zwyczajem, by ka d transakcj ko czyć rozkazem COMMIT lub ROLLBACK. Je li nie jest to
zrobione, a ko czy si skrypt lub blok, to system sam podejmuje decyzj , czy rozpocz t transakcj wykonać czy
wycofać. Mo e to w pewnych przypadkach prowadzić do rezultatów, które nie były oczekiwane przez osob tworz c
skrypt.
26
6. NORMALIZACJA RELACJI
6.1. Cele normalizacji
Normalizacja relacji ma na celu takie jej przekształcenie, by nie posiadała ona cech niepo danych. We my dla
przykładu relacj opisuj c zaj cia odbywaj ce si na uczelni w jednym semestrze. Relacja ta mo e zawierać
nast puj ce atrybuty:
x nazwa przedmiotu,
x imie,
x nazwisko,
x adres prowadz cego
Przykładowa krotka takiej relacji mogłaby mieć postać:
x (j zyk angielski, Lucyna, Nowak,
x ul. Królewska 30/3 Kraków)
Jednak z tak zaprojektowan relacj zwi zane s nast puj ce problemy:
x adres składaj cy si z kilku cz ci nie został podzielony w zwi zku z tym nie jest mo liwe sprawdzenie ile
osób mieszka w Krakowie i nie potrzebuje hotelu;
x jeden prowadz cy mo e mieć zaj cia z kilku przedmiotów, w zwi zku z tym wyst puje redundancja
danych;
x zmiana jednej z informacji o prowadz cym (np. adresu) powoduje konieczno ć zmiany wszystkich krotek
zawieraj cych te dane w celu zachowania integralno ci;
x nie jest mo liwe wprowadzenie informacji o prowadz cym, który w aktualnym semestrze nie ma adnych
zaj ć;
x usuni cie przedmiotu mo e spowodować równie usuni cie wszelkich informacji o osobie, która go
prowadziła
Utrzymanie integralno ci takiej bazy nie jest wi c proste. Jednak opisan relacj mo na zamienić na dwie inne,
które nie b d posiadały tych wad:
x relacja Prowadz cy:
x identyfikator, imi , nazwisko, kod pocztowy, miejscowo ć, ulica, nr_domu, nr_mieszkania
x relacja Zaj cia
x nazwa, id_prowadz cego
Ka da krotka relacji "Zaj cia", jest powi zana z krotk relacji "Prowadz cy" za pomoc klucza obcego o nazwie
"id_prowadz cego". Te dwie relacje nie posiadaj opisanych wcze niej cech niepo danych poniewa :
x adres jest zdekomponowany na cz ci składowe, w zwi zku z czym mo liwe jest wysukiwanie danych np.
według miejscowo ci zamieszkania prowadz cego;
x zmiana informacji o prowadz cym (np. adresu) nie powoduje konieczno ci zmian danych w relacji
"Zaj cia". Zmiana ta odbywa si tylko w jednym miejscu;
x mo liwe jest wprowadzenie informacji o osobie, która nie ma zaj ć w aktualnym semestrze, ale być mo e
b dzie je miała w semestrze nast pnym;
x usuni cie przedmiotu nie powoduje usuni cia informacji o osobie, która go prowadziła.
Jednak taka reprezentacja danych posiada wady podobne do opisanych wcze niej, ale dotycz ce przedmiotów.
Dlatego w dobrze zaprojektowanej bazie danych konieczne jest wydzielenie trzeciej tabeli, która b dzie zawierała spis
przedmiotów.
6.2. Pierwsza postać normalna
Relacja jest w pierwszej postaci normalnej, je li warto ci atrybutów s elementarne tzn. s to pojedyncze warto ci
okre lonego typu, a nie zbiory warto ci.
27
Pierwsza postać normalna jest konieczna aby, tabel mo na było nazwać relacj . Wi kszo ć systemów baz danych
nie ma mo liwo ci zbudowania tabel nie b d cych w pierwszej postaci normalnej. Przekształcenie z postaci nie
znormalizowanej do pierwszej postaci normalnej ilustruje rysunek:
6.3. Definicje pomocnicze
Aby ułatwić przekształcanie relacji do postaci optymalnej wprowadzono poj cie postaci normalnej. Przed
omówieniem procesu normalizacji konieczne jest jednak wprowadzenie kilku poj ć:
x Uniwersalny schemat relacji R = {A1, A2, ..., An} jest zbiorem atrybutów tworz cych relacj .
x Zbiorem identyfikuj cym relacji R = {A1, A2, ..., An} nazywamy zbiór atrybutów S R, który
jednoznacznie identyfikuje wszystkie krotki relacji o schemacie R. Inaczej mówi c w adnej relacji o
schemacie R nie mog istnieć dwie krotki t1 i t2 takie, e t1[S] = t2[S].
x Kluczem K schematu relacji R nazywamy minimalny zbiór identyfikuj cy, tzn. taki, e nie istnieje K' K
b d ce zbiorem identyfikuj cym schematu R. Klucze dziel si na klucze proste i zło one.
Klucz nazywamy kluczem prostym, je eli zbiór atrybutów wchodz cych w jego skład jest zbiorem
jednoelementowym; w przeciwnym wypadku mamy do czynienia z kluczem zło onym. Najcz ciej w relacji mo na
wyró nić wiele kluczy, które nazywamy kluczami potencjalnymi. Jeden (wybrany) klucz spo ród kluczy potencjalnych
nazywamy kluczem głównym (primary key), natomiast pozostałe kluczami drugorz dnymi (secondary key).
Dla przykładu w relacji "Zamówienia" jedynym kluczem potencjalnym jest para atrybutów (Nr zamówienia, Id
cz ci). Nale y zauwa yć, e sam numer zamówienia nie jest kluczem, poniewa jedno zamówienie mo e dotyczyć
wielu cz ci.
x Atrybut relacji nazywamy podstawowym, je eli nale y do dowolnego z kluczy tej relacji.
x Atrybut relacji nazywamy wtórnym, je eli nie nale y do adnego z kluczy tej relacji.
x Atrybut B relacji R jest funkcjonalnie zale ny od atrybutu A tej relacji (co okre la si równie słowami, e
A identyfikuje B i oznacza A B), je li dowolnej warto ci a atrybutu A odpowiada nie wi cej ni jedna
warto ć b atrybutu B.
Zale no ć funkcjonalna mi dzy dwoma atrybutami A i B nie jest zwi zana z przypadkowym układem ich warto ci,
ale wynika z charakteru zale no ci mi dzy tymi atrybutami w modelowanej rzeczywisto ci.
28
Informacj , e atrybut A identyfikuje B (tzn. e B jest funkcjonalnie zale ny od A) zaznacza si na rysunkach
strzałk biegn c od A do B. W ten sposób schemat zale no ci funkcjonalnych dla przykładowej relacji "Zamówienia"
mo na narysować nast puj co:
Atrybut B jest w pełni funkcjonalnie zale ny od zbioru atrybutów X w schemacie R, je eli X Y i nie istnieje
podzbiór X' X taki, e X' Y.
Nale y zwrócić uwag , e wszystkie zale no ci funkcjonalne przedstawione w poprzednim przykładzie s pełnymi
zale no ciami funkcjonalnymi.
x Zbiór atrybutów Y jest cz ciowo funkcjonalnie zale ny od zbioru atrybutów X w schemacie R, je eli X Y
i istnieje podzbiór X' X taki, e X' Y.
x Niech X, Y i Z b d trzema rozł cznymi podzbiorami atrybutów danej relacji. Podzbiór atrybutów Z jest
przechodnio funkcjonalnie zale ny od podzbioru atrybutów X, je li podzbiór atrybutów Z jest
funkcjonalnie zale ny od podzbioru atrybutów Y i podzbiór atrybutów Y jest funkcjonalnie zale ny od
podzbioru atrybutów X, natomiast podzbiór atrybutów X nie jest funkcjonalnie zale ny od Y i podzbiór
atrybutów Y nie jest funkcjonalnie zale ny od Z.
Przykładowo w relacji "Zamówienia" atrybuty "Nazwa dostawcy" i "Adres dostawcy" s przechodnio funkcjonalnie
zale ne od atrybutu "Nr zamówienia", poniewa atrybut "Id dostawcy" jest funkcjonalnie zale ny od atrybutu "Nr
zamówienia" i atrybuty "Nazwa dostawcy" oraz "Adres dostawcy" s funkcjonalnie zale ne od "Id dostawcy".
Równocze nie mo na zauwa yć, e "Id dostawcy" nie jest funkcjonalnie zale ne od "Nazwy dostawcy" (mog być
dostawcy o tej samej nazwie) ani od "Adresu dostawcy" (poniewa dwóch dostawców mo e mieć ten sam adres.
Mówimy, e podzbiór atrybutów Y jest wielowarto ciowo funkcjonalnie zale ny od podzbioru atrybutów X w
schemacie R, je eli dla dowolnej relacji r w schemacie R i dla dowolnej pary krotek t1 i t2 z relacji r takich, e t1[X] =
t2[X], istnieje taka para krotek s1 i s2 w relacji r, e
s1[X] = s2[X] = t1[X] = t2[X] oraz
s1[Y] = t1[Y] i s1[R-X-Y] = t2[R-X-Y] oraz
s2[Y] = t2[Y] i s2[R-X-Y] = t1[R-X-Y].
Mówi c inaczej, je eli dla dowolnej pary krotek t1 i t2 z relacji r takich, e warto ci tych krotek dla atrybutów z
podzbioru X s sobie równe (co zapisujemy t1[x] = t2[X]), zamienimy w tych krotkach warto ci atrybutów z podzbioru
Y, to otrzymane w ten sposób krotki s1 i s2 równie nale do relacji r.
Przykładowo chc c zapisać, e "Kowalski" ma dzieci o imionach "Agnieszka" i "Magda" oraz, e wykłada
przedmioty"J zyk C" i "Systemy operacyjne", natomiast "Nowak" ma dzieci "Jarosław", "Jan" i "Aleksander" oraz
29
wykłada przedmioty "Bazy danych" i "Teoria kompilatorów" mo na zdefiniować nast puj c tabel :
Okre laj c podzbiór X = (Nazwisko) i Y = (Dziecko) otrzymujemy, e R-X-Y = (Wykład). Parze krotek:
t1 = (Kowalski, Agnieszka, J zyk C)
t2 = (Kowalski, Magda, Systemy operacyjne)
odpowiada para krotek
s1 = (Kowalski, Agnieszka, Systemy operacyjne)
s2 = (Kowalski, Magda, J zyk C)
Nale y zwrócić uwag , e wyst pienie krotek spełniaj cych wymienione wy ej warunki, wi e si najcz ciej z
niezale no ci jednego zbioru atrybutów od drugiego (w tym wypadku jednoelementowego zbioru atrybutów (Dziecko)
od jednoelementowego zbioru atrybutów (Wykład)).
x Z definicji wielowarto ciowej zale no ci funkcjonalnej wynika, e:
podzbiór pusty jest zawsze wielowarto ciowo funkcjonalnie zale ny od dowolnego zbioru
atrybutów X
je li XY=R, to X jest wielowarto ciowo funkcjonalnie zale ny od Y i na odwrót.
Zale no ci te nazywamy trywialnymi wielowarto ciowymi zale no ciami funkcjonalnymi.
x Dekompozycj schematu R = (A1, A2, ..., An) nazywamy zast pienie go zbiorem (niekoniecznie
rozł cznych) schematów relacji (R1, R2, ..., Rm) takich, e ka dy schemat Ri z tego zbioru stanowi
podzbiór zbioru atrybutów (A1, ..., An) i
x Mówimy, e w schemacie relacji R = (A1, ..., An) wyst puje poł czeniowa zale no ć funkcjonalna (co
zapisuje si : *R[R1, ..., Rn]) wtedy i tylko wtedy, gdy mo liwa jest dekompozycja relacji r (o schemacie R)
na relacje r1, ..., rn taka, e relacj pierwotn r mo na zrekonstruować przez wykonanie sekwencji operacji
poł czenia relacji r1, ..., rm.
Mo na pokazać, e wielowarto ciowa zale no ć funkcjonalna stanowi szczególny przypadek poł czeniowej
zale no ci funkcjonalnej dla m = 2.
x Mówimy, e poł czeniowa zale no ć funkcjonalna *R[R1, ..., Rm] wynika z zale no ci atrybutów
schematu R od klucza wtedy i tylko wtedy, gdy w dowolnej sekwencji poł cze relacji składowych
wykonywanych w celu rekonstrukcji relacji r, ka da operacja poł czenia jest wykonywana wzgl dem
zbioru identyfikuj cego schematu R.
6.4. Druga postać normalna
Relacja jest w drugiej postaci normalnej, je eli ka dy atrybut wtórny (tzn. nie wchodz cy w skład adnego klucza
potencjalnego) tej relacji jest w pełni funkcjonalnie zale ny od wszystkich kluczy potencjalnych tej relacji.
Mo na zauwa yć, e relacja "Zamówienia" nie jest w drugiej postaci normalnej, poniewa atrybuty "Id dostawcy",
"Nazwa dostawcy", "Adres dostawcy" i "Nazwa cz ci" nie s w pełni funkcjonalnie zale ne od jedynego klucza
potencjalnego - pary ("Nr zamówienia", "Id cz ci").
W celu sprowadzenia relacji do drugiej postaci normalnej, nale y podzielić j na takie relacje, których wszystkie
atrybuty b d w pełni funkcjonalnie zale ne od kluczy. W tym celu przykładow relacj "Zamówienia" nale y
podzielić na trzy relacje: "Dostawca na zamówieniu", "Zamówione dostawy", "Cz ci" w nast puj cy sposób:
30
Jak widać wszystkie te trzy relacje s w drugiej postaci normalnej, poniewa klucze relacji "Dostawca na
zamówieniu" oraz "Cz ci" s kluczami prostymi, natomiast atrybut "Ilo ć" w relacji "Zamówione dostawy" jest w
pełni funkcjonalnie zale ny od klucza zło onego ("Nr zamówienia", "Id cz ci").
Nale y zauwa yć, e relacja b d ca w pierwszej postaci normalnej, jest równoczesnie w drugiej postaci normalnej,
je li wszystkie jej klucze potencjalne s kluczami prostymi.
Po przekształceniu relacji "Zamówienia" do drugiej postaci normalnej otrzymujemy nast puj ce zale no ci
funkcjonalne:
x Dostawca na zamówieniu
x Zamówione dostawy
31
x Cz ci
6.5. Trzecia postać normalna
Dana relacja jest w trzeciej postaci normalnej, je li jest ona w drugiej postaci normalnej i ka dy jej atrybut nie
wchodz cy w skład adnego klucza potencjalnego nie jest przechodnio funkcjonalnie zale ny od adnego klucza
potencjalnego tej relacji.
Aby doprowadzić relacj , której atrybuty pozostaj w przechodniej zale no ci funkcjonalnej, nale y podzielić j na
relacje zawieraj ce tylko zale no ć funkcjonaln . Podział relacji ilustruje rysunek:
W opisywanym przykładzie przechodnia zale no ć funkcjonalna wyst puje pomi dzy atrybutami "Nazwa
dostawcy" i "Adres dostawcy" a atrubutem "Nr zamówienia" w relacji "Dostawca na zamówieniu". W zwi zku z tym
konieczne jest dokonanie podziału relacji "Dostawca na zamówieniu" na dwie relacje: "Zamówienia" i "Dostawcy" w
nast puj cy sposób:
7. CZWARTA POSTAĆ NORMALNA
Dana jest relacja o schemacie R oraz trzy parami rozł czne i niepuste podzbiory X, Y, Z atrybutów z R takie, e X
Y Z = R i podzbiór Y jest nietrywialnie wielowarto ciowo zale ny od X.
32
Dana relacja R jest w czwartej postaci normalnej wtedy i tylko wtedy, gdy jest w trzeciej postaci normalnej i
wielowarto ciowa zale no ć zbioru Y od X poci ga za sob funkcjonaln zale no ć wszystkich atrybutów tej relacji od
X.
A
atwo zauwa yć, e tabela "Pracownicy" z definicji wielowarto ciowej zale no ci funkcjonalnej jest w trzeciej
postaci normalnej, ale nie jest w czwartej postaci normalnej, poniewa atrybuty "Dziecko" i "Wykład" nie s
funkcjonalnie zale ne od atrybutu "Nazwisko".
Jak wynika z definicja relacja, która zawiera trywialn wielowarto ciow zale no ć funkcjonaln jest w czwartej
postaci normalnej. St d wniosek, e relacj zawieraj c nietrywialn wielowarto ciow zale no ć funkcjonaln nale y
podzielić na takie relacje, które b d zawierać tylko zale no ci trywialne.
W opisywanym przykładzie relacj "Pracownicy" mo na podzielić na dwie relacje: "Dzieci" i "Wykłady", które
b d zawierać tylko trywialn wielowarto ciow zale no ć funkcjonaln :
7.1. Pi ta postać normalna
Dana relacja r o schemacie R jest w pi tej postaci normalnej wtedy i tylko wtedy, gdy jest w czwartej postaci
normalnej i w przypadku wyst powania w niej poł czeniowej zale no ci funkcjonalnej *R[R1, ..., Rm] zale no ć ta
wynika z zale no ci atrybutów od klucza.
Wynika z tego, e w celu doprowadzenia pewnej relacji do pi tej postaci normalnej konieczne jest podzielenie jej na
takie relacje, które spełniać b d podany wy ej warunek.
7.2. Podsumowanie
Przekształcenie relacji do kolejnych postaci normalnych wi e si najcz ciej ze zmniejszeniem ilo ci pami ci
potrzebnej do przechowania informacji.
Proces normalizacji ma na celu takie przekształcenie relacji, by unikn ć dublowania informacji. Unikanie powtórze
pozwala na łatwiejsze i w wielu przypadkach szybsze posługiwanie si baz danych.
33
8. WARUNKI I WYRA ENIA
8.1. Warunki i wyra enia
Warunki i wyra enia składaj si z operatorów, funkcji oraz danych, na których działaj .
8.2. Operatory arytmetyczne
Operatory arytmetyczne działaj zasadniczo na danych typu numerycznego. Jednak niektóre z tych operatorów
mog być u yte do danych typu DATE. Spis operatorów arytmetycznych podzielonych według priorytetu przedstawia
tabela:
Operator Opis Przykład
Zmienia normaln kolejno ć wykonywania działa . Wszystkie
( ) działania wewn trz nawiasów s wykonywane przed działaniami poza SELECT (X+Y)/(Y+Z) ...
nawiasami.
WHERE NR = -1
+, - Operatory jednoargumentowe zachowania i zmiany znaku.
WHERE -PLACA < 0
SELECT 2*X+1
*, / Mno enie, dzielenie
WHERE X > Y/2
SELECT 2*X+1
+, - Dodawanie, odejmowanie
WHERE X > Y-Z
8.3. Operatory znakowe
Jedynym operatorem działaj cym na ci gach znaków jest operator konkatenacji. Rezultatem działania tego
operatora jest ci g znakó b d cy poł czeniem operandów. Nale y pami tać, e ci gi znaków typu CHAR nie mog być
dłu sze ni 255 znaków. Ograniczenie to dotyczy równie ci gu znakowego b d cego wynikiem działania operatora
konkatenacji.
Operator Opis Przykład
|| Konkatenacja ci gów znaków SELECT 'Nazwa: ' || ENAME ...
8.4. Operatory porównania
Operatory porównania s wykorzystywane w wyra eniach i warunkach do porównywania dwóch wyra e .
Wynikiem działania operatorów porównania jest zawsze warto ć logiczna (TRUE lub FALSE).
Operator Opis Przykład
Zmienia normaln kolejno ć wykonywania
( ) ... NOT (A=1 OR B=1)
działa
=Sprawdza, czy dwa wyra enia s równe ... WHERE PLACA = 1000
!=, ^=, <> Sprawdza, czy dwa wyra enia s ró ne ... WHERE PLACA != 1000
>Wi ksze ni ... WHERE PLACA > 1000
< Mniejsze ni ... WHERE PLACA < 1000
>= Wi ksze lub równe ni ... WHERE PLACA >= 1000
<= Mniejsze lub równe ni ... WHERE PLACA <= 1000
... WHERE ZAWOD IN ('URZEDNIK',
'INFORMATYK')
Równy dowolnemu elementowi.
IN
Synonim do " = ANY"
... WHERE PLACA IN (SELECT PLACA FROM
PRAC WHERE WYDZIAL=30)
... WHERE PLACA NOT IN (SELECT PLACA
Ró ny od ka dego z elementów. Wynikiem
NOT IN
jest FALSE je li dowolny element zbioru jest FROM PRAC WHERE WYDZIAL=30)
34
równy NULL
Synonim do "!= ALL"
Porównuje warto ć z ka d warto ci ze
zbioru po prawej stronie. Musi być
... WHERE PLACA = ANY (SELECT PLACA
ANY poprzedzony jednym z operatorów: =, !=, >,
FROM PRAC WHERE WYDZIAL =30)
<, <=, >=. Zwraca TRUE, je li przynajmniej
jeden z elementów spełnia podany warunek.
Porównuje warto ć z ka d warto ci ze
zbioru po prawej stronie. Musi być
... WHERE (PLACA, PREMIA) >= ALL ((14900,
ALL poprzedzony jednym z operatorów: =, !=, >,
300), (3000, 0))
<, <=, >=. Zwraca TRUE, je li ka dy z
elementów spełnia podany warunek.
[NOT] BETWEEN x [Nie] wi kszy lub równy x i mniejszy lub
... WHERE A BETWEEN 1 AND 9
AND y równy y.
Zwraca TRUE je li zapytanie [nie] zwraca ... WHERE EXISTS (SELECT PLACA FROM
[NOT] EXISTS
przynajmniej jeden wiersz. PRAC WHERE WYDZIAL = 30)
[Nie] spełnia podany wzorzec. Litera '%' jest
u ywana do zapisywania dowolnego ci gu
[NOT] LIKE znaków (0 lub wi cej), który nie jest równy ... WHERE STAN LIKE 'T%'
NULL. Litera '_' zast puje dowoln
pojedyncz liter .
IS [NOT] NULL [Nie] jest równe NULL. ... WHERE ZAWOD IS NULL
Operator NOT IN zwróci FALSE (co w przypadku klauzuli WHERE spowoduje, e adne wiersze nie zostan
zwrócone), je li choć jeden z elementów listy jest równy NULL. Np. rozkaz:
6(/(&7 758(
)520 SUDF
:+(5( Z\G]LDO 127 ,1 18//
nie zwróci adnych wierszy, poniewa
Z\G]LDO 127 ,1 18//
zostanie rozwini ty do
Z\G]LDO $1' Z\G]LDO $1' Z\G]LDO 18//
Wynikiem działania operatorów porównania i logicznych dla warto ci NULL jest warto ć NULL. Dlatego te
wynikiem całego opisywanego rozkazu b dzie warto ć NULL.
8.5. Operatory logiczne
Operatory logiczne słu do wykonywania oblicze na warto ciach typu logicznego (w szczególno ci b d cych
wynikiem obliczania warunków).
Operator Opis Przykład
Zmienia normaln kolejno ć wykonywania
( ) SELECT ... WHERE x = y AND (a = b OR p = q)
działa
... WHERE NOT (zawod IS NULL)
NOT Zaprzeczenie wyra enia logicznego
WHERE NOT (A=1)
Logiczne 'i'. Wynik jest równy TRUE, je li
AND ... WHERE A = 1 AND B = 2
warto ci obu operandów s równe TRUE
Logiczne 'lub'. Wynike jest równy TRUE, jesli
OR warto ć przynajmniej jednego operandu jest ... WHERE A = 1 OR B = 3
równa TRUE
Poni sze tabele przedstawiaj wynik działania operatora AND i OR dla ró nych warto ci:
AND true false null
true true false null
35
false false false false
null null false null
OR true false null
true true true true
false true false null
null true null null
8.6. Operatory mnogo ciowe
Operatory zbiorowe działaj na wynikach zapyta lub listach warto ci.
Operator Opis Przykład
Unia dwóch zbiorów. A
czy dwa zbiory, powtarzaj ce si elementy wyst puj SELECT ...
UNION
tylko raz. UNION SELECT ...
SELECT ...
INTERSECT Cz ć wspólna dwóch zbiorów. Powtarzaj ce si elementy wyst puj tylko raz INTERSECT
SELECT ...
Oblicza ró nic dwóch zbiorów. W wyniku umieszczane s tylko te elementy,
SELECT ...
MINUS które wyst puj w pierwszym zbiorze i nie wyst puj w drugim. Elementy
MINUS SELECT ...
powtarzaj ce si wyst puj tylko raz
8.7.
Wyra enia
Wyra enie jest ci giem jednej lub wi cej warto ci, operatorów lub funkcji. Wynik obliczania wyra enia musi być
warto ci . W ogólno ci typ wyniku zale y od typów operandów.
Nast puj ce przykłady pokazuj wyra enia ró nych typów:
x numeryczny: 2 * 2
x znakowy: TO_CHAR(TRUNC(SYSDATE + 7))
Wyra enie mo e być u yte wsz dzie tam, gdzie mo liwe jest u ycie warto ci stałej, np.:
6(7 1D]ZLVNR /2:(5 1D]ZLVNR
Istnieje pi ć form wyra e :
kolumna, stała lub warto ć specjalna
Składnia:
>WDEOH @ ^ FROXPQ _ 52:,' `
WH[W
QXPEHU
VHTXHQFH &8559$/
VHTXHQFH 1(;79$/
18//
52:180
/(9(/
6<6'$7(
8,'
86(5
Przykłady:
SUDFRZQLF\ QD]ZLVNR
WR MHVW FL J ]QDNyZ
6<6'$7(
]PLHQQD á F]RQD ] RSFMRQDOQ ]PLHQQ LQG\NDWRURZ
Składnia:
36
^ Q _ YDULDEOH ` > LQGBYDULDEOH @
Przykłady:
QD]ZLVNRBSUDFRZQLND QD]ZLVNRBSUDFRZQLNDBLQG\NDWRU
SRáR HQLHBZ\G]LDáX
Z\ZRáDQLH IXQNFML
Składnia:
IXQFWLRQBQDPH >',67,1&7 _ $//@ H[SU > H[SU@
3U]\NáDG\
/(1*7+ .RZDOVNL
5281'
NRPELQDFMD Z\UD H Z\PLHQLRQ\FK Z SRSU]HGQLFK SXQNWDFK
Składnia:
H[SU
H[SU H[SU 35,25 H[SU
H[SU H[SU H[SU H[SU
H[SU H[SU H[SU H[SU H[SU __ H[SU
Przykłady:
.RZDOVNL __ 1DXF]\FLHO
/(1*7+ 1RZDN
6457
OLVWD Z\UD H Z QDZLDVDFK
Składnia:
H[SU > H[SU@
3U]\NáDG\
.RZDOVNL 1RZDN %XU]\QVNL
/(1*7+ .RZDOVNL 6457
Wyra enia s u ywane w:
x li cie kolumn w rozkazie SELECT
x jako warunek w klauzulach WHERE i HAVING
x w klauzulach CONNECT BY, START WITH, ORDER BY
x klauzuli VALUE w rozkazie INSERT
x w klauzuli SET rozkazu UPDATE
8.8. Warunki
Warunkiem nazywamy ci g jednego lub wi cej wyra e i operatorów logicznych. Warunek jest zawsze obliczany
do warto ci TRUE lub FALSE. Warunki mog mieć siedem ró nych postaci:
x porównanie z wyra eniem lub wynikiem zapytania
H[SU! FRPSDULVRQ RSHUDWRU! H[SU!
H[SU! FRPSDULVRQ RSHUDWRU! TXHU\!
H[SU OLVW! HTXDO RU QRW! H[SU OLVW!
H[SU OLVW! HTXDO RU QRW! TXHU\!
x porównanie z dowolnym lub ze wszystkimi elementami listy lub zapytania
H[SU! FRPSDULVRQ! ^ $1< _ $// `
H[SU! > H[SU@
H[SU! FRPSDULVRQ! ^ $1< _ $// ` TXHU\!
H[SU OLVW! HTXDO RU QRW! ^ $1< _ $// `
H[SU OLVW! > H[SUBOLVW!@
H[SU OLVW! HTXDO RU QRW! ^ $1< _ $// ` TXHU\!
x sprawdzenie przynale no ci do listy lub zapytania
H[SU! >127@ ,1 H[SU! > H[SU!@
H[SU! >127@ ,1 TXHU\!
H[SU OLVW! >127@ ,1
H[SU OLVW! > H[SU OLVW!@
H[SU OLVW! >127@ ,1 TXHU\!
x sprawdzenie przynale no ci do zakresu warto ci
37
 H[SU! >127@ %(7:((1 H[SU! $1' H[SU!
x sprawdzenie czy warto ć jest równa NULL
H[SU! ,6 >127@ 18//
x sprawdzenie czy zapytanie zwróciło jakiekolwiek wiersze
(;,676 TXHU\!
NRPELQDFMD LQQ\FK ZDUXQNyZ SRGDQD ]JRGQLH ] SULRU\WHWDPL
FRQGLWLRQ!
127 FRQGLWLRQ!
FRQGLWLRQ! $1' FRQGLWLQ!
FRQGLWLRQ! 25 FRQGLWLRQ!
Przykłady:
1D]ZLVNR .RZDOVNL
SUDFRZQLF\ :\G]LDO :\G]LDO\ :\G]LDO
'DWDBXURG]HQLD ! -$1
=DZRG ,1 '\UHNWRU 8U]HGQLN ,QIRUPDW\N
3ODFD %(7:((1 $1'
38
9. STANDARDOWE FUNKCJE J ZYKA SQL
9.1. Funkcje numeryczne
Składnia Przeznaczenie Przykład
ABS(-15)
ABS(n) Zwraca warto ć absolutn liczby n
Wynik: 15
CEIL(15.7)
CEIL(n) Zwraca najmniejsz liczb całkowit wi ksz lub równ n
Wynik: 16
FLOOR(15.7)
FLOOR(n) Zwraca najwi ksz liczb całkowit mniejsz lub równ n
Wynik: 15
MOD(7, 5)
MOD(m, n) Zwraca reszt z dzielenia liczby m przez n
Wynik: 2
Zwraca liczb m podniesion do pot gi n. Liczba n musi być całkowita; POWER(2, 3)
POWER(m, n)
w przeciwnym wypadku wyst pi bł d. Wynik: 8
ROUND(16.167, 1)
Zwraca liczb n zaokr glon do m miejsc po przecinku. Jesli m jest
Wynik: 16.2
ROUND(n[, m]) pomini te, to przyjmuje si 0. Liczba m mo e być dodatnia lub ujemna
ROUND(16, 167, -1)
(zaokr glenie do odpowiedniej liczby cyfr przed przecinkiem)
Wynik: 20
Zwraca 0, je li n jest równe 0, -1 je li n jest mniejsze od 0, 1 je li n jest SIGN(-15)
SIGN(n)
wi ksze od 0 Wynik: -1
SQRT(25)
SQRT(n) Zwraca pierwiastek kwadratowy liczby n. Je li n<0 to wyst pi bł d
Wynik: 5
TRUNC(15.79, 1)
Zwraca m obci te do n miejsc po przecinku. Je li n nie jest podane, to
Wynik: 15.7
TRUNC(m[, n]) przyjmue si 0. Je li n jest ujemne to obcinane s cyfry przed
TRUNC(15.79, -1)
przecinkiem.
Wynik: 10
9.2. Funkcje znakowe
Składnia Przeznaczenie Przykład
CHR(65)
CHR(n) Zwraca znak o podanym kodzie
Wynik: "A"
Zwraca string, w którym ka de słowo ma du INITCAP('PAN JAN NOWAK')
INITCAP(string)
pierwsz liter , a pozostałe s małe. Wynik: "Pan Jan Nowak"
Zamienia wszystkie litery w podanym stringu na LOWER('PAN JAN NOWAK')
LOWER(string)
małe. Wynik: "pan jan nowak"
Zwraca string 1 uzupełniony do długo ci n
lewostronnie ci gami znaków ze stringu 2. Je li
LPAD('Ala ma ', kota*, 17)
LPAD(string1, n string2 nie jest podany to przyjmowana jest
Wynik:
[, string2]) spacja. Je li n jest mniejsze od długo ci string1,
"kota*kota*Ala ma "
to zwracane jest n pierwszych znaków z tekstu
string1.
Usuwa litery z tekstu string od lewej strony a
LTRIM('xxxXxxOstatnie słowo', 'x')
LTRIM(string do napotkania litery nie nale cej do tekstu
Wynik:
[, zbiór]) zbiór. Je li zbiór nie jest podany to
"XxxOstatnie słowo"
przyjmowany jest ci g pusty.
REPLACE('Jack & Jue', 'J', Bl')
REPLACE(string, search [, Zwraca string z zamienionym ka dym
Wynik:
replace]) wyst pieniem tekstu search na tekst replace.
"Black & Blue"
RPAD(string1, n Zwraca string 1 uzupełniony prawostronnie do RPAD('Ala ma ', 17, 'kota*')
39
[, string2]) długo ci n ci gami string2. Je li string2 nie jest Wynik:
podany, to przyjmuje si spacj , Je li n jest "Ala ma kota*kota*"
mniejsze od długo ci string1, to zwracane jest n
pierwszych znaków z tekstu string1.
Zwraca string1 z usuni tymi ostatnimi literami, RTRIM('Ostatnie słowoxxXxxx', 'x')
RTRIM(string
które znajduj si w stringu zbiór. Je li zbiór nie Wynik:
[, zbiór])
jest podany to przyjmowany jest ci g pusty "Ostatnie słowoxxX"
Zwraca ci g znaków reprezentuj cy wymow
słów wchodz cych w skład string1. Funkcja SELECT nazwisko FROM bibl
SOUNDEX(string) SOUNDEX mo e być u yta do porównywania WHERE SOUNDEX(nazwisko) =
słów zapisywanych w ró ny sposób, ale SOUNDEX ('Mickiewicz');
wymawianych tak samo.
Zwraca podci g z ci gu znaków string
zaczynaj cy si na znaku m i o długo ci n. Je li SUBSTR('ABCDE',2, 3)
SUBSTR(string, m
n nie jest podane, to zwracany jest podci g od Wynik:
[, n])
znaku m do ostatniego w string. Pierwszy znak "BCD"
wci gu ma numer 1.
TRANSLATE( 'HELLO! THERE!',
Zwraca string powstały po zamianie wszystkich '!', '-')
TRANSLATE( string, from, to)
znaków from na znak to. Wynik:
"HELLO- THERE-"
UPPER(
Zamienia wszystkie znaki z ci gu string na du e 'Jan Nowak')
UPPER(string)
litery. Wynik:
"JAN NOWAK"
Zwraca kod ASCII pierwszej litery w podanym ASCII('A')
ASCII(string)
ci gu znaków Wynik: 65
Zwraca pozycj m-tego wyst pienia string2 w
string1, je li szukanie rozpocz to od pozycji n.
INSTR(string1, string2 [, n [, INSTR( 'MISSISSIPPI', 'S', 5, 2)
Je eli m jest pomini te, to przyjmowana jest
m]]) Wynik: 7
warto ć 1. Je li n jest pomini te, przyjmowana
jest warto ć 1.
LENGTH('Nowak')
LENGTH(string) Zwraca długo ć podanego ci gu znaków.
Wynik: 5
9.3. Funkcje grupowe
Funkcje grupowe zwracaj swoje rezultaty na podstawie grupy wierszy a nie pojedynczych warto ci. Domy lnie
cały wynik jest traktowany jako jedna grupa. Klauzula GROUP BY z rozkazu SELECT mo e jednak podzielić wiersze
wynikowe na grupy.
Klauzula DISTINCT wybiera z grupy tylko pojedyncze warto ci (drugie i nast pne s pomijane). Klauzula ALL
powoduje wybranie wszystkich wierszy wynikowych do obliczenia wyniku. Wszystkie wymienione w tym
podrozdziale funkcje opuszczaj warto ci NULL z wyj tkiem COUNT(*). Wyra enia b d ce argumentami funkcji
mog być typu CHAR, NUMBER lub DATE.
Składnia Przeznaczenie Przykład
Zwraca warto ć redni ignoruj c SELECT AVG(placa) "Srednia" FROM
AVG( [DISTINCT | ALL] num)
warto ci puste pracownicy
Zwraca liczb wierszy, w których expr SELECT COUNT(nazwisko) "Liczba"
COUNT( [DISTINCT | ALL] expr)
nie jest równe NULL FROM pracownicy
Zwraca liczb wierszy w tabeli
SELECT COUNT(*) "Wszystko" FROM
COUNT(*) wł czaj c powtarzaj ce si i równe
pracownicy
NULL
MAX( [DISTINCT | ALL] expr)
Zwraca maksymaln warto ć SELECT MAX(Placa) "Max" FROM
40
wyra enia pracownicy
SELECT MIN(Placa) "Min" FROM
MIN( [DISTINCT | ALL] expr) Zwraca minimaln warto ć wyra enia
pracownicy
Zwraca odchylenie standardowe
SELECT STDDEV(Placa) "Odchylenie"
STDDEV( [DISTINCT | ALL] num) warto ci num ignoruj c warto ci
FROM pracownicy
NULL.
SELECT SUM(Placa) "Koszty osobowe"
SUM( [DISTINCT | ALL] num) Zwraca sum warto ci num.
FROM pracownicy
VARIANCE( [DISTINCT | ALL] Zwraca wariancj warto ci num SELECT VARIANCE(Placa)
num) ignoruj c warto ci NULL "Wariancja" FROM pracownicy
9.4. Funkcje konwersji
Funkcje konwersji słu do zamiany warto ci jednego typu na warto ć innego typu. Ogólnie nazwy funkcji
konwersji tworzone s według nast puj cego schematu: typTOtyp. Pierwszy typ jest typem, z którego wykonywana jest
konwersja, drugi jest typem wynikowym.
Składnia Przeznaczenie Przykład
SELECT nazwisko FROM pracownicy
Wykonuje konwersj ci gu znaków
CHARTOROWID (string) WHERE ROWID = CHARTOROWID
na ROWID
('0000000F.0003.0002')
Wykonuje konwersj pomi dzy
CONVERT(string SELECT CONVERT ('New WORD',
dwoma ró nymi implementacjami
[,dest_char_set [,source_char_set 'US7ASCII', 'WE8HP')
zestawu znaków. Zestawem
] ]) "Conversion" FROM DUAL
domy lnym jest US7ASCII.
Konwertuje ci g znaków zawieraj cy
INSERT INTO GRAPHICS
cyfry szesnastkowe na warto ć
HEXTORAW (string) (RAW_COLUMN) SELECT HEXTORAW
binarn , któr mo na umie cić w polu
('7D') FROM DUAL
typu RAW
Przekształca warto ć typu raw na
SELECT RAWTOHEX (RAW_COLUMN)
ROWTOHEX(raw) tekst zawieraj cy cyfry szesnastkowe
"Graphics" FROM GRAPHICS
odpowiadaj ce podanej liczbie.
Przekształca identyfikator wiersza na SELECT ROWID FROM GRAPHICS
ROWIDTOCHAR tekst. Wynik konwersji ma zawsze WHERE ROWIDTOCHAR(ROWID) LIKE
długo ć 18 znaków. '%F38%'
Konwertuje warto ć numeryczn na
znakow u ywaj c opcjonalnego
TO_CHAR(n [, fmt]) ci gu formatuj cego. Je li ci g SELECT TO_CHAR(17145, '$099,999')
(konwersja numeryczna) formatuj cy nie jest podany, to "Char" FROM DUAL
warto ć jest konwertowana tak, by
zawrzeć wszystkie cyfry znacz ce.
SELECT TO_CHAR(HIREDATE, 'Month
TO_CHAR(d [, fmt]) Konwertuje dat na tekst, u ywaj c
DD, YYYY') "New date format" FROM EMP
(konwersja daty) podanego formatu.
WHERE ENAME = 'SMITH'
Przekształca ci g znaków w dat .
U ywa danych aktualnych, je li nie INSERT INTO BONUS (BONUS_DATE)
mog być one odczytane z podanego SELECT TO_DATE ('January 15, 1989',
TO_DATE(string [, fmt])
tekstu. Do konwersji u ywany jest 'Month dd, YYYY')
podany ci g formatuj cy lub warto ć FROM DUAL
domy lna pstaci "DD-MON-YY"
UPDATE EMP
Przekształca tekst zawieraj cy zapis
SET SAL = SAL +
TO_NUMBER (string)
liczby na liczb
TO_NUMBER(
SUBSTR('$100 raise', 2, 3))
41
WHERE ENAME =
'BLAKE'
9.5. Funkcje operacji na datach
Składnia Przeznaczenie Przykład
SELECT ADD_MONTHS (HIREDATE,
12)
Zwraca padan dat powiekszon o
"Next year"
ADD_MONTHS (date, n) podan liczb miesi cy n. Liczba ta
FROM EMP
mo e być ujemna
WHERE ENAME =
'SMITH'
Zwraca dat b d c ostatnim dniem w SELECT LAST_DAY (SYSDATE) "Last"
LAST_DAY(date)
miesi cu zawartym w podanej dacie. FROM DUAL
Zwraca liczb miesi cy pomi dzy
datami date1 i date2. Wynik mo e być SELECT MONTHS_BETWEEN
MONTHS_BETWEEN (date1,
dodatni lub ujemny. Cz ć ułamkowa ('02-feb-86', '01-jan-86'))
date2)
jest cz ci miesi ca zawieraj cego 31 "Months"
dni.
Zwraca dat i czas w strefie czasowej
b, je li data i czas w strefie a s równe
date. Parametry a i b s wyra eniami
znakowymi i mog być jednym z:
AST, ADT - Atlantic Standard or
Daylight Time
BST, BDT - Bering Standard or
Daylight Time
CST, CDT - Central Standard or
SELECT TO_CHAR(
Daylight Time
NEW_TIME(TO_DATE(
EST, EDT - Eastern Standard or
NEW_TIME(date, a, b) '17:47', 'hh24:mi'), 'PST', 'GMT'),
Daylight Time
'hh24:mi') "GREENWICH TIME"
GMT - Greenwich Mean Time
FROM DUAL
HST, HDT- Alaska-Hawaii Standard
or Daylight Time
MST, MDT - Mountain Standard or
Daylight Time
NST - Newfoundland Standard Time
PST, PDT - Pacific Standard or
Daylight Time
YST, YDT - Yukon Standard or
Daylight Time
Zwraca dat pierwszego dnia tygodnia SELECT NEXT_DAY(
podanego w string, który jest '17-MAR-89', 'TUESDAY')
NEXT_DAY(date, string)
pó niejszy ni data date. Parametr "NEXT DAY"
string musi być poprawn nazw dnia. FROM DUAL
SELECT ROUND ( TO_DATE(
Zwraca dat zaokr glon do jednostki
'27-OCT-88'), 'YEAR')
ROUND(date [, fmt]) zaokr glania podanej w fmt.
"FIRST OF THE YEAR"
Domy lnie jest to najbli szy dzie .
FROM DUAL
Zwraca aktualny czas i dat . Nie
SYSDATE SELECT SYSDATE FROM DUAL
wymaga podania argumentów.
Zwraca dat obci t do jednostki SELECT TRUNC(
podanej w fmt. Domy lnie jest to TO_DATE('28-OCT-88', 'YEAR')
TRUNC(date [, fmt])
dzie , tzn. usuwana jest informacja o "First Of The Year"
czasie. FROM DUAL
42
W funkcjach ROUND i TRUNC mo na u ywać nast puj cych tekstów do identyfikacji jednostki zaokr glenia lub
obci cia:
CC, SCC wiek
SYYY, YYYY, YEAR,
rok (zaokr glenie w zwy od 1.07)
SYEAR, YYY, YY, Y
Q kwartał (zaokr glenie w gór od 16-tego drugiego miesi ca)
MONTH, MON, MM miesi c (zaokr glenie w gór od 16)
WW pierwszy tydzie roku
W pierwszy tydzie miesi ca
DDD, DD, J dzie
DAY, DY, D najbli sza niedziela
HH, HH12, HH24 godzina
MI minuta
9.6. Inne funkcje
Składnia Przeznaczenie Przykład
Znajduje najwi ksz z listy warto ci.
Wszystkie wyra enia pocz wszy od SELECT GREATEST ('Harry', 'Harriot',
GREATEST(expr [, expr] ...) drugiego s konwertowane do typu 'Harold')
pierwszego wyra enia przed "GREATEST" FROM DUAL
wykonaniem porównania.
Zwraca najmniejsz z listy warto ci.
Wszystkie wyra enia pocz wszy od
SELECT LEAST ('Harry', 'Harriot', 'Harold')
LEAST(expr [, expr] ...) drugiego s konwertowane do typu
"LEAST" FROM DUAL
pierwszego wyra enia przed
wykonaniem porównania.
SELECT ENAME
NVL(TO_CHAR(COMM),'NOT
Je li expr1 jest równe NULL, to zwraca
APPLICABLE')
NVL (expr1, expr2) expr2, w przeciwnym wypadku zwraca
"COMMISION"
expr1.
FROM EMP
WHERE DEPTNO = 30
Zwraca unikalny identyfikator
UID SELECT USER, UID FROM DUAL
u ytkownika wywołuj cego funkcj .
USER Zwraca nazw u ytkownika SELECT USER, UID FROM DUAL
9.7. Formaty zapisu danych
Formaty zapisu danych u ywane s w dwóch podstawowych celach:
x zmiany sposobu wy wietlania informacji w kolumnie;
x wprowadzanie danej zapisanej inaczej ni domy lnie
Formaty zapisu u ywane s w funkcjach TO_CHAR i TO_DATE.
9.8. Formaty numeryczne
Formaty numeryczne s u ywane w poł czeniu z funkcj TO_CHAR do przekształcenia warto ci numerycznej na
warto ć znakow .
U ycie formatu numerycznego powoduje zaokr glenie do podanej w nim liczby cyfr znacz cych.
43
Jesli warto ć numeryczna ma wi cej cyfr z lewej strony ni to zostało przewidziane, to warto ć ta zast powana jest
gwiazdk '*'.
Poni sza tabela przedstawia elementy, które mo e zawierać specyfikacja formatu numerycznego:
Element Przykład Opis
9 9999 Liczba '9' okre la szeroko ć wy wietlania
0 0999 Pokazuje wiod ce zera
$ $9999 Poprzedza warto ć znakiem '$'
B B9999 Wy wietla zera jako spacje (nie jako zera)
MI 9999MI Wy wietla '-' po warto ci ujemnej
PR 9999PR Wy wietla warto ć ujemn w nawiasach k towych '<', '>'
, (przecinek) 9,999 Wy wietla przecinek na podanej pozycji
. (kropka) 99.99 Wy wietla kropk na podanej pozycji
V 999V99 Mno y warto ć przez 10 , gdzie n jest liczb dziewi tek po 'V'
Wy wietla liczb w notacji wykładniczej (format musi zawierać dokładnie cztery
E 9.999EEEE
litery E)
Dla dat przechowywanych w postaci numerycznej. Wy wietla dat w formacie
DATE DATE
'MM/DD/YY'
9.9. Formaty dat
Formaty dat s u ywane w funkcji TO_CHAR w celu wy wietlenia daty. Mog być równie u yte w funkcji
TO_DATE w celu wprowadzenia daty w okre lonym formacie. Format standardowy, to 'DD-MON-YY'.
Elementy formatu dat przedstawia tabela:
Element Opis
SCC lub CC Wiek; 'S' poprzedza daty przed nasz er znakiem '-'
YYYY lub SYYYY Czterocyfrowy rok, 'S' poprzedza daty przed nasz er znakiem '-'
YYY, YY lub Y Ostatnie 3, 2 lub 1 cyfra roku
Y,YYY Rok z przecinkiem na podanej pozycji
SYEAR lub YEAR Rok przeliterowany. 'S' powoduje poprzedzenie daty przed nasz er znakiem '-'
BC lub AD Znak BC/AD (przed nasz er /naszej ery)
B.C. lub A.D Znak BC/AD z kropkami
Q Kwartał roku (1, 2, 3 lub 4)
MM Miesi c (01-12)
MONTH Nazwa miesi ca wyrównana do 9 znaków za pomoc spacji
MON Trzyliterowy skrót nazwy miesi ca
WW Tydzie roku (1-52) (tydzie zaczyna si w pierwszy dniu roku i trwa 7 dni)
W Tydzie miesi ca (1-5) (tydzie zaczyna si w pierwszym dniu miesi ca i trwa 7 dni)
DDD Dzie roku (1-366)
DD Dzie miesi ca (1-31)
D Dzie tygodnia (1-7)
DAY Nazwa dnia wyrównana do 9 znaków za pomoc spacji
44
DY Trzyliterowy skrót nazwy dnia
AM lub PM Wska nik pory dnia
A.M. lub P.M. Wska nik pory dnia z kropkami
HH lub HH12 Godzina (1-12)
HH24 Godzina (1-24)
MI Minuta (0-59)
SS Sekunda (0-59)
SSSS Sekundy po północy (0-86399)
/ ., Znaki przestankowe umieszczane w wyniku
"..." Ci g znaków umieszczany w wyniku
Dodatkowo w ci gu znaków okre laj cych format mo na u yć:
x FM - "Fill Mode" przeł cznik wł czaj cy/wył czaj cy wypełnianie tekstów spacjami i liczb zerami;
x TH - dodany po kodzie pola powoduje wy wietlanie liczby porz dkowej np. 4 dla liczby 4;
x SP - dodany po kodzie pola powoduje, e jest ono literowane
x SPTH lub THSP - poł czenie SP i TH.
45
10. PROGRAMOWANIE PROCEDURANE - PL/SQL
10.1. Wprowadzenie
Rozkazy j zyka SQL s niewystarczaj ce do tworzenia efektywnych systemów baz danych, a w szczególno ci do
kontroli warunków integralno ci bazy w momencie wprowadzania do niej danych. Dlatego firma Oracle wprowadziła
rozszerzenia proceduralne do swojej implementacji j zyka SQL i tak powstały j zyk nazwała PL/SQL.
PL/SQL pozwala wykorzystywać prawie wszystkie operacje standardowego SQL. Wyj tkiem s tu operacje
definiowania danych (ALTER, CREATE i RENAME) oraz niektóre rozkazy kontroli danych jak CONNECT, GRANT
i REVOKE.
Kod napisany w PL/SQL składa si z rozkazów standardowego SQL oraz rozszerze proceduralnych. Mo liwe jest
stosowanie wszystkich standardowych funkcji SQL w rozkazach SQL oraz prawie wszystkich (tj. z wył czeniem
funkcji grupowych) w rozszerzeniach. Ka dy rozkaz PL/SQL ko czy si rednikiem.
PL/SQL pozwala na definiowanie zmiennych. Zmienne słu do przechowywania wyników zapyta i oblicze w
celu ich pó niejszego wykorzystania. Jednak wszystkie zmienne musz być zadeklarowane przed ich u yciem. Ka da
zmienna posiada typ. Typy zmiennych s takie same jak typy stosowane w SQL'u. Zmienne deklaruje si pisz c nazw
zmiennej a nast pnie jej typ, np.:
SUHPLD 180%(5
Warto ć do zmiennej przypisuje si za pomoc operatora przypisania ":=", np.:
SRGDWHN FHQD VWRSD
Druga mo liwo ć nadania warto ci zmiennej to u ycie rozkazu SELECT lub FETCH do wpisania warto ci do
zmiennej:
6(/(&7 SODFD ,172 SODFDBDNWXDOQD )520 SUDFRZQLF\
:+(5( QD]ZLVNR 1RZDN
PL/SQL posiada zmienne strukturowe nazywane rekordami, które podzielone s na pola.
Istnieje mo liwo ć deklarowania stałych. Deklaracja taka jest podobna do deklaracji zmiennej, ale konieczne jest
dodatkowo u ycie słowa CONSTANT i natychmiastowe przypisanie warto ci do zmiennej. Deklaracj stałej pokazuje
nast puj cy przykład:
VWRSDBSUHPLL &2167$17 180%(5
Wszystkie obiekty posiadaj atrybuty. Jednym z nich jest typ (zarówno zmiennej jak i kolumny). Mo liwe jest
u ycie zapisu %TYPE w celu zapisania typu np. w deklaracji zmiennej. Zapis taki pozwala zadeklarować zmienn o
takim samym typie jak inna zmienna lub kolumna (nale y przy tym zauwa yć, e typ ten nie jest znany osobie pisz cej
program):
W\WXO ERRNV W\WXO 7<3(
Mo liwe jest równie zadeklarowanie rekordu odpowiadaj cego jednemu wierszowi tabeli. W tym celu nale y u yć
konstrukcji %ROWTYPE.
W PL/SQL mo na stosować nast puj ce operatory porówna :
=, !=, <, >, >=, <=. Mog one działać na operandach ró nych typów: numerycznym, daty i ci gach znaków
(wykonuj wtedy porównanie leksykalne).
10.2. Struktura bloku
Kod j zyka PL/SQL jest podzielony na bloki. Blok ma nast puj c struktur :
'(&/$5(
deklaracje
%(*,1
rozkazy wykonywalne
(;&(37,21
obsługa sytuacji wyj tkowych
(1'
Ka dy blok mo e zawierać inne bloki, tzn. bloki mog być zagnie d one. W PL/SQL identyfikator jest nazw
dowolnego obiektu (tj. stałej, zmiennej, rekordu, kursora lub wyj tku). Nie jest mo liwa dwukrotna deklaracja tego
samego identyfikatora w jednym bloku. Mo na jednak zadeklarować te same identyfikatory w dwóch ró nych blokach.
Oba takie obiekty s ró ne i jakakolwiek zmiana w jednym z nich nie powoduje zmiany w drugim. Zakres
obowi zywania identyfikatora okre la, w którym bloku mog wyst pić do niego odwołania. Blok ma dost p tylko do
obiektów lokalnych i globalnych. Identyfikatory zadeklarowane w bloku s lokalne dla tego bloku i globalne dla
wszystkich bloków w nim zawartych (podbloków). Identyfikatory globalne mog zostać zredefiniowane w podblokach,
46
co powoduje, e obiekt lokalny ma pierwsze stwo przed globalnym. Dost p do obiektu globalnego jest mo liwy w tym
przypadku tylko wtedy, gdy u yta zostanie nazwa odpowiedniego bloku. Blok nie ma dost pu do obiektów
zadeklarowanych w innych blokach na tym samym poziomie zagnie d enia, poniewa nie s one ani lokalne, ani
globalne w tym bloku.
10.3. Procedury i funkcje
PL/SQL w wersji 2.0 pozwala na definiowanie funkcji i procedur.
Składnia definicji procedury jest nast puj ca:
352&('85( QDPH > SDUDPHWHU > SDUDPHWHU@ @ ,6
>ORFDO GHFODUDWLRQV@
%(*,1
H[HFXWDEOH VWDWHPHQWV
>(;&(37,21
H[FHSWLRQ KDQGOHUV@
(1' >QDPH@
Składnia definicji parametrów jest nast puj ca:
YDUBQDPH >,1 _ 287 _ ,1 287@ GDWDW\SH
>^ _ '()$8/7 ` YDOXH@
Okre lenie typu dla danego parametru nie mo e zawierać ogranicze , tzn. nie jest mo liwe zapisanie np. INT(5).
Procedura składa si z dwóch cz ci - nagłówka i ciała. Nagłówek rozpoczyna si słowem PROCEDURE i ko czy
na nazwie procedury lub li cie parametrów. Deklaracja parametrów jest opcjonalna. Procedury bezparametrowe
zapisuje si bez nawiasów. Ciało procedury rozpoczyna si słowem IS a ko czy słowem END (po którym mo e
opcjonalnie wyst pić nazwa procedury). Cz ć deklaracyjna (pomi dzy słowem IS i BEGIN) zawiera deklaracje
obiektów lokalnych. W tym przypadku nie u ywa si słowa DECLARE. Cz ć wykonywalna (pomi dzy BEGIN a
EXCEPTION lub END) zawiera rozkazy j zyka PL/SQL. W tej cz ci musi wyst pić conajmniej jeden rozkaz.
Definicj procedury zwi kszaj c płac wybranego pracownika pokazuje przykład:
352&('85( ]ZLHNV] SUDFBLG ,17(*(5 NZRWD 5($/ ,6
SODFDBDNWXDOQD 5($/
%(*,1
83'$7( SUDFRZQLF\ 6(7 SODFDBSRGVWDZRZD
SODFDBSRGVWDZRZD NZRWD
:+(5( SUDFBLG LGBSUDFRZQLND
(1' ]ZLHNV]
Zadaniem funkcji jest obliczenie warto ci. Definicja funkcji jest taka sama jak procedury z tym wyj tkiem, e
funkcja posiada klauzul RETURN.
)81&7,21 QDPH > DUJXPHQW > DUJXPHQW@ @
5(7851 GDWDW\SH ,6
>ORFDO GHFODUDWLRQV@
%(*,1
H[HFXWDEOH VWDWHPHQWV
>(;&(37,21
H[FHSWLRQ KDQGOHUV@
(1' >QDPH@
Wewn trz funkcji musi pojawić si przynajmniej jeden rozkaz RETURN. Rozkaz RETURN natychmiast ko czy
wykonanie funkcji i zwraca sterowanie do modułu wywołuj cego. W przypadku funkcji musi wyst pić przynajmniej
jeden rozkaz RETURN, w którym musi być wyra enie. Wynik tego wyra enia musi mieć taki typ jak podany w
nagłówku. Je li funkcja nie ko czy si rozkazem RETURN, to PL/SQL zgłosi odpowiedni wyj tek. W przypadku
procedury rozkaz RETURN nie mo e zawierać adnego wyra enia.
10.4. Kursory
W celu wykonania rozkazu SQL system tworzy pewien obszar roboczy nazywany przestrzeni kontekstu. W
przestrzeni tej przechowywane s informacje konieczne do wykonania rozkazu. PL/SQL pozwala nazwać przestrze
kontekstu i odwoływać si do zawartych w niej danych za pomoc mechanizmu nazywanego kursorem. PL/SQL u ywa
dwóch typów kursorów:
x jawnych - u ytkownik mo e w sposób jawny utworzyć kursor dla zapyta , których wynikiem jest wiele
wierszy i wykonywać operacje na tych wierszach (najcz ciej za pomoc rozkazu FOR);
x niejawnych - PL/SQL automatycznie tworzy kursor dla wszystkich pozostałych operacji.
47
Je li zapytanie zwraca wiele wierszy, to mo liwe jest utworzenie kursora, który b dzie umo liwiał dost p do
pojedynczych wierszy ze zwracanej listy. Kursor definiuje si wcz ci deklaracji bloku PL/SQL przez nazwanie go i
specyfikacj zapytania. Sposób deklaracji kursora pokazuje przykład:
'(&/$5(
&85625 SUDFBNXUVRU ,6 6(/(&7 QD]ZLVNR Z\G]LDá
)520 SUDFRZQLF\
:+(5( SáDFD !
%(*,1
Sama deklaracja nie powoduje wykonania wyst puj cego w niej zapytania. Do tego konieczne jest otwarcie kursora,
którego mo na dokonać instrukcj OPEN w nast puj cy sposób:
23(1 SUDFBNXUVRU
W celu odczytania pojedynczego wiersza z kursora konieczne jest nast pnie u ycie rozkazu FETCH. Ka dy kolejny
rozkaz FETCH dla danego kursora powoduje odczytanie kolejnego wiersza. Rozkaz FETCH mo e być u yty w
nast puj cy sposób:
)(7&+ SUDFBNXUVRU ,172 SUDFBQD]Z SUDFBZ\G]
Po zako czeniu pracy z kursorem konieczne jest poinformowanie o tym systemu w celu zwolnienia zasobów.
Dokonuje si tego rozkazem CLOSE, np.:
&/26( SUDFBNXUVRU
Ka dy kursor posiada pewne atrybuty, których warto ci informuj o jego stanie. Nazwy atrybutów poprzedzone s
znakiem '%' i wpisywane bezpo rednio po nazwie kursora. Zdefiniowano nast puj ce atrybuty kursorów:
x %NOTFOUND - TRUE, je li ostatni rozkaz FETCH zako czył si niepowodzeniem z powodu braku
wierszy
x %FOUND - TRUE, je li ostatni rozkaz FETCH zako czył si sukcesem
x %ROWCOUNT - liczba wierszy w kursorze (po otwarciu kursora)
x %ISOPEN - TRUE, je Å‚i kursor jest otwarty
Nast puj ce przykłady pokazuj sposób u ycia atrybutów kursora:
/223
)(7&+ SUDFBNXUVRU ,172 SUDFBQD]Z SUDFBZ\G]
,) SUDFBNXUVRU 52:&2817 ! 7+(1
ZLHFHM QL
(;,7 :+(1 SUDFBNXUVRU 127)281'
(1' /223
10.5. Rekordy
W j zyku PL/SQL rekordem nazywana jest zmienna zło ona, b d ca grup zmiennych elementarnych. Ka da
zmienna elementarna w rekordzie nazywana jest polem. Rekordy s u ywane najcz ciej do przechowywania
zawarto ci pojedynczego wiersza w bazie danych. PL/SQL pozwala definiować rekordy odpowiadaj ce pojedynczym
wierszom w tabeli, widoku lub kursorze, nie pozwala jednak na definiowanie typów poszczególnych pól.
Deklarowanie rekordu najlepiej jest wyja nić na przykładzie. Je li wyst piła deklaracja kursora w tabeli
pracownicy, który zwraca nazwisko, wydział i dat zatrudnienia, to mo liwe jest zadeklarowanie odpowiedniego
rekordu za pomoc atrybutu %ROWTYPE:
'(&/$5(
&85625 SUDFBNXUVRU ,6
6(/(&7 QD]ZLVNR Z\G]LDO GDWDB]DWUXGQLHQLD
)520 SUDFRZQLF\
SUDFBUHN SUDFBNXUVRU 52:7<3(
)(7&+ SUDFBNXUVRU ,172 SUDFBUHN
Rekord mo e być tworzony nie tylko za pomoc kursora, ale równie za pomoc nazwy tabeli w nast puj cy
sposób:
QD]ZDBUHNRUGX QD]ZDBWDEHOL 52:7<3(
Rzeczywist deklaracj pokazuje przykład:
'(&/$5(
48
SUDFBUHN SUDFRZQLF\ 52:7<3(
%(*,1
(1'
Dost p do pola rekordu mo liwy jest za pomoc nazwy tego rekordu i poprzedzonej kropk nazwy pola:
QD]ZDBUHNRUGX QD]ZDBSROD
Aby wi c dodać pojedyncze wynagrodzenie do sumy (przy u ycia zdefiniowanego wcze niej rekordu prac_rek)
mo na napisać:
VXPD VXPD SUDFBUHN SODFD
Mo liwe jest przypisanie warto ci do pola rekordu lub rekordu jako cało ci. Nale y jednak pami tać, e rekord jest
zmienn i zmiana warto ci jego pól nie powoduje zmiany warto ci odpowiedniego wiersza w bazie danych.
Przypisania warto ci do pola rekordu mo na dokonać za pomoc operatora przypisania ':=' w nast puj cy sposób:
QD]ZDBUHNRUGX QD]ZDBSROD SOVTOBZ\UD HQLH
U ycie tej konstrukcji ilustruje przykład:
SUDFBUHN QD]ZLVNR 833(5 SUDFBUHN QD]ZLVNR
Przypisanie zawarto ci całego mo liwe jest na dwa sposoby:
x przypisanie zawarto ci jednego rekordu do drugiego (deklaracje obu rekordów musz odwoływać si do
tego samego kursora lub tabeli;
x wstawienie warto ci do rekordu rozkazem SELECT ... INTO lub FETCH ... INTO
U ycie tych dwóch operacji ilustruje przykład:
'(&/$5(
SUDFBUHN SUDFRZQLF\ 52:7<3(
SUDFBUHN SUDFRZQLF\ 52:7<3(
%(*,1
6(/(&7 QD]ZLVNR LPLH Z\G]LDO SODFDBSRGVWDZRZD
,172 SUDFBUHN )520 SUDFRZQLF\
:+(5( Z\G]LDO
SUDFBUHN SUDFBUHN
(1'
PL/SQL niejawnie deklaruje rekord w p tli FOR dla kursora. Sytuacj t ilustruje przykład:
'(&/$5(
&85625 SUDFBNXUVRU ,6
6(/(&7 QD]ZLVNR LPLH Z\G]LDO SODFDBSRGVWDZRZD
)520 SUDFRZQLF\
%(*,1
)25 SUDFRZQLN ,1 SUDFBNXUVRU /223
VXPD VXPD SUDFRZQLN SODFDBSRGVWDZRZD
(1' /223
Niejawnie deklarowanym rekordem jest tu zmienna o nazwie pracownik. Zmienna ta jest automatycznie
deklarowana tak, jakby wyst piła jawna deklaracja postaci nazwa_kursora%ROWTYPE.
10.6. Obsługa bł dów
Informacje podstawowe
Bł dy podczas wykonania programu powodowane s wieloma ró nymi przyczynami. W ród nich mo na wymienić
nast puj ce: bł dy projektowe, bł dy kodowania, uszkodzenia sprz tu, niewła ciwe dane itp. Nie jest mo liwe
przewidzenie wszystkich mo liwych bł dów, mo na jednak zaplanować obsług niektórych z nich. W starszkych
j zykach programowania bł d taki jak "Przepełnienie stosu" powodował zgłoszenie komunikatu i zako czenie
wykonania programu. W nowoczesnych j zykach (C++, Java, PL/SQL) zmieniło si podej cie do obsługi bł dów.
J zyki te udost pniaj bowiem mechanizm nazywany obsług wyj tków, który pozwala zdefiniować akcj
wywoływan w momencie wyst pienia bł du i dalej kontynuować wykonanie programu.
Wyj tkiem nazywamy spełnienie warunków wyst pienia bł dów. Wyj tki dziel si na predefiniowane (przez
twórców j zyka) i definiowane przez u ytkownika. Przykładami wyj tków predefiniowanych mog być: "Out of
memory", "Division by zero". W j zyku PL/SQL u ytkownik mo e definiować wyj tki w cz ci deklaracyjnej bloku
PL/SQL. Przykładowo mo liwe jest zdefiniowanie wyj tku "Płaca poni ej minimalnej", aby wskazać, e proponowana
płaca jest zbyt niska. Gdy zachodzi bł d, to wyj tek jest wywoływany (raise), tzn. wykonywanie programu zostaje
49
przerwane i sterowanie jest przekazywane do odpowiedniego fragmentu programu, którego zadaniem jest obsługa
danego wyj tku (funkcji obsługi wyj tku). Wyj tki predefiniowane s wykrywane i wywoływane automatycznie.
Wyj tki u ytkownika musz być wywołane jawnie za pomoc rozkazu RAISE. W momencie zaistnienia wyj tku
wykonanie aktualnego bloku ko czy si , nast pnie wywołuje si funkcj obsługi tego wyj tku i sterowanie jest
zwracane do nast pnej instrukcji w bloku zawieraj cym blok, w którym wyst pił wyj tek. Je li taki blok nie istnieje to
sterowanie jest zwracane do systemu.
Przykładowy fragment programu zawieraj cy wyj tek "dzielenie przez zero" (ZERO_DIVIDE) oraz jego obsług
obliczaj cy wska nik giełdowy C/Z:
'(&/$5(
F]BZVN 180%(5
%(*,1
6(/(&7 FHQD ]\VN )520 DNFMH
:+(5( QD]ZD $%& PR H Z\ZRáDü Z\M WHN
=(52B',9,'(
,16(57 ,172 LQIRUPDFMH QD]ZD FB]
9$/8(6 $%& F]BZVN
&200,7
(;&(37,21
:+(1 =(52B',9,'( 7+(1
,16(57 ,172 LQIRUPDFMH QD]ZD FB]
9$/8(6 $%& 18//
&200,7
:+(1 27+(56 7+(1
52//%$&.
(1'
Posługiwanie si wyj tkami ma wiele zalet. Za pomoc uprzednio stosowanych technik kontrola wyst pienia
bł dów był bardzo zło ona i prowadziła do znacznego skomplikowania kodu programu. W szczególno ci konieczne
mogło być sprawdzanie poprawno ci wykonania ka dego rozkazu:
%(*,1
6(/(&7
NRQWUROD Eá GX EUDN GDQ\FK
6(/(&7
NRQWUROD Eá GX EUDN GDQ\FK
6(/(&7
NRQWUROD Eá GX EUDN GDQ\FK
(1'
Ponadto kod obsługi bł du nie był odseparowany od kodu wykonywanego normalnie, co zmniejszało przejrzysto ć
programu i powodowało, e algortymy stawały si nieczytelne.
Ten sam problem mo na znacznie pro ciej i łatwiej rozwi zać za pomoc wyj tków:
%(*,1
6(/(&7
6(/(&7
6(/(&7
(;&(37,21
:+(1 12B'$7$B)281' 7+(1
REVáXJD ZV]\VWNLFK Eá GyZ EUDN GDQ\FK
(1'
Wyj tki nie tylko zwi kszaj czytelno ć programu i upraszczaj jego konstrukcj . Zapewniaj równie , e
wszystkie bł dy zostan obsłu one. Je li odpowiedniej funkcji nie ma w aktualnym bloku, to wyj tek jest
przekazywany do bloku nadrz dnego, a do znalezienia funkcji obsługi lub powrotu do systemu.
Wyj tki predefiniowane
Twórcy j zyka PL/SQL zdefiniowali zestaw wyj tków, zwi zanych z systemem zarz dzania baz danych i
j zykiem. Wyj tki te wywoływane s automatycznie w momencie zaj cia odpowiednich warunków. Wybrane z nich
przedstawione s poni ej:
x CURSOR_ALREADY_OPEN - wywoływany w przypadku próby otwarcia kursora ju otwartego;
x DUP_VAL_ON_INDEX - wywoływany w przypadku próby wykonania rozkazu INSERT lub UPDATE,
który spowodowałby utworzenie dwóch takich samych wierszy w indeksie zadeklarowanym jako
UNIQUE;
50
x INVALID_CURSOR - wywoływany w przypadku próby dost pu do nieprawidłowego kursora (np. nie
otwartego);
x INVALID_NUMBER - wywoływany w przypadku próby wykonania konwersji do typu numerycznego z
tekstu, który nie reprezentuje liczby;
x NO_DATA_FOUND - wywoływany wtedy, gdy rozkaz SELECT powinien zwrócić jeden wiersz a nie
zwraca adnego (np. SELECT ... INTO);
x STORAGE_ERROR - wywoływany w przypadku braku wolnej pami ci lub uszkodzenia zawarto ci
pami ci;
x TOO_MANY_ROWS - wywoływany w przypadku, gdy rozkaz SELECT zwraca wi cej ni jeden wiersz, a
oczekiwany jest tylko jeden (np. SELECT ... INTO);
x VALUE_ERROR - wywoływany w przypadku przypisania złej warto ci do zmiennej lub pola;
x ZERO_DIVIDE - próba dzielenia przez zero;
Obsługa wyj tków
Aby obsłu yć ("złapać") wyj tek konieczne jest napisane własnej funkcji obsługi tego wyj tku. Dokonuje si tego w
cz ci bloku PL/SQL rozpoczynaj cej si słowem kluczowym EXCEPTION, które wyst puje zawsze na ko cu bloku.
Ka da funkcja obsługi wyj tku składa si ze słowa WHEN, po którym podaje si nazw wyj tku oraz słowa THEN, po
którym wyst puje ci g instrukcji wykonywanych w momencie zaj cia podanego bł du. Funkcja obsługi wyj tku
ko czy wykonanie bloku, w zwi zku z czym nie jest mo liwy powrót do miejsca, w którym bł d wyst pił. Opcjonalne
słowo OTHERS (zamiast nazwy wyj tku) pozwala zdefiniować funkcj obsługi wszystkich pozostałych wyj tków (tzn.
nie wymienionych wcze niej). Ostatecznie cz ć EXCEPTION wygl da nast puj co:
(;&(37,21
:+(1 7+(1
REVáXJD Z\M WNX
:+(1 7+(1
REVáXJD Z\M WNX
:+(1 7+(1
REVáXJD Z\M WNX
:+(1 27+(56 7+(1
REVáXJD SR]RVWDá\FK Z\M WNyZ
(1'
Je li zachodzi potrzeba przypisania tej samej akcji ró nym wyj tkom, to mo na nazwy tych wyj tków wypisać w
klauzuli WHEN oddzielaj c słowem OR:
:+(1 RYHUBOLPLW 25 XQGHUBOLPLW 25 9$/8(B(5525 7+(1
Nie mo na jednak u yć słowa OTHERS w takiej li cie. Słowo OTHERS zawsze musi wyst pić oddzielnie. Nale y
pami tać równie , e dla jednego wyj tku mo e być zdefiniowana tylko jedna funkcja obsługi w danym bloku.
W funkcjach obsługi wyj tków maj zastosowanie normalne reguły przesłaniania tzn. widoczne s tylko zmienne
globalne lub lokalne.
Wyj tki zdefiniowane przez u ytkownika
J zyk PL/SQL pozwala u ytkownikowi na definiowanie swoich własnych wyj tków.Wyj tki takie musz być
jawnie zadeklarowane i w przypadku zaj cia odpowiednich warunków, jawnie wywołane za pomoc rozkazu RAISE.
Wyj tki deklaruje si podobnie jak zmienne, z t ró nic , e zamiast nazwy typu wyst puje słowo EXCEPTION.
Deklarowanie wyj tku ilustruje nast puj cy przykład:
'(&/$5(
RYHUIORZ (;&(37,21
UHVXOW 180%(5
%(*,1
(1'
Nale y zwrócić uwag , e wyj tek w j zyku PL/SQL nie jest obiektem (w przeciwie stwie do zmiennych), ale
informacj o spełnieniu pewnych okre lonych warunków. W zwi zku z tym do wyj tku nie jest mo liwe przypisanie
adnej warto ci, ani skojarzenie z nim dodatkowej informacji. Wyj tek nie mo e być równie u ywany w rozkazach
SQL.
Nie jest mo liwa deklaracja tego samego wyj tku dwa razy w tym samym bloku. Mo na jednak zadeklarować ten
sam wyj tek w ró nych blokach.
51
Jak podano wcze niej wyj tki predefiniowane wywoływane s przez system automatycznie. Wyj tki zdefiniowane
przez u ytkownika, musz być przez niego wywołane. Słu y do tego rozkaz RAISE. U ycie tego rozkazu ilustruje
przykład:
'(&/$5(
EUDNBF]HVFL (;&(37,21
OLF]EDBF]HVFL 180%(5
%(*,1
,) OLF]EDBF]HVFL 7+(1
5$,6( EUDNBF]HVFL
(1' ,)
(;&(37,21
:+(1 EUDNBF]HVFL 7+(1
REVáXJD Eá GX EUDN F] FL
(1'
Mo liwe jest równie jawne (za pomoc rozkazu RAISE) wywoływanie predefiniowanych wyj tków:
5$,6( ,19$/,'B180%(5
Czasami istnieje konieczno ć powtórnego wywołania wyj tku z funkcji, która go obsługuje, w celu przekazania go
do bloku nadrz dnego. Przykładem mo e tu być wycofanie transakcji w bloku zagnie d onym i zgłoszenie informacji o
bł dzie w bloku nadrz dnym. W zwi zku z tym mo liwe jest u ycie rozkazu RAISE w funkcji obsługi wyj tku. Nale y
pami tać, e wyj tki zgłoszone w funkcji obsługi innego wyj tku s zawsze przekazywane do bloku nadrz dnego i tam
wyszukiwana jest odpowiednia funkcja obsługi zgodnie z zasadami opisanymi wcze niej. Podobnie wyj tki zgłaszane
wcz ci deklaracyjnej przekazywane s do bloku nadrz dnego i tam podlegaj przetwarzaniu.
52
11. ROZKAZY J ZYKA PL/SQL
11.1. Rozkaz OPEN
Rozkaz OPEN wykonuje zapytanie skojarzone z jawnie zadeklarowanym kursorem i alokuje niezb dne zasoby
potrzebne do wykonywania dalszych operacji. Kursor ustawiany tu przed pierwszym wierszem wyniku zapytania.
Składnia:
23(1 FXUVRUBQDPH
> LQSXWBSDUDPHWHU > LQSXWBSDUDPHWHU@ @
Parametry:
x cursor_name - nazwa kursora uprzednio zadeklarowanego, który nie jest aktualnie otwarty.
x input_parameter - wyra enie j zyka PL/SQL, które przekazywane jest do kursora. Jest ono najcz ciej
u ywane do wykonania zapytania (najcz ciej stosowane jest w klauzuli WHERE).
Parametry w rozkazie OPEN mog być u yte tylko wtedy, gdy odpowiednia ilo ć parametrów została podana w
deklaracji kursora. Ilo ć parametrów w isntrukcji OPEN musi być równa ilo ci parametrów w deklaracji kursora.
Parametry instrukcji OPEN słu tylko i wył cznie do wczytywania danych do kursora i nie mog być stosowane w
celu pobrania ich z kursora.
Przyporz dkowanie parametrów aktualnych (w instrukcji OPEN) do parametrów formalnych (w deklaracji kursora)
mo e odbywać si na dwa sposoby:
x przyporz dkowanie przez pozycj
x przyporz dkowanie przez nazw
W pierwszym przypadku warto ć wyra enia na odpowiedniej pozycji w instrukcji OPEN jest przyporz dkowywana
parametrowi znajduj cemu si na tej samej pozycji w deklaracji kursora. W drugim przypadku parametry mog być
podane w dowolnej kolejno ci, ale ka de wyra enie musi być poprzedzone nazw parametru formalnego i znakami '=>'.
Sposób u ycia obu mo liwo ci ilustruj przykłady:
'(&/$5(
&85625 SUDFBNXU QD]Z &+$5 Z\G] 180%(5 ,6
%(*,1
23(1 SUDFBNXU .RZDOVNL
23(1 SUDFBNXU Z\G] ! QD]Z ! 1RZDN
(1'
Mo na u ywać równocze nie przyporz dkowania przez pozycj i przez nazw , nale y wtedy jednak pami tać, e
parametry przyporz dkowywanej przez pozycj musz wyst pić przed parametrami przyporz dkowywanymi przez
nazw .
11.2. Rozkaz CLOSE
Rozkaz CLOSE słu y do zamkni cia aktualnie otwartego kursora. Ka dy kursor przed ponownym otrwarciem musi
zostać zamkni ty. Rozkaz CLOSE zwalnia wszystkie zasoby przydzielone do obsługi kursora.
Składnia:
&/26( FXUVRUBQDPH
Parametry:
FXUVRUBQDPH QD]ZD DNWXDOQLH RWZDUWHJR NXUVRUD
11.3. Rozkaz FETCH
Rozkaz FETCH zwraca nast pny wiersz danych z aktywnego zbioru (danych spełniaj cych warunek rozkazu
SELECT w kursorze). Odczytane informacje przechowywane s w zmiennych. Zwrócone dane odpowiadaj zawarto ci
kolejnych kolumn w aktualnym wierszu.
Składnia:
)(7&+ FXUVRUBQDPH ,172
^ UHFRUGBQDPH _
YDULDEOHBQDPH > YDULDEOHBQDPH@ `
Parametry:
53
x cursor_name - nazwa aktualnie otwartego kursora.
x variable_name - prosta zmienna, do której zostan zapisane dane. Wszystkie zmienne na li cie musz być
uprzednio zadeklarowane. Dla ka dej kolumny w kursorze, musi wyst pić odpowiadaj ca jej zmienna. W
dodatku typy kolumn musz być takie same jak odpowiadaj ce im typy zmiennych lub konwertowalne do
nich.
x record_name - nazwa zmiennej b d cej rekordem (deklarowanej z u yciem atrybutu %ROWTYPE).
Przykład:
23(1 SUDFBNXUVRU
/223
)(7&+ SUDFBNXUVRU ,172 SUDFBUHN
(;,7 :+(1 SUDFBNXUVRU 127)281'
(1' /223
11.4. Rozkaz SELECT ... INTO
Rozkaz SELECT ... INTO odczytuje informacje z bazy danych i zapisuje je do zmiennych. W j zuku PL/SQL
standardowy (z SQL) rozkaz SELECT został rozszerzony o klauzul INTO. Aby rozkaz ten działał poprawnie
konieczne jest by SELECT zwracał tylko jeden wiersz (w przypadku wielu wierszy nale y zadeklarować kursor i za
jego pomoc odczytywać dane).
Składnia rozkazu SELECT z klauzul INTO:
6(/(&7 VHOHFWBOLVWBLWHP > VHOHFWBOLVWBLWHP@ ,172
^ UHFRUGBQDPH _
YDULDEOHBQDPH > YDULDEOHBQDPH@ `
UHVWBRIBVHOHFWBVWDWHPHQW
Parametry:
x Zobacz opis rozkazu FETCH.
Przykład:
6(/(&7 QD]ZLVNR SODFD ,172 SQD]Z SODFBVXP
)520 SUDFRZQLF\
:+(5( SUDFRZQLNBQU
11.5. Rozkaz IF
Rozkaz IF pozwala na warunkowe wykonywanie rozkazów.
Składnia:
,) SOVTOBFRQGLWLRQ 7+(1 VHTBRIBVWDWHPHQWV
>(/6(,) SOVTOBFRQGLWLRQ 7+(1 VHTBRIBVWDWHPHQWV@
>(/6( VHTBRIBVWDWHPHQWV@
(1' ,)
Parametry:
x plsql_condition - warunek (wyra enie obliczane do warto ci logicznej)
x seq_of_statements - ci g instrukcji, które maj być wykonane w razie spełnienia (b d nie spełnienia)
podanego warunku
Opis:
Rozkaz IF pozwala uzale nić wykonanie rozkazów od wyników obliczania warunku (lub warunków). Je li pierwszy
z warunków jest prawdziwy, to wykonywany jest ci g instrukcji po THEN, a do napotkania odpowiedniego ELSEIF,
ELSE lub END IF, a nast pnie sterowanie przekazywane jest do najbli szego rozkazu po odpowiednim END IF. W
przypadku, gdy warunek nie jest spełniony, to sprawdzany jest warunek w pierwszym ELSEIF. Je li ten warunek jest
spełniony, to wykonywany jest ci g instrukcji po odpowiadaj cym mu THEN i wykonanie instrukcji IF si ko czy.
Je li jednak ten warunek równie nie jest spełniony, to sprawdzany jest warunek w nast pnym ELSEIF. Je li aden z
warunków nie jest prawdziwy, to wykonywany jest ci g instrukcji po ELSE (je li istnieje). Wynik obliczania warunku
równy NULL jest traktowany jako niespełnienie tego warunku.
54
Przykład:
,) OLF]EDBF]HVFL 7+(1
LORVFB]DPDZLDQ\FK
(/6(,) OLF]EDBF]HVFL 7+(1
LORVFB]DPDZLDQ\FK
(/6(
LORVFB]DPDZLDQ\FK
(1' ,)
,16(57 ,172 ]DPRZLHQLD
9$/8(6 W\SBF]HVFL LORVFB]DPDZLDQ\FK
11.6. Rozkaz LOOP
Rozkaz LOOP umo liwia tworzenie p tli w j zyku PL./SQL. Dopuszczalne s cztery rodzaje p tli:
x p tle podstawowe
x p tle WHILE
x p tle FOR numeryczne
x p tle FOR dla kursorów
Składnia:
> ODEHOBQDPH!!@
> ^ :+,/( SOVTOBFRQGLWLRQ ` _
^ )25 ^QXPHULFBORRSBSDUDP _ FXUVRUBORRSBSDUDP ` ` @
/223 VHTBRIBVWDWHPHQWV (1' /223 > ODEHOBQDPH @
Składnia numeric_loop_papram:
LQGH[ ,1 >5(9(56(@ LQWHJHUBH[SU LQWHJHUBH[SU
Składnia cursor_loop_param:
UHFRUGBQDPH ,1
^ FXUVRUBQDPH > SDUDPHWHU > SDUDPHWHU@ @ _
VHOHFWBVWDWHPHQW `
Parametry:
x label_name - ten parametr pozwala na opcjonalne nazwanie p tli. Mo liwe jest wtedy u ycie nazwy p tli w
rozkazie EXIT w celu okre lenie, z ktorej p tli powinno nast pić wyj cie. Ponadto mo liwy jest dost p do
indeksu p tli zewn trznej w p tli wewn trznej, je li indeksy te maj t sam nazw za pomoc konstrukcji:
x label_name.index
x seq_of_statements - ci g rozkazów, które b d powtarzane w p tli
x plsql_condition - warunek j zyka PL./SQL. W p tli WHILE warunek ten jest obliczany przed ka d
iteracj . Odpowiedni ci g instrukcji wykonuje si tylko wtedy, gdy warunek ten ma warto ć TRUE. W
przeciwnym wypadku sterowanie przechodzi do pierwszej instrukcji za p tl .
x index - zmienna steruj ca p tli FOR. Nie jest konieczna wcze niejsza jej deklaracja.
x integer_expr - wyra enie, którego wynikiem jest liczba całkowita. Wyra enie to jest obliczane tylko przy
pierwszym wej ciu do p tli FOR.
x REVERSE - klauzula, nakazuj ca zmniejszać index (zamiast zwi kszania).
x cursor_name - nazwa uprzednio zadeklarowanego kursora. W momencie wej cia do p tli FOR kursor jest
automatycznie otwierany.
x paremeter - jeden z parametrów otwarcia kursora (je li kursor został zadeklarowany z parametrami).
x select_statament - zapytanie zwi zane z wewn trznym kursorem, niedost pnym dla u ytkownika. PL./SQL
automatycznie tworzy, otwiera i pobiera dane z kursora (a nast pnie zamyka go).
Opis:
Instrukcje w p tli podstawowej s powtarzane bez sprawdzania adnych warunków. Twórca programu jest
odpowiedzialny za zako czenie p tli instrukcj EXIT. Przykład p tli podstawowej:
ORRS !! /223
55
,) [ ! 7+(1 (;,7 ORRS
(1' /223 ORRS
P tla WHILE pozwala powtarzać ci g instrukcji, dot d dopóki podany warunek jest prawdziwy. Warunek jest
obliczany przed ka dym powtórzeniem ci gu instrukcji w p tli. W zwi zku z tym ci g instrukcji mo e nie wykonać si
ani raz. Prykład:
:+,/( [ /223
[ [ \
(1' /223
3 WOD )25 SR]ZDOD SRZWDU]Dü SRGDQ\ FL J LQVWUXNFML RNUH ORQ LOR ü UD]\ 'R
VWZLHUG]HQLD NWyUH SRZWyU]HQLH MHVW DNWXDOQLH Z\NRQ\ZDQH VáX \ ]PLHQQD
VWHUXM FD QD]\ZDQD LQGHNVHP ,QGHNV PR H E\ü ]ZL NV]DQ\ OXE ]PQLHMV]DQ\
3U]\NáDG
)25 L ,1 Q /223
VLOQLD VLOQLD Q
(1' /223
P tl FOR mo na stosować równie w celu odczytywania kolejnych wierszy z kursora (lub zapytania). Ci g
instrukcji w p tli wykonywany jest wtedy, dla ka dego wiersza. Przykład:
'(&/$5(
&85625 SUDFBNXUVRU ,6 VHOHFW )520 SUDFRZQLF\
SUDFBUHN SUDFBNXUVRU 52:7<3(
%(*,1
)25 SUDFBUHN ,1 SUDFBNXUVRU /223
VXPD VXPD SUDFBUHN SODFDBSRGVWDZRZD
(1' /223
(1'
11.7. Rozkaz EXIT
Rozkaz EXIT słu y do wyj cia z p tli. Rozkaz ten ma dwie formy: bezwarunkow i warunkow .
Składnia:
(;,7 >ODEHOBQDPH@ >:+(1 SOVTOBFRQGLWLRQ@
Parametry:
x label_name - opcjonalna nazwa p tli, z której ma nast pić wyj cie. Je li nazwa nie jest podana, to rozkaz
EXIT powoduje wyj cie z najbardziej zagnie d onej p tli aktualnie wykonywanej.
x plsql_condition - u ywany w instrukcji EXIT warunkowej. Musi być poprawnym warunkiem j zyka
PL./SQL. Wyj cie nast puje tylko wtedy, gdy wynikiem obliczenia warunku jest warto ć TRUE.
11.8. Rozkaz GOTO
Rozkaz GOTO słu y do natychmiastowego przekazania sterowania od rozkazu aktualnego do pierwszego rozkazu
wyst puj cego po podanej etykiecie.
Składnia deklaracji etykiety:
ODEHOBQDPH !!
Składnia rozkazu GOTO:
*272 ODEHOBQDPH
Opis:
56
Rozkaz GOTO umo liwia przeniesienie sterowania do innego miejsca w tym samym bloku lub bloku nadrz dnym,
ale nie do funkcji obsługi wyj tku. Z funkcji obsługi wyj tku mo liwy jest skok do bloku nadrz dnego, ale nie do bloku
aktualnego. Nie jest mo liwe równie przeniesienie sterowania do p tli z zewn trz. Je li rozkaz GOTO u ywany jest w
celu opuszczenia p tli FOR dla kursora, to kursor zamykany jest automatycznie.
57
12. INNE
12.1. PL/SQL - zmienne zło one.
Wiele osób tworz c aplikacje, cz sto zapomina o istnieniu i mo liwo ciach stosowania zmiennych zło onych.
Tworz c podprogramy PL/SQL (procedury, funkcje, pakiety, bloki anonimowe i wyzwalacze) mamy do dyspozycji
dwa zło one typy danych: rekordy i tablice. Nie nale y mylić tablic PL/SQL z tabelami bazy danych. Ka dy z tych
typów przechowuje w swoich polach zbiory informacji, ułatwiaj c nam dokonywanie skomplikowanych operacji na
danych przechowywanych w bazach.
Zacznijmy od rekordów. Definiuj c zmienne rekordowe, składaj ce si zwykle z kilku czy kilkunastu pól, mamy do
wyboru dwa sposoby działania. (Wszystkie przykłady przedstawione poni ej korzystaj z tabel demo (EMP, DEPT)
doł czanych przez firm ORACLE do swoich produktów.)
Pierwszy sposób pozwala na definiowanie rekordów zwi zanych z wierszem tabeli lub wierszem jawnego kursora,
np.:
'(&/$5(
SUDFRZQLNBUHNBZLHUV] HPS
52:7<3(
FXUVRU F LV VHOHFW HPSQR HQDPH
VDO IURP HPS ZKHUH GHSWQR
SUDFRZQLNBUHNBNXUVRU F
52:7<3(
Uzyskujemy to dzi ki atrybutowi %ROWTYPE. Nie daje on nam jednak mo liwo ci definiowania rekordów o
dowolnych zestawach pól.
Sposób drugi to u ycie typu RECORD. Tego typu zmienne rekordowe s definiowane w dwóch krokach. Krok
pierwszy to definicja typu RECORD o okre lonych polach, krok drugi to definicja zmiennych rekordowych.
'(&/$5(
7<3( SUDFRZQLNBW\S ,6 5(&25' QXPHU 180%(5
QD]ZD HPS HQDPH 7<3(
VWDQRZLVNR HPS MRE 7<3(
SODFD HPS VDO 7<3(
NLHURZQLN PJU VDO 7<3( 127 18//
Definiuj c pole jako NOT NULL zapobiegamy sytuacji, w której by warto ć pola jest znieokre lona. Takie pole
powinno być zawsze zainicjowane. J zyk PL/SQL pozwala równie na definiowanie rekordów, których polami s inne
rekordy.
'(&/$5(
7<3( DGUHVBW\S ,6 5(&25' PLDVWR 9$5&+$5
NRG 9$5&+$5
XOLFD 9$5&+$5
GRP 9$5&+$5
7<3( ILUPDBW\S ,6 5(&25' LG 180%(5 127 18//
QD]ZD 9$5&+$5
DGUHV $'5(6B7<3
Po zdefiniowaniu typu mo emy zadeklarować zmienne tych typów:
SUDFRZQLNB]P SUDFRZQLNBW\S
ILUPDB]P ILUPDBW\S
SUDFRZQLNB]P VWDQRZLVNR
ILUPDB]P DGUHV PLDVWR
Rekordy mog być parametrami procedur i funkcji. Te ostatnie mog równie zwracać zmienne typu rekordowego.
)81&7,21 SUDFRZQLN QXPHU 180%(5 5(7851 SUDFRZQLNBW\S ,6
Odwołanie do pól rekordu zwracanego przez funkcj wygl da nast puj co:
]PLHQQD SUDFRZQLN VWDQRZLVNR
Kolejna wła ciwo ć i jednocze nie główna korzy ć ze stosowania rekordów, to ułatwienie przechowywania
informacji zwracanych przez zapytania w PL/SQL.
6(/(&7 HPSQR HQDPH MRE VDO PJU ,172 SUDFRZQLNB]P )520 HPS :+(5( HPSQR
Drugi typ danych zło onych, to tablice PL/SQL. S to zmienne zawieraj ce zbiory elementów tego samego typu.
Ka dy element posiada unikalny indeks, który okre la jego pozycj w zbiorze. Rozmiar takiej tablicy jest
nieograniczony, co oznacza e zmienna mo e si rozrastać dynamicznie.
Definicja tablicy przebiega podobnie jak dla rekordów, dwuetapowo. Krok pierwszy to definicja typu.
'(&/$5(
58
7<3( QD]ZLVNRBW\S ,6 7$%/( 2) HPS HQDPH 7<3(
,1'(; %< %,1$5Do definicji tablicy mo emy równie u yć atrybutu %ROWTYPE i typu RECORD.
7<3( SUDFBURZBWDEBW\S ,6 7$%/( 2) HPS 52:7<3(
,1'(; %< %,1$57<3( SUDFRZQLNBWDEBW\S ,6 7$%/( 2) SUDFRZQLNBW\S
,1'(; %< %,1$5 GHNODUDFMD ]PLHQQ\FK
QD]ZLVNRB]P QD]ZLVNRBW\S
SUDFRZQLNBWDEB]P SUDFRZQLNBWDEBW\S
Obiekty takie, jak procedury czy funkcje, mog posiadać parametry typu TABLE. Funkcje natomiast mog zwracać
warto ci tego typu. By odwołać si do elementu tablicy PL/SQL nale y u yć składni jak poni ej:
x nazwa_tablicy_plsql(indeks), gdzie indeks jest wyra eniem zwracaj cym warto ć z zakresu typu
BINARY_INTEGER lub warto ci .
x nazwisko_zm(i) - odwołanie si do pola tablicy
x pracownik_tab_zm(i).stanowisko - odwołanie si do pola rekordu w tablicy
x pracownik_fun(7902)(3) - je eli pracownik_fun jest funkcj zwracaj c tablic , to jest to odwołanie do 3-
go elementu tej tablicy.
x pracownik_fun(7902)(3).stanowisko - je eli pracownik_fun jest funkcj zwracaj c tablic rekordów
typu pracownik, to jest to odwołanie do 3-go elementu tej tablicy, pola stanowisko.
Tablice PL/SQL mog być stosowane do przechowywania informacji zwracanych przez komend SELECT lub
przez kursory jawne np.:
6(/(&7 HPSQR HQDPH MRE VDO PJU ,172 SUDFRZQLNBWDEB]P )520 HPS
:+(5( HPSQR
'(&/$5(
7<3( SUDFRZQLNBW\S ,6 5(&25'
QXPHU 180%(5
QD]ZD HPS HQDPH 7<3(
VWDQRZLVNR HPS MRE 7<3(
SODFD HPS VDO 7<3(
NLHURZQLN PJU VDO 7<3( 127 18//
7<3( 7<3( SUDFRZQLNBWDEBW\S ,6 7$%/( 2) SUDFRZQLNBW\S
,1'(; %< %,1$5SUDFRZQLNBWDEB]P SUDFRZQLNBWDEBW\S
&85625 F ,6 6(/(&7 HPSQR HQDPH MRE VDO PJU )520 HPS
%(*,1
23(1 F
)(7&+ F ,172 SUDFRZQLNBWDEB]P
&/26( F
(1'
Jak ju wspomniałem, tabela rozrasta si dynamicznie wraz z podstawianiem nowych warto ci do kolejnych
wierszy. Te nowo tworzone wiersze nie musz posiadać kolejnych warto ci indeksu. Wymagane jest jedynie, aby te
warto ci były z zakresu przewidzianego dla typu BINARY_INTEGER (-2147483647..2147483647).
By wła ciwie i sprawnie zarz dzać takimi obiektami, mamy do dyspozycji siedem atrybutów tablic (odwołujemy si
do nich: nazwa_tabeli.nazwa_atrybutu[(n)]):
(;,676 VSUDZG]D F]\ LVWQLHMH Q W\ HOHPHQW LQGHNV Q WDEOLF\ =ZUDFD ZDUWR ü
758( OXE )$/6(
,I SUDFRZQLNBWDEB]P (;,676 , WKHQ HQG LI
x COUNT - zwraca liczb wierszy w tabeli. Je eli indeksy w tabeli zmieniaj si o jeden od warto ci
pocz tkowej, to mo emy tego atrybutu u yć do sterowania p tl FOR.
)25 , ,1 SUDFRZQLNBWDEB]P &2817
/223 (1' /223
x FIRST - zwraca najmniejszy indeks w tablicy. Je eli jest pusta to zwraca NULL.
x LAST - zwraca najwi kszy indeks w tablicy. Je eli jest pusta to zwraca NULL.
)25 , ,1 SUDFRZQLNBWDEB]P ),567 SUDFRZQLNBWDEB ]P /$67
59
/223 (1' /223
x PRIOR - PRIOR(n), zwraca indeks elementu przed n-tym. Je eli element nie istnieje zwraca NULL.
x NEXT - PRIOR(n), zwraca indeks elementu po n-tym. Je eli element nie istnieje zwraca NULL.
x DELETE - zachowuje si jak procedura, nie zwraca adnych warto ci. Kasuje wiersze w tablicy.
x DELETE - kasuje wszystkie wiersze
x DELETE(n) - kasuje n-ty wiersz
x DELETE(m,n) - kasuje wiersze od n do m.
Jak widać zmienne rekordowe i tablicowe mog mieć ró norakie zastosowanie. S przydatne szczególnie tam, gdzie
wymagane jest wielokrotne przetwarzanie danych. Umo liwiaj przekazywanie paczek informacji pomi dzy
procedurami i funkcjami istniej cymi w naszych aplikacjach. Dzi ki temu, unikamy potrzeby wykonywania przez te
obiekty po raz kolejny, tych samych zapyta do bazy danych. Wi cej informacji na temat u ywania zmiennych mo na
odnale ć w "PL/SQL - User Guide and Reference "
60