15. Funkcje i procedury składowane PL/SQL
15.1. SQL i PL/SQL (Structured Query Language - SQL)
Język zapytań strukturalnych SQL jest zbiorem poleceń, za pomocą których
programy i u\
ytkownicy uzyskują dostęp do bazy danych. SQL pozwala pra-
cować z danymi na poziomie logicznym. Wszystkie liczące się w przemyśle
systemy zarządzania relacyjnymi bazami danych obsługują SQL. PL/SQL jest
proceduralnym rozszerzeniem języka SQL, opracowanym przez Oracle.
PL/SQL jest językiem programowania 4-tej generacji (4GL), który charaktery-
zuje się cechami takimi jak enkapsulacja danych, przecią\
anie funkcji, kolekcje,
obsługa wyjątków, ukrywanie informacji. [40]
U\
ywając funkcji i procedur języka PL/SQL mo\
na ułatwić wprowadzanie,
aktualizację i zapytania o dane. Dotyczy to zarówno pracy przy konsoli
z językiem SQL, jak i bardziej zaawansowanych zastosowań  takich jak opera-
cje na danych za pomocą aplikacji internetowych wykonywanych po stronie
serwera. Dodatkową zaletą stosowania funkcji składowanych jest zwiększenie
bezpieczeństwa systemu, poprzez ukrycie przed u\
ytkownikami struktury prze-
chowywanych danych.
15.2. Podstawy PL/SQL
Na przykładach pokazane zostaną podstawowe właściwości języka PL/SQL.
15.2.1 Typy danych u\
ytkownika
Funkcje składowane mogą zwracać wartości skalarne lub zło\
one. Typami ska-
larnymi są standardowe typy języka ANSI SQL, takie jak INT, FLOAT, CHAR.
Typy zło\
one wykorzystujemy dla funkcji, które zwracają kolekcje, identycznie
jak instrukcja SELECT języka SQL. Rozpatrzmy utworzenie typów danych,
które będą wykorzystane przez funkcję pokaz_klientow zwracającą informa-
cje modelowane przez encję KLIENT z rys. 15.1.
194
Funkcje i procedury składowane PL/SQL
W celu usunięcia istniejących definicji typów u\
ywamy poleceń:
drop type zbior_klientow;
drop type klient;
Tworzymy typ opisujący pojedynczy wiersz tabeli:
create type klient as object (
id_kl integer,
nazwa_kl varchar2(64),
adres varchar2(64)
);
/
show error;
Polecenie show error pokazuje stan operacji. Tworzymy kolekcję opisującą
wiele wierszy tabeli:
create type zbior_klientow as table of klient;
/
15.2.2 Usuwanie funkcji.
W celu usunięcia istniejącej funkcji lub procedury u\
ywamy poleceń:
drop function nazwa_funkcji;
drop procedure nazwa_procedury;
W przypadku, gdy funkcja lub procedura nie istnieje, pojawi się komunikat
o błędzie, który mo\
na zignorować.
15.2.3 Tworzenie procedur i funkcji
Procedury są podprogramami nie zwracającymi wartości. Na przykład, usuwa-
nie klienta na podstawie podanego identyfikatora mo\
e zrealizować procedura:
create procedure usun_klienta (id integer) is
begin
delete from klienci where id_kl = id;
end;
/
show error;
195
Funkcje i procedury składowane PL/SQL
Zarówno procedury jak i funkcje mogą posiadać zmienne lokalne. Deklarowane
są one przed słowem BEGIN. Funkcje modyfikujące dane muszą być definiowa-
ne z dyrektywą kompilatora PRAGMA AUTONOMOUS_TRANSACTION. Na przy-
kład, funkcja dodająca nowego klienta mo\
e wyglądać następująco:
create function
nowy_klient (nazwa varchar2, adres varchar2)
return integer is
PRAGMA AUTONOMOUS_TRANSACTION;
id int;
begin
select klient_seq.nextval into id from dual;
insert into klienci values (id,adres,nazwa);
commit;
return id;
end;
/
show error;
Poniewa\
nastąpi modyfikacja danych, u\
ywamy odpowiedniej dyrektywy
kompilatora. Działanie funkcji jest następujące:
" u\
ywając sekwencji wybieramy kolejny identyfikator klienta,
" wstawiamy nowe dane,
" zatwierdzamy wstawienie danych,
" zwracamy identyfikator nowego klienta.
Funkcje lub procedury mogą posiadać parametry domyślne, których nie musi-
my podawać przy wywołaniu, np.:
create function
nowe_zamowienie(klient integer,
data date default sysdate)
return integer is begin
....
end;
15.2.4 Wywołanie funkcji lub procedury
W celu wywołania funkcji zwracającej wartość skalarną u\
ywamy instrukcji:
196
Funkcje i procedury składowane PL/SQL
select nazwa_funkcji(parametr, ... parametr) from dual;
Funkcję zwracającą tabelę wywołujemy w następujący sposób:
select * from TABLE(
CAST(nazwa_funkcji(par_1, ...,par_n) as typ_funkcji)
);
Procedurę wywołuje instrukcja:
call nazwa_procedury(parametr, ..., parametr);
15.2.5 Sterowanie warunkowe, iteracje i kursory
Kursor jest zmienną, za pomocą której uzyskujemy dostęp do wyniku zapyta-
nia. Jest wygodnym narzędziem przy wykonywaniu iteracji. Pokazane zostanie
zastosowanie kursora, iteracji i instrukcji warunkowej, na przykładzie funkcji
zwracającej 5 kolejnych wierszy tabeli KLIENCI.
create function pokaz_klientow(pierwszy int)
return zbior_klientow as
wynik zbior_klientow; k klient;
i int;
cursor c is select * from klienci
where id_kl >= pierwszy order by id_kl;
begin
i := 5; wynik := zbior_klientow();
k := klient(null,null,null);
for dane in c loop
if i <= 0 then exit; end if;
i := i-1; wynik.extend;
k.id_kl := dane.id_kl; k.nazwa_kl:=dane.nazwa_kl;
k.adres:=dane.adres;
wynik(wynik.count) := dane_klienta;
end loop;
return wynik;
end;
/
show error;
197
Funkcje i procedury składowane PL/SQL
Działanie funkcji jest następujące:
" deklarujemy kolekcję przechowującą dane zwracane przez funkcję
(zmienna wynik), licznik pobranych wierszy (zmienna i), kursor
(zmienna c) i rekord na jeden wiersz kursora (zmienna k)
" inicjalizujemy licznik, kolekcję i rekord,
" przeprowadzamy iterację po wyniku zapytania o klientów, wykorzystu-
jąc pętlę for,
" dołączamy do wyniku kolejne wiersze zapytania; sprawdzamy warunek
wyjścia z pętli instrukcją warunkową if.
Poniewa\
funkcja nie modyfikuje danych, nie jest potrzebna ani dyrektywa
PRAGMA AUTONOMOUS_TRANSACTION, ani potwierdzanie transakcji instrukcją
commit.
Warto nadmienić, \
e w bazie danych Oracle mo\
na tak\
e w inny sposób uzy-
skać zwrócenie N wierszy zapytania:
select * from klienci where
id_kl >= 10 and ROWNUM <= N order by id_kl;
15.2.6 Zgłaszanie wyjątku
W przypadku, gdy istnieje potrzeba przerwania funkcji i zgłoszenia błędu u\
yt-
kownikowi, mo\
na u\
yć funkcji raise_application_error, np.:
IF cena <= 0 THEN
raise_application_error(-20000, 'Musi być cena > 0');
END IF;
15.3. Przedstawienie problemu
Celem ćwiczenia jest utworzenie funkcji składowanych umo\
liwiających prze-
prowadzanie niektórych operacji na danych dla prostego systemu przetwarzania
zamówień.
15.4. Model danych systemu
Model danych przedstawiony w postaci diagramu ERD pokazany jest na
rys. 15.1. Atrybuty encji z diagramu przedstawiono w tab. 15.1.
198
Funkcje i procedury składowane PL/SQL
Tab. 15.1 Atrybuty encji
Encja Atrybut Opis Typ Atrybut
kluczowy
numer Numer porządkowy INTEGER x
pozycji
POZYCJA ilosc Ilość towaru NUMBER(6,2)
ZAMÓWIENIA cena Wartość jedn. towaru NUMBER(6,2)
wartosc cena*ilosc NUMBER(6,2)
id_kl Identyfikator klienta INTEGER x
KLIENT nazwa_kl Nazwa klienta VARCHAR2(64)
adres_kl Adres klienta VARCHAR2(64)
id_zam Identyfikator INTEGER x
zamówienia
data_wyst Data wystawienia DATE
ZAMÓWIENIE stan Zło\
one, INTEGER
zrealizowane lub
anulowane
zam_og Wartość zamówienia NUMBER(6,2)
kod_wyr Kod wyrobu INTEGER x
nazwa_wyr Nazwa wyrobu VARCHAR2(64)
WYRÓB ilosc Ilość w magazynie NUMBER(6,4)
dostepne Ilość po NUMBER(6,4)
uwzględnieniu
zło\
onych zamówień
cena_jedn Cena jednostkowa NUMBER(6,2)
199
Funkcje i procedury składowane PL/SQL
ZAMOWIENIE
KLIENT
zlozone przez
sklada
zawiera
jest na
dotyczy
POZYCJA_ZAMÓWIENIA
WYROB
wystepuje na
Rys. 15.1 Diagram ERD wykorzystywany w ćwiczeniu
15.5. Przebieg ćwiczenia
Zalogować się do bazy danych Oracle za pomocą programu SQL Plus i urucho-
mić skrypt tworzący schemat relacyjny odpowiadający diagramowi z rys. 15.1.
Wykorzystując przykłady z rozdziału 15.2 wykonać następujące ćwiczenia:
" Napisać funkcję nowy_klient wstawiającą dane nowego klienta do
tabeli KLIENCI. Identyfikator klienta wygenerować za pomocą pseudo-
kolumny nextval sekwencji klient_seq. Funkcja powinna zwracać
identyfikator wstawianego klienta (typ danych int).
" Napisać procedurę usuwającą klienta na podstawie podanego identy-
fikatora.
" Utworzyć typy danych: obiekt klient przechowujący dane identyczne
jak encja KLIENT; kolekcję zbior_klientow przechowującą informa-
cje o wielu klientach.
" Napisać funkcję pokaz_klientow_p zwracającą informacje o klien-
tach. Funkcja przyjmuje dwa parametry: identyfikator pierwszego
klienta i ilość klientów następujących po nim. Typ danych zwracany
przez funkcję to kolekcja utworzona w poprzednim zadaniu. Wykorzy-
stać iterację i kursor. Funkcja zwraca klientów, których identyfikatory
są większe od podanego parametru.
200
Funkcje i procedury składowane PL/SQL
" Napisać funkcję pokaz_klientow_t zwracającą informacje o klien-
tach. Funkcja przyjmuje dwa parametry: identyfikator ostatniego klien-
ta i ilość poprzedzających go klientów. Typ danych zwracany przez
funkcję to kolekcja utworzona w poprzednim zadaniu. Wykorzystać ite-
rację i kursor. Funkcja zwraca klientów, których identyfikatory są
mniejsze od podanego parametru.
" Napisać funkcję aktualizuj_dane_klienta aktualizującą informa-
cje o kliencie.
" Sprawdzić działanie funkcji i procedury.
" Wzorując się na funkcjach obsługujących tabelę klienci, napisać
funkcje obsługujące tabelę wyroby.
201