Bazy danych – wykład dwunasty

PL/SQL, c.d.

Konrad Zdanowski

Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty PL/SQL, c.d.

1 / 37

SQL to za mało

SQL brakuje mo˙zliwo´sci dost ˛epnych w j ˛ezykach proceduralnych.

W SQL wywołujemy pojedy ´ncze polecenia.

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty PL/SQL, c.d.

2 / 37

PL/SQL

J ˛ezyk proceduralny.

Zintegrowany z SQL.

Mo˙zliwo´s´c składowania procedur i funkcji w bazie danych.

Stosowany zarówno po stronie serwera jak i w aplikacjach
klientów.
Pozwala na korzystanie z bibliotek,

I

np. I/O - set serveroutput on.

Pozwala na dynamiczne konstruowanie zapyta ´n SQL w trakcie
wykonywania procedury.

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty PL/SQL, c.d.

3 / 37

PL/SQL – posta´c programu

DECLARE
...
BEGIN
....
EXCEPTION
...
END;

W programie musz ˛

a wyst ˛

api´c tylko

BEGIN i END.

Pozostałe cz ˛e´sci s ˛

a opcjonalne.

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty PL/SQL, c.d.

4 / 37

Kursory

PL/SQL nie umo˙zliwia zapyta ´n SQL, które zwracaj ˛

a całe tabele

(zawieraj ˛

ace wi ˛ecej ni˙z jeden wiersz).

Je´sli chcemy wykona´c inne zapytanie, musimy u˙zy´c kursora.

Kursor to zmienna, która przebiega zbiór wyników zapytania.

Rodzaje kursorów: jawne i niejawne.

Z ka˙zdym zapytaniem SQL zwi ˛

azany jest kursor niejawny.

Kursor jawny deklarujemy w bloku DECLARE.

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty PL/SQL, c.d.

5 / 37

Kursory jawne – deklarowanie

cursor nazwa ( parametry ) i s

{ z a p y t a n i e |

r e t u r n t y p }

[

f o r update [ of parametry ] ] ;

Parametry podajemy jako list ˛e oddzielona przecinkami:

parametr t y p [

d e f a u l t w a r t o s c ]

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty PL/SQL, c.d.

6 / 37

Kursory jawne

Zanim zaczniemy u˙zywa´c kursora musimy go otworzy´c komend ˛

a:

open nazwa_kursora ( parametry ) ;

Po zako ´nczeniu pracy kursor zamykamy:

close nazwa_kursora ;

Próba otwarcia otwartego kursora generuje bł ˛

ad, podobnie

zamkni ˛ecie nieotwartego kursora.

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty PL/SQL, c.d.

7 / 37

Kursory jawne – pobieranie rekordów

Kursor mo˙zemy traktowa´c jako zmienn ˛

a typu rekordowego

przebiegaj ˛

ac ˛

a zbiór wyników kwerendy.

Aby odczyta´c krotk˛e u˙zywamy instrukcji:

I

fetch kursor into lista zmiennych,

I

fetch kursor into zmienna rekordowa.

Zmienne przyjmuj ˛

a warto´s´c krotki, na któr ˛

a wskazuje kursor.

Kursor przechodzi do nast ˛epnej krotki.

Je´sli kursor przeczytał wszystkie krotki wyst ˛

api bł ˛

ad.

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty PL/SQL, c.d.

8 / 37

Kursory jawne – pobieranie rekordów

declare
cursor cur_osoby i s

s e l e c t ∗ from osoby ;

osoba osoby%

rowtype ;

nazwisko osoby . nazwisko%

type ;

i m i e osoby . i m i e%

type ;

i d osoby . i d%

type ;

begin

open cur_osoby ;

f e t c h cur_osoby i n t o i m i e , nazwisko , i d ;

dbms_output . p u t _ l i n e ( i m i e

| |

’

’ | | nazwisko | | ’

’ | |

t o _ c h a r ( i d ) ) ;

f e t c h cur_osoby i n t o osoba ;

dbms_output . p u t _ l i n e

( osoba . i m i e | | ’

’ | | osoba . nazwisko | | ’

’ | |

t o _ c h a r ( osoba . i d ) ) ;

close cur_osoby ;

end ;

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty PL/SQL, c.d.

9 / 37

Typy danych

Mo˙zemy zadeklarowa´c zmienn ˛

a typu atrybutu jako

R.atrybut%type, gdzie R to tabela lub kursor.

Mo˙zemy zadeklarowa´c zmienn ˛

a typu krotki jako R%rowtype,

gdzie R to tabela lub kursor.

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty PL/SQL, c.d.

10 / 37

Kursory jawne – atrybuty

Stan kursora mo˙zemy sprawdzi´c poprzez jego atrybuty:

%ISOPEN,

%FOUND – przyjmuje warto´sci true, false lub null (przed
pierwszym pobraniem),

%NOTFOUND,

%ROWCOUNT – liczba pobranych rekordów (zero przed
pobraniem).

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty PL/SQL, c.d.

11 / 37

Kursory jawne – atrybuty

declare
cursor cur_osoby i s

s e l e c t ∗ from osoby ;

osoba osoby%

rowtype ;

begin

i f ( not cur_osoby%is ope n ) then

open cur_osoby ;

end i f ;

loop

f e t c h cur_osoby i n t o osoba ;

e x i t when cur_osoby%n o t f o u n d ;

dbms_output . p u t _ l i n e ( osoba . i m i e

| |

’

’ | | osoba . nazwisko | | ’

’ | |

t o _ c h a r ( osoba . i d ) ) ;

end loop ;

close cur_osoby ;

end ;

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty PL/SQL, c.d.

12 / 37

Kursory jawne – p ˛etla

for

Mo˙zemy w łatwiejszy sposób przebiec wszystkie wyniki zapytania
w p ˛etli

for:

f o r l i c z n i k i n k u r s o r

loop

. . .

end loop ;

W tej konstrukcji nie musimy otwiera´c ani zamyka´c kursora.

Zamiast kursora mo˙zemy u˙zy´c zapytania SQL.

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty PL/SQL, c.d.

13 / 37

Kursory jawne – p ˛etla

for

declare
cursor cur _osob y_imi e ( imie_osoby

varchar2 d e f a u l t

’ Jan ’ )

i s

s e l e c t ∗ from osoby where osoby . i m i e =imie_osoby ;

begin

f o r c u r i n cur _osob y_imi e

loop

dbms_output . p u t _ l i n e ( c u r . i m i e

| | ’

’

| |

c u r . nazwisko | |

’

’

| |

t o _ c h a r ( c u r . i d ) ) ;

end loop ;
end ;

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty PL/SQL, c.d.

14 / 37

Kursory jawne – modyfikacje tabel

Mo˙zemy zadeklarowa´c kursor jako

for update.

Kursor taki zakłada blokad ˛e na wszystkie rekordy, które mo˙ze
zmodyfikowa´c.

Mo˙zemy modyfikowa´c rekord, który został aktualnie odczytany
przez kursor.

update t a b e l a set . . .

where c u r r e n t of c u r _ t a b e l a ;

d e l e t e from t a b e l a

where c u r r e n t of c u r _ t a b e l a ;

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty PL/SQL, c.d.

15 / 37

Kursory niejawne

Ka˙zde polecenie SQL (select into, insert, update, delete) tworzy
kursor.

Nazwa kursora to sql.

Sprawdzaj ˛

ac jego własno´sci mo˙zemy sprawdzi´c własno´sci

ostatniego zapytania, np. sql%rowcount zwraca liczb ˛e krotek
zapytania.

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty PL/SQL, c.d.

16 / 37

Wyj ˛

atki

Je´sli w czasie wykonywania programu PL/SQL wyst ˛

api bł ˛

ad,

działanie programu zostaje przerwane.

Program mo˙ze przechwyci´c obsług ˛e błedu w cz ˛e´sci

exception.

Program mo˙ze zgłosi´c te˙z własny wyj ˛

atek przez:

raise nazwa_wyj ˛

atku.

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty PL/SQL, c.d.

17 / 37

Wyj ˛

atki

Wyj ˛

atki prefefiniowane:

cursor_already_open,

invalid_cursor,

no_date_found,

too_many_rows,

value_error,

zero_divide.

W cz ˛e´sci obsługuj ˛

acej wyj ˛

atki mo˙zemy u˙zy´c:

when others aby

obsłu˙zy´c wyj ˛

atki, których wcze´sniej nie wymienilismy.

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty PL/SQL, c.d.

18 / 37

Wyj ˛

atki u˙zytkownika

Wyj ˛

atki definiujemy w cz ˛esci

declare przez

nazwa_wyjatku

exception;

Zgłaszamy przez

raise nazwa_wyjatku.

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty PL/SQL, c.d.

19 / 37

Obsługa wyj ˛

atków – sterowanie

declare
cursor cur_osoby i s s e l e c t ∗ from osoby

osoba osoby%

rowtype ;

begin

begin

open cur_osoby ; open cur_osoby ;

dbms_output . p u t _ l i n e ( ’ T u t a j s t e r o w a n i e n i e d o t r z e ’ ) ;

exception

when c u r s o r _ a l r e a d y _ o p e n then

close cur_osoby ; dbms_output . p u t _ l i n e ( ’ Zamknieto k u r s o r ’ ) ;

end ;

dbms_output . p u t _ l i n e ( ’ T u t a j wraca s t e r o w a n i e ’ ) ;
close cur_osoby ;
exception

when i n v a l i d _ c u r s o r then

dbms_output . p u t _ l i n e ( ’ Zabroniona o p e r a c j a na k u r s o r z e ’ ) ;

end ;

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty PL/SQL, c.d.

20 / 37

Obsługa wyj ˛

atków – sterowanie

Po obsłu˙zeniu wyjatku sterowanie wraca do bloku programu, który
wywołał program, w którym powstał wyj ˛

atek.

Raz obsłuzony wyj ˛

atek nie jest obsługiwany wy˙zej.

Nie obsłu˙zony wyj ˛

atek powoduje przerwanie programu i

zgłoszenie bł ˛edu aplikacji.

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty PL/SQL, c.d.

21 / 37

Obsługa wyj ˛

atków – sterowanie

Je´sli wyj ˛

atek powstanie w sekcji

declare, to sterowanie wraca do

programu, który wywołał program, który zgłosił wyj ˛

atek.

Podobnie zachowuj ˛

a si ˛e wyj ˛

atki powstałe w cz ˛e´sci obsługuj ˛

acej

wyj ˛

atki.

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty PL/SQL, c.d.

22 / 37

Obsługa wyj ˛

atków – sterowanie

begin

declare

i

number ( 3 ) : = 1 0 0 0 ;

begin

n u l l ;

exception

when v a l u e _ e r r o r then

dbms_output . p u t _ l i n e ( ’ Z l a w a r t o s c ’ ) ;

−− t u t a j s t e r o w a n i e n i e d o t r z e

end ;

n u l l ;

exception

when v a l u e _ e r r o r then

dbms_output . p u t _ l i n e ( ’ T u t a j n a s t e p u j e obsluga w y j a t k u ’ ) ;

end ;

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty PL/SQL, c.d.

23 / 37

Obsługa wyj ˛

atków – przykład

cr ea te t a b l e Kura (

i d

i n t primary key ,

j a j k o _ i d

i n t

) ;

cr ea te t a b l e J a j k o (

i d

i n t primary key ,

k u r a _ i d

i n t

) ;

a l t e r t a b l e Kura add c o n s t r a i n t KuraRefJajko

f o r e i g n key ( j a j k o _ i d ) references J a j k o ( i d )

i n i t i a l l y deferred d e f e r r a b l e ;

a l t e r t a b l e J a j k o add c o n s t r a i n t JajkoRefKura

f o r e i g n key ( k u r a _ i d ) references Kura ( i d )

i n i t i a l l y deferred d e f e r r a b l e ;

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty PL/SQL, c.d.

24 / 37

Obsługa wyj ˛

atków – przykład

cr ea te or replace

t r i g g e r D o d a j J a j k o

before update or i n s e r t on Kura

f o r each row

declare

i

number ;

begin

s e l e c t i d i n t o i from J a j k o

where k u r a _ i d = :new . k u r a _ i d ;

exception

when no_data_found then

i n s e r t i n t o J a j k o values ( : new . j a j k o _ i d , n u l l ) ;

end ;

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty PL/SQL, c.d.

25 / 37

Składowane programy

Przechowywane po skompilowaniu w BD.

Procedury, funkcje, wyzwalacze, pakiety.

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty PL/SQL, c.d.

26 / 37

Składowane programy – parametry

Pozwalaj ˛

a przekazywa´c warto´sci do programu i z programu.

Paremetry programu w deklaracji nazywamy formalnymi, przy
wywołaniu programu aktualnymi.

Typy parametrów to

in, out, in out.

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty PL/SQL, c.d.

27 / 37

Składowane programy – składnia

cr ea te [ or replace ] nazwa_procedury [ ( parametry ) ] i s

d e k l a r a c j e

begin

. . .

end [ nazwa_procedury ] ;

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty PL/SQL, c.d.

28 / 37

Składowane programy – składnia

cr ea te [ or replace ] n a z w a _ f u n k c j i [ ( parametry ) ]

r e t u r n t y p [ i s | as ]

d e k l a r a c j e

begin

. . .

end [ n a z w a _ f u n k c j i ] ;

Funkcja powinna zawiera´c

return. Po wykonaniu tej instrukcji

wychodzimy z funkcji.

Funkcji mo˙zemy u˙zy´c w zapytaniu SQL.

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty PL/SQL, c.d.

29 / 37

Składowane programy – parametry

Parametry deklarujemy jako:

nazwa_parametru

[ t r y b p r z e k a z a n i a ] t y p

[ {

d e f a u l t w a r t o s c ] .

Typy parametrów to:

in – domy´slny tryb, przekazuje warto´s´c do programu, parametr
zachowuje si ˛e jak stała,

out – parametr musi by´c zmienn ˛

a, traktujemy j ˛

a jako zmienn ˛

a

niezainicjowan ˛

a, przekazuje warto´s´c na zewn ˛

atrz procedury,

in out – zmienna zainicjowana przekazuj ˛

aca warto´s´c na zewn ˛

atrz.

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty PL/SQL, c.d.

30 / 37

Składowane programy – przykład

cr ea te or replace procedure

i n k r e m e n t a c j a ( n

i n out number )

r e t u r n number i s

begin

n : = n + 1 ;

r e t u r n ( n ) ;

end i n k r e m e n t a c j a ;

Teraz mo˙zemy procedur ˛e wywoła´c:

declare

n

number : = 3 ;

i

number ;

begin

−− t u t a j n = 3

i : = i n k r e m e n t a c j a ( n ) ;

−− t u t a j n = 4

end ;

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty PL/SQL, c.d.

31 / 37

Pakiety

cr ea te [ or replace ] package nazwa_pakietu i s

d e k l a r a c j e zmiennych , s t a l y c h ,

kursorow , f u n k c j i , procedur ,

. . .

end ;

cr ea te [ or replace ] package body nazwa_pakietu i s

d e k l a r a c j e zmiennych , s t a l y c h ,

kursorow n i e d o s t e p n y c h w i n t e r f e j s i e p a k i e t u ,

d e f i n i c j e

f u n k c j i

i p r o c e d u r

end ;

Funkcje i procedury z pakietu wywołujemy jako:
nazwa_pakietu.nazwa_programu().

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty PL/SQL, c.d.

32 / 37

Dynamiczny SQL

Je´sli nie znamy podczas kompilacji pytania SQL, mo˙zemy je
skonstruowa´c podczas wykonywania programu.

Typowe sytuacje to: nie znamy nazwy tabeli, z której b ˛edziemy
pobiera´c dane, nie znamy warunku.

Dynamiczny SQL jest mniej efektywny i bardziej podatny na błedy
(np. podczas kompilacji zapytania).

Mo˙zemy te˙z w ten sposób wywoływa´c programy PL/SQL.

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty PL/SQL, c.d.

33 / 37

Dynamiczny SQL – przykład

cr ea te or replace procedure wloz_osoba (

osoba_id

i n out number ,

i m i e

i n varchar2 ,

nazwisko

i n varchar2 ) as

begin

osoba_id : = osoby_seq . n e x t v a l ;

i n s e r t i n t o osoby values ( osoba_id , i m i e , nazwisko ) ;

end ;

declare

plsql_komenda

varchar2 ( 5 0 0 ) ;

nowe_imie

varchar2 ( 5 0 ) : = ’ Jan ’ ;

nowe_nazwisko

varchar2 ( 1 0 0 ) : = ’ K o w a l s k i ’ ;

begin
plsql_komenda : =

’ beg in wloz_osoba ( : a , : b , : c ) ; end ; ’ ;

execute immediate plsql_komenda

using i n out nowe_id , nowe_imie , nowe_nazwisko ;

end ;

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty PL/SQL, c.d.

34 / 37

Dynamiczny SQL – przykład

Nie mo˙zna przekaza´c stałej

null ale mo˙zna przekaza´c zmienn ˛

a,

która ma t ˛

a warto´s´c.

Trzeba uwa˙za´c na typy przekazywanych zmiennych, by były
zgodne z SQL, np. nie mo˙zna u˙zy´c w tworzonym zapytaniu SQL
zmiennej o typie

boolean.

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty PL/SQL, c.d.

35 / 37

Dynamiczny SQL – parametry

Je´sli wywołujemy zapytanie SQL, to nie s ˛

a istotne nazwy u˙zytych

w definicji parametrów.
Ten kod wstawi do tabeli osoby pana Jana Nowaka:

begin

s q l _ z a p y t a n i e : =

’ i n s e r t i n t o osoby v a l u e s ( : x , : x , : y ) ’ ;

execute immediate s q l _ z a p y t a n i e

using 18 , ’ Jan ’ ,

’ Nowak ’ ;

end ;

Je´sli wywołujemy program PL/SQL nazwy parametrów s ˛

a istotne.

Ten kod obliczy warto´s´c funkcja(a,b,a):

begin

s q l _ z a p y t a n i e : = ’ be gin f u n k c j a ( : x , : y , : x ) ’ ;

execute immediate s q l _ z a p y t a n i e using

a , b ;

end ;

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty PL/SQL, c.d.

36 / 37

Dynamiczny SQL – przykład

declare
type KursorTyp i s r e f cursor ;
cur_osoby KursorTyp ;

v _ s t r

varchar2 ( 2 0 0 ) ;

osoba osoby%

rowtype ;

begin

v _ s t r : = ’ s e l e c t ∗ from osoby where i m i e = : i ’ ;

open cur_osoby f o r v _ s t r using ’Ewa ’ ;

loop

f e t c h cur_osoby i n t o osoba ;

e x i t when cur_osoby%n o t f o u n d ;

dbms_output . p u t _ l i n e ( osoba . i m i e

| |

’

’

| |

osoba . nazwisko ) ;

end loop ;
end ;

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty PL/SQL, c.d.

37 / 37