Show page

Differences

This shows you the differences between two versions of the page.

--- bd2:laboratoare:05 [2020/11/01 18:56]
apetrescu0506
+++ — (current)
@@ Line 1: / Line 1: @@
-===== Laboratorul 05 - PL/SQL Subprograme =====
-Exercitii date spre rezolvare: {{bd2:laboratoare:lab05_-_luni10-14.txt | Enunt/Rezolvare pentru Luni EG305 10-14}}
-===== Conținut =====
-  * Subprograme
-  * Proceduri PL/SQL
-  * Funcții PL/SQL
-  * Considerente asupra procedurilor și funcțiilor
-  * Informații din dicționarul bazei de date
-===== 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:
-  * 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 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:
-<code sql>
-parameter_name [IN | OUT [NOCOPY] | IN OUT [NOCOPY]] parameter_type  [ {:=  | DEFAULT } {expresion | value}]
-</code>
-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.
-===== 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
-<code sql>
-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;
-</code>
-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
-    * <color red>GREȘIT: nume_param varchar2(30)</color>
-    * <color red>CORECT: nume_param varchar2</color>
-  * **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
-<color red>Ex. 1.</color> Exemplu de procedură declarată într-un bloc.
-{{:bd2:laboratoare:l05ex01.png?nolink&730|}}
-<color red>Ex. 2.</color> 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.
-{{:bd2:laboratoare:l05ex02.png?nolink&730|}}
-==== 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:
-<code sql>
-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];
-</code>
-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)
-<color red>Ex. 3.</color> 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.
-{{:bd2:laboratoare:l05ex03.png?nolink&730|}}
-<note>
-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**, <color red>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</color>
-  * Pentru a șterge o procedură stocată se folosește comanda DDL drop: **DROP PROCEDURE procedure_name**
-</note>
-==== 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.
-<color red>Ex. 4.</color> 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:
-  - manageri de departament:
-    * Vechimea < 13 de ani primesc 20 zile de concediu
-    * Vechimea >= 13 de ani primesc 22 zile de concediu
-  - angajații care nu sunt șefi:
-    * Vechimea < 13 de ani primesc 15 zile de concediu
-    * Vechimea >= 13 de ani primesc 22 zile de concediu
-{{:bd2:laboratoare:l05ex04.png?nolink&730|}}
-<color red>Ex. 5.</color> 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ă.
-{{:bd2:laboratoare:l05ex05.png?nolink&730|}}
-==== 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:
-  * Varray
-  * Nested table
-<color red>Ex. 6.</color> 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.
-{{:bd2:laboratoare:l05ex06a.png?nolink&730|}}
-{{:bd2:laboratoare:l05ex06b.png?nolink&730|}}
-{{:bd2:laboratoare:l05ex06c.png?nolink&730|}}
-{{:bd2:laboratoare:l05ex06d.png?nolink&730|}}
-{{:bd2:laboratoare:l05ex06e.png?nolink&730|}}
-O procedură stocată poate să primească ca parametru un cursor. Acesta trebuie să fie de tipul SYS_REFCURSOR.
-<color red> Ex. 7. </color> Exemplu de cursor ca parametru.
-| {{:bd2:laboratoare:l05ex07a.png?nolink&730|}} | {{:bd2:laboratoare:l05ex07b.png?nolink&730|}} |
-| {{:bd2:laboratoare:l05ex07c.png?nolink&730|}} |
-===== 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:
-<code sql>
-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;
-</code>
-Unde:
-  * **function_name** – numele funcției
-  * **parameter_name** – numele unui parametru formal din lista de parametri
-  * **parameter_type** – reprezintă tipul parametrului formal, <color red>se specifica fără precizie</color>
-  * **return_data_type** – tipul de date întors de funcție, <color red>se specifica fără precizie</color>
-  * **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**
-<color red>Ex. 8.</color>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.
-{{:bd2:laboratoare:l05ex08.png?nolink&730|}}
-==== 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:
-<code sql>
-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];
-</code>
-Unde:
-  * **function_name** – numele funcției
-  * **parameter_name** – numele unui parametru formal din lista de parametri
-  * **parameter_type** – reprezintă tipul parametrului formal, <color red>se specifica fără precizie</color>
-  * **return_data_type** – tipul de date întors de funcție, <color red>se specifica fără precizie</color>
-  * **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)
-<color red> Ex. 9 </color> 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:
-  - vechimea în firmă
-    * Ani vechime ''>''  32 => 30 puncte
-    * Ani vechime ''<''''='' 32 => 15 puncte
-  - salariul maxim pe departament
-    * Salariu angajat ''='' salariu maxim din departament => 20 puncte
-    * Salariu angajat ''<'' salariu maxim din departament => 10 puncte
-  - 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.
-{{:bd2:laboratoare:l05ex09.png?nolink&730|}}
-<note>
-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**, <color red>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</color>
-  * 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.
-</note>
-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:
-<code sql>
-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;
-</code>
-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:
-  * 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:
-  * <color red>O funcție întoarce întotdeauna în mod direct o valoare</color> în programul apelant și poate întoarce mai multe valori prin parametrii de tip **OUT** sau **IN OUT**
-  * <color red>O procedură nu returnează direct o valoare</color>, dar poate întoarce mai multe valori în programul apelant prin parametrii de tip **OUT** sau **IN OUT**
-  * <color red>Funcțiile stocate pot fi apelate direct din comenzi SQL</color>
-  * <color red>Procedurile stocare **NU** pot fi apelate direct din comenzi SQL</color>
-  * 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:
-<code sql>
- GRANT EXECUTE ON {procedure_name | function_name} TO user_name;
-</code>
-Ștergerea privilegiilor se face folosind comanda **REVOKE**, sintaxa este:
-<code sql>
-REVOKE EXECUTE ON {procedure_name | function_name} FROM user_name;
-</code>
-===== 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ă:
-{{:bd2:laboratoare:l05s1.png?nolink&730|}}
-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:
-<code sql>
-SELECT text FROM user_source WHERE lower(name) = 'f_puncte' ORDER BY line;
-</code>
-{{:bd2:laboratoare:l05s2.png?nolink&730|}}
-Pentru a vizualiza toate procedurile, funcțiile și tipurile de date create de utilizatorul curent, se poate folosi următoarea cerere SQL:
-<code sql>
-SELECT object_name, object_type
-FROM user_objects
-WHERE lower(object_type) IN ('function', 'procedure', 'type');
-</code>
-{{:bd2:laboratoare:l05s3.png?nolink&730|}}
-<color red>Ex. 10. </color> 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:
-  * Dacă grade = 1 atunci impozitul este 10% din venit
-  * Dacă grade = 2 atunci impozitul este 15% din venit
-  * Dacă grade = 3 atunci impozitul este 20% din venit
-  * Dacă grade = 4 atunci impozitul este 25% din venit
-  * Dacă grade = 5 atunci impozitul este 30% din venit
-Să se afișeze numele angajatului, dacă este șef de departament sau nu, gradul de salarizare, procentul, venitul și impozitul.
-{{:bd2:laboratoare:l05ex10.png?nolink&730|}}

bd2/laboratoare/05.1604249804.txt.gz · Last modified: 2020/11/01 18:56 by apetrescu0506

Show page Old revisions

Media Manager Back to top