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 .