Pakiety, procedury, funkcje

Podprogramy to nazwane, zapamiętane w bazie bloki PL/SQL. Kasdy z nich zalicza się do jednej z trzech głównych kategorii:

procedur do przeprowadzania akcji,

funkcji do obliczania wartości,

pakietów do zbierania logicznie powiązanych ze sobą procedur i funkcji.

Procedury

Procedury składowane (stored procedures) dają mosliwość zapisania w bazie danych bloku PL/SQL (zestawu poleceń) i odwoływania się później do niego poprzez nazwę procedury.

Składnia

CREATE [OR REPLACE] PROCEDURE nazwa_procedury

[ (argument [IN|OUT|IN OUT] typ danych [ wyrasenie]

[, argument [IN|OUT|IN OUT] typ danych ] )] 

[lokalne deklaracje]

BEGIN

zestaw_instrukcji;

[EXCEPTION

obsługa_wyjątków]

END [nazwa_procedury];

OR REPLACE – opcja pozwala zastąpić wcześniej załosoną procedurę bez konieczności jej usuwania i nadawania jeszcze raz uprawnień do niej

argument – nazwa argumentu procedury, jeśli nie procedura nie ma argumentów mosna pominąć nawiasy

IN – domyślny parametr wejściowy (musi być podany przy wywoływaniu procedury), w procedurze zachowuje się jak stała

OUT – parametr wyjściowy przekazywany po zakończeniu procedury do środowiska wywołującego procedurę

IN OUT – parametr, który musi być podany przy wywołaniu procedury, i który równocześnie jest przekazywany przy wyjściu z procedury

typ danych – typ argumentu (dowolny typ danych dostępny w PL/SQL), ale bez podawania długości i precyzji

:= wyrasenie – klauzula pozwalająca przypisać inicjalizującą wartość argumentowi

Przykład

CREATE OR REPLACE PROCEDURE zmien_pensje

(nr_pracownika IN NUMBER, nowa_pensja IN NUMBER)

IS

BEGIN

UPDATE emp

SET sal = nowa_pensja

WHERE empno = nr_pracownika;

END zmien_pensje;

Wywołanie procedury w SQL*Plus:

SQL> exec zmien_pensje (7839, 20);

Pomiędzy słowami IS a BEGIN umieszcza się wszystkie lokalne dla procedury deklaracje

Funkcje

Funkcja to zapisany w bazie danych podprogram, który zwraca wartość (i tym rósni się od procedury). Funkcja mose być wywoływana jako część wyrasenia.

Składnia

CREATE [OR REPLACE] FUNCTION nazwa_funkcji

[ (argument [IN|OUT|IN OUT] typ danych [ wyrasenie]

[, argument [IN|OUT|IN OUT] typ danych ] )] 

RETURN typ danych

[lokalne deklaracje]

BEGIN

zestaw_instrukcji;

[EXCEPTION

obsługa_wyjątków]

END [nazwa_funkcji];

RETURN typ danych – obowiązkowa deklaracja typu zwracanej wartości (bez podawania długości i precyzji)

Przykład

CREATE OR REPLACE FUNCTION rocznie

(nr_pracownika NUMBER)

RETURN NUMBER IS

suma NUMBER;

BEGIN

SELECT 12 * (sal + nvl(comm,0))

INTO suma

FROM emp

WHERE empno = nr_pracownika;

RETURN suma;

END rocznie;

Wywołanie funkcji w SQL*Plus:

SQL>  select rocznie(7839)/12 'Średnie zarobki' from dual;

Średnie zarobki

6000

Pomiędzy słowami BEGIN a END musi być umieszczona instrukcja RETURN, która ustawia wartość wyjściową funkcji

Wielokrotne usycie instrukcji RETURN jest dozwolone (często wykorzystywane np. w poleceniu IF), ale tylko jedna instrukcja RETURN zostanie wykonana przy pojedynczym wywołaniu  funkcji.

Pakiety

Pakiet to obiekt zapisywany w bazie danych, który łączy w sobie procedury, funkcje i inne obiekty PL/SQL’owe. Tworzenie pakietu odbywa się w dwóch krokach. Po pierwsze w sekcji specyfikacji deklaruje się wszystkie publiczne obiekty wchodzące w skład pakietu, które będą stanowiły interfejs do pakietu.

Składnia

CREATE [OR REPLACE] PACKAGE nazwa_pakietu

sekcja_deklaracji

END [nazwa_pakietu];

sekcja_deklaracji – deklaracje zmiennych, stałych, kursorów, wyjątków, typów danych, procedur i funkcji

Przykład

CREATE OR REPLACE PACKAGE emp_akcje IS

TYPE EmpRecTyp IS RECORD (nr INTEGER, pensja REAL);

CURSOR zarobki RETURN EmpRecTyp;

PROCEDURE zatrudnij

(ename CHAR,

job CHAR,

mgr NUMBER,

sal NUMBER,

comm NUMBER,

deptno NUMBER);

PROCEDURE zwolnij (emp_id NUMBER);

END emp_akcje;

Drugi krok, to definiowanie pakietu poprzez deklaracje obiektów prywatnych i definiowanie obiektów zadeklarowanych w sekcji specyfikacji.

Składnia

CREATE [OR REPLACE] PACKAGE BODY nazwa_pakietu

sekcja_deklaracji_obiektów_prywatnych

definicje_obiektów

[BEGIN

instrukcje_inicjujące ]

END [nazwa_pakietu];

PACKAGE BODY - to część implementacyjna. Zawiera definicje kasdego kursora i podprogramu zdefiniowanego w sekcji deklaracji

Przykład

CREATE OR REPLACE PACKAGE BODY emp_akcje IS

CURSOR zarobki RETURN EmpRecTyp IS

SELECT empno, sal FROM emp ORDER BY sal DESC;

PROCEDURE zatrudnij

(ename CHAR,

job CHAR,

mgr NUMBER,

sal NUMBER,

comm NUMBER,

deptno NUMBER) IS

BEGIN

INSERT INTO emp VALUES (empno_seq.NEXTVAL, ename, job,mgr, SYSDATE, sal, comm, deptno);

END zatrudnij;

PROCEDURE zwolnij (emp_id NUMBER) IS

BEGIN

DELETE FROM emp WHERE empno = emp_id;

END zwolnij;

END emp_akcje;

Wywołanie funkcji z pakietu w SQL*Plusie:

SQL> exec  emp_akcje.zatrudnij ('Kowalski','CLERK',7698,1500,10,10)