Exercitii date spre rezolvare: Enunt/Rezolvare pentru Luni EG305 10-14
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 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.
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:
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.
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 20 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:
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ă.
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.
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:
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:
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:
>
32 ⇒ 30 puncte<
=
32 ⇒ 15 puncte=
salariu maxim din departament ⇒ 20 puncte<
salariu maxim din departament ⇒ 10 puncte>
0 ⇒ 10 puncte=
0 ⇒ 5 punctePentru 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:
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;
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');
Ex. 10. Să se scrie o funcție vare calculează impozitul pe venit în funcție de gradul de salarizare (gradul se găsește în tabela SALGRADE), folosindu-se următorul algoritm:
Să se afișeze numele angajatului, dacă este șef de departament sau nu, gradul de salarizare, procentul, venitul și impozitul.