SQL10G pl_sql.pdf

Podstawy Oracle 10g SQL i PL/SQL dla pocz ! tkuj ! cych

8. P ODSTAWY PL/SQL

8.1.2. Rodzaje bloków PL/SQL

W PL/SQL istniej ! trzy podstawowe rodzaje bloków.

!"#$%&'(#(%)#*+&

Nie s ! przechowywane na serwerze bazy danych ani w postaci kodu, ani w postaci

skompilowanej. Przed ka $ dym wykonaniem musz ! by # kompilowane.

DECLARE

/* Sekcja deklaracyjna */

BEGIN

/* Sekcja wykonywalna */

EXCEPTION

/* Sekcja obsugi b!dów */

END;

,-#.+/0-1&%&20($.3+&

Procedury i funkcje (podprogramy skadowane) s ! to bloki PL/SQL, które maj ! swoj ! nazw " .

S ! one kompilowane tylko jeden raz podczas tworzenia. Na serwerze bazy danych jest

przechowywany zarówno kod procedury, jak i posta # skompilowana. Wykonanie procedury

lub funkcji nie wymaga ponownej kompilacji.

Do procedur i funkcji mog ! by # przekazywane parametry. Parametry mog ! by # równie $

przekazywane z procedur i funkcji do % rodowiska, które je wywouje.

CREATE OR REPLACE PROCEDURE procedura1

/* Sekcja deklaracyjna */

BEGIN

/* Sekcja wykonywalna */

EXCEPTION

/* Sekcja obsugi b!dów */

END;

415*'"'.5+&

Wyzwalacze s ! to bloki PL/SQL, które nie mog ! by # wywoywane bezpo % rednio poleceniem

SQL. Ich wykonanie jest bezpo % rednio zale $ ne od zaj % cia pewnego zdarzenia, z którym

zostay powi ! zane.

Do wyzwalaczy nie mo $ na bezpo % rednio przekaza # parametrów.

Przed wersj ! 7.3 wyzwalacze byy przechowywane w bazie danych tylko w postaci kodu. Od

wersji 7.3 w ! cznie przechowywana jest tak $ e posta # skompilowana wyzwalaczy, co ma

wpyw na wydajno %# .

CREATE OR REPLACE TRIGGER trigger1

BEFORE INSERT ON tabela1

DECLARE

/* Sekcja deklaracyjna */

BEGIN

/* Sekcja wykonywalna */

EXCEPTION

/* Sekcja obsugi b!dów */

END;

8.1. Wst ! p

8.1.1. Budowa bloku PL/SQL

Podstawow ! jednostk ! j " zyka PL/SQL jest blok.

Blok PL/SQL przed wykonaniem musi zosta # skompilowany, nie kompiluje si " jednak do

postaci wykonalnej (.exe, .dll, .lib), któr ! mo $ na uruchomi # z poziomu systemu operacyjnego.

(!) W PL/SQL nie mo ! na bezpo " rednio u ! ywa # ! adnych instrukcji DDL.

Ka $ dy blok PL/SQL mo $ e zawiera # trzy sekcje:

DECLARE

/* Sekcja deklaracyjna */

BEGIN

/* Sekcja wykonywalna */

EXCEPTION

/* Sekcja obsugi b!dów */

END;

Sekcja deklaracyjna i sekcja obsugi wyj ! tków s ! opcjonalne, zatem najprostszy blok PL/SQL

mo $ e wygl ! da # w nast " puj ! cy sposób:

BEGIN

/* Sekcja wykonywalna */

END;

Przykad 150

Napisa ! blok PL/SQL, który b " dzie wstawia do tabeli grupy_cen nowy rekord.

BEGIN

INSERT INTO grupy_cen VALUES (30, 10000, 30000);

END;

Bloki PL/SQL mog ! by # w sobie zagnie $ d $ ane.

S TRONA 98 Z 164

S TRONA 99 Z 164

Podstawy Oracle 10g SQL i PL/SQL dla pocz ! tkuj ! cych

Przykad 151:

-- Poprawny blok autonomiczny

DECLARE

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

INSERT INTO ...

COMMIT ;

END ;

8.1.3. Bloki PL/SQL a transakcje

Polecenie COMMIT lub ROLLBACK wydane w bloku PL/SQL maj ! wpyw nie tylko na dane

zmodyfikowane w tym $ e bloku, ale na wszystkie dane zmodyfikowane w caej transakcji,

niezale $ nie od tego, w jakim bloku ta transakcja zostaa rozpocz " ta, dlatego nie zaleca si "

wstawiania w procedurach instrukcji steruj ! cych transakcjami.

6-'(7'$.3+&'08#(#)%.5(+&

Wszystkie operacje SQL odbywaj ! si " w kontek % cie pewnych transakcji, które musz ! zosta #

albo w cao % ci zatwierdzone lub w cao % ci wycofane. Przed wersj ! 8i nie byo mo $ liwo % ci,

aby pewne operacje zostay zatwierdzone lub wycofane niezale $ nie od innych operacji w tej

samej transakcji. W Oracle 8i i wy $ ej mo $ na tego dokona # dzi " ki transakcjom

autonomicznym.

Transakcja autonomiczna to transakcja, która wykonuje si " w kontek % cie innej transakcji,

zwanej transakcj ! zewn " trzn ! , ale jest cakowicie od niej niezale $ na.

,-'9)'&:;6<=<><;?@6A:=?:B6C<=&

Jedynym sposobem, aby wykona # pewne instrukcje w transakcji autonomicznej jest

umieszczenie ich w jednym bloku PL/SQL i zaznaczenie tego bloku jako autonomicznego

poprzez wprowadzenie w sekcji deklaracyjnej pragmy AUTONOMOUS_TRANSACTION.

Nie wszystkie bloki PL/SQL mog ! by # przeksztacone w bloki autonomiczne. Tylko

nast " puj ! ce typy mog ! zawiera # pragm " autonomiczno % ci:

• najbardziej zewn " trzne bloki anonimowe,

• procedury i funkcje samodzielne i w pakietach,

• wyzwalacze.

4D'E.%*#E.%&8-'(7'$.3%&'08#(#)%.5(1.F&

Wszystkie polecenia do sterowanie transakcjami: COMMIT. ROLLBACK, SAVEPOINT,

SET TRANSACTION mog ! by # u $ ywane w transakcjach autonomicznych. Savepointy s !

lokalne w transakcji autonomicznej i nie mo $ na wycofa # operacji z transakcji autonomicznej

do punktu zachowania w transakcji zewn " trznej i równie $ nie mo $ na wycofa # polece & z

transakcji zewn " trznej do punktu zachowania umieszczonego w transakcji autonomicznej.

Transakcje autonomiczne nie ko & cz ! si " automatycznie wraz z wyj % ciem z bloku

autonomicznego. Je $ eli sterowanie opu % ci blok autonomiczny bez zako & czenia transakcji, to

generowany jest b ! d ORA-6519 i caa transakcja autonomiczna jest wycofywana.

(!) Poniewa ! transakcja autonomiczna i transakcja zewn $ trzna s % niezale ! ne, to równie ! blokuj %

sobie nawzajem zasoby, tak jakby byy to dwie rywalizuj % ce ze sob % transakcje wykonuj % ce si $

równolegle w dwu sesjach.

-- Niepoprawny blok autonomiczny

DECLARE

v_1 number;

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

INSERT INTO ...

COMMIT ;

v_1 := 10;

END ;

-- Niepoprawny blok autonomiczny

CREATE OR REPLACE PROCEDURE procedura1

v_1 number;

BEGIN

...

v_1 := 10;

DELETE FROM tabela1 ...

DECLARE

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

INSERT INTO tabela2...

COMMIT ;

END ;

v_1 := 10;

...

END ;

S TRONA 100 Z 164

S TRONA 101 Z 164

Podstawy Oracle 10g SQL i PL/SQL dla pocz ! tkuj ! cych

8.1.4. Deklarowanie zmiennych

W PL/SQL niektóre typy danych s ! inne ni $ w SQL:

• CHAR(L) – maksymalna dugo %# wynosi 32768 znaków zamiast 255 w SQL

• VARCHAR2(L) – maksymalna dugo %# wynosi 32768 znaków zamiast 4000 w SQL.

• BOOLEAN – typ, który mo $ e przyjmowa # tylko trzy warto % ci: TRUE, FALSE, NULL.

Nie mia swojego odpowiednika w SQL.

Zmienne s ! deklarowane w sekcji deklaracyjnej bloku PL/SQL.

Skadnia deklaracji zmiennej jest nast " puj ! ca:

nazwa_zmiennej typ_zmiennej [ CONSTANT ] [ NOT NULL ] [:= warto"# ];

Zamiast operatora przypisania mo $ e pojawi # si " sowo DEFAULT .

Zamiast jawnego podawania typu zmiennej mo $ na odwoa # si " do typu zmiennej wcze % niej

zadeklarowanej lub do typu kolumny bazodanowej poprzez u $ ycie nazwy tej zmiennej

(nazwy kolumny) i operatora %TYPE.

(!) Je ! eli zmienna nie zostanie zainicjowana, to jej warto "# wynosi NULL.

Przykad poprawnych deklaracji:

DECLARE

v_1 number(9);

v_2 number CONSTANT := 3.14;

v_3 varchar2(10) := ‘Ala’;

v_4 v_1%TYPE;

v_5 klienci.imie%TYPE;

v_6 char (4) NOT NULL := ‘Ala’;

v_7 v_1%TYPE;

BEGIN

...

Przykad niepoprawnych deklaracji:

DECLARE

v_1 number(2) := 123;

v_2,v_3 varchar2(10);

v_4 number(3) NOT NULL ;

v_5 varchar2;

BEGIN

...

W PL/SQL zasi " g zmiennych, a wi " c obszar kodu, w którym zachowuj ! swoj ! warto %#

rozci ! ga si " od momentu zadeklarowania zmiennej do ko & ca bloku, w którym zmienna ta

zostaa zadeklarowana.

8.1.5. Instrukcja SELECT i inne instrukcje DML w PL/SQL

Instrukcje DML: INSERT , UPDATE i DELETE w PL/SQL stosuje si " podobnie jak w SQL, z t !

ró $ nic ! , $ e zamiast podawania warto % ci staych mo $ na stosowa # zmienne lub nawet

wyra $ enia.

Przykad 152

Napisa ! blok PL/SQL, który wstawi nowe zamówienie dla Jana Kowalskiego wraz z dwoma pozycjami

obejmuj # cymi komputer i pi "! procesorów. Identyfikatory zamówienia i pozycji powinny zosta !

zaczerpni " te z sekwencji.

DECLARE

v_id_klienta number (9) := 1000;

v_id_komputera number (9) := 340;

v_id_procesora number (9) := 510;

BEGIN

INSERT INTO zamowienia (zam_id, zam_numer, zam_data_zamowienia,

zam_data_realizacji, zam_opis, zam_klt_id)

VALUES (seq_zamowienia.nextval, 200, sysdate, null,

'Nowe zamówienie na komputery', v_id_klienta);

INSERT INTO pozycje_zamowien(pzm_id, pzm_lp, pzm_ilosc,

pzm_zam_id, pzm_tow_id)

VALUES (seq_pozycje_zamowien.nextval, 1, 1,

seq_zamowienia.currval, v_id_komputera);

INSERT INTO pozycje_zamowien(pzm_id, pzm_lp, pzm_ilosc,

pzm_zam_id, pzm_tow_id)

VALUES (seq_pozycje_zamowien.nextval, 2, 5,

seq_zamowienia.currval, v_id_procesora);

COMMIT ;

END ;

Inaczej jest w przypadku instrukcji SELECT . Je $ eli u $ ywamy jej w zwykym SQL, to wynik

zwrócony przez t ! instrukcj " jest wy % wietlany na konsoli. W PL/SQL nie ma konsoli, a dane

zwrócone przez zapytanie nie powinny zosta # pokazane u $ ytkownikowi, ale przetworzone

przez kolejne instrukcje kodu. Aby to umo $ liwi # , instrukcja SELECT w PL/SQL zostaa

wzbogacona o kolejn ! klauzul " , która pozwala przekierowa # warto % ci zwrócone przez

zapytanie do wskazanych zmiennych.

SELECT kol_1, kol_2, kol_3, ..., kol_n

INTO zmienna_1, zmienna_2, zmienna_3, ..., zmienna_n

FROM tabela

WHERE ...

Przykad 153

Napisa ! blok PL/SQL, który wstawi nowe zamówienie dla Jana Kowalskiego wraz z dwoma pozycjami

obejmuj # cymi komputer i pi "! procesorów. Identyfikatory zamówienia i pozycji powinny zosta !

zaczerpni " te z sekwencji. Identyfikatory klienta i towarów powinny zosta ! odczytane z bazy.

S TRONA 102 Z 164

S TRONA 103 Z 164

Podstawy Oracle 10g SQL i PL/SQL dla pocz ! tkuj ! cych

DECLARE

v_id_klienta number (9);

v_id_komputera number (9);

v_id_procesora number (9);

BEGIN

SELECT klt_id

INTO v_id_klienta

FROM klienci

WHERE klt_imie = 'Jan'

AND klt_nazwisko = 'Kowalski';

INSERT INTO zamowienia (zam_id, zam_numer, zam_data_zamowienia,

zam_data_realizacji, zam_opis, zam_klt_id)

VALUES (seq_zamowienia.nextval, 200, sysdate, null,

'Nowe zamówienie na komputery', v_id_klienta);

SELECT tow_id

INTO v_id_komputera

FROM towary

WHERE tow_nazwa = 'Komputer';

INSERT INTO pozycje_zamowien(pzm_id, pzm_lp, pzm_ilosc,

pzm_zam_id, pzm_tow_id)

VALUES (seq_pozycje_zamowien.nextval, 1, 1,

seq_zamowienia.currval, v_id_komputera);

SELECT tow_id

INTO v_id_procesora

FROM towary

WHERE tow_nazwa = 'Procesor Pentium III 700 MHz';

INSERT INTO pozycje_zamowien(pzm_id, pzm_lp, pzm_ilosc,

pzm_zam_id, pzm_tow_id)

VALUES (seq_pozycje_zamowien.nextval, 2, 5,

seq_zamowienia.currval, v_id_procesora);

COMMIT ;

END ;

(!) Je ! eli korzystamy z instrukcji SELECT w PL/SQL, to instrukcja ta MUSI zwraca # dokadnie

jeden wiersz. Je ! eli zwróci mniej lub wi $ cej, to instrukcja ta ko & czy si $ niepowodzeniem.

C(78-0$.3'&*%+"#$-#8(+9#&*1G#-0&

Instrukcja ta pozwala na zwróceniepewnej warto % ci w zale $ no % ci od warto % ci wskazanego

wyra $ enia.

zmienna := CASE wyra!enie_testowane

WHEN wyra!enie1 THEN warto#$1

WHEN wyra!enie2 THEN warto#$2

...

WHEN wyra!enieN THEN warto#$N

[ ELSE warto#$N+1]

END ;

Instrukcja wielokrotnego wyboru pozwala tak $ e wykona # pewne instrukcje w zale $ no % ci od

warunku.

CASE wyra!enie_testowane

WHEN wyra!enie1 THEN instrukcja1;

WHEN wyra!enie2 THEN instrukcja2;

...

WHEN wyra!enieN THEN instrukcjaN

[ ELSE instrukcjaN+1]

END ;

,H8"'&I<<,&

Najprostsza p " tla skada si " z ciaa instrukcji, które maj ! by # powtarzane, zamkni " tego

mi " dzy ogranicznikami LOOP i END LOOP .

LOOP

ci%g_instrukcji

END LOOP;

Za ka $ dym razem, gdy wykonanie osi ! gnie instrukcj " END LOOP sterowanie jest

przekazywane z powrotem do instrukcji poprzedzonej przez klauzul " LOOP .

Aby wyj %# z takiej p " tli nale $ y wykona # instrukcj " EXIT , która powoduje natychmiastowe

opuszczenie p " tli (bez wykonania si " bie $! cego cyklu). Je $ eli do instrukcji EXIT dodamy

klauzul " WHEN wraz z odpowiednim warunkiem, to p " tla zostanie opuszczona tylko w

przypadku prawdziwo % ci tego warunku;

EXIT [ WHEN warunek];

Przykad 154

Napisa ! blok PL/SQL, który wstawi do tabeli grupy_cen 50 wierszy.

DECLARE

v1 binary_integer;

BEGIN

v1 := 0;

LOOP

v1 := v1 + 1;

EXIT WHEN v1 > 50;

INSERT INTO grupy_cen

VALUES (30, 100000, 500000);

END LOOP;

END ;

,H8"'&4JCIK&

P " tla WHILE jest nieco zmodyfikowan ! p " tl ! LOOP. Do zako & czenia nie wymaga ona

wpisania instrukcji EXIT , chocia $ instrukcja ta mo $ e si " w niej pojawi # . Warunek

wykonywania si " p " tli jest okre % lany w jej nagówku po sowie WHILE .

8.1.6. Instrukcje steruj % ce

C(78-0$.3'&/+.1513('&

Instrukcja decyzyjna IF pozwala na selektywne wykonywanie pewnych instrukcji w oparciu o

warunek logiczny okre % lony wyra $ eniem.

Pena skadnia instrukcji IF:

IF wyra$enie_logiczne1 THEN

ci%g_instrukcji1

ELSIF wyra$enie_logiczne2 THEN

ci%g_instrukcji2

...

ELSIF wyra$enie_logiczne3 THEN

ci%g_instrukcji3

ELSE

ci%g_instrukcji4

END IF ;

Klauzule ELSIF i ELSE s ! opcjonalne i je $ eli nie ma takiej potrzeby, mog ! zosta # pomini " te.

Podczas wykonywania instrukcji IF obliczane jest wyra $ enie wyra!enie_logiczne1 .

Je $ eli jest ono prawdziwe, to wykonywane s ! instrukcje ci"g_instrukcji1, a nast " pnie

sterowanie przechodzi do pierwszej instrukcji po klauzuli END IF . W przypadku, gdy

wyra!enie_logiczne1 jest faszywe, lub ma warto %# NULL, obliczane s ! kolejne

warunki logiczne, a $ który % z nich oka $ e si " prawdziwy. Je $ eli $ aden z nich nie b " dzie

prawdziwy, wykonywane s ! instrukcje nast " puj ! ce po klauzuli ELSE ( ci"g_instrukcji4 ).

S TRONA 104 Z 164

S TRONA 105 Z 164

Podstawy Oracle 10g SQL i PL/SQL dla pocz ! tkuj ! cych

WHILE warunek_logiczny LOOP

ci%g_instrukcji

END LOOP ;

P " tla wykonuje si " cyklicznie, dopóki warunek warunek_logiczny jest prawdziwy.

Przykad 155

Napisa ! blok PL/SQL, który wstawi do tabeli grupy_cen 50 wierszy.

DECLARE

v1 binary_integer;

BEGIN

v1 := 0;

WHILE v1 < 50 LOOP

v1 := v1 + 1;

INSERT INTO grupy_cen VALUES (30, 100000, 500000);

END LOOP;

END ;

8.1.7. Podsumowanie

Najwa $ niejsze wiadomo % ci, jakie powiniene % posiada # po przerobieniu poprzedniego

rozdziau, to:

• W jaki sposób jest zorganizowany kod PL/SQL?

• Z jakich sekcji zbudowany mo $ e by # blok PL/SQL?

• Jakie rodzaje bloków mo $ na utworzy # w PL/SQL i czym si " one charakteryzuj ! ?

• Jakie wyst " puj ! ró $ nice pomi " dzy typami danych w SQL i PL/SQL?

• W jaki sposób deklaruje si " zmienne w bloku PL/SQL?

• Jak dziaa konstrukcja decyzyjna?

• Jakie istniej ! w PL/SQL p " tle i jak dziaaj ! ?

Je $ eli nie potrafisz wyczerpuj ! co odpowiedzie # na ka $ de z powy $ szych pyta & , popro %

instruktora o powtórne, b ! d ' bardziej szczegóowe wyja % nienie w ! tpliwego tematu.

,H8"'&L<A&

P " tla FOR posiada dodatkowo przed sowem kluczowym LOOP fraz " steruj ! c ! okre % laj ! c !

liczb " iteracji do wykonania.

Skadnia p " tli FOR:

FOR zmienna IN [ REVERSE ] zakres_dolny..zakres_górny LOOP

ci%g_instrukcji

END LOOP ;

Podczas ka $ dej kolejnej iteracji zmienna steruj ! ca p " tli FOR przybiera kolejn ! warto %# z

zakresu okre % lonego przez zakres_dolny i zakres_górny .

Je $ eli u $ yjemy sowa REVERSE , to zmienna steruj ! ca b " dzie przybiera # warto % ci z

okre % lonego zakresu malej ! co.

Zmienna steruj ! ca p " tl ! nie musi by # deklarowana, a jej zasi " g jest tylko w zakresie p " tli

FOR, któr ! steruje.

Przykad 156

Napisa ! blok PL/SQL, który wstawi do tabeli grupy_cen 50 wierszy.

DECLARE

v1 number;

v2 number;

BEGIN

v1 := 11;

v2 := 60;

FOR v3 IN v1..v2 LOOP –- P!tla wykona si! 50 razy

v2 := 15; -- Przypisanie do v2 warto"ci 15

INSERT INTO grupy_cen

VALUES (30, 100000, 500000);

END LOOP ; -- Zmiana warto"ci v1 i v2 nie ma wpywu na p!tl!

END ;

S TRONA 106 Z 164

S TRONA 107 Z 164

Podstawy Oracle 10g SQL i PL/SQL dla pocz ! tkuj ! cych

8.1.8. ' wiczenia

67. Napisa # i wykona # prosty blok anonimowy, który wypisze napis: „Wykonaem si " ” na

konsol " klienta. Skorzysta # z procedury wbudowanej

dbms_output.put_line( tekst ) . Nale $ y pami " ta # o w ! czeniu opcji SQL*Plus

serveroutput .

68. Zamieni # powy $ szy blok na procedur " i wykona # j ! .

69. * Sprawd ' , jaki jest format przechowywania danych w zmiennych typu char i varchar2.

Jak s ! przechowywane spacje?

• Utwórz i uruchom nast " puj ! cy blok anonimowy:

• Zadeklaruj w nim dwie zmienne typu char (25) i jedn ! zmienn ! typu

varchar2(25).

• Przypisz do jednej ze zmiennych typu char warto %# ‘Ala ma kota’, do dwu

pozostaych zmiennych przypisz po pi "# spacji.

• Wypisz zawarto %# zmiennych na konsol " korzystaj ! c z procedury

dbms_output.put_line(tekst) . Dla lepszego zobrazowania wyników

przekszta # zmienne przed wypisaniem wywouj ! c funkcj " translate:

translate(zmienna, ‘ ‘, ‘*’) .

• Przeanalizuj wyniki.

70. Napisz i wykonaj blok PL/SQL, który wstawi do pewnej tabeli liczby podzielne przez 7 i

liczby podzielne przez 13 z zakresu od jeden do tysi ! ca.

• Utwórz tabel " z kolumn ! typu number(9) .

• Napisz blok anonimowy, który:

• Wyczy % ci t " tabel " i wo $ y do kolumny numerycznej odpowiednie warto % ci

zgodne z wymaganiami zadania. Skorzystaj z funkcji mod .

Dlaczego do czyszczenia tabeli nie moge % u $ y # polecenia truncate

table ?

• Zatwierdzi transakcj " .

• Policz, ile wierszy zostao wstawionych? Poprawna ilo %# to 208.

• Zapisz skrypt w pliku tekstowym.

Je $ eli masz kopoty ze zrozumieniem lub wykonaniem # wiczenia, lub nie rozumiesz,

dlaczego serwer Oracle zachowuje si " w taki, a nie inny sposób, popro % instruktora o

dodatkowe wyja % nienia.

8.2. Procedury i funkcje

8.2.1. Tworzenie i usuwanie podprogramów skadowanych

Procedury i funkcje s ! to bloki PL/SQL przechowywane w bazie danych zarówno w postaci

kodu jak i w postaci skompilowanej.

Tworzenie procedury

CREATE OR REPLACE PROCEDURE nazwa_procedury

BEGIN

...

END ;

Przykad 157

Stworzy ! procedur " , która usunie z tabeli klienta o numerze 1001 wraz z wszystkimi podlegymi

rekordami.

CREATE OR REPLACE PROCEDURE del_kli_1001

BEGIN

-- usuni!cie pozycji zamówie& zo$onych przez klienta 1001

DELETE pozycje_zamowien

WHERE pzm_zam_id IN ( SELECT zam_id

FROM zamowienia

WHERE zam_klt_id = 1001);

-- usuni!cie zamowie& zo$onych przez klienta 1001

DELETE zamowienia

WHERE zam_klt_id = 1001;

-- usuni!cie klienta

DELETE klienci

WHERE klt_id = 1001;

END ;

Tworzenie funkcji

CREATE OR REPLACE FUNCTION nazwa_funkcji RETURN typ_zwracany

BEGIN

...

RETURN warto"# ;

...

END ;

Przykad 158

Stworzy ! funkcj " , która sprawdzi, czy jakie $ zamówienie czeka na realizacj " du % ej ni % miesi # c. Je % eli

takie zamówienia istniej # ma zosta ! zwrócona prawda, w przeciwnym wypadku fasz.

CREATE OR REPLACE FUNCTION opoznienia

RETURN boolean

v_opoznione binary_integer;

BEGIN

SELECT count(*) INTO v_opoznione

FROM zamowienia

WHERE zam_data_realizacji IS NULL

AND add_months(zam_data_zamowienia,1) < sysdate;

IF v_opoznione > 0 THEN

RETURN TRUE;

ELSE

RETURN FALSE;

END IF ;

END ;

Wywoanie procedury w kodzie PL/SQL polega na podaniu jej nazwy

S TRONA 108 Z 164

S TRONA 109 Z 164

Podstawy Oracle 10g SQL i PL/SQL dla pocz ! tkuj ! cych

nazwa_procedury ;

Poniewa $ ka $ da funkcja zwraca jak !% warto %# , to warto %# ta musi zosta # „zagospodarowana”.

W zwi ! zku z tym funkcja musi by # wywoywana jako skadnik wyra $ enia, w najprostszym

przypadku:

zmienna := nazwa_funkcji ;

Przykad 159

Napisa ! blok anonimowy, który wywoa wcze $ niej stworzon # procedur " i funkcj "

DECLARE

v_opoznienia boolean;

BEGIN

del_kli_1001;

v_opoznienia := opoznienia;

END ;

Usuwanie procedury

DROP PROCEDURE nazwa_procedury ;

Usuwanie funkcji

DROP FUNCTION nazwa_funkcji ;

Przykad 160

Usun #! stworzon # poprzednio procedur " i funkcj " .

DROP PROCEDURE del_kli_1001;

DROP FUNCTION opoznienia;

IS ...

-- wywoanie

p1(’Ala ma kota’,

v_num

);

Notacja imienna

W notacji imiennej kolejno %# wpisywania parametrów nie ma znaczenia. W momencie

wywoywania okre % lamy % ci % le, który parametr aktualny ma by # przypisany do którego

parametru formalnego.

Przykad 163

-- deklarecja

CREATE PROCEDURE p1 (

par1 IN varchar2,

par2 IN number,

par3 OUT number

)

...

typ_warto#ci nie mo $ e zawiera # precyzji. Precyzja jest pobierana z parametru aktualnego

a nie z formalnego.

Przykad 161

-- dobrze

CREATE PROCEDURE p1 (p_1 IN varchar2) IS ...

-- 'le

CREATE PROCEDURE p1 (p_1 IN varchar2(64) ) IS ...

61M1&M'-')+8-N*&

W PL/SQL istniej ! trzy typy parametrów: IN, OUT, IN OUT. Je $ eli $ aden z nich nie zostanie

wyspecyfikowany, przyjmowany domy % lnie jest typ IN.

IN – Warto %# parametru aktualnego jest przekazywana do podprogramu. Parametr formalny

zachowuje si " tak jakby by sta ! (tylko do odczytu). Po wyj % ciu z podprogramu

parametr aktualny si " nie zmienia.

OUT – Warto %# parametru aktualnego jest ignorowana. Parametr formalny ma warto %# NULL

do momentu inicjalizacji. Mo $ na go czyta # i zapisywa # . W momencie wyj % cia z

podprogramu warto %# jest przepisywana z parametru formalnego do aktualnego.

IN OUT – jest to kombinacja obu poprzednich typów. W momencie wywoania parametr

aktualny zostaje skopiowany do parametru formalnego, który zachowuje si " jak

zmienna zainicjalizowana. Przy wychodzeniu z podprogramu warto %# parametru

formalnego zostaje przekopiowana do parametru aktualnego.

(!) Je ! eli podprogram wyrzuci wyj % tek i nie zostanie on obsu ! ony wewn % trz tego podprogramu, to

kopiowanie parametru formalnego do aktualnego w parametrach OUT i IN OUT nie wyst $ puje.

IS ...

-- wywoanie

p1(par1 => ’Ala ma kota’,

par2 => 5,

par3 => v_num

);

Przykad 164

Stworzy ! i wywoa ! procedur " , która dla zadanego przez numer zamówienia zwróci rabat, jaki

przysuguje do tego zamówienia w procentach i kwotowo.

S TRONA 110 Z 164

S TRONA 111 Z 164

Podstawy Oracle 10g SQL i PL/SQL dla pocz ! tkuj ! cych

CREATE OR REPLACE PROCEDURE oblicz_rabat

(p_numer IN number,

p_rabat_proc OUT number,

p_rabat_kw OUT number)

v_zam_id zamowienia.zam_id% TYPE ;

v_kwota number(6);

BEGIN

-- odczytanie identyfikatora zamówienia

SELECT zam_id INTO v_zam_id

FROM zamowienia

WHERE zam_numer = p_numer;

-- policzenie kwoty zamówienia

SELECT sum(PZ.pzm_ilosc*T.tow_cena) INTO v_kwota

FROM pozycje_zamowien PZ, towary T

WHERE PZ.pzm_tow_id = T.tow_id

AND PZ.pzm_zam_id = v_zam_id;

-- odczytanie przysugujacego rabatu

SELECT grc_rabat, grc_rabat*v_kwota/100

INTO p_rabat_proc, p_rabat_kw

FROM grupy_cen

WHERE v_kwota BETWEEN grc_cena_od AND grc_cena_do;

END ;

-- wywoanie procedury

DECLARE

v_pr number(2);

v_kw number(10);

BEGIN

oblicz_rabat(124, v_pr, v_kw);

dbms_output.put_line('Rabat: '

||to_char(v_pr, '999G9990D99')||' %');

dbms_output.put_line('Rabat: '

||to_char(v_kw, '999G9990D99')||' PLN');

END ;

• instrukcja odwouje si " do nieistniej ! cych lub niepoprawnych obiektów (np. próba

wykonania procedury, która jest skompilowana z b " dami),

• próba modyfikacji danych powoduj ! ca naruszenie wi " zów integralno % ci, itp.

W bazie danych Oracle jest zdefiniowanych kilka tysi " cy ró $ nych b " dów odpowiadaj ! cych

ró $ nym nie przewidzianym sytuacjom. Ka $ dy b ! d, jaki jest zdefiniowany w bazie danych

Oracle ma swój unikalny numer i odpowiadaj ! cy mu komunikat.

Przykad 165

Wykona ! instrukcj " , która spowoduje wyst # pienie b " du.

SQL> DROP USER sys;

DROP USER sys

ERROR w linii 1:

ORA-01031: insufficient privileges

(!) Je ! eli próba wykonania instrukcji spowoduje b % d, to oznacza to, ! e dana instrukcja si $ nie

wykonaa, nie powoduje to natomiast wycofania transakcji, w której bya podj $ ta próba wykonania

si $ instrukcji.

413O8$%&M-+/+2%(%#*'(+&

Je $ eli b ! d zostanie spowodowany przez pojedyncz ! instrukcj " SQL wpisan ! przez jakiego %

u $ ytkownika, to instrukcja nie zostanie wykonana, w sesji u $ ytkownika pojawi si " numer i

komunikat b " du i sterowanie powróci do konsoli u $ ytkownika, który b " dzie móg

odpowiednio zareagowa # na powstanie nieprzewidzianej sytuacji.

Je $ eli natomiast b ! d wyst ! pi podczas wykonywania si " kodu PL/SQL, to u $ ytkownik nie

mo $ e bezpo % rednio na niego zareagowa # , natomiast mo $ e przewidzie # wyst ! pienie b " du i

odpowiednio przygotowa # podprogram w celu obsugi takiej sytuacji. Bardzo wa $ nym

elementem ka $ dej aplikacji jest wa % nie obsuga b " dów.

Je $ eli w trakcie wykonywania si " kodu PL/SQL wygenerowany zostanie b ! d bazy danych

Oracle, to w kodzie PL/SQL zgaszany jest tzw. wyj ! tek.

W PL/SQL zdefiniowanych jest kilkana % cie standartowych wyj ! tków, które s ! zgaszane

przez Oracle w przypadku wyst ! pienia pewnych b " dów podczas wykonywania si " kodu.

Poni $ ej przedstawiono najcz "% ciej zgaszane wyj ! tki:

invalid_cursor Je % li u $ yjemy nie otwartego kursora.

cursor_already_open Je % li usiujemy otworzy # ju $ otwarty kursor.

dup_val_on_index Naruszenie unikalno % ci warto % ci w kolumnie bazodanowej.

no_data_found Brak danych do odszukania lub przetworzenia.

too_many_rows Zbyt wiele wierszy, gdy spodziewano si " jednego.

zero_divide Próba wykonania dzielenia przez zero.

rowtype_mismatch Niezgodno %# typów danych kursora.

invalid_number Zmienna znakowa zostaa u $ yta jako numeryk.

(!) Wyj % tek i b % d to nie to samo.

Wywoanie wyj ! tku najcz "% ciej jest powodowane wyst ! pieniem b " du, ale zdecydowana

wi " kszo %# b " dów nie powoduje zgoszenia $ adnego wyj ! tku. Mog ! tak $ e istnie # wyj ! tki,

które nie s ! powodowane b " dem na bazie danych Oracle.

<G7D09'&*13O8$N*&

W momencie wyst ! pienia jakiegokolwiek b " du (nie tylko zwi ! zanego ze zdefiniowanym

wyj ! tkiem), wykonywanie bloku jest przerwane, a sterowanie przechodzi do sekcji obsugi

wyj ! tków w bloku, w którym b ! d wyst ! pi.

Sekcja obsugi wyj ! tków skada si " z sekwencji nast " puj ! cych po sobie ci ! gów instrukcji, z

których ka $ dy jest dedykowany obsudze okre % lonego wyj ! tku.

8.2.3. Przywileje zwi % zane z podprogramami, kontekst

podprogramów

Aby wykona # jakikolwiek podprogram potrzebne jest uprawnienie EXECUTE na tym

podprogramie.

U $ ytkownik wykonuj ! cy podprogram nie musi mie # praw do obiektów, do których odwouje

si " podprogram.

Podprogramy s ! wykonywane w kontek % cie danych i obiektów wa % ciciela podprogramu, a

nie u $ ytkownika, który wykonuje podprogram.

8.2.4. Obsuga b $ dów i wyj % tków

!DH/1&*&G'5%+&/'(1.F&<-'."+&

Je $ eli u $ ytkownik lub jakikolwiek proces próbuje wykona # czynno %# , której serwer bazy

danych nie potrafi wykona # , to serwer bazy danych generuje b ! d. W takim wypadku

mówimy, $ e pewna konkretna instrukcja spowodowaa wyst ! pienie b " du.

Polecenie mo $ e zako & czy # si " b " dem, gdy:

• nie ma fizycznych mo $ liwo % ci wykonania si " instrukcji (np. koniec miejsca

przeznaczonego dla tabeli podczas instrukcji INSERT ),

• u $ ytkownik nie ma prawa wykonania instrukcji (np. u $ ytkownik próbuje zmodyfikowa #

dane w tabeli, na której nie ma prawa UPDATE ),

S TRONA 112 Z 164

S TRONA 113 Z 164

?M#7NG&*1*#D1*'(%'&

Wywoywa # podprogramy z parametrami mo $ na na dwa sposoby:

Notacja pozycyjna

W notacji pozycyjnej parametry aktualne s ! przyporz ! dkowywane do parametrów formalnych

na podstawie pozycji, na której wyst ! piy poszczególne parametry w momencie deklaracji i

wywoania.

Przykad 162

-- deklarecja

CREATE PROCEDURE p1 (

par1 IN varchar2,

par2 IN number,

par3 OUT number

)

8.2.2. Parametry

?M#7NG&/+$"'-'.3%&

CREATE PROCEDURE | FUNCTION nazwa_podprogramu (

nazwa typ typ_warto"ci DEFAULT warto"#

{, nazwa typ typ_warto"ci DEFAULT warto"# }

)

Podstawy Oracle 10g SQL i PL/SQL dla pocz ! tkuj ! cych

BEGIN

...

EXCEPTION

WHEN wyj%tek1 THEN

ci%g_instrukcji1

WHEN wyj%tek2 THEN

ci%g_instrukcji2

WHEN OTHERS THEN

ci%g_instrukcji3

END ;

Wyj ! tek OTHERS su $ y do przechwycenia wszystkich b " dów (nie tylko wyj ! tków). W

powy $ szym przykadzie ci"g_instrukcji3 wykona si " w przypadku wyst ! pienia ka $ dego

b " du lub wyj ! tku poza wyj ! tkami wyj"tek1 i wyj"tek2 .

Po obsu $ eniu wyj ! tku sterowanie nie powraca ju $ do bloku, w którym wyst ! pi b ! d.

Przykad 166

Wstawi ! do tabeli uczniowie osob " o imieniu Jan i nazwisku Kowalski ale tylko pod warunkiem, % e

w tabeli jeszcze taka osoba nie jest wstawiona.

BEGIN

INSERT INTO uczniowie (imie, nazwisko)

VALUES (‘Jan’, ‘Kowalski’);

EXCEPTION

WHEN dup_val_on_index THEN

NULL;

END ;

P+2%(%#*'(%+&*D'7(1.F&*13O8$N*&%&%(78-0$.3'&A:C?K&

W bloku PL/SQL mo $ e wyst ! pi # sytuacja b " dna, pomimo tego, $ e Oracle nie zgasza

$ adnego b " du. B ! d ten mo $ e wynika # z logiki aplikacji, np., gdy w wyniku pewnych

oblicze & dostaniemy liczb " ujemn ! na okre % lenie wieku pewnej osoby.

W takim wypadku mo $ na zdefiniowa # wasny wyj ! tek, nast " pnie wywoa # go instrukcj !

RAISE i obsu $ y # w sekcji obsugi wyj ! tków.

DECLARE

wyj"tek1 EXCEPTION ;

BEGIN

...

RAISE wyj"tek1;

...

EXCEPTION

...

WHEN wyj"tek1 THEN

ci%g_instrukcji

...

END;

Tak zdefiniowany i wywoany wyj ! tek jest traktowany na równi z pozostaymi,

predefiniowanymi wyj ! tkami i podlega wszystkim prawom propagacji omówionym poni $ ej.

Przykad 167

DECLARE

v1 number;

v2 number;

e_zly_wiek EXCEPTION ;

BEGIN

...

v1 := v1 – v2 * 365;

IF v1 < 0 THEN

RAISE e_zly_wiek;

END IF;

...

EXCEPTION

...

WHEN e_zly_wiek THEN

INSERT INTO err_msg VALUES (USER, SYSDATE, ‘Wiek < 0’);

...

END;

<G7D09'&%((1.F&GDH/N*&<-'."+&

Wyst ! pienie wszystkich b " dów, nie tylko tych powi ! zanych z predefiniowanymi wyj ! tkami,

powoduje przej % cie do sekcji obsugi wyj ! tków. B " dy te s ! przyporz ! dkowane wyj ! tkowi

OTHERS.

SQLCODE i SQLERRM

Aby podczas obsugi wyj ! tku OTHERS mo $ na byo rozpozna # i w ró $ ny sposób obsu $ y #

ró $ ne b " dy, nale $ y skorzysta # z funkcji SQLCODE i SQLERRM. Zawieraj ! one

odpowiednio kod i komunikat b " du, który spowodowa przej % cie do sekcji obsugi wyj ! tków.

Przykad 168

BEGIN

...

INSERT INTO tabela1 VALUES (...); -- instrukcja powoduje b%d

...

EXCEPTION

...

WHEN OTHERS THEN

IF SQLCODE = -14400 THEN

ci"g_instrukcji

ELSIF ...

...

END ;

Pragma EXCEPTION_INIT

Aby nie korzysta # z funkcji SQLCODE, mo $ na zdefiniowa # wasny wyj ! tek, a nast " pnie

powi ! za # go z konkretnym b " dem Oracle.

DECLARE

wyj"tek EXCEPTION ;

PRAGMA EXCEPTION_INIT (wyj"tek, numer);

BEGIN

W takim przypadku, ka $ de wyst ! pienie b " du o numerze numer b " dzie powodowao

zgoszenie wyj ! tku wyj"tek .

S TRONA 114 Z 164

S TRONA 115 Z 164

Podstawy Oracle 10g SQL i PL/SQL dla pocz ! tkuj ! cych

Przykad 169

DECLARE

e_zly_zakres_partycji EXCEPTION ;

PRAGMA EXCEPTION_INIT (e_zly_zakres_partycji, -14400);

BEGIN

...

INSERT INTO tabela1 VALUES (...); -- instrukcja powoduje b%d

...

EXCEPTION

WHEN e_zly_zakres_partycji THEN

ci"g_instrukcji

END ;

,-#M'9#*'(%+&7%H&*13O8$N*&

Je $ eli wyj ! tek zostanie obsu $ ony w bloku, w którym zosta zgoszony, to po tym obsu $ eniu

wykonywanie bloku zostaje zako & czone, ale blok zewn " trzny traktuje blok z b " dem tak,

jakby zako & czy si " on pomy % lnie.

Je $ eli wyj ! tek nie zostanie obsu $ ony w bloku wewn " trznym, to blok ten ko & czy si " po

wyst ! pieniu b " du, ale w bloku zewn " trznym w momencie powrotu sterowania z bloku

wewn " trznego zostaje zgoszony nie obsu $ ony w bloku wewn " trznym b ! d, a zatem

sterowanie natychmiast przechodzi do sekcji obsugi b " dów w bloku zewn " trznym.

Je $ eli b ! d równie $ w bloku zewn " trznym nie zosta obsu $ ony, to sytuacja si " powtarza do

momentu obsu $ enia b " du na którym % poziomie zagnie $ d $ enia lub do momentu osi ! gni " cia

najwy $ szego poziomu.

A:C?K@:,,ICB:6C<=@KAA<A&

W PL/SQL mo $ na nie tylko zdefiniowa # wasne wyj ! tki, ale tak $ e wasne b " dy.

Su $ y do tego procedura RAISE_APPLICATION_ERROR.

raise_application_error( numer , komunikat );

Oracle pozostawia u $ ytkownikom do wykorzystania numery od –20,999 do –20,000.

Komunikat mo $ e mie # maksymalnie 512 znaków dugo % ci.

Wywoanie tej funkcji spowoduje b ! d, a SQLCODE i SQLERRM przybior ! odpowiednie

warto % ci podane jako parametry.

Przykad 170

DECLARE

v1 number;

v2 number;

BEGIN

v1 := v1 – v2 * 365;

IF v1 < 0 THEN

raise_application_error(-20001, ’Wiek mniejszy od zera’);

END IF;

END;

Po wykonaniu tego bloku pojawi si " komunikat:

ORA-20001: Wiek mniejszy od zera

ORA-06512: at line 7

8.2.5. Podsumowanie

Najwa $ niejsze wiadomo % ci, jakie powiniene % posiada # po przerobieniu poprzedniego

rozdziau, to:

• Jaka jest gówna cecha odró $ niaj ! ca procedury i funkcje od bloków anonimowych?

• Na czym polegaj ! ró $ nice pomi " dzy funkcjami i procedurami?

• W jaki sposób stworzy # procedur " i funkcj " ?

• W jaki sposób przekazywa # parametry do procedur i funkcji?

• Jak wywoa # podprogram skadowany z parametrami?

• Jakie trzeba mie # przywileje, aby móc wykona # podprogram?

• Co to s ! wyj ! tki?

• W jaki sposób mo $ na zareagowa # na wyst ! pienie wyj ! tku lub b " du?

• Jak i po co definiowa # wasne wyj ! tki i jak je wywoywa # ?

• W jaki sposób powi ! za # wasny wyj ! tek z b " dem Oracle?

• Jak korzysta # z SQLCODE i SQLERRM?

• Co si " dzieje, je $ eli wyj ! tek nie zostanie obsu $ ony?

• Jak mo $ na zdefiniowa # i wywoa # wasny b ! d?

Je $ eli nie potrafisz wyczerpuj ! co odpowiedzie # na ka $ de z powy $ szych pyta & , popro %

instruktora o powtórne, b ! d ' bardziej szczegóowe wyja % nienie w ! tpliwego tematu.

S TRONA 116 Z 164

S TRONA 117 Z 164

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: