• Subprograme
• Proceduri PL/SQL
• Funcții PL/SQL
• Considerente asupra procedurilor și funcțiilor
• Informații din dicționarul bazei de date

Un subprogram este un bloc PL/SQL cu nume (spre deosebire de blocurile anonime) care poate primi parametri și poate fi invocat dintr-un anumit mediu (e.g., SQL*Plus, Oracle Forms, Oracle Reports etc.).

Subprogramele sunt bazate pe structura de bloc PL/SQL. Similar unui bloc PL/SQL ele conțin o parte declarativă facultativă, o parte executabilă obligatorie și o parte de tratare excepțiilor facultativă.

Exista 2 tipuri de subprograme:

• Proceduri
• Funcții (trebuie să conțină cel puțin o comandă RETURN);

Subprogramele pot fi:

• Locale (în cadrul altui bloc PL/SQL sau subprogram)
• Stocate (create cu comanda CREATE)

Odată create, procedurile și funcțiile sunt stocate în baza de date de aceea ele se numesc subprograme stocate.

Parametrii sunt variabile disponibile atât pentru programul principal (apelant) cât și pentru subprogram. Parametrii determină funcționalitatea și rezultatele procesării datelor. Parametrii unui subprgram sunt opționali și sunt declarați în momentul creării subprogramului. Un nume de parametru trebuie să înceapă cu o literă, să nu conțină spații, și să aibă lungimea maximă de 30 de caractere. Parametrii actuali reprezintă valorile efective transmise subprogramului în momentul apelului sau rezultatele întoarse de subprogram (în cazul definirii unor parametri OUT sau IN OUT), în timp ce parametrii formali sunt cei care recepționează valorile parametrilor actuali, fiind referiți conform logicii subprogramului.

Sintaxa:

parameter_name [IN | OUT [NOCOPY] | IN OUT [NOCOPY]] parameter_type  [ {:=  | DEFAULT } {expresion | VALUE}]

Unde:

• parameter_name – numele parametrului
• parameter_name – tipul parametrului
• NOCOPY – specifică că parametrul se transmite prin referință (adresă) nu prin valoare și este valabil doar pentru OUT și IN OUT care se transmit implicit prin valoare
• := | DEFAULT – pentru a da o valoare implicită
• expresion | value – se pot atribui expresii sau valori parametrilor

Parametrii unui subprogram pot fi transmiși în două moduri, prin referință sau prin valoare. În primul caz un pointer către parametrul actual este transmis parametrului formal corespunzător și nu se copiază efectiv, ceea ce duce la creșterea performanțelor mai ales la parametrii de tip colecție. La transmiterea prin valoare are loc copierea valorii parametrului actual la cel formal. Până la versiunea 8i doar parametrii de intrare (IN) erau transmiși prin referință (se întâmplă în continuare acest lucru), iar cei de ieșire (OUT) prin valoare. Folosindu-se NOCOPY se pot transmite parametrii de ieșire (OUT) și cei de intrare-ieșire (IN OUT) prin referință. Când un parametru este transmis prin referință, orice modificare asupra parametrului actual va modifica și parametrul formal, deoarece ambii au pointeri către aceeași valoare.

Există trei tipuri de parametri:

• IN – parametrul poate fi referit în interiorul subprogramului, dar nu poate fi modificat;
• OUT – parametrul nu poate fi referit în interiorul subprogramului, dar poate fi modificat și poate fi referit în afara subprogramului (în programul apelant);
• IN OUT – parameterul poate fi referit în interiorul subprogramului, poate fi modificat și poate fi referit în afara subprogramului (în programul apelant).

Parametrul IN este tipul implicit. Parametrul OUT este inițializat cu NULL, iar subprogramul atribuie parametrului o valoare care poate fi referită în afara ei. Un parametru IN OUT poate avea (sau nu) o valoare inițială, valoarea inițială poate fi modificată în subprogram și orice modificare poate fi returnată în programul apelant.

Parametrii sunt declarați ca tipuri de date dar fără lungime de tip sau de precizie, adică un parametru poate fi declarat ca VARCHAR2 dar fără o componentă de lungime(de ex. VARCHAR2(30) nu este permis). Parametrii pot avea o valoare implicită similară variabilelor, deci se poate folosi operatorul de atribuire (:=) sau opțiunea DEFAULT. Dacă un parametru are o valoare implicită nu este nevoie să se includă în apel. Se poate folosi operatorul de asociere (=>) pentru a trimite valori efective unui parametru, în acest caz ordinea nu contează. Se indică folosirea operatorului de asociere în cazul subprogramelor cu mulți parametri mai ales pentru că o astfel de scriere este mult mai clară și nu există posibilitatea strecurării unei erori în momentul când se apelează subprogramul.

O procedură este un subprogram care execută un set de instrucțiuni și nu returnează direct o valoare către programul apelant. Rezultatele obținute prin procesarea datelor pot fi folosite în programul apelant, în funcție de modul de declarare a parametrilor.

O procedură stocată poate fi declarată în cadrul unui bloc PL/SQL. Durata de viață al unei astfel de proceduri este numai pe perioada existenței blocului. După terminarea execuției blocului procedura se pierde, deoarece este stocată doar în memorie, nu este stocată permanent în SGBD.

Sintaxa

DECLARE
  [block_variables;]
  PROCEDURE procedure_name [(parameter_name [IN|OUT|IN OUT] parameter_type,...)] 
  {IS|AS}
    [procedure_declaration_section]
  BEGIN
    procedure_executable_section;
  [EXCEPTION
    procedure_exception_section]
  END [procedure_name];
  ...
BEGIN
  lock_executable_section;
  procedure_name [(parameters)];
  block_executable_section;
[EXCEPTION
  block_exception_section]
END;

Unde:

• procedure_name – numele procedurii
• parameter_name – numele unui parametru formal din lista de parametri
• parameter_type – reprezintă tipul parametrului formal, se dă fără precizie
  • GREȘIT: nume_param varchar2(30)
  • CORECT: nume_param varchar2
• procedure_declaration_section – secțiunea de declarare a variabilelor locale folosite în procedură, poate să lipsească
• procedure_executable_section – secțiunea executabilă a procedurii
• procedure_exception_section – secțiune de tratare a excepțiilor din cadrul procedurii, poate să lipsească
• block_variables – secțiunea de declarare a variabilelor locale folosite în bloc
• block_executable_section – secțiunea executabilă a blocului
• block_exception_section – secțiunea de tratare a excepțiilor din cadrul blocului, este opțională
• IN | OUT | IN OUT – specifică dacă parametrul poate fi referit sau modificat în interiorul sau exteriorul procedurii

Ex. 1. Exemplu de procedură declarată într-un bloc.

Ex. 2. Să se scrie o procedură declarată în cadrul unui bloc care întoarce salariu maxim pentru un ID de departament și o funcție introduse de la tastatură. Salariu maxim să fie returnat folosindu-se o variabilă scalară. Să se traducă joburile în limba română, în cadrul procedurii. Aveți grijă la cum sunt repartizate job-urile în departamente.

O procedură stocată este creată ca un obiect în dicționarul de date de la nivelul SGBD-ului și poate fi folosită oricând.

Sintaxa:

CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter_name 	[IN|OUT|IN OUT] parameter_type, ... )] 
  [AUTHID {DEFINER|CURRENT_USER}]
  [PRAGMA AUTONOMOUS_TRANSACTION]
{IS|AS}
  [declaration_section]
BEGIN
  execution_section;
[EXCEPTION 
  exception_section;]
END [procedure_name];

Unde:

• procedure_name – numele procedurii
• parameter_name – numele unui parametru formal din lista de parametri
• parameter_type – reprezintă tipul parametrului formal, se specifica fără precizie
• declaration_section – secțiunea de declarare a variabilelor locale folosite în procedură
• executable_section – secțiunea executabilă a procedurii
• exception_section – secțiune de tratare a excepțiilor din cadrul procedurii, poate să lipsească
• IN | OUT | IN OUT – specifică dacă parametrul poate fi referit sau modificat în interiorul sau exteriorul procedurii
• [AUTHID {DEFINER | CURRENT_USER}] – specifică dacă o procedură stocată se execută cu drepturile celui care a creat-o (valoarea implicită) sau ale utilizatorului curent
• [PRAGMA AUTONOMOUS_TRANSACTION] – specifică că execuția procedurii suspendă tranzacția curentă care se reia după terminarea execuției procedurii, adică într-o tranzacție imbricăm o altă tranzacție cu propriile sale comenzi TCL (COMMIT și ROLLBACK)

Ex. 3. Să se creeze o procedură stocată care calculează veniturile angajaților cu o vechime de peste 10 de ani în firmă, dintr-un anumit departament.

Cursoarele declarate într-o procedură urmează aceleași reguli ca într-un bloc obișnuit. O procedură poate conține mai multe cursoare înlănțuite sau imbricate.

Ex. 4. Să se scrie o procedură nestocată, care utilizează un cursor, pentru calculul numărului de zile de concediu pentru toți angajații, după următorul algoritm:

1. manageri de departament:
   • Vechimea < 13 de ani primesc 20 zile de concediu
   • Vechimea >= 13 de ani primesc 22 zile de concediu
2. angajații care nu sunt șefi:
   • Vechimea < 13 de ani primesc 15 zile de concediu
   • Vechimea >= 13 de ani primesc 22 zile de concediu

Ex. 5. Să se scrie un program PL/SQL cu o procedură care distribuie salariul șefului de departament la subalternii lui, în funcție de vechimea în companie, astfel:

• grupa 1 – subalternii cu o vechime ⇐ 31 de ani să primească un un fond de premiere egal cu 30% din salariul șefului
• grupa 2 – subalternii cu o vechiime > 31 de ani să primească un fond de premiere egal cu 70% din salariul șefului

Fondul de premiere se distribuie în mod egal la toți subalternii dintr-o grupă.

Pentru a folosi un tip colecție ca parametru pentru o procedură stocată acesta trebuie să fie definit la nivelul bazei de date folosindu-se instrucțiunea DDL create. Tipurile colecție care pot fi declarate la nivelul bazei de date sunt:

• Varray
• Nested table

Ex. 6. Să se creeze un vector fie la nivelul dicționarului de date, fie local. Într-un bloc, să se definească și inițializeze o variabilă de tipul nou creat și să se apeleze o procedură care afișează valorile elementelor. Să se șteargă tipul din dicționarul de date.

O procedură stocată poate să primească ca parametru un cursor. Acesta trebuie să fie de tipul SYS_REFCURSOR.

Ex. 7. Exemplu de cursor ca parametru.

O funcție este un subprogram care acceptă parametri, poate fi apelată dintr-un program apelant și returnează o valoare. În general, funcțiile sunt folosite pentru a procesa un set de date și a întoarce un rezultat. Funcțiile și procedurile au structuri asemănătoare. O funcție trebuie să întoarcă o valoare folosind clauza return (și mai multe folosind parametri de tip OUT și IN OUT) în timp ce o procedură poate să întoarcă una sau mai multe valori prin intermediul parametrilor de tip OUT sau IN OUT.

Funcțiile declarate în cadrul unui bloc sunt funcții stocate în memorie împreună cu blocul PL/SQL în care sunt declarate și au o funcționalitate asemănătoare procedurilor. Durata de viață a unei astfel de funcții este doar pe perioada de existență a blocului. Aceste funcții pot să fie apelate doar în cadrul blocului în care sunt declarate. După terminarea execuției blocului funcțiile locale sunt șterse din memorie. Pentru a apela din nou o astfel de funcție trebuie să se execute din nou și blocul în care a fost declarată. Funcțiile pot fi folosite în blocul apelant, folosind operatorul de atribuire, în cadrul unor expresii și condiții;

Sintaxa:

DECLARE
  block_variables;
  var return_data_type; -- variabilă de tipul întors de funcție
  block_variables;
 
  FUNCTION function_name [(parameter_name [IN|OUT|IN OUT] parameter_type,...)] RETURN return_data_type
  {IS | AS}
    [function_declaration_section]
  BEGIN -- blocul funcției
    function_executable_section;
  [EXCEPTION
    function_exception_section]
  END [function_name]; -- sfârșitul declarării funcției
...
BEGIN
  block_executable_section;
  var := function_name[(parametrii)];
  block_executable_section;
[EXCEPTION
     block_exception_section]
END;

Unde:

• function_name – numele funcției
• parameter_name – numele unui parametru formal din lista de parametri
• parameter_type – reprezintă tipul parametrului formal, se specifica fără precizie
• return_data_type – tipul de date întors de funcție, se specifica fără precizie
• function_declaration_section – secțiunea de declarare a variabilelor locale folosite în funcție, poate să lipsească
• function_executable_section – secțiunea executabilă a funcției, trebuie să conțină cel puțin un RETURN care să întoarcă o variabilă care are tipul de date return_data_type
• function_exception_section – secțiune de tratare a excepțiilor din cadrul funcției, poate să lipsească, în schimb, dacă se folosește trebuie să conțină câte un RETURN pentru fiecare excepție tratată
• block_variables – secțiunea de declarare a variabilelor locale folosite în bloc
• block_executable_section – secțiunea executabilă a blocului
• block_exception_section – secțiunea de tratare a excepțiilor din cadrul blocului, este opțională
• IN | OUT | IN OUT – specifică dacă parametrul poate fi referit sau modificat în interiorul sau exteriorul funcției
• var – numele variabile în care funcția returnează o valoare, are tipul return_data_type

Ex. 8.Să se scrie o funcție locală care primește ca parametru un identificator de departament și returnează numărul salariaților din departamentul respectiv.

Funcțiile stocate sunt funcții create în dicționarul bazei de date și pot fi accesate ca orice obiect, dacă utilizatorul are suficiente privilegii.

Sintaxa:

CREATE [OR REPLACE] FUNCTION function_name [(parameter_name [IN|OUT|IN OUT] parameter_type, ... )] 
RETURN return_data_type
  [AUTHID {DEFINER|CURRENT_USER}]
  [PRAGMA AUTONOMOUS_TRANSACTION]
{IS|AS}
  [declaration_section]
BEGIN
  execution_section;
[EXCEPTION 
  exception_section;]
END [function_name];

Unde:

• function_name – numele funcției
• parameter_name – numele unui parametru formal din lista de parametri
• parameter_type – reprezintă tipul parametrului formal, se specifica fără precizie
• return_data_type – tipul de date întors de funcție, se specifica fără precizie
• declaration_section – secțiunea de declarare a variabilelor locale folosite în funcție
• executable_section – secțiunea executabilă a funcției, trebuie să conțină cel puțin un RETURN care să întoarcă o variabilă care are tipul de date return_data_type
• exception_section – secțiune de tratare a excepțiilor din cadrul funcției, poate să lipsească
• IN | OUT | IN OUT – specifică dacă parametrul poate fi referit sau modificat în interiorul sau exteriorul procedurii
• [AUTHID {DEFINER|CURRENT_USER}] – specifică dacă o procedură stocată se execută cu drepturile celui care a creat-o (valoarea implicită) sau ale utilizatorului curent
• [PRAGMA AUTONOMOUS_TRANSACTION] – specifică că execuția procedurii suspendă tranzacția curentă care se reia după terminarea execuției procedurii, adică într-o tranzacție imbricăm o altă tranzacție cu propriile sale comenzi TCL (COMMIT și ROLLBACK)

Ex. 9 Să se scrie o funcție stocată, f_puncte, care calculează numărul de puncte acumulate de angajați în vederea numirii unui șef pe fiecare departament. Criteriile de punctaj și punctele aferente sunt următoarele:

1. vechimea în firmă
   • Ani vechime > 32 ⇒ 30 puncte
   • Ani vechime <= 32 ⇒ 15 puncte
2. salariul maxim pe departament
   • Salariu angajat = salariu maxim din departament ⇒ 20 puncte
   • Salariu angajat < salariu maxim din departament ⇒ 10 puncte
3. comision
   • Comision angajat > 0 ⇒ 10 puncte
   • Comision angajat = 0 ⇒ 5 puncte

Pentru stocarea punctelor acumulate de fiecare angajat, se va crea o tabelă temp_puncte care are următoarele coloane: idAng, nrPuncte, idDept.

Funcția f_puncte creată anterior, care returnează numărul de puncte obținute de un angajat în vederea întocmirii unui clasament pe departamente, poate fi folosită astfel:

SELECT d.dname, e.ename, f_puncte(e.empno) punctaj
FROM emp e 
INNER JOIN DEPT d ON d.deptno = e.deptno
WHERE d.deptno = 10;
 
SELECT d.dname, e.ename, f_puncte(e.empno) punctaj
FROM emp e 
INNER JOIN dept d ON d.deptno = e.deptno AND f_puncte(e.empno) >= 40
ORDER BY d.dname, punctaj DESC;
 
SELECT 
  d.dname Departament,
  (SELECT MAX(sal) FROM emp WHERE deptno = e.deptno) Salariu_Maxim, 
  e.ename NumeAngajat,
  trunc(months_between(sysdate, e.hiredate)/12) Ani_Vechime,
  nvl(e.comm, 0) Comision,
  f_puncte(e.empno) Punctaj
FROM emp e INNER JOIN dept d ON d.deptno = e.deptno
WHERE f_puncte(e.empno) = (SELECT MAX(f_puncte(empno)) FROM emp WHERE deptno = e.deptno)
ORDER BY d.dname;

Informațiile obținute din ultimul select sunt echivalente cu cele obținute prin programul PL/SQL prezentat în exercițiul 3. Se observă că dacă utilizatorul are cunoștințe de SQL, acesta poate să creeze fără dificultăți liste simple. Comanda se bazează pe un cursor implicit creat de SGBD. Faptul că o comandă SQL poate să facă același lucru ca un bloc PL/SQL nu înseamnă că este mai ușor să scrii comenzi SQL decât blocuri PL/SQL. Totuși, în dezvoltarea aplicațiilor, un dezvoltator va încerca întotdeauna, în momentul construirii unui bloc PL/SQL, să minimizeze numărul de cereri SQL, încercând să obțină toate informațiile necesare folosind cât mai puține cereri SQL.

În general, o procedură este folosită pentru secvențe de cod mai complexe, în timp ce o funcție este folosită în cadrul procedurii pentru calcule repetate și mai simple. De exemplu, într-o aplicație de salarizare:

• O procedură se pretează pentru implementarea algoritmului de calcul al salariului net, considerând salariul de bază, pontajele lunare, sporurile, creșterile salariale, etc.
• O funcție se pretează pentru calculul taxelor și impozitelor aferente

Din punct de vedere tehnic, sunt câteva mici diferențe între proceduri și funcții:

• O funcție întoarce întotdeauna în mod direct o valoare în programul apelant și poate întoarce mai multe valori prin parametrii de tip OUT sau IN OUT
• O procedură nu returnează direct o valoare, dar poate întoarce mai multe valori în programul apelant prin parametrii de tip OUT sau IN OUT
• Funcțiile stocate pot fi apelate direct din comenzi SQL
• Procedurile stocare NU pot fi apelate direct din comenzi SQL
• Funcțiile pot fi folosite în operațiile de atribuire, în expresii și condiții (adică, atât în clauza WHERE cât și în condițiile structurilor de control)
• Procedurile NU pot fi folosite în operațiile de atribuire, în expresii și condiții (adică, atât în clauza WHERE cât și în condițiile structurilor de control)
• Procedurile și funcțiile pot fi apelate direct din programe PL/SQL, sau din interfețe specifice, de exemplu Oracle Forms Builder sau Oracle Reports Builder
• Procedurile și funcțiile pot fi apelate și din limbaje de programare de nivel înalt (Java, C++, C#, Python, etc) folosindu-se API-uri dedicate.

Funcțiile apelate din cereri SQL trebuie să îndeplinească următoarele condiții:

• Când este folosită într-o cerere SELECT, funcția NU trebuie să conțină comenzi INSERT, UPDATE sau DELETE
• Când este folosită în comenzile INSERT, UPDATE sau DELETE, funcția NU trebuie să modifice conținutul tabelelor afectate de comanda SQL
• Când este folosită în comenzile SELECT, INSERT, UPDATE sau DELETE , funcția NU trebuie să conțină comenzi TCL (e.g., COMMIT sau ROLLBACK).

Dacă într-o procedură sau funcție apare o excepție, tratată de sistem sau definită de utilizator, controlul este preluat de secțiunea EXCEPTION sau de blocul superior, iar parametrilor de tip OUT sau IN OUT nu li se transmite nicio valoare. În cazul în care apar excepții, parametrii actuali din procedura sau funcția apelată vor păstra aceleași valori pe care le-au avut înainte de a se face apelul. Execuția comenzilor CREATE OR REPLACE sau DROP pentru proceduri și funcții , precum și a comenzilor ALTER TABLE sau ALTER VIEW pentru tabele și view-uri, pot modifica starea altor obiecte din baza de date cu care sunt relaționate. De exemplu dacă într-o procedură este declarat un cursor care lucrează pe o anumită tabelă, atunci ștergerea sau modificarea structurii tabelei poate invalida procedura care apelează cursorul respectiv. Probabilitatea apariției acestor efecte, ca urmare a relaționării obiectelor, este ridicată și de aceea serverul Oracle le tratează corespunzător în timpul execuției sau apelării obiectelor corespunzătoare. La apariția unei astfel de situații serverul modifică în dicționarul bazei de date starea obiectelor afectate ca DISABLE și este necesară o recompilare a lor pentru a reveni în starea ENABLE.

Sistemul de gestionare ORACLE permite acordarea privilegiului de execuție asupra procedurilor și funcțiilor și altor utilizatori, acțiunea permisă proprietarului lor (utilizatorul care le-a creat) sau administratorului care are privilegii de DBA. Sintaxa comenzii GRANT este:

 GRANT EXECUTE ON {procedure_name | function_name} TO user_name;

Ștergerea privilegiilor se face folosind comanda REVOKE, sintaxa este:

REVOKE EXECUTE ON {procedure_name | function_name} FROM user_name;

Codul sursă al procedurilor și funcțiilor stocate se găsește în dicționarul bazei de date, în view-ul USER_SOURCE, care are următoarea structură:

Unde:

• name – este numele obiectului (procedură, funcție, etc)
• type – reprezintă tipul obiectului
• line – este numărul liniei de cod
• text – este codul sursă de la linia respectivă

Dacă vrem să vedem toate liniile de cod ale funcției f_puncte, putem folosi următoarea cerere SQL:

SELECT text FROM user_source WHERE LOWER(name) = 'f_puncte' ORDER BY line;

Pentru a vizualiza toate procedurile, funcțiile și tipurile de date create de utilizatorul curent, se poate folosi următoarea cerere SQL:

SELECT object_name, object_type 
FROM user_objects
WHERE LOWER(object_type) IN ('function', 'procedure', 'type');