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 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;

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

.