9. Proceduri si functii

 

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).

 

9.1.          Declararea unei proceduri

 

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

 

9.2.         Declararea unei functii

 

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.

 

9.3.         Apelarea unei functii in SQL

 

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).

 

9.4.         Parametrii unui subprogram

 

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