15. Funkcje i proceduryskładowane PL/SQLoffice.prz-rzeszow.pl/instrukcje/plsql.pdf · PL/SQL jest...
Transcript of 15. Funkcje i proceduryskładowane PL/SQLoffice.prz-rzeszow.pl/instrukcje/plsql.pdf · PL/SQL jest...
194
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.
Funkcje i procedury składowane PL/SQL
195
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;
Funkcje i procedury składowane PL/SQL
196
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:
Funkcje i procedury składowane PL/SQL
197
select nazwa_funkcji(parametr, ... parametr) from d ual;
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_k l;
k.adres:=dane.adres;
wynik(wynik.count) := dane_klienta;
end loop;
return wynik;
end;
/
show error;
Funkcje i procedury składowane PL/SQL
198
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.
Funkcje i procedury składowane PL/SQL
199
Encja Atrybut Opis Typ Atrybut
kluczowy
numer Numer porządkowy
pozycji
INTEGER x
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
zamówienia
INTEGER x
data_wyst Data wystawienia DATE
ZAMÓWIENIE stan ZłoŜone,
zrealizowane lub
anulowane
INTEGER
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
uwzględnieniu
złoŜonych zamówień
NUMBER(6,4)
cena_jedn Cena jednostkowa NUMBER(6,2)
Tab. 15.1 Atrybuty encji
Funkcje i procedury składowane PL/SQL
200
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.
POZYCJA_ZAMÓWIENIA
ZAMOWIENIE
WYROB
KLIENT
jest na
zawiera
zlozone przez
sklada
dotyczy
wystepuje na
Rys. 15.1 Diagram ERD wykorzystywany w ćwiczeniu
Funkcje i procedury składowane PL/SQL
201
• 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 .