Table of Contents

Laboratorul 05 - PL/SQL Subprograme

Conținut

Subprograme

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:

Subprogramele pot fi:

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

Parametrii unui subprogram

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:

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:

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.

Proceduri PL/SQL

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.

Proceduri declarate în cadrul unui bloc

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:

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.

Proceduri stocate

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:

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.

Observații:

  • Procedura se va crea prima și dacă nu are erori de compilare se va afișa mesajul: Procedure created
  • În caz contrar se va afișa mesajul: Warning: Procedure created with compilation errors
  • Pentru a vedea erorile de compilare se va folosi comanda show errors
  • Dacă în dicționarul de date există o procedură cu un nume, atunci nu se mai poate crea alta cu același nume
  • Pentru a suprascrie o procedură existentă se folosește comanda CREATE OR REPLACE, folosiți comanda aceasta cu atenție deoarece puteți să înlocuiți din greșeală o procedură deja existentă, scrisă de un alt dezvoltator, care are același nume dar are altă funcționalitate
  • Pentru a șterge o procedură stocată se folosește comanda DDL drop: DROP PROCEDURE procedure_name

Cursoare în proceduri

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:

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

Tipul Colecție ca parametri

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:

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.

Funcții PL/SQL

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ții declarate în cadrul unui bloc

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:

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ții stocate

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:

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.

Observații:

  • Funcția se va crea prima și dacă nu are erori de compilare se va afișa mesajul: Function created
  • În caz contrar se va afișa mesajul: Warning: Function created with compilation errors
  • Pentru a vedea erorile de compilare se va folosi comanda show errors
  • Dacă în dicționarul de date există o funcție cu un nume, atunci nu se mai poate crea alta cu același nume
  • Pentru a suprascrie o funcție existentă se folosește comanda CREATE OR REPLACE, folosiți comanda aceasta cu atenție deoarece puteți să înlocuiți din greșeală o funcție deja existentă, scrisă de un alt dezvoltator, care are același nume dar are altă funcționalitate
  • Pentru a șterge o funcție stocată se folosește comanda DDL drop: DROP FUNCTION function_name
  • Odată create și stocate în baza de date, funcțiile pot fi apelate în comenzi SQL, dar cu anumite restricții.

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.

Considerente asupra procedurilor și funcțiilor

Î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:

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

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

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;

Informații din dicționarul bazei de date

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:

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