Procedurile si functiile sunt denumite si
subprograme. Ele sunt in fapt blocuri PL/SQL avand un nume, o lista de
parametri si, in cazul functiilor, o valoare returnata.
Mai multe proceduri si/sau functii pot fi
'impachetate' sub forma unui 'pachet' (package).
Sintaxa definitiei unei proceduri este:
[CREATE [OR REPLACE]]
PROCEDURE nume_procedura[(parametru[, parametru]...)]
[AUTHID {DEFINER |
CURRENT_USER}] {IS | AS}
[PRAGMA
AUTONOMOUS_TRANSACTION;]
[declaratii locale]
BEGIN
instructiuni executabile sau
NULL;
[EXCEPTION
tratare erori]
END [nume_procedura];
unde:
§
[CREATE
[OR REPLACE]] - arata ca
procedura se defineste ca obiect al bazei de date si va fi stocata in aceasta.
Altfel ea este parte a unui alt bloc PL/SQL.
§
[AUTHID
{DEFINER | CURRENT_USER}] - specifica daca o procedura stocata se executa cu drepturile celui
care a creat-o (valoare implicita) sau ale utilizatorului curent.
§
[PRAGMA
AUTONOMOUS_TRANSACTION;] - daca se specifica aceasta caracteristica, executia procedurii
suspenda tranzactia curenta care se reia dupa terminarea executiei procedurii.
E ca si cand intr-o tranzactie imbricam alta tranzactie, cu propriile sale
COMMIT sau ROLLBACK.
Fiecare parametru formal se defineste astfel:
nume_parametru
[IN | OUT [NOCOPY] | IN OUT [NOCOPY]] numetip
[{:=
| DEFAULT} expresie]
unde:
1.
IN,
OUT si IN OUT arata ca este vorba de un
parametru de intrare, de iesire sau bidirectional. In cazul in care nu se
specifica, parametrul este implicit transmis ca IN. In corpul unui subprogram nu se poate asigna o valoare unui parametru
transmis cu IN.
2.
NOCOPY. In mod implicit parametrii
de tip IN sunt transmisi prin referinta iar cei OUT si IN OUT prin valoare. In
cazul unor parametri voluminosi transmiterea prin valoare e consumatoare de
resurse. Se poate specifica in acest caz (OUT, IN OUT) transmiterea prin
referinta folosind NOCOPY.
3.
numetip este un nume de tip fara
specificarea dimensiunii (deci VARCHAR2 de exemplu si nu VARCHAR2(10). Se poate
totusi asocia o dimensiune maxima prin definirea unui subtip care sa corespunda
dorintelor noastre. Exemplu:
DECLARE
SUBTYPE Char25 IS CHAR(25);
PROCEDURE cauta (persoana Char25) IS ...
Cand o procedura este parte a unui alt bloc ea va fi
plasata (ca definitie) in zona DECLARE a acestuia si poate fi apelata in zona
executabila cu parametri actuali.
Exemplu:
DECLARE
PROCEDURE avansare (cod_ang INTEGER, suma
REAL) IS
sal_actual REAL;
fara_sal EXCEPTION;
BEGIN
SELECT sal INTO sal_actual FROM emp
WHERE empno = cod_ang;
IF sal_actual IS NULL THEN
RAISE fara_sal;
ELSE
UPDATE emp SET sal = sal + suma
WHERE empno = cod_ang;
dbms_output.put_line('Actualizat pt.cod '||cod_ang);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('Nu exista angajatul');
WHEN fara_sal THEN
dbms_output.put_line('Nu are salariu');
END avansare;
BEGIN -- blocul principal
avansare(7369, 100);
avansare(7500, 300);
avansare(7654, 200);
END;
Rezultatul va fi:
Actualizat pt.cod 7369
Nu exista angajatul
Actualizat pt.cod 7654
Sintaxa simplificata a definitiei unei functii este
urmatoarea:
[CREATE
[OR REPLACE ] ]
FUNCTION
nume_functie [ ( parametru [ , parametru ]... )]
RETURN tip_date_returnate
[ AUTHID { DEFINER | CURRENT_USER } ]
{IS
| AS}
[
PRAGMA AUTONOMOUS_TRANSACTION; ]
[declaratii locale]
BEGIN
instructiuni executabile sau
NULL;
[EXCEPTION
tratare erori]
END [nume_functie];
Elementele care apar si la proceduri au aceeasi
semnificatie. In plus apare tipul
valorii returnate care ca si parametrii formali nu poate fi constrans
prin specificarea unei dimensiuni maxime.
In zona executabila trebuie sa apara instructiunea RETURN expresie care specifica valoarea
returnata de functie.
Mai jos este rescris programul de marire de salariu
prin transformarea procedurii intr-o functie care intoarce un sir de caractere:
DECLARE
FUNCTION
avansare(cod_ang INTEGER, suma REAL) RETURN VARCHAR2
IS
sal_actual
REAL; fara_sal EXCEPTION;
BEGIN
SELECT sal INTO sal_actual
FROM emp
WHERE empno = cod_ang;
IF sal_actual IS NULL THEN
RAISE fara_sal;
ELSE
UPDATE emp SET sal = sal +
suma
WHERE empno = cod_ang;
RETURN 'Actualizat pt.cod
'||cod_ang;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 'Nu exista angajatul';
WHEN fara_sal THEN
RETURN 'Nu are salariu';
END avansare;
BEGIN --
blocul principal
dbms_output.put_line(avansare(7369, 100));
dbms_output.put_line(avansare(7500, 300));
dbms_output.put_line(avansare(7654, 200));
END;
Dupa cum se poate observa RETURN poate fi
prezent si in zona de tratare a exceptiilor.
Pentru ca o functie stocata sa fie apelabila in cereri SQL
trebuie sa respecte urmatoarele restrictii:
1.
Cand este
apelata intr-o cerere SELECT functia nu trebuie sa modifice nimic in tabelele
bazei de date.
2.
Cand este
apelata in INSERT, UPDATE sau DELETE functia nu poate regasi sau modifica
datele actualizate de cererea SQL.
3.
Cand este
apelata din SELECT, INSERT, UPDATE sau DELETE functia nu poate executa cereri
de control a tranzactiilor (ex.: COMMIT), cereri de control a sesiunii de lucru
(ex.: SET ROLE) sau cereri de control sistem (ca ALTER SYSTEM). De asemenea nu
poate executa cereri care se comit automat (ca de exemplu CREATE).
In momentul declararii unui subprogram se folosesc parametri
formali. Asa cum am mentionat acestia pot fi transmisi in trei moduri:
Modul IN:
§
Parametrul
este un parametru de intrare.
§
Parametrul
este transmis prin referinta, deci se transmite o adresa a originalului.
§
Parametrul
actioneaza ca o constanta: in corpul subprogramului acesta nu poate fi
modificat (de exemplu prin atribuire sau SELECT INTO), o astfel de operatie
ducand la erori de compilare. Modul IN este modul implicit de transmitere.
§
La
apelarea subprogramului parametrul actual poate fi o constanta, un literal, o variabila
initializata sau o expresie.
§
Spre
deosebire de celelalte doua moduri pentru acesti parametri se pot defini valori
implicite (DEFAULT).
Specificarea unei valori implicite se face ca in
exemplul urmator:
FUNCTION avansare(
cod_ang INTEGER, suma REAL
DEFAULT 100) RETURN VARCHAR2
IS
. . . .
In acest caz functia se poate chema cu 2 parametri
actuali dar si cu unul singur, in al doilea caz al doilea avand valoarea
implicita 100.
In cazul urmator insa:
FUNCTION avansare(
suma REAL DEFAULT 100,
cod_ang INTEGER) RETURN
VARCHAR2
IS
. . . .
apelurile:
avansare(2456);
avansare(, 2456);
semnaleaza eroare si nu actualizeaza salariul
angajatului cu codul 2456. Rezolvarea acestor tip de situatii este prezentata
la sfarsitul acestui subcapitol.
Modul OUT:
§
Parametrul
este un parametru de iesire.
§
Parametrul
este transmis prin valoare, deci se transmite o copie a originalului.
§
Parametrul
poate fi modificat in corpul subprogramului.
§
La
apelarea subprogramului parametrul actual trebuie sa fie o variabila.
§
La
apelare acesta poate avea o valoare care se pierde insa la apel (in afara
cazului cand se specifica NOCOPY).
§
In
momentul apelului parametrul este initializat cu NULL. Din acest motiv tipul
lui nu poate fi unul constrans de NOT NULL (ex.: POSITIVEN, NATURALN)
§
Parametrului
nu i se poate asocia o valoare implicita (DEFAULT).
§
In
corpul subprogramului trebuie sa i se asocieze o valoare (altfel aceasta ramane
valoarea NULL).
§
La
iesirea din subprogram PL/SQL asigneaza valoarea respectiva (inclusiv NULL)
parametrului actual.
§
Daca
subprogramul iese cu o exceptie netratata asignarea de mai sus nu este facuta.
Modul IN OUT:
§
Parametrul
este un parametru de intrare si de iesire.
§
Parametrul
este transmis prin valoare, deci se transmite o copie a originalului.
§
Parametrul
poate fi modificat in corpul subprogramului.
§
La
apelarea subprogramului parametrul actual trebuie sa fie o variabila
initializata care se poate folosi in corpul subprogramului.
§
Parametrului
nu i se poate asocia o valoare implicita (DEFAULT).
§
In
corpul subprogramului parametrului i se poate asocia o valoare (altfel aceasta
ramane valoarea de la apel).
§
La
iesirea din subprogram PL/SQL asigneaza valoarea respectiva (inclusiv NULL)
parametrului actual.
§
Daca
subprogramul iese cu o exceptie netratata asignarea de mai sus nu este facuta.
Optiunea NOCOPY:
§
Se
aplica pentru parametrii OUT sau IN OUT specificand ca transmiterea se face
prin referinta nu prin valoare.
§
In
unele cazuri ea este ignorata, deci transmiterea se face tot prin valoare.
§
In
cazul iesirii din subprogram cu exceptie netratata nu se poate sti exact starea
parametrului actual:
o
daca
s-a transmis totusi prin referinta modificarile facute pana la aparitia
exceptiei se reflecta in valoarea parametrului actual
o
daca
transmiterea s-a facut prin valoare PL/SQL nu copiaza valorile in parametrul
actual.
§
Protocolul
RPC nu permite decat transmiterea prin valoare. In cazul mutarii unei proceduri
avand parametri NOCOPY pe alt host automat la folosirea ei acestia vor fi
transmisi prin valoare.
Specificarea numelui parametrului formal
Exista posibilitatea ca la apelul unui subprogram parametrii
actuali sa fie in alta ordine decat cei formali. In acest caz se specifica
pentru parametrii formali care sunt parametrii actuali asociati:
Exemplu. Functia:
FUNCTION avansare(
suma REAL DEFAULT 100,
cod_ang INTEGER) RETURN
VARCHAR2
IS
. . . .
poate fi apelata in urmatoarele moduri:
avansare(200, 2456); --
pozitional
avansare(suma => 200,
cod_ang => 2456); -- cu nume
avansare(cod_ang => 2456,
suma => 200); -- cu nume
avansare(cod_ang =>
2456); -- cu nume. suma = 100
avansare(200, cod_ang =>
2456); -- mixt