Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych
8.1.2. Rodzaje bloków PL/SQL
W PL/SQL istniej! trzy podstawowe rodzaje bloków.
8. PODSTAWY PL/SQL
!"#$%&'(#(%)#*+&
Nie s! przechowywane na serwerze bazy danych ani w postaci kodu, ani w postaci
8.1. Wst!p
skompilowanej. Przed ka$dym wykonaniem musz! by# kompilowane.
DECLARE
8.1.1. Budowa bloku PL/SQL
/* Sekcja deklaracyjna */
Podstawow! jednostk! j"zyka PL/SQL jest blok.
BEGIN
Blok PL/SQL przed wykonaniem musi zosta# skompilowany, nie kompiluje si" jednak do
/* Sekcja wykonywalna */
postaci wykonalnej (.exe, .dll, .lib), któr! mo$na uruchomi# z poziomu systemu operacyjnego. EXCEPTION
/* Sekcja obsługi bł!dów */
(!) W PL/SQL nie mo!na bezpo"rednio u!ywa# !adnych instrukcji DDL.
END;
Ka$dy blok PL/SQL mo$e zawiera# trzy sekcje:
DECLARE
,-#.+/0-1&%&20($.3+&
/* Sekcja deklaracyjna */
BEGIN Procedury i funkcje (podprogramy składowane) s! to bloki PL/SQL, które maj! swoj! nazw".
/* Sekcja wykonywalna */
S! one kompilowane tylko jeden raz podczas tworzenia. Na serwerze bazy danych jest
EXCEPTION
przechowywany zarówno kod procedury, jak i posta# skompilowana. Wykonanie procedury
/* Sekcja obsługi bł!dów */
lub funkcji nie wymaga ponownej kompilacji.
END;
Do procedur i funkcji mog! by# przekazywane parametry. Parametry mog! by# równie$
Sekcja deklaracyjna i sekcja obsługi wyj!tków s! opcjonalne, zatem najprostszy blok PL/SQL
przekazywane z procedur i funkcji do %rodowiska, które je wywołuje.
mo$e wygl!da# w nast"puj!cy sposób:
CREATE OR REPLACE PROCEDURE procedura1
BEGIN
IS
/* Sekcja wykonywalna */
/* Sekcja deklaracyjna */
END;
BEGIN
Przykład 150
/* Sekcja wykonywalna */
Napisa! blok PL/SQL, który b"dzie wstawiał do tabeli grupy_cen nowy rekord.
EXCEPTION
BEGIN
/* Sekcja obsługi bł!dów */
INSERT INTO grupy_cen VALUES (30, 10000, 30000);
END;
END;
415*'"'.5+&
Bloki PL/SQL mog! by# w sobie zagnie$d$ane.
Wyzwalacze s! to bloki PL/SQL, które nie mog! by# wywoływane bezpo%rednio poleceniem
SQL. Ich wykonanie jest bezpo%rednio zale$ne od zaj%cia pewnego zdarzenia, z którym
zostały powi!zane.
Do wyzwalaczy nie mo$na bezpo%rednio przekaza# parametrów.
Przed wersj! 7.3 wyzwalacze były przechowywane w bazie danych tylko w postaci kodu. Od
wersji 7.3 wł!cznie przechowywana jest tak$e posta# skompilowana wyzwalaczy, co ma
wpływ na wydajno%#.
CREATE OR REPLACE TRIGGER trigger1
BEFORE INSERT ON tabela1
DECLARE
/* Sekcja deklaracyjna */
BEGIN
/* Sekcja wykonywalna */
EXCEPTION
/* Sekcja obsługi bł!dów */
END;
STRONA 98 Z 164 STRONA 99 Z 164
Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych
Przykład 151:
8.1.3. Bloki PL/SQL a transakcje
-- Poprawny blok autonomiczny
Polecenie COMMIT lub ROLLBACK wydane w bloku PL/SQL maj! wpływ nie tylko na dane
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION; zmodyfikowane w tym$e bloku, ale na wszystkie dane zmodyfikowane w całej transakcji,
BEGIN
niezale$nie od tego, w jakim bloku ta transakcja została rozpocz"ta, dlatego nie zaleca si"
INSERT INTO ...
wstawiania w procedurach instrukcji steruj!cych transakcjami.
COMMIT;
END; 6-'(7'$.3+&'08#(#)%.5(+&
/
Wszystkie operacje SQL odbywaj! si" w kontek%cie pewnych transakcji, które musz! zosta#
-- Niepoprawny blok autonomiczny albo w cało%ci zatwierdzone lub w cało%ci wycofane. Przed wersj! 8i nie było mo$liwo%ci,
DECLARE
aby pewne operacje zostały zatwierdzone lub wycofane niezale$nie od innych operacji w tej
v_1 number;
samej transakcji. W Oracle 8i i wy$ej mo$na tego dokona# dzi"ki transakcjom
PRAGMA AUTONOMOUS_TRANSACTION;
autonomicznym.
BEGIN
INSERT INTO ...
Transakcja autonomiczna to transakcja, która wykonuje si" w kontek%cie innej transakcji,
COMMIT;
zwanej transakcj! zewn"trzn!, ale jest całkowicie od niej niezale$na.
v_1 := 10;
END;
,-'9)'&:;6<=<><;?@6A:=?:B6C<=&
/
Jedynym sposobem, aby wykona# pewne instrukcje w transakcji autonomicznej jest
-- Niepoprawny blok autonomiczny
umieszczenie ich w jednym bloku PL/SQL i zaznaczenie tego bloku jako autonomicznego
CREATE OR REPLACE PROCEDURE procedura1
poprzez wprowadzenie w sekcji deklaracyjnej pragmy AUTONOMOUS_TRANSACTION.
IS
v_1 number;
Nie wszystkie bloki PL/SQL mog! by# przekształcone w bloki autonomiczne. Tylko
BEGIN
nast"puj!ce typy mog! zawiera# pragm" autonomiczno%ci:
...
v_1 := 10;
" najbardziej zewn"trzne bloki anonimowe,
DELETE FROM tabela1 ...
DECLARE " procedury i funkcje samodzielne i w pakietach,
PRAGMA AUTONOMOUS_TRANSACTION;
" wyzwalacze.
BEGIN
INSERT INTO tabela2...
4D'E.%*#E.%&8-'(7'$.3%&'08#(#)%.5(1.F&
COMMIT;
END; Wszystkie polecenia do sterowanie transakcjami: COMMIT. ROLLBACK, SAVEPOINT,
v_1 := 10;
SET TRANSACTION mog! by# u$ywane w transakcjach autonomicznych. Savepointy s!
...
lokalne w transakcji autonomicznej i nie mo$na wycofa# operacji z transakcji autonomicznej
END;
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 cała transakcja autonomiczna jest wycofywana.
(!) Poniewa! transakcja autonomiczna i transakcja zewn$trzna s% niezale!ne, to równie! blokuj%
sobie nawzajem zasoby, tak jakby były to dwie rywalizuj%ce ze sob% transakcje wykonuj%ce si$
równolegle w dwu sesjach.
STRONA 100 Z 164 STRONA 101 Z 164
Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych
8.1.5. Instrukcja SELECT i inne instrukcje DML w PL/SQL
8.1.4. Deklarowanie zmiennych
Instrukcje DML: INSERT, UPDATE i DELETE w PL/SQL stosuje si" podobnie jak w SQL, z t!
W PL/SQL niektóre typy danych s! inne ni$ w SQL:
ró$nic!, $e zamiast podawania warto%ci stałych mo$na stosowa# zmienne lub nawet
" CHAR(L)  maksymalna długo%# wynosi 32768 znaków zamiast 255 w SQL wyra$enia.
" VARCHAR2(L)  maksymalna długo%# wynosi 32768 znaków zamiast 4000 w SQL. Przykład 152
" BOOLEAN  typ, który mo$e przyjmowa# tylko trzy warto%ci: TRUE, FALSE, NULL.
Napisa! blok PL/SQL, który wstawi nowe zamówienie dla Jana Kowalskiego wraz z dwoma pozycjami
Nie miał swojego odpowiednika w SQL. obejmuj#cymi komputer i pi"! procesorów. Identyfikatory zamówienia i pozycji powinny zosta!
Zmienne s! deklarowane w sekcji deklaracyjnej bloku PL/SQL. zaczerpni"te z sekwencji.
DECLARE
Składnia deklaracji zmiennej jest nast"puj!ca:
v_id_klienta number (9) := 1000;
nazwa_zmiennej typ_zmiennej [CONSTANT] [NOT NULL] [:= warto"#];
v_id_komputera number (9) := 340;
Zamiast operatora przypisania mo$e pojawi# si" słowo DEFAULT.
v_id_procesora number (9) := 510;
Zamiast jawnego podawania typu zmiennej mo$na odwoła# si" do typu zmiennej wcze%niej
BEGIN
zadeklarowanej lub do typu kolumny bazodanowej poprzez u$ycie nazwy tej zmiennej INSERT INTO zamowienia (zam_id, zam_numer, zam_data_zamowienia,
zam_data_realizacji, zam_opis, zam_klt_id)
(nazwy kolumny) i operatora %TYPE.
VALUES (seq_zamowienia.nextval, 200, sysdate, null,
(!) Je!eli zmienna nie zostanie zainicjowana, to jej warto"# wynosi NULL.
'Nowe zamówienie na komputery', v_id_klienta);
INSERT INTO pozycje_zamowien(pzm_id, pzm_lp, pzm_ilosc,
Przykład poprawnych deklaracji:
pzm_zam_id, pzm_tow_id)
DECLARE
VALUES (seq_pozycje_zamowien.nextval, 1, 1,
v_1 number(9);
seq_zamowienia.currval, v_id_komputera);
v_2 number CONSTANT := 3.14;
INSERT INTO pozycje_zamowien(pzm_id, pzm_lp, pzm_ilosc,
v_3 varchar2(10) :=  Ala ;
pzm_zam_id, pzm_tow_id)
v_4 v_1%TYPE;
VALUES (seq_pozycje_zamowien.nextval, 2, 5,
v_5 klienci.imie%TYPE;
seq_zamowienia.currval, v_id_procesora);
v_6 char (4) NOT NULL :=  Ala ;
COMMIT;
v_7 v_1%TYPE;
END;
BEGIN
...
Inaczej jest w przypadku instrukcji SELECT. Je$eli u$ywamy jej w zwykłym SQL, to wynik
zwrócony przez t! instrukcj" jest wy%wietlany na konsoli. W PL/SQL nie ma konsoli, a dane
Przykład niepoprawnych deklaracji:
DECLARE zwrócone przez zapytanie nie powinny zosta# pokazane u$ytkownikowi, ale przetworzone
v_1 number(2) := 123;
przez kolejne instrukcje kodu. Aby to umo$liwi#, instrukcja SELECT w PL/SQL została
v_2,v_3 varchar2(10);
wzbogacona o kolejn! klauzul", która pozwala przekierowa# warto%ci zwrócone przez
v_4 number(3) NOT NULL;
zapytanie do wskazanych zmiennych.
v_5 varchar2;
SELECT kol_1, kol_2, kol_3, ..., kol_n
BEGIN
INTO zmienna_1, zmienna_2, zmienna_3, ..., zmienna_n
...
FROM tabela
W PL/SQL zasi"g zmiennych, a wi"c obszar kodu, w którym zachowuj! swoj! warto%#
WHERE ...
rozci!ga si" od momentu zadeklarowania zmiennej do ko&ca bloku, w którym zmienna ta
Przykład 153
została zadeklarowana.
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.
STRONA 102 Z 164 STRONA 103 Z 164
Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych
DECLARE
C(78-0$.3'&*%+"#$-#8(+9#&*1G#-0&
v_id_klienta number (9);
v_id_komputera number (9); Instrukcja ta pozwala na zwróceniepewnej warto%ci w zale$no%ci od warto%ci wskazanego
v_id_procesora number (9);
wyra$enia.
BEGIN
zmienna := CASE wyra!enie_testowane
SELECT klt_id
WHEN wyra!enie1 THEN warto#$1
INTO v_id_klienta
WHEN wyra!enie2 THEN warto#$2
FROM klienci
...
WHERE klt_imie = 'Jan'
WHEN wyra!enieN THEN warto#$N
AND klt_nazwisko = 'Kowalski';
[ELSE warto#$N+1]
INSERT INTO zamowienia (zam_id, zam_numer, zam_data_zamowienia,
END;
zam_data_realizacji, zam_opis, zam_klt_id)
Instrukcja wielokrotnego wyboru pozwala tak$e wykona# pewne instrukcje w zale$no%ci od
VALUES (seq_zamowienia.nextval, 200, sysdate, null,
warunku.
'Nowe zamówienie na komputery', v_id_klienta);
CASE wyra!enie_testowane
SELECT tow_id
WHEN wyra!enie1 THEN instrukcja1;
INTO v_id_komputera
WHEN wyra!enie2 THEN instrukcja2;
FROM towary
...
WHERE tow_nazwa = 'Komputer';
WHEN wyra!enieN THEN instrukcjaN
INSERT INTO pozycje_zamowien(pzm_id, pzm_lp, pzm_ilosc,
[ELSE instrukcjaN+1]
pzm_zam_id, pzm_tow_id)
END;
VALUES (seq_pozycje_zamowien.nextval, 1, 1,
seq_zamowienia.currval, v_id_komputera); ,H8"'&I<<,&
SELECT tow_id
Najprostsza p"tla składa si" z ciała instrukcji, które maj! by# powtarzane, zamkni"tego
INTO v_id_procesora
FROM towary
mi"dzy ogranicznikami LOOP i END LOOP.
WHERE tow_nazwa = 'Procesor Pentium III 700 MHz';
LOOP
INSERT INTO pozycje_zamowien(pzm_id, pzm_lp, pzm_ilosc,
ci%g_instrukcji
pzm_zam_id, pzm_tow_id)
END LOOP;
VALUES (seq_pozycje_zamowien.nextval, 2, 5,
seq_zamowienia.currval, v_id_procesora); Za ka$dym razem, gdy wykonanie osi!gnie instrukcj" END LOOP sterowanie jest
COMMIT;
przekazywane z powrotem do instrukcji poprzedzonej przez klauzul" LOOP.
END;
(!) Je!eli korzystamy z instrukcji SELECT w PL/SQL, to instrukcja ta MUSI zwraca# dokładnie Aby wyj%# z takiej p"tli nale$y wykona# instrukcj" EXIT, która powoduje natychmiastowe
jeden wiersz. Je!eli zwróci mniej lub wi$cej, to instrukcja ta ko&czy si$ niepowodzeniem.
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;
8.1.6. Instrukcje steruj%ce
EXIT [WHEN warunek];
C(78-0$.3'&/+.1513('&
Przykład 154
Instrukcja decyzyjna IF pozwala na selektywne wykonywanie pewnych instrukcji w oparciu o
Napisa! blok PL/SQL, który wstawi do tabeli grupy_cen 50 wierszy.
warunek logiczny okre%lony wyra$eniem.
DECLARE
Pełna składnia instrukcji IF:
v1 binary_integer;
IF wyra$enie_logiczne1 THEN
BEGIN
ci%g_instrukcji1
v1 := 0;
ELSIF wyra$enie_logiczne2 THEN
LOOP
ci%g_instrukcji2
v1 := v1 + 1;
...
EXIT WHEN v1 > 50;
ELSIF wyra$enie_logiczne3 THEN
INSERT INTO grupy_cen
ci%g_instrukcji3
VALUES (30, 100000, 500000);
ELSE
END LOOP;
ci%g_instrukcji4
END;
END IF;
,H8"'&4JCIK&
Klauzule ELSIF i ELSE s! opcjonalne i je$eli nie ma takiej potrzeby, mog! zosta# pomini"te.
P"tla WHILE jest nieco zmodyfikowan! p"tl! LOOP. Do zako&czenia nie wymaga ona
Podczas wykonywania instrukcji IF obliczane jest wyra$enie wyra!enie_logiczne1.
wpisania instrukcji EXIT, chocia$ instrukcja ta mo$e si" w niej pojawi#. Warunek
Je$eli jest ono prawdziwe, to wykonywane s! instrukcje ci"g_instrukcji1, a nast"pnie
wykonywania si" p"tli jest okre%lany w jej nagłówku po słowie WHILE.
sterowanie przechodzi do pierwszej instrukcji po klauzuli END IF. W przypadku, gdy
wyra!enie_logiczne1 jest fałszywe, 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).
STRONA 104 Z 164 STRONA 105 Z 164
Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych
WHILE warunek_logiczny LOOP
8.1.7. Podsumowanie
ci%g_instrukcji
Najwa$niejsze wiadomo%ci, jakie powiniene% posiada# po przerobieniu poprzedniego
END LOOP;
rozdziału, to:
P"tla wykonuje si" cyklicznie, dopóki warunek warunek_logiczny jest prawdziwy.
" W jaki sposób jest zorganizowany kod PL/SQL?
Przykład 155
" Z jakich sekcji zbudowany mo$e by# blok PL/SQL?
Napisa! blok PL/SQL, który wstawi do tabeli grupy_cen 50 wierszy.
" Jakie rodzaje bloków mo$na utworzy# w PL/SQL i czym si" one charakteryzuj!?
DECLARE
" Jakie wyst"puj! ró$nice pomi"dzy typami danych w SQL i PL/SQL?
v1 binary_integer;
BEGIN " W jaki sposób deklaruje si" zmienne w bloku PL/SQL?
v1 := 0;
" Jak działa konstrukcja decyzyjna?
WHILE v1 < 50 LOOP
" Jakie istniej! w PL/SQL p"tle i jak działaj!?
v1 := v1 + 1;
Je$eli nie potrafisz wyczerpuj!co odpowiedzie# na ka$de z powy$szych pyta&, popro%
INSERT INTO grupy_cen VALUES (30, 100000, 500000);
END LOOP; instruktora o powtórne, b!d' bardziej szczegółowe wyja%nienie w!tpliwego tematu.
END;
,H8"'&LP"tla FOR posiada dodatkowo przed słowem kluczowym LOOP fraz" steruj!c! okre%laj!c!
liczb" iteracji do wykonania.
Składnia 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 słowa 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.
Przykład 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 wpływu na p!tl!
END;
STRONA 106 Z 164 STRONA 107 Z 164
Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych
8.1.8. 'wiczenia
8.2. Procedury i funkcje
67. Napisa# i wykona# prosty blok anonimowy, który wypisze napis:  Wykonałem si" na
konsol" klienta. Skorzysta# z procedury wbudowanej
8.2.1. Tworzenie i usuwanie podprogramów składowanych
dbms_output.put_line(tekst). Nale$y pami"ta# o wł!czeniu opcji SQL*Plus
Procedury i funkcje s! to bloki PL/SQL przechowywane w bazie danych zarówno w postaci
serveroutput.
kodu jak i w postaci skompilowanej.
68. Zamieni# powy$szy blok na procedur" i wykona# j!.
Tworzenie procedury
69. * Sprawd', jaki jest format przechowywania danych w zmiennych typu char i varchar2. CREATE OR REPLACE PROCEDURE nazwa_procedury
IS
Jak s! przechowywane spacje?
BEGIN
" Utwórz i uruchom nast"puj!cy blok anonimowy:
...
" Zadeklaruj w nim dwie zmienne typu char (25) i jedn! zmienn! typu
END;
varchar2(25).
Przykład 157
" Przypisz do jednej ze zmiennych typu char warto%#  Ala ma kota , do dwu
Stworzy! procedur", która usunie z tabeli klienta o numerze 1001 wraz z wszystkimi podległymi
pozostałych zmiennych przypisz po pi"# spacji.
rekordami.
CREATE OR REPLACE PROCEDURE del_kli_1001
" Wypisz zawarto%# zmiennych na konsol" korzystaj!c z procedury
IS
dbms_output.put_line(tekst). Dla lepszego zobrazowania wyników
BEGIN
przekształ# zmienne przed wypisaniem wywołuj!c funkcj" translate:
-- usuni!cie pozycji zamówie& zło$onych przez klienta 1001
translate(zmienna,   ,  * ). DELETE pozycje_zamowien
WHERE pzm_zam_id IN (SELECT zam_id
" Przeanalizuj wyniki.
FROM zamowienia
70. Napisz i wykonaj blok PL/SQL, który wstawi do pewnej tabeli liczby podzielne przez 7 i
WHERE zam_klt_id = 1001);
liczby podzielne przez 13 z zakresu od jeden do tysi!ca.
-- usuni!cie zamowie& zło$onych przez klienta 1001
DELETE zamowienia
" Utwórz tabel" z kolumn! typu number(9).
WHERE zam_klt_id = 1001;
" Napisz blok anonimowy, który:
-- usuni!cie klienta
" Wyczy%ci t" tabel" i wło$y do kolumny numerycznej odpowiednie warto%ci DELETE klienci
WHERE klt_id = 1001;
zgodne z wymaganiami zadania. Skorzystaj z funkcji mod.
END;
Dlaczego do czyszczenia tabeli nie mogłe% u$y# polecenia truncate
Tworzenie funkcji
table?
CREATE OR REPLACE FUNCTION nazwa_funkcji RETURN typ_zwracany
" Zatwierdzi transakcj".
IS
BEGIN
" Policz, ile wierszy zostało wstawionych? Poprawna ilo%# to 208.
...
" Zapisz skrypt w pliku tekstowym.
RETURN warto"#;
Je$eli masz kłopoty ze zrozumieniem lub wykonaniem #wiczenia, lub nie rozumiesz,
...
dlaczego serwer Oracle zachowuje si" w taki, a nie inny sposób, popro% instruktora o
END;
dodatkowe wyja%nienia.
Przykład 158
Stworzy! funkcj", która sprawdzi, czy jakie$ zamówienie czeka na realizacj" dłu%ej ni% miesi#c. Je%eli
takie zamówienia istniej# ma zosta! zwrócona prawda, w przeciwnym wypadku fałsz.
CREATE OR REPLACE FUNCTION opoznienia
RETURN boolean
IS
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;
Wywołanie procedury w kodzie PL/SQL polega na podaniu jej nazwy
STRONA 108 Z 164 STRONA 109 Z 164
Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych
nazwa_procedury;
?M#7NG&*1*#D1*'(%'&
Poniewa$ ka$da funkcja zwraca jak!% warto%#, to warto%# ta musi zosta#  zagospodarowana .
Wywoływa# podprogramy z parametrami mo$na na dwa sposoby:
W zwi!zku z tym funkcja musi by# wywoływana jako składnik wyra$enia, w najprostszym
Notacja pozycyjna
przypadku:
W notacji pozycyjnej parametry aktualne s! przyporz!dkowywane do parametrów formalnych
zmienna := nazwa_funkcji;
na podstawie pozycji, na której wyst!piły poszczególne parametry w momencie deklaracji i
Przykład 159
wywołania.
Napisa! blok anonimowy, który wywoła wcze$niej stworzon# procedur" i funkcj"
Przykład 162
DECLARE
v_opoznienia boolean; -- deklarecja
BEGIN CREATE PROCEDURE p1 (
del_kli_1001; par1 IN varchar2,
v_opoznienia := opoznienia; par2 IN number,
END; par3 OUT number
)
Usuwanie procedury
IS ...
DROP PROCEDURE nazwa_procedury;
-- wywołanie
Usuwanie funkcji
p1( Ala ma kota ,
DROP FUNCTION nazwa_funkcji;
5,
Przykład 160
v_num
);
Usun#! stworzon# poprzednio procedur" i funkcj".
DROP PROCEDURE del_kli_1001; Notacja imienna
DROP FUNCTION opoznienia;
W notacji imiennej kolejno%# wpisywania parametrów nie ma znaczenia. W momencie
wywoływania okre%lamy %ci%le, który parametr aktualny ma by# przypisany do którego
8.2.2. Parametry parametru formalnego.
Przykład 163
?M#7NG&/+$"'-'.3%&
-- deklarecja
CREATE PROCEDURE | FUNCTION nazwa_podprogramu(
CREATE PROCEDURE p1 (
nazwa typ typ_warto"ci DEFAULT warto"#
par1 IN varchar2,
{, nazwa typ typ_warto"ci DEFAULT warto"#}
par2 IN number,
)
par3 OUT number
IS
)
...
IS ...
typ_warto#ci nie mo$e zawiera# precyzji. Precyzja jest pobierana z parametru aktualnego
-- wywołanie
a nie z formalnego. p1(par1 =>  Ala ma kota ,
par2 => 5,
Przykład 161
par3 => v_num
-- dobrze
);
CREATE PROCEDURE p1 (p_1 IN varchar2) IS ...
Przykład 164
-- 'le
CREATE PROCEDURE p1 (p_1 IN varchar2(64) ) IS ...
Stworzy! i wywoła! procedur", która dla zadanego przez numer zamówienia zwróci rabat, jaki
przysługuje do tego zamówienia w procentach i kwotowo.
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 wywołania 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 obsłu!ony wewn%trz tego podprogramu, to
kopiowanie parametru formalnego do aktualnego w parametrach OUT i IN OUT nie wyst$puje.
STRONA 110 Z 164 STRONA 111 Z 164
Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych
CREATE OR REPLACE PROCEDURE oblicz_rabat
" instrukcja odwołuje si" do nieistniej!cych lub niepoprawnych obiektów (np. próba
(p_numer IN number,
wykonania procedury, która jest skompilowana z bł"dami),
p_rabat_proc OUT number,
" próba modyfikacji danych powoduj!ca naruszenie wi"zów integralno%ci, itp.
p_rabat_kw OUT number)
IS W bazie danych Oracle jest zdefiniowanych kilka tysi"cy ró$nych bł"dów odpowiadaj!cych
v_zam_id zamowienia.zam_id%TYPE;
ró$nym nie przewidzianym sytuacjom. Ka$dy bł!d, jaki jest zdefiniowany w bazie danych
v_kwota number(6);
Oracle ma swój unikalny numer i odpowiadaj!cy mu komunikat.
BEGIN
Przykład 165
-- odczytanie identyfikatora zamówienia
SELECT zam_id INTO v_zam_id
Wykona! instrukcj", która spowoduje wyst#pienie bł"du.
FROM zamowienia
SQL> DROP USER sys;
WHERE zam_numer = p_numer;
DROP USER sys
-- policzenie kwoty zamówienia
*
SELECT sum(PZ.pzm_ilosc*T.tow_cena) INTO v_kwota
ERROR w linii 1:
FROM pozycje_zamowien PZ, towary T
ORA-01031: insufficient privileges
WHERE PZ.pzm_tow_id = T.tow_id
(!) Je!eli próba wykonania instrukcji spowoduje bł%d, to oznacza to, !e dana instrukcja si$ nie
AND PZ.pzm_zam_id = v_zam_id;
wykonała, nie powoduje to natomiast wycofania transakcji, w której była podj$ta próba wykonania
-- odczytanie przysługujacego rabatu
si$ instrukcji.
SELECT grc_rabat, grc_rabat*v_kwota/100
INTO p_rabat_proc, p_rabat_kw 413O8$%&M-+/+2%(%#*'(+&
FROM grupy_cen
Je$eli bł!d zostanie spowodowany przez pojedyncz! instrukcj" SQL wpisan! przez jakiego%
WHERE v_kwota BETWEEN grc_cena_od AND grc_cena_do;
u$ytkownika, to instrukcja nie zostanie wykonana, w sesji u$ytkownika pojawi si" numer i
END;
komunikat bł"du i sterowanie powróci do konsoli u$ytkownika, który b"dzie mógł
--
-- wywołanie procedury
odpowiednio zareagowa# na powstanie nieprzewidzianej sytuacji.
DECLARE
Je$eli natomiast bł!d wyst!pi podczas wykonywania si" kodu PL/SQL, to u$ytkownik nie
v_pr number(2);
mo$e bezpo%rednio na niego zareagowa#, natomiast mo$e przewidzie# wyst!pienie bł"du i
v_kw number(10);
BEGIN odpowiednio przygotowa# podprogram w celu obsługi takiej sytuacji. Bardzo wa$nym
oblicz_rabat(124, v_pr, v_kw); elementem ka$dej aplikacji jest wła%nie obsługa bł"dów.
dbms_output.put_line('Rabat: '
Je$eli w trakcie wykonywania si" kodu PL/SQL wygenerowany zostanie bł!d bazy danych
||to_char(v_pr, '999G9990D99')||' %');
Oracle, to w kodzie PL/SQL zgłaszany jest tzw. wyj!tek.
dbms_output.put_line('Rabat: '
W PL/SQL zdefiniowanych jest kilkana%cie standartowych wyj!tków, które s! zgłaszane
||to_char(v_kw, '999G9990D99')||' PLN');
END; przez Oracle w przypadku wyst!pienia pewnych bł"dów podczas wykonywania si" kodu.
Poni$ej przedstawiono najcz"%ciej zgłaszane wyj!tki:
invalid_cursor Je%li u$yjemy nie otwartego kursora.
8.2.3. Przywileje zwi%zane z podprogramami, kontekst
cursor_already_open Je%li usiłujemy otworzy# ju$ otwarty kursor.
podprogramów
dup_val_on_index Naruszenie unikalno%ci warto%ci w kolumnie bazodanowej.
Aby wykona# jakikolwiek podprogram potrzebne jest uprawnienie EXECUTE na tym
no_data_found Brak danych do odszukania lub przetworzenia.
podprogramie.
too_many_rows Zbyt wiele wierszy, gdy spodziewano si" jednego.
U$ytkownik wykonuj!cy podprogram nie musi mie# praw do obiektów, do których odwołuje
zero_divide Próba wykonania dzielenia przez zero.
si" podprogram.
rowtype_mismatch Niezgodno%# typów danych kursora.
Podprogramy s! wykonywane w kontek%cie danych i obiektów wła%ciciela podprogramu, a
invalid_number Zmienna znakowa została u$yta jako numeryk.
nie u$ytkownika, który wykonuje podprogram.
(!) Wyj%tek i bł%d to nie to samo.
Wywołanie wyj!tku najcz"%ciej jest powodowane wyst!pieniem bł"du, ale zdecydowana
wi"kszo%# bł"dów nie powoduje zgłoszenia $adnego wyj!tku. Mog! tak$e istnie# wyj!tki,
8.2.4. Obsługa bł$dów i wyj%tków
które nie s! powodowane bł"dem na bazie danych Oracle.
!DH/1&*&G'5%+&/'(1.F&<-'."+&
Je$eli u$ytkownik lub jakikolwiek proces próbuje wykona# czynno%#, której serwer bazy
W momencie wyst!pienia jakiegokolwiek bł"du (nie tylko zwi!zanego ze zdefiniowanym
danych nie potrafi wykona#, to serwer bazy danych generuje bł!d. W takim wypadku
wyj!tkiem), wykonywanie bloku jest przerwane, a sterowanie przechodzi do sekcji obsługi
mówimy, $e pewna konkretna instrukcja spowodowała wyst!pienie bł"du.
wyj!tków w bloku, w którym bł!d wyst!pił.
Polecenie mo$e zako&czy# si" bł"dem, gdy:
Sekcja obsługi wyj!tków składa si" z sekwencji nast"puj!cych po sobie ci!gów instrukcji, z
" nie ma fizycznych mo$liwo%ci wykonania si" instrukcji (np. koniec miejsca
których ka$dy jest dedykowany obsłudze okre%lonego wyj!tku.
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),
STRONA 112 Z 164 STRONA 113 Z 164
Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych
BEGIN
Przykład 167
...
DECLARE
EXCEPTION
v1 number;
WHEN wyj%tek1 THEN
v2 number;
ci%g_instrukcji1
e_zly_wiek EXCEPTION;
WHEN wyj%tek2 THEN
BEGIN
ci%g_instrukcji2
...
WHEN OTHERS THEN
v1 := v1  v2 * 365;
ci%g_instrukcji3
IF v1 < 0 THEN
END;
RAISE e_zly_wiek;
Wyj!tek OTHERS słu$y do przechwycenia wszystkich bł"dów (nie tylko wyj!tków). W
END IF;
powy$szym przykładzie ci"g_instrukcji3 wykona si" w przypadku wyst!pienia ka$dego ...
EXCEPTION
bł"du lub wyj!tku poza wyj!tkami wyj"tek1 i wyj"tek2.
...
Po obsłu$eniu wyj!tku sterowanie nie powraca ju$ do bloku, w którym wyst!pił bł!d.
WHEN e_zly_wiek THEN
Przykład 166
INSERT INTO err_msg VALUES (USER, SYSDATE,  Wiek < 0 );
...
Wstawi! do tabeli uczniowie osob" o imieniu Jan i nazwisku Kowalski ale tylko pod warunkiem, %e
END;
w tabeli jeszcze taka osoba nie jest wstawiona.
BEGIN
INSERT INTO uczniowie (imie, nazwisko)
Wyst!pienie wszystkich bł"dów, nie tylko tych powi!zanych z predefiniowanymi wyj!tkami,
VALUES ( Jan ,  Kowalski );
powoduje przej%cie do sekcji obsługi wyj!tków. Bł"dy te s! przyporz!dkowane wyj!tkowi
EXCEPTION
WHEN dup_val_on_index THEN OTHERS.
NULL;
SQLCODE i SQLERRM
END;
Aby podczas obsługi wyj!tku OTHERS mo$na było rozpozna# i w ró$ny sposób obsłu$y#
P+2%(%#*'(%+&*D'7(1.F&*13O8$N*&%&%(78-0$.3'&A:C?K&
ró$ne bł"dy, nale$y skorzysta# z funkcji SQLCODE i SQLERRM. Zawieraj! one
W bloku PL/SQL mo$e wyst!pi# sytuacja bł"dna, pomimo tego, $e Oracle nie zgłasza odpowiednio kod i komunikat bł"du, który spowodował przej%cie do sekcji obsługi wyj!tków.
$adnego bł"du. Bł!d ten mo$e wynika# z logiki aplikacji, np., gdy w wyniku pewnych Przykład 168
oblicze& dostaniemy liczb" ujemn! na okre%lenie wieku pewnej osoby. BEGIN
...
W takim wypadku mo$na zdefiniowa# własny wyj!tek, nast"pnie wywoła# go instrukcj!
INSERT INTO tabela1 VALUES (...); -- instrukcja powoduje bł%d
RAISE i obsłu$y# w sekcji obsługi wyj!tków.
...
DECLARE
EXCEPTION
wyj"tek1 EXCEPTION;
...
BEGIN
WHEN OTHERS THEN
...
IF SQLCODE = -14400 THEN
RAISE wyj"tek1;
ci"g_instrukcji
...
ELSIF ...
EXCEPTION
...
...
END;
WHEN wyj"tek1 THEN
Pragma EXCEPTION_INIT
ci%g_instrukcji
... Aby nie korzysta# z funkcji SQLCODE, mo$na zdefiniowa# własny wyj!tek, a nast"pnie
END;
powi!za# go z konkretnym bł"dem Oracle.
Tak zdefiniowany i wywołany wyj!tek jest traktowany na równi z pozostałymi, DECLARE
wyj"tek EXCEPTION;
predefiniowanymi wyj!tkami i podlega wszystkim prawom propagacji omówionym poni$ej.
PRAGMA EXCEPTION_INIT(wyj"tek, numer);
BEGIN
W takim przypadku, ka$de wyst!pienie bł"du o numerze numer b"dzie powodowało
zgłoszenie wyj!tku wyj"tek.
STRONA 114 Z 164 STRONA 115 Z 164
Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych
Przykład 169
8.2.5. Podsumowanie
DECLARE
Najwa$niejsze wiadomo%ci, jakie powiniene% posiada# po przerobieniu poprzedniego
e_zly_zakres_partycji EXCEPTION;
rozdziału, to:
PRAGMA EXCEPTION_INIT(e_zly_zakres_partycji, -14400);
" Jaka jest główna cecha odró$niaj!ca procedury i funkcje od bloków anonimowych?
BEGIN
...
" Na czym polegaj! ró$nice pomi"dzy funkcjami i procedurami?
INSERT INTO tabela1 VALUES (...); -- instrukcja powoduje bł%d
" W jaki sposób stworzy# procedur" i funkcj"?
...
" W jaki sposób przekazywa# parametry do procedur i funkcji?
EXCEPTION
WHEN e_zly_zakres_partycji THEN " Jak wywoła# podprogram składowany z parametrami?
ci"g_instrukcji
" Jakie trzeba mie# przywileje, aby móc wykona# podprogram?
END;
" Co to s! wyj!tki?
,-#M'9#*'(%+&7%H&*13O8$N*&
" W jaki sposób mo$na zareagowa# na wyst!pienie wyj!tku lub bł"du?
Je$eli wyj!tek zostanie obsłu$ony w bloku, w którym został zgłoszony, to po tym obsłu$eniu
" Jak i po co definiowa# własne wyj!tki i jak je wywoływa#?
wykonywanie bloku zostaje zako&czone, ale blok zewn"trzny traktuje blok z bł"dem tak,
" W jaki sposób powi!za# własny wyj!tek z bł"dem Oracle?
jakby zako&czył si" on pomy%lnie.
" Jak korzysta# z SQLCODE i SQLERRM?
Je$eli wyj!tek nie zostanie obsłu$ony w bloku wewn"trznym, to blok ten ko&czy si" po
" Co si" dzieje, je$eli wyj!tek nie zostanie obsłu$ony?
wyst!pieniu bł"du, ale w bloku zewn"trznym w momencie powrotu sterowania z bloku
" Jak mo$na zdefiniowa# i wywoła# własny bł!d?
wewn"trznego zostaje zgłoszony nie obsłu$ony w bloku wewn"trznym bł!d, a zatem
Je$eli nie potrafisz wyczerpuj!co odpowiedzie# na ka$de z powy$szych pyta&, popro%
sterowanie natychmiast przechodzi do sekcji obsługi bł"dów w bloku zewn"trznym.
instruktora o powtórne, b!d' bardziej szczegółowe wyja%nienie w!tpliwego tematu.
Je$eli bł!d równie$ w bloku zewn"trznym nie został obsłu$ony, to sytuacja si" powtarza do
momentu obsłu$enia bł"du na którym% poziomie zagnie$d$enia lub do momentu osi!gni"cia
najwy$szego poziomu.
A:C?K@:,,ICB:6C<=@KAAW PL/SQL mo$na nie tylko zdefiniowa# własne wyj!tki, ale tak$e własne bł"dy.
Słu$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 długo%ci.
Wywołanie tej funkcji spowoduje bł!d, a SQLCODE i SQLERRM przybior! odpowiednie
warto%ci podane jako parametry.
Przykład 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
STRONA 116 Z 164 STRONA 117 Z 164
Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych
8.2.6. 'wiczenia
8.3. Zło"one typy danych i kursory
71. Przekształ# skrypt wstawiaj!cy liczby podzielne przez 7 i liczby podzielne przez 13 na
procedur". Uruchom j!.
8.3.1. Rekordy
72. Napisz funkcj", która b"dzie sprawdzała, czy liczba przekazana jej jako parametr jest
Typ rekordowy grupuje w sobie pewn! ilo%# powi!zanych logicznie ze sob! zmiennych.
podzielna przez 7 lub przez 13. Funkcja powinna zwraca# fałsz lub prawd".
(wietnie nadaje si" do odwzorowania jednego wiersza tabeli w kodzie PL/SQL.
73. Zmodyfikuj i uruchom procedur" z #wiczenia 71, w taki sposób, aby wykorzystywała TYPE typ_rekordowy IS RECORD (
funkcj" napisan! w #wiczeniu 72. zmienna1 typ1,
zmienna2 typ2
74. Zmodyfikuj i uruchom procedur" z poprzedniego #wiczenia w taki sposób, aby miała ona
...
trzy parametry: pierwszy i drugi maj! okre%la# zakres, z jakiego liczby maj! by#
)
wstawiane do tabeli, trzeci ma by# parametrem wyj%ciowym, w którym po wykonaniu si"
Przykład 171
procedury powinna si" znale'# ilo%# wstawionych do tabeli elementów.
Zdefiniowa! typ danych do przechowywania informacji o osobie: nazwiska, imienia i daty urodzenia.
75. * Napisz procedur", dzi"ki której dowiesz si" jaki numer bł"du i jaki komunikat jest
Zadeklarowa! kilka zmiennych nowego typu.
generowany w momencie, gdy nast"puje próba wprowadzenia do zmiennej typu data
DECLARE
warto%ci spoza dopuszczalnego zakresu dat. W tym celu zadeklaruj zmienn! typu data, TYPE t_osoba IS RECORD (
nazwisko varchar2(64) NOT NULL,
przypisz jej dat" dzisiejsz!, a nast"pnie w p"tli niesko&czonej zwi"kszaj dat" o 1000 dni.
imie varchar2(40) NOT NULL,
W pewnym momencie wyst!pi bł!d. Przechwy# go i wy%wietl numer i komunikat.
data_ur date);
Je$eli masz kłopoty ze zrozumieniem lub wykonaniem #wiczenia, lub nie rozumiesz,
v_ojciec t_osoba;
dlaczego serwer Oracle zachowuje si" w taki, a nie inny sposób, popro% instruktora o
v_matka t_osoba;
dodatkowe wyja%nienia. BEGIN
...
Inna metoda deklaracji zmiennej rekordowej polega na referencji do istniej!cej zmiennej lub
do tabeli:
zmienna tabela%ROWTYPE;
Dost"p do pól rekordu uzyskuje si" przy pomocy notacji kropkowej.
Nie mo$na u$y# zmiennej rekordowej bezpo%rednio po słowie kluczowym VALUES w
instrukcji INSERT, ani po słowie INTO w instrukcji SELECT.
Przykład 172
Zdefiniowa! zmienn# do przechowywania informacji o towarze zawartych w tabeli towary.
Nast"pnie wczyta! do tej zmiennej informacje o towarze  Szafka .
DECLARE
v_tow towary%ROWTYPE;
BEGIN
SELECT tow_id, tow_nazwa, tow_jednostka, tow_cena, tow_podatek,
tow_tow_id
INTO v_tow.tow_id, v_tow.tow_nazwa, v_tow.tow_jednostka,
v_tow.tow_cena, v_tow.tow_podatek, v_tow.tow_tow_id
FROM towary
WHERE tow_nazwa =  Szafka ;
...
8.3.2. Tablice
Zmienne typu tablicowego przechowuj! szereg warto%ci innego, jednorodnego typu w postaci
ci!gu pojedynczych elementów. Do poszczególnych pozycji w zmiennej tablicowej
uzyskujemy dost"p poprzez podanie nazwy tej zmiennej i numeru pozycji (indeksu) dla
konkretnego elementu.
TYPE typ_tablicowy IS TABLE OF typ_składowy
INDEX BY BINARY_INTEGER
Zmienne typu tablicowego w PL/SQL nie maj! ograniczonej wielko%ci, mo$na do nich
wkłada# elementy na dowolnej pozycji z zakresu liczb binary_integer. Dopiero
przypisanie warto%ci do jakiego% elementu tablicy tworzy ten element.
Odwołania do poszczególnych pozycji tablicy odbywaj! si" poprzez podanie indeksu w
okr!głych nawiasach.
STRONA 118 Z 164 STRONA 119 Z 164
Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych
Przykład 173
8.3.3. Przetwarzanie tablicowe SQL
DECLARE
TYPE t_nazwiska IS TABLE OF varchar2(100) !"#$%&'"#'()$* %'+,)-.&$* /.#&','* &01'2'3* )* &4-)56'3* &)$,$* &)$"7#4* 2.* )* #* +'#4*
INDEX by binary_integer;
2'(4-8* 9$2(4:* /.,$-$()$:;* !"#4* 7/"#49'95-4-8* &'"<(0'-8* :.=$* %.* /.&.2.&'3*
v_nazwiska t_nazwiska;
('&$%* 2#)$7)>-).0".%($* /"#47/)$7#$()$* /"#$%&'"#'()'* 2'(4-8* /"#$#* /.2/".6"':*
v1 varchar2(100);
!?@AB?;* !.,$6'* .(.* ('* /"#46.%.&'()<* 2'(4-8* &* %'+,)-4* !?@AB?C* '* ('7%>/()$*
BEGIN
v_nazwiska(0) :=  Kowalski ; &1.=$()$*-'1$9*%'+,)-4*2.*%'+$,)*+'#.2'(.&$9C*,<+*&*/"#$-)&(5*7%".(>D*/.+"'()$*&)$,<*
v_nazwiska(1000) :=  NOWAK ;
&)$"7#4*9$2(4:*/.,$-$()$:*)*&-#4%'()$*)-8*2.*%'+,)-4*!?@AB?E.&$9;*
v_nazwiska(-157230) :=  Misiak ;
v_1 := v_nazwiska(1000);
L ...
:8-1G081&5)%+((1.F&8'G"%.#*1.F& F#)>0)*0,'<#<,)*GHIJ??*:.=,)&$*9$7%*/"#$71'()$*&)$,<*&)$"7#4*9$2(4:*/.,$-$()$:*2.*
%'+$,)*+'#.2'(.&$9;*
Zmienne tablicowe posiadaj! szereg atrybutów, które pozwalaj! łatwiej operowa# na ich
zawarto%ci. Poni$ej zostały one pokrótce scharakteryzowane. FORALL zmienna_indeksowa IN min..max
polecenie_sql;
EXIST(n)  Sprawdza, czy na pozycji n w tablicy znajduje si" jaki% element. Zwraca TRUE
lub FALSE.
,-51$D'/&QRS&
COUNT  Zwraca ilo%# elementów znajduj!cych si" w tablicy.
DECLARE
FIRST, LAST  Zwracaj! indeks pierwszego i ostatniego elementu w tablicy. TYPE t_salary IS TABLE OF emp.sal%type
INDEX BY binary_integer;
NEXT(n), PRIOR(n)  Zwracaj! indeks nast"pnego i poprzedniego elementu w tablicy,
TYPE t_ename IS TABLE OF emp.ename%type
wzgl"dem zadanej pozycji n.
INDEX BY binary_integer;
DELETE, DELETE(n), DELETE(m,n)  usuwa elementy z tablicy. W zale$no%ci od v_salary t_numbers;
v_ename t_chars;
parametrów usuwa cał! tablic", n-ty element, lub elementy od m do n.
BEGIN
Przykład 174
-- wypełnienie tablic
Napisa! funkcj", która z zadanej tablicy liczb usunie wszystkie liczby wi"ksze ni% 1000, natomiast
FOR i IN 1..1000 LOOP
liczby ujemne zamieni na zero. Funkcja powinna zwróci! ilo$! elementów, jakie pozostan# w tablicy v_salary(i) := i;
po jej przetworzeniu. v_ename(i) :=  Name  ||to_char(i);
CREATE OR REPLACE FUNCTION modyfikuj_tablice RETURN number END LOOP;
IS
-- Tradycyjne wstawienie
TYPE t_liczby IS TABLE OF number
FOR i IN 1..1000 LOOP
INDEX BY BINARY_INTEGER; -- definicja typu
INSERT INTO emp(sal, ename)
v_liczby_tab t_liczby; -- deklaracja zmiennej tablicowej
VALUES (v_salary(i), v_ename(i));
v_index binary_integer; -- deklaracja zmiennych pomocniczych
END LOOP;
v_koniec binary_integer;
-- wstawienie tablicowe
BEGIN
FORALL i IN 1..1000
-- wywołanie hipotetycznej procedury, która inicjalizuje
-- tablice wstawiajc tam w dowolny sposób dowolna ilo"# liczb, INSERT INTO test_table (sal, ename)
-- np. przeczytanych z pliku lub tabeli bazodanowej
VALUES (v_salary(i), v_ename(i));
tab_init(v_liczby_tab);
END;
-- znalezienie najmniejszego indeksu zaj!tego w tablicy
/
v_index := v_liczby_tab.FIRST;
-- znalezienie najwi!kszego indeksu zaj!tego w tablicy
K,'<#<,' FORALL*:.=$*+43*&40."#47%'('*()$*%4,0.*2.*/.,$-$()'*INSERT;*
v_koniec := v_liczby_tab.LAST;
-- przetwarzanie wszystkich elementów zawartych w tablicy
LOOP
,-51$D'/&QRT&
-- wykonanie operacji
...
IF v_liczby_tab(v_index) > 1000 THEN
v_liczby_tab.DELETE(v_index); FORALL i IN 1..1000
ELSIF v_liczby_tab(v_index) < 0 THEN UPDATE empe
v_liczby_tab(v_index) := 0;
SET ename = v_ename(i)
END IF;
WHERE sal = v_salary(i);
-- je$eli przetworzylimy ostatni element, to koczymy p!tl!
...
EXIT WHEN v_index = v_koniec;
FORALL i IN 1..1000
-- znalezienie indeksu kolejnego elementu
DELETE emp
v_index := v_liczby_tab.NEXT(v_index);
END LOOP; WHERE sal = v_salary(i);
-- zwrócenie ilo#ci elementów w przetworzonej tablicy ...
RETURN v_liczby_tab.COUNT;
END;
STRONA 120 Z 164 STRONA 121 Z 164
Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych
SQL%BULK_EXCEPTIONS(n).error_code R* 2,'* (R%$6.* +1$2<* .2-#4%<9$:4C* 9'0)* +152*
!;IU&B&47%5/)1*
K,'<#<,$* %$* /.#&','95* &4-)56(53* &)>07#5* ),.L3* &)$"7#4* #* %'+$,)* +'#.2'(.&$9* )*
<:)$L-)3*9$*9$2(4:*/.,$-$()$:*&*%'+,)-4*!?@AB?;* 8.3.4. Kursory
Kursory s! konstrukcj! PL/SQL słu$!c! do sekwencyjnego przetwarzania zbioru wielu
BULK COLLECT INTO kolekcja1 [, kolekcja2 ...];
wierszy zwróconych przez zapytanie z bazy danych.
,-51$D'/&QRR&
U#-5178'(%+&5&$0-7#-'&
DECLARE
TYPE t_salary IS TABLE OF emp.sal%type;
Przetwarzanie ka$dego kursora mo$na podzieli# na cztery etapy:
TYPE t_ename IS TABLE OF emp.ename%type;
Deklaracja
v_sal t_salary;
Kursory deklaruje si" w sekcji deklaracyjnej bloku PL/SQL.
v_ename t_ename;
DECLARE
BEGIN
CURSOR nazwa_kursora IS
...
wyra$enie_select;
SELECT sal, ename
BEGIN
BULK COLLECT INTO v_sal, v_ename
wyra!enie_select jest to dowolne zapytanie zwracaj!ce pewn! ilo%# kolumn i wierszy.
FROM emp;
... Otwarcie
FORALL i IN 1..500
Otwarcia kursora dokonujemy w sekcji wykonywalnej bloku PL/SQL. Ten sam kursor mo$e
DELETE emp
by# otwarty tylko jeden raz.
WHERE sal = v_sal(i)
OPEN nazwa_kursora;
RETURNING ename BULK COLLECT INTO v_ename;
Pobieranie wierszy
...
END; Przetworzenie wszystkich danych (lub niekoniecznie wszystkich) wybranych przez zapytanie
/
odbywa si" poprzez sekwencyjne wywoływanie instrukcji FETCH.
FETCH nazwa_kursora INTO lista_zmiennych;
I.#:)'"4* 0.,$0-9)* .* .6"'()-#.($9* 2$M)()-95* &)$,0.L-)* #.7%'95* '<%.:'%4-#()$*
Ka$da kolejna instrukcja FETCH %ci!ga jeden, kolejny wiersz ze zbioru utworzonego przez
".#7#$"#.($* 2.* ".#:)'"<* %'0)$6.C* '+4* /.:)$L-)3* -'14* #+)N"* 2'(4-8* &49>%4-8*
zapytanie. Ka$dy wiersz jest %ci!gni"ty tylko jeden raz i nie ma mo$liwo%ci powtórnego
#'/4%'()$:;*
%ci!gni"cia tego samego wiersza.
Zamkni$cie
Zamkni"cie kursora powoduje zwolnienie zasobów zarezerwowanych na jego otwarcie.
H/$"'-9$*%'+,)-.&'*&40.(<95*7)>*9'0.*/.9$24(-#$*/.,$-$()$;*O*#&)5#0<*#*%4:*',+.*
Kursor przestaje by# dost"pny i mo$e by# ponownie otworzony.
CLOSE nazwa_kursora;
-'1'*./$"'-9'*7)>*&40.(<9$C*',+.*-'1'*./$"'-9'*7)>*()$*<2'9$;**
:8-1G081&$0-7#-'&
O* /"#4/'20<* (/;* 1'2.&'()'* 9$2(4:* /.,$-$()$:* 0),0<* %47)>-4* &)$"7#4C* 624* )7%()$9$*
Atrybuty kursora opisuj! pewne jego cechy i mog! by# wykorzystane np. do okre%lenia, kiedy
2<=$* /"'&2./.2.+)$P7%&.C* =$* 0%N"4L* #* %4-8* &)$"7#4* 9$7%* ()$/./"'&(4C* +>2#)$:4*
nale$y zaprzesta# pobierania kolejnych wierszy.
:<7)$,)* &40.('3* 2<=.* /"'-4C* 0%N"'* #.7%'()$* &4-.M'('C* 624=* 9<=* &7%'&).($* 0),0'*
Do atrybutów kursora odwołujemy si" w nast"puj!cy sposób:
%47)>-4*&)$"7#4*+>2#)$*:<7)'1.*+43*&4-.M'(4-8;* nazwa_kursora%nazwa_atrybutu
Istniej! cztery atrybuty kursora:
H2* &$"79)* Q)* :.=,)&$* 9$7%* &40.('()$* +$#&'"<(0.&$* /.,$-$()'* %'+,)-.&$6.* +$#*
FOUND  Zwraca TRUE, je%li ostatnio wykonana komenda FETCH zwróciła wiersz, w
#&"'-'()'* <&'6)*('* /.9$24(-#$* +1>24* R* %$* "$0."24C* 0%N"$* :.65* #.7%'3* &7%'&).($C*
przeciwnym wypadku FALSE. Je$eli kursor jest nie otwarty, odwołanie si" powoduje
#.7%'95*&7%'&).($C*('%.:)'7%*+1>2($*"$0."24*#.7%'95*/.:)()>%$*)*#'"'/."%.&'($;* bł!d ORA-01001 i zgłoszenie wyj!tku: invalid_cursor
NOTFOUND  Zwraca TRUE, je%li ostatnio wykonana komenda FETCH nie zwróciła ju$
FORALL i IN min..max
SAVE EXCEPTION poprawnego wiersza, w przeciwnym wypadku FALSE. Je$eli kursor jest nie otwarty,
...
odwołanie si" powoduje bł!d ORA-01001 i zgłoszenie wyj!tku: invalid_cursor
ISOPEN  Zwraca TRUE b!d' FALSE w zale$no%ci od tego, czy kursor jest zamkni"ty, czy
!.* &40.('()<* %'0)$6.* /.,$-$()'* :.=$:4* .2-#4%'3* "'/."%* #$* 7/$-9',($9*
otwarty.
/"$2$M)().&'($9*0.,$0-9)*SQL%BULK_EXCEPTIONS
ROWCOUNT  Zwraca ilo%# wierszy ju$ %ci!gni"tych z kursora.
Przykład 178
SQL%BULK_EXCEPTIONS.COUNT - ),.L3*+1>2N&
Napisa! funkcj", która zwróci opisy wszystkich niezrealizowanych zamówie&  sklejone w postaci
SQL%BULK_EXCEPTIONS(n).error_index R*2,'*(R%$6.*+1$2<*.2-#4%<9$:4C*0%N"$9*/.#4-9)* jednego napisu, w którym poszczególne opisy b"d# rozdzielone znakami  $^$ 
0.,$0-9)*2.%4-#41*
STRONA 122 Z 164 STRONA 123 Z 164
Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych
CREATE OR REPLACE FUNCTION opisy RETURN varchar2
Przykład 180
IS
DECLARE
CURSOR c_opisy IS
v1 klienci.klt_typ%TYPE;
SELECT zam_opis
CURSOR c_klt IS
FROM zamowienia
SELECT * FROM klienci WHERE klt_typ = v1;
WHERE zam_data_realizacji IS NULL;
BEGIN
v_napis varchar2(32760);
v1 :=  IND ;
v_opis zamowienia.zam_opis%TYPE;
OPEN c_klt;
BEGIN
...
OPEN c_opisy;
v1 :=  GSP ;
LOOP
OPEN c_klt;
FETCH c_opisy INTO v_opis;
...
EXIT WHEN c_opisy%NOTFOUND;
END;
v_napis := v_napis || v_opis || ' $^$ ';
Parametryzowanie poprzez parametry
END LOOP;
CLOSE c_opisy; Ten sposób parametryzacji jest bardziej czytelny i polega na zdefiniowaniu parametrów
RETURN substr(v_napis, 1, length(v_napis)-3);
formalnych podczas deklarowania kursora, a nast"pnie wprowadzeniu parametrów aktualnych
END;
podczas otwierania tego kursora.
DECLARE
,H8"'&$0-7#-#*'&
CURSOR nazwa_kursora(parametr1 typ1{, parametr2 typ2}) IS
Przetwarzanie kursorów mo$na upro%ci# dzi"ki tzw. p"tlom kursorowym.
wyra$enie_select
FOR zmienna_kursorowa IN nazwa_kursora LOOP
BEGIN
ci%g_instrukcji
OPEN nazwa_kursora(warto"#1{,warto"#2});
END LOOP;
...
Zmienna zmienna_kursorowa jest zmienn! typu rekordowego. Nie powinna by# END;
deklarowana.
Przykład 181
Stosuj!c p"tle kursorowe nie trzeba pami"ta# o otwieraniu, %ci!ganiu i zamykaniu kursora.
DECLARE
P"tla wykona si" tyle razy, ile wierszy zostanie znalezionych przez zapytanie kursora, a przy CURSOR c_klt(cup_typ varchar2) IS
SELECT * FROM klienci WHERE klt_typ = cup_typ;
ka$dym wykonaniu si" p"tli zmienna_kursorowa b"dzie zawiera# kolejny wiersz
BEGIN
zwrócony z zapytania.
OPEN c_klt( IND );
Przykład 179
...
OPEN c_klt( GSP );
Napisa! funkcj", która zwróci opisy wszystkich niezrealizowanych zamówie&  sklejone w postaci
...
jednego napisu, w którym poszczególne opisy b"d# rozdzielone znakami  $^$ 
END;
CREATE OR REPLACE FUNCTION opisy RETURN varchar2
IS
CURSOR c_opisy IS
SELECT zam_opis
FROM zamowienia
WHERE zam_data_realizacji IS NULL;
v_napis varchar2(32760);
BEGIN
FOR zamowienie IN c_opisy LOOP
v_napis := v_napis || zamowienie.zam_opis || ' $^$ ';
END LOOP;
RETURN substr(v_napis, 1, length(v_napis)-3);
END;
,'-')+8-15#*'(%+&$0-7#-N*&
Kursory mo$na parametryzowa# poprzez zmienne lub poprzez parametry.
Parametryzowanie poprzez zmienne
Zakres wierszy obejmowanych przez kursor mo$na uzale$ni# od pewnego parametru
odwołuj!c si" przy deklaracji kursora w klauzuli WHERE zapytania do pewnej zadeklarowanej
wcze%niej zmiennej.
W czasie wykonywania si" bloku zmienna ta mo$e przybiera# ró$ne warto%ci, a zatem kursor
b"dzie definiowany przez inne zapytanie.
STRONA 124 Z 164 STRONA 125 Z 164
Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych
8.3.5. Podsumowanie 8.3.6. 'wiczenia
Najwa$niejsze wiadomo%ci, jakie powiniene% posiada# po przerobieniu poprzedniego 76. Napisa# procedur", która wypisze na konsol" raport obejmuj!cy wszystkie zamówienia w
rozdziału, to: postaci:
Lp Opis Zamówiono Zrealizowano
" Jak zbudowane s! rekordy?
--------------------------------------------------
" Jak mo$na zdefiniowa# typ rekordowy i zmienne tego typu?
1 Zestaw komputerowy 2000.12.10
" Czym charakteryzuj! si" tablice indeksowane?
2 Meble biurowe 2000.05.08 2000.05.12
3 Wyposa!enie biura 1999.03.01 2000.05.30
" Jak odwoływa# si" do elementów tablicy?
...
" Jak operowa# atrybutami kolekcji?
...
" Do czego słu$! kursory?
-------------------------------------------------
" Jaki jest cykl korzystania z kursora? Ilo#$ zamówie% zrealizowanych: 9
Ilo#$ zamówie% nie zrealizowanych: 3
" Jakie atrybuty posiada kursor i do czego mo$na je wykorzysta#?
Skorzysta# z kursora opartego na tabeli zamowienia.
" W jaki sposób mo$na upro%ci# przetwarzanie kursora przy pomocy p"tli kursorowej?
77. * Zmodyfikowa# poprzednio napisany blok, w taki sposób, aby raport przyj!ł posta#:
" W jaki sposób mo$na sparametryzowa# kursor?
KLIENT: Jan Kowalski
Je$eli nie potrafisz wyczerpuj!co odpowiedzie# na ka$de z powy$szych pyta&, popro%
Lp Opis Zamówiono Zrealizowano
instruktora o powtórne, b!d' bardziej szczegółowe wyja%nienie w!tpliwego tematu.
----------------------------------------------------
1 Zestaw komputerowy 2000.12.10
----------------------------------------------------
Ilo#$ zamówie% zrealizowanych: 0
Ilo#$ zamówie% nie zrealizowanych: 1
KLIENT: Biuro nieruchomo#ci  Swój domek
Lp Opis Zamówiono Zrealizowano
----------------------------------------------------
1 Akcesoria komputerowe 2000.07.26 2000.07.29
...
...
------------------------------
Ilo#$ zamówie% zrealizowanych: 9
Ilo#$ zamówie% nie zrealizowanych: 3
...
...
Stworzy# kursor oparty na tabeli klienci oraz kursor z parametrem oparty na tabeli
zamowienia.
78. Zmodyfikowa# procedur" z pierwszego #wiczenia, w taki sposób, aby do procedury było
przekazywane jako parametr nazwisko klienta lub jego nazwa. Procedura powinna
wypisywa# tylko zamówienia wskazanego klienta. Je$eli nie istnieje $aden klient o
podanym nazwisku, powinien zosta# wypisany odpowiedni komunikat o bł"dzie:  Podany
klient nie istnieje .
Je$eli masz kłopoty 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.
STRONA 126 Z 164 STRONA 127 Z 164
Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych
CREATE OR REPLACE PACKAGE BODY pakiet1
IS
-- deklaracje podprogramów
8.4. Pakiety
-- definicje typów
-- deklaracje zmiennych i kursorów
Pakiety s! obiektami, które słu$! przede wszystkim do grupowania wielu podprogramów w
-- deklaracje wyj%tków
jedn! logiczn! cało%#.
-- implementacja podprogramów
Oprócz tej funkcjonalno%ci posiadaj! jeszcze wiele innych cech, które czyni! je bardzo
END;
u$ytecznym elementem j"zyka PL/SQL.
Przykład 183
8.4.1. Tworzenie i usuwanie pakietów CREATE OR REPLACE PACKAGE BODY Pkg1
IS
Pakiet składa si" z dwu cz"%ci: specyfikacji i ciała. Specyfikacja i ciało jednego pakietu to dla
v_osoba3 t_osoba;
Oracle dwa ró$ne obiekty w specyficzny sposób powi!zane ze sob!.
PROCEDURE zeruj_licznik
Specyfikacja pakietu, generalnie mówi!c, zawiera wszystkie elementy, które znajdowały si"
IS
w cz"%ci deklaracyjnej bloku PL/SQL, a wi"c deklaracje podprogramów, deklaracje
BEGIN
zmiennych, definicje typów, definicje kursorów, wyj!tków. v_licznik := 0;
CREATE OR REPLACE PACKAGE nazwa_pakietu END;
--
IS
FUNCTION get_osoba(p_numer number) RETURN t_osoba
-- deklaracje podprogramów
IS+
-- definicje typów
BEGIN
-- deklaracje zmiennych i kursorów
OPEN c_klient;
-- deklaracje wyj%tków
FETCH c_klient INTO v_osoba3.imie, v_osoba3.nazwisko;
END;
CLOSE c_klient;
Elementy specyfikacji mog! wyst"powa# w dowolnym porz!dku, przy zastrze$eniu, $e
IF v_osoba3.nazwisko = v_osoba1.nazwisko THEN
element musi by# zadeklarowany wcze%niej ni$ si" do niego odwołuje inny element.
RAISE e_nazwisko_podwojne;
Przykład 182
END IF;
CREATE OR REPLACE PACKAGE Pkg1 RETURN v_osoba3;
IS END;
-- definicje typów END;
TYPE t_osoba IS RECORD (
Mo$e istnie# pakiet zawieraj!cy tylko specyfikacj" bez ciała, natomiast nie mo$e istnie#
imie varchar2(30),
poprawne ciało bez specyfikacji pakietu.
nazwisko varchar2(30)
);
-- deklaracje zmiennych i kursorów
8.4.2. Przeładowywanie jednostek programowych
v_osoba1 t_osoba;
W ramach jednego pakietu procedury i funkcje mog! zosta# przeładowywane, tzn. mo$e
v_osoba2 t_osoba;
zosta# zdefiniowanych wiele podprogramów o tej samej nazwie. Powinny si" one ró$ni#
v_licznik number;
CURSOR c_klient IS ilo%ci! i/lub typem warto%ci parametrów, na tej podstawie w momencie próby uruchomienia
SELECT klt_imie, klt_nazwisko
podprogramu Oracle stwierdza, który podprogram powinien zosta# wykonany.
FROM klienci;
Nie mo$na przeładowa# podprogramu, je$eli ró$ni! si" tylko nazw! lub typem parametrów
-- deklaracje wyj%tków
(IN, OUT, IN OUT). Nie mo$na przeładowa# dwu funkcji, je$eli ró$ni! si" tylko zwracanym
e_nazwisko_podwojne EXCEPTION;
-- deklaracje podprogramów typem.
PROCEDURE zeruj_licznik;
Nie mo$na przeładowa# podprogramów, je$eli ró$ni! si" tylko typem warto%ci parametrów,
FUNCTION get_osoba(p_numer number) RETURN t_osoba;
ale typy te nale$! do tej samej grupy, np. char i varchar2.
END;
Przykład 184
CREATE OR REPLACE PACKAGE Pkg2
W ciele pakietu znajduj! si" wszystkie elementy, które mogły wyst"powa# w specyfikacji
IS
pakietu, oprócz tego wszystkie zadeklarowane w specyfikacji podprogramy musz! by#
e_niejednoznaczna_nazwa EXCEPTION;
zaimplementowane, przy czym nazwy wszystkich parametrów w podprogramach w ciele
FUNCTION get_cena(p_id number) RETURN number;
pakietu musz! by# identyczne jak w specyfikacji. FUNCTION get_cena(p_nazwa varchar2) RETURN number;
END;
STRONA 128 Z 164 STRONA 129 Z 164
Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych
CREATE OR REPLACE PACKAGE BODY Pkg2 CREATE OR REPLACE PACKAGE BODY pakiet1
IS IS
v_cena number; ...
FUNCTION get_cena(p_id number) RETURN number BEGIN
IS -- kod inicjuj%cy pakiet
BEGIN END;
SELECT tow_cena
Przykład 185
INTO v_cena
CREATE OR REPLACE PACKAGE Pkg3
FROM towary
IS
WHERE tow_id = p_id;
v_partycje boolean;
RETURN v_cena;
...
END;
PROCEDURE split_partition;
FUNCTION get_cena(p_nazwa varchar2) RETURN number
...
IS
END;
BEGIN
SELECT tow_cena
CREATE OR REPLACE PACKAGE BODY Pkg3
INTO v_cena
IS
FROM towary
...
WHERE tow_nazwa = p_nazwa;
PROCEDURE split_partition
RETURN v_cena;
IS
EXCEPTION
BEGIN
WHEN too_many_rows THEN
...
RAISE e_niejednoznaczna_nazwa;
END;
END;
...
END;
BEGIN
SELECT value
INTO v_partycje
8.4.3. Inicjalizacja, współdzielenie i czas !ycia pakietów
FROM v$option
Kiedy pierwszy raz zostaje u$yty jaki% składnik pakietu, to tworzona jest tzw. instancja
WHERE parameter =  Partitioning ;
pakietu. Oznacza to, $e pakiet jest wczytywany z dysku do pami"ci do obszaru SGA i
END;
alokowane s! zasoby na wszystkie zadeklarowane w pakiecie zmienne w obszarze PGA.
Ka$da sesja korzystaj!ca z pakietu ma swoj! własn! kopi" wszystkich zmiennych
8.4.4. Hermetyzacja i globalizacja
pakietowych, dzi"ki czemu wiele sesji mo$e równocze%nie, bezkolizyjnie korzysta# z jednego
Ka$dy element zadeklarowany w specyfikacji pakietu jest widoczny na jego zewn!trz i
pakietu.
mo$na si" do niego odnosi# prefiksuj!c nazw" obiektu nazw! pakietu. Dotyczy to zarówno
Zmienne te przechowuj! swoj! warto%# do momentu zako&czenia sesji.
podprogramów, zmiennych, typów, wyj!tków i wszystkich pozostałych elementów.
W momencie, gdy tworzy si" instancja pakietu, po zaalokowaniu miejsca na zmienne, ale
W ten sposób elementy umieszczone w specyfikacji dowolnego pakietu staj! si" globalne w
jeszcze przed pierwszym odwołaniem, które spowodowało utworzenie instancji wykonuje si"
skali schematu, a po przydzieleniu odpowiednich uprawnie&, równie$ globalne w skali całej
sekcja inicjuj!ca pakietu, je$eli została zdefiniowana.
bazy danych.
Sekcj" inicjalizuj!c! pakietu umieszcza si" na ko&cu ciała pakietu, poprzedzaj!c j! słowem
Wewn!trz ciała pakietu wszystkie elementy zadeklarowane i zdefiniowane w specyfikacji s!
BEGIN.
osi!galne bez prefiksowania ich nazw! pakietu.
Natomiast wszystkie elementy zadeklarowane w ciele pakietu, nie wył!czaj!c podprogramów,
s! globalne, ale tylko dla elementów znajduj!cych si" w ciele pakietu, a zatem nie mo$na si"
b"dzie do nich odwoływa# z innych bloków PL/SQL, w szczególno%ci nawet ze specyfikacji
tego pakietu.
STRONA 130 Z 164 STRONA 131 Z 164
Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych
8.4.5. Podsumowanie 8.4.6. 'wiczenia
Najwa$niejsze wiadomo%ci, jakie powiniene% posiada# po przerobieniu poprzedniego 79. Utwórz pakiet, który b"dzie zawierał funkcj" i procedur" do wstawiania liczb podzielnych
rozdziału, to: przez 7 i 13, które zostały utworzone w poprzednich #wiczeniach. Procedura powinna by#
" Jakie jest główne zastosowanie pakietów? publiczna, a funkcja powinna by# prywatna. Uruchom procedur" i spróbuj uruchomi#
funkcj".
" W jaki sposób pakiety s! zbudowane?
Je$eli masz kłopoty ze zrozumieniem lub wykonaniem któregokolwiek #wiczenia, lub nie
" W jaki sposób tworzy# i usuwa# specyfikacj" i ciało pakietu, jakie elementy j"zyka
rozumiesz, dlaczego serwer Oracle zachowuje si" w taki, a nie inny sposób, popro%
PL/SQL mog!, a jakie musz! si" znale'# w specyfikacji i ciele pakietu?
instruktora o dodatkowe wyja%nienia.
" Na jakich zasadach mo$na przeładowywa# podprogramy w PL/SQL?
" W jaki sposób mo$na zainicjowa# pakiet?
" W jaki sposób pakiety s! współu$ytkowane przez wiele sesji? Jaki jest zasi"g zmiennych
zadeklarowanych w pakiecie?
" Jakie własno%ci posiadaj! elementy j"zyka umieszczone w specyfikacji pakietu, a jakie w
ciele pakietu?
" W jaki sposób mo$na si" odwoływa# do składowych pakietu z zewn!trz i z jego wn"trza?
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.
STRONA 132 Z 164 STRONA 133 Z 164
Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych
,-+/1$'81&
8.5. Wyzwalacze
Jeden wyzwalacz mo$e by# powi!zany z wszystkimi operacjami: INSERT, UPDATE i
Wyzwalacze s! to bloki PL/SQL, których wywołanie nast"puje automatycznie w przypadku
DELETE.
zaj%cia jakiego% zdarzenia.
Dzi"ki predykatom wewn!trz kodu wyzwalacza mo$na rozpozna#, jaka operacja
Definiuj!c wyzwalacz okre%lamy, w jakim przypadku ma si" on uruchomi#. W wersji Oracle
spowodowała uruchomienie wyzwalacza.
poni$ej 8i mo$na było zdefiniowa# tylko wyzwalacze powi!zane z operacjami DML na
Istniej! trzy predykaty: INSERTING, UPDATING i DELETING i przybieraj! one odpowiednio
tabelach. W wersji 8i mo$liwe jest powi!zanie wyzwalaczy z operacjami DML na widokach
warto%# TRUE b!d' FALSE w zale$no%ci od tego, jaka operacja spowodowała uruchomienie
oraz ze zdarzeniami systemowymi.
wyzwalacza.
8.5.1. Wyzwalacze DML Przykład 187
6*#-5+(%+V&070*'(%+& Doda! do tabeli zamowienia kolumn" do przechowywania kwoty całego zamówienia. Nast"pnie
wypełni! odpowiednio t" kolumn".
CRAETE [OR REPLACE] TRIGGER nazwa_wyzwalacza
{BEFORE|AFTER} Napisa! wyzwalacz (wyzwalacze), które:
{INSERT|UPDATE|DELERE|INSERT OR UPDATE OR DELETE}
" Nie pozwol# zmodyfikowa! zamówie& i pozycji zamówie&, które zostały ju% zrealizowane.
ON nazwa_tabeli
" B"d# automatycznie czuwa!, aby informacja zawarta w nowej kolumnie była zawsze aktualna w
[FOR EACH ROW [WHEN warunek]]
przypadku modyfikacji cen towarów i pozycji zamówie& niezrealizowanych.
ciało_wyazwalacza;
-- dodanie nowej kolumny
ALTER TABLE zamowienia
DROP TRIGGER nazwa_wyzwalacza;
ADD zam_kwota number(9);
-- uzupełnienie nowej kolumny danymi
ALTER TRIGGER nazwa_wyzwalacza {DISABLE|ENABLE};
UPDATE zamowienia Z
SET zam_kwota = (SELECT sum(PZ.pzm_ilosc * T.tow_cena)
U#"+3(#EW&*1$#(1*'(%'&7%H&*15*'"'.51&P>I&
FROM pozycje_zamowien PZ, towary T
Je$eli na pewnej tabeli s! zało$one wszystkie rodzaje wyzwalaczy, to ich kolejno%# jest %ci%le
WHERE PZ.pzm_tow_id = T.tow_id
okre%lona. AND PZ.pzm_zam_id = Z.zam_id);
-- wyzwalacz zabraniaj%cy jakiejkolwiek modyfikacji lub usuni!cia
Przykład 186
-- rekordu ze zrealizowanym zamówieniem
-- zostaje wykonana instrukcja:
CREATE OR REPLACE TRIGGER tg_zam_zrealiz
UPDATE tabela1
BEFORE UPDATE OR DELETE ON zamowienia
SET kol1 = warto"#1
FOR EACH ROW
WHERE kol1 = warto"#2;
WHEN (new.zam_data_realizacji IS NOT NULL)
-- Instrukcja ta spowodowała modyfikacj! 3 wierszy w tabela1
BEGIN
-- Kolejno"# wykonywania wyzwalaczy jest nast!puj%ce
raise_application_error(-20001,
BEFORE UPDATE ON tabela1
'Nie mo!na modyfikowa$ ani usuwa$ zrealizowanych zamówie%');
BEFORE UPDATE ON tabela1 FOR EACH ROW
END;
-- modyfikacja jednego wiersza
-- wyzwalacz, który zmodyfikuje kwoty zamówie& w momencie zmiany ceny
AFTER UPDATE ON tabela1 FOR EACH ROW
-- jakiego" towaru
BEFORE UPDATE ON tabela1 FOR EACH ROW
CREATE OR REPLACE TRIGGER tg_tow_kwota_zamowienia
-- modyfikacja jednego wiersza
AFTER UPDATE ON towary
AFTER UPDATE ON tabela1 FOR EACH ROW
FOR EACH ROW
BEFORE UPDATE ON tabela1 FOR EACH ROW
WHEN (new.tow_cena <> old.tow_cena)
-- modyfikacja jednego wiersza
DECLARE
AFTER UPDATE ON tabela1 FOR EACH ROW
-- kursor zwraca wszystkie pozycje zamówie&, w których znajduje si!
AFTER UPDATE ON tabela1
-- modyfikowany towar
,7+0/#-+$#-/1&/#78HM(+&*&*15*'"'.5'.F& CURSOR c_pzm(cup_tow_id number) IS
SELECT pzm_zam_id, pzm_ilosc
W ka$dym wyzwalaczu FOR EACH ROW dost"pne s! dwie zmienne rekordowe, które
FROM pozycje_zamowien
zawieraj! informacje o wierszu, który aktualnie jest przetwarzany.
WHERE pzm_tow_id = cup_tow_id;
Zmienne te nazywaj! si" :old i :new i zawieraj! odpowiednio w zale$no%ci od typu BEGIN
-- dla wszystkich pozycji zamówie& obejmuj%cych zmodyfikowany towar
wyzwalacza wiersz usuwany, dodawany, b!d' modyfikowany.
FOR pz IN c_pzm(:new.tow_id) LOOP
Zmienne rekordowe mog! by# wykorzystywane nie tylko do odczytu, ale w wyzwalaczach
-- zmodyfikowanie kwoty niezrealizowanych zamówie&
BEFORE INSERT i BEFORE UPDATE mog! by# równie$ modyfikowane, co skutkuje
UPDATE zamowienia
wstawieniem do tabeli odpowiednio zmodyfikowanych danych. SET zam_kwota = zam_kwota+(:new.tow_cena -
:old.tow_cena)*pz.pzm_ilosc
Je$eli w wyzwalaczach DML zostanie wywołany bł!d, to operacja, która spowodowała
WHERE zam_id = pozycja.pzm_zam_id
wyzwolenie tego wyzwalacza zako&czy si" niepowodzeniem i zgłosi bł!d, który został
AND zam_data_realizacji IS NOT NULL;
wywołany w wyzwalaczu.
END LOOP;
END;
STRONA 134 Z 164 STRONA 135 Z 164
Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych
-- Obsługa modyfikacji pozycji zamówie&
CREATE OR REPLACE TRIGGER tg_pzm_modif
8.5.2. Wyzwalacze Instead-of
AFTER UPDATE OR DELETE OR INSERT ON pozycje_zamowien
FOR EACH ROW Wyzwalacze te mog! by# powi!zane tylko z widokami i wykonuj! si" nie przed operacj!
DECLARE
DML, nie po operacji DML, ale zamiast operacji DML, i słu$! do tego, aby interpretowa#
v_dummy number(1);
odpowiednio operacje na widokach, na których dotychczas nie było wolno wykonywa#
v_cena towary.tow_cena%TYPE;
operacji DML.
BEGIN
-- Czy modyfikacja nie dotyczy zamówienia zrealizowanego? CRAETE [OR REPLACE] TRIGGER nazwa_wyzwalacza
BEGIN INSTEAD OF {INSERT|UPDATE|DELETE|INSERT OR UPDATE OR DELETE}
SELECT 1 INTO v_dummy ON nazwa_widoku
FROM zamowienia [WHEN warunek]
WHERE zam_data_realizacji IS NOT NULL ciało_wyazwalacza;
AND (zam_id = :old.pzm_zam_id
Klauzula FOR EACH ROW jest opcjonalna i nic nie wnosi, poniewa$ wszystkie wyzwalacze
OR zam_id = :new.pzm_zam_id);
INSTEAD OF s! wyzwalaczami na poziomie wiersza.
RAISE too_many_rows;
W wyzwalaczach INSTEAD OF dost"pne s! tak$e zmienne rekordowe :old i :new oraz
EXCEPTION
WHEN no_data_found THEN predykaty INSERTING, UPDATING i DELETING.
NULL;
Przykład 188
WHEN too_many_rows THEN
Stworzy! widok obejmuj#cy identyfikator, imi", nazwisko lub nazw" klienta oraz identyfikatory i opisy
raise_application_error(-20002,
wszystkich zamówie& składanych przez tych klientów. Nast"pnie stworzy! wyzwalacz, który obsłu%y
'Nie mo!na modyfikowa$ zrealizowanych zamówie%');
END; operacj" usuwania rekordów ze stworzonego widoku. Wyzwalacz powinien:
-- Odpowiednie zmodyfikowanie kwoty zamówienia
" usun#! wszystkie pozycje usuwanego zamówienia,
IF INSERTING THEN  - dodanie nowej pozycji do zamówienia
SELECT tow_cena INTO v_cena
" usun#! zamówienie,
FROM towary
" je%eli było to jedyne zamówienie danego klienta, to powinien zosta! usuni"ty tak%e klient.
WHERE tow_id = :new.pzm_tow_id;
-- stworzenie widoku
UPDATE zamowienia
CREATE OR REPLACE VIEW kli_zam AS
SET zam_kwota = zam_kwota + (v_cena * :new.pzm_ilosc)
SELECT K.klt_id id_kl,
WHERE zam_id = :new.pzm_zam_id;
decode(K.klt_typ,'IND',K.klt_nazwisko||' '||K.klt_imie,
ELSIF DELETING THEN - usuni!cie pozycji z zamówienia
K.klt_nazwa) klient,
SELECT tow_cena INTO v_cena
Z.zam_opis opis,
FROM towary
Z.zam_id id_zam
WHERE tow_id = :old.pzm_tow_id;
FROM klienci K, zamowienia Z
UPDATE zamowienia
WHERE K.klt_id = Z.zam_klt_id;
SET zam_kwota = zam_kwota - (v_cena * :old.pzm_ilosc)
-- stworzenie odpowiedniego wyzwalacza
WHERE zam_id = :old.pzm_zam_id;
CREATE OR REPLACE TRIGGER tg_kli_zam_del
ELSE -- modyfikacja pozycji zamówienia
INSTEAD OF DELETE ON kli_zam
SELECT tow_cena INTO v_cena
DECLARE
FROM towary
v_ile binary_integer;
WHERE tow_id = :new.pzm_tow_id;
BEGIN
UPDATE zamowienia
-- usuni!cie pozycji usuwanego zamowienia
SET zam_kwota = zam_kwota + (v_cena * :new.pzm_ilosc)
DELETE pozycje_zamowien
WHERE zam_id = :new.pzm_zam_id;
WHERE pzm_zam_id = :old.id_zam;
SELECT tow_cena INTO v_cena
-- usuni!cie zamówienia
FROM towary
DELETE zamowienia
WHERE tow_id = :old.pzm_tow_id;
WHERE zam_id = :old.id_zam;
UPDATE zamowienia
-- sprawdzenie, czy klienta ma jeszcze jakie" zamówienia
SET zam_kwota = zam_kwota - (v_cena * :old.pzm_ilosc)
SELECT count(*) INTO v_ile
WHERE zam_id = :old.pzm_zam_id;
FROM zamowienia
END IF;
WHERE zam_klt_id = :old.id_kl;
END; -- je$eli nie ma wi!cej zamówie&, to usuni!cie klienta
IF v_ile = 0 THEN
DELETE klienci
WHERE klt_id = :old.id_kl;
END IF;
END;
STRONA 136 Z 164 STRONA 137 Z 164
Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych
Ograniczenia powy$sze dotycz! wszystkich wyzwalaczy na poziomie wierszy (FOR EACH
8.5.3. Wyzwalacze DDL
ROW).
Wyzwalacze systemowe mog! by# powi!zane z operacjami DDL takimi jak CREATE, ALTER i
Je$eli Polecenie INSERT dotyczy tylko jednego wiersza, to w ciele wyzwalacza BEFORE i
DROP, oraz ze zdarzeniami systemowymi, takimi jak startup/shutdown serwer, logon/logof
AFTER wyj!tkowo mo$na si" odwoływa# do tabeli, na której jest oparty wyzwalacz.
u$ytkownik i server error.
CREATE [OR REPLACE] TRIGGER [schemat.]nazwa
6-'(7'$.3+&
{BEFORE|AFTER}
Ciało wyzwalaczy nie mo$e zawiera# $adnych polece& zwi!zanych z transakcjami. Co za tym
{lista_zdarze&_ddl|lista_zdarze&_systemowych}
ON {DATABASE | SCHEMA} idzie, nie mo$e wywoływa# po%rednio podprogramów, które zawieraj! takie polecenia
[WHEN warunek]
bezpo%rednio lub po%rednio.
ciało_wyzwalacza
Nie dla wszystkich zdarze& systemowych mo$na zało$y# wyzwalacze BEFORE i AFTER.
Wyzwalacze systemowe mog! by# definiowane na poziomie pojedynczego schematu lub
całej bazy. Wyj!tkiem jest startup i shutdown, które mog! by# definiowane tylko na poziomie
bazy.
W przypadku wyzwalaczy systemowych istnieje kilka funkcji pozwalaj!cych otrzyma#
informacje na temat zdarzenia, które zainicjowało uruchomienie wyzwalacza (analogiczne do
:new, :old w wyzwalaczach DML i INSTEAD OF).
Przykład 189
Stworzy! wyzwalacz, który b"dzie odnotowywał w specjalnej tabeli ddl_log informacje o wszystkich
tworzonych obiektach na schemacie, na którym b"dzie stworzony wyzwalacz.
-- logowanie wszystkich tworzonych obiektów na schemacie
CREATE OR REPLACE TRIGGER tg1
AFTER CREATE ON SCHEMA
BEGIN
INSERT INTO ddl_log (user_id
, obj_type
, obj_name
, obj_owner
, cre_time)
VALUES (USER
, SYS.DICTIONARY_OBJ_TYPE
, SYS.DICTIONARY_OBJ_NAME
, SYS.DICTIONARY_OBJ_OWNER
, SYSDATE);
END;
8.5.4. Ograniczenia wyzwalaczy
6'G+"+&)#/12%$#*'(+&
Istniej! pewne ograniczenia w wyzwalaczach dotycz!ce dost"pu do tabel. Wa$ne jest aby
rozró$ni# pewne typy tabel.
Tabele modyfikowane (mutating tables) to te tabele, które s! modyfikowane przez instrukcj"
DML, która spowodowała uruchomienie wyzwalacza. S! to tak$e tabele podrz"dne
przył!czone do tabeli modyfikowanej kluczem obcym z opcj! CASCADE DELETE.
Tabele ograniczone (constraints tables) to tabele, które musz! by# odczytywane podczas
modyfikowania danych w tabeli modyfikowanej. Praktycznie s! to tabele powi!zane kluczem
obcym z tabelami modyfikowanymi.
" )adne polecenie SQL w ciele wyzwalacza nie mo$e czyta# ani modyfikowa# tabeli
modyfikowanej, zwłaszcza tabeli, na której jest wyzwalacz oparty.
" )adne polecenie SQL w ciele wyzwalacza nie mo$e czyta# ani modyfikowa# kluczy
unikalnych, głównych i obcych tabel ograniczonych. Mo$liwe jest modyfikowanie
pozostałych kolumn.
STRONA 138 Z 164 STRONA 139 Z 164
Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych
8.5.5. Podsumowanie 8.5.6. 'wiczenia
Najwa$niejsze wiadomo%ci, jakie powiniene% posiada# po przerobieniu poprzedniego 80. Napisa# wyzwalacz, który b"dzie odnotowywał wszystkie operacje DML wykonane na
rozdziału, to: tabeli towary.
" Czym charakteryzuj! si" wyzwalacze, co odró$nia je od innych rodzajów bloków
" Utworzy# tabel" tglog składaj!c! si" z kolumn do przechowywania nazwy
PL/SQL?
u$ytkownika, czasu, typu operacji, numeru towaru, na którym wykonano operacj".
" Czym charakteryzuj! si" wyzwalacze DML, Instead-of, Systemowe?
" W odpowiednio zapisanym wyzwalaczu umie%ci# instrukcj" INSERT, która
" W jaki sposób okre%la# sposób wyzwalania si" wyzwalaczy DML?
zanotuje wszystkie operacje wykonane na tabeli towary w tabeli test.
" W jaki sposób mo$na si" odwoływa# i modyfikowa# rekordy, których przetwarzanie 81. Napisz wyzwalacz, który niezale$nie od polecenia INSERT wykonywanego przez
spowodowało wyzwolenie wyzwalacza?
u$ytkowników zawsze b"dzie wpisywał do tabeli towary nazwy towarów małymi
" W jaki sposób rozpozna#, jakiego typu operacja spowodowała wyzwolenie wyzwalacza?
literami zaczynaj!ce si" tylko od du$ej litery.
" W jakim celu stosuje si" wyzwalacze Instead-of? 82. Napisz wyzwalacz, który nie pozwoli zmniejszy# ceny $adnemu towarowi obj"temu
" Jakie mo$liwo%ci s! dostarczone poprzez mechanizm wyzwalaczy systemowych? stawk! podatku 7%.
" W jaki sposób rozpoznawa# w wyzwalaczach systemowych stan %rodowiska i przyczyn" 83. * Napisz wyzwalacz, który nie pozwoli $adnemu u$ytkownikowi stworzy# tabeli, w której
wyzwolenia si" wyzwalacza? b"dzie si" znajdowała kolumna typu LONG.
Je$eli nie potrafisz wyczerpuj!co odpowiedzie# na ka$de z powy$szych pyta&, popro% Je$eli masz kłopoty ze zrozumieniem lub wykonaniem któregokolwiek #wiczenia, lub nie
instruktora o powtórne, b!d' bardziej szczegółowe wyja%nienie w!tpliwego tematu. rozumiesz, dlaczego serwer Oracle zachowuje si" w taki, a nie inny sposób, popro%
instruktora o dodatkowe wyja%nienia.
STRONA 140 Z 164 STRONA 141 Z 164
Podstawy Oracle 10g SQL i PL/SQL dla pocz!tkuj!cych
STRONA 142 Z 164