Training - PL SQL Oracle - Basics_final (1).pdf

Vauban IT RO S.R.L. Curs Training PL/SQL

Version 1.0

Prepared By

Lazarescu Florin

December 28, 2015


Development This document is meant to give the junior developers basic knowledge about developing in Oracle PL-SQL. For any other areas, not explained in this guide or for any need for assistance, please email to:

mailto:[email protected]


Cuprins 1.

Partea 1 ......................................................................................6

1.1.

Caracteristici generale ..............................................................6

1.2. Blocuri PL-SQL.........................................................................6 1.2.1. Blocurile anonime ...............................................................7 1.2.2. 1.2.3. 1.2.4. 1.2.5. 1.2.6.

Blocuri anonime imbricate....................................................8 Functii stocate .................................................................... 9 Proceduri stocate ................................................................9 Pachete ........................................................................... 10 Declansatori (triggeri) ....................................................... 11

1.3.

Functii SQL suportate in PL/SQL .............................................. 13

1.4.

Comenzile SQL suportate in PL/SQL ......................................... 13

1.5. Variabile ............................................................................... 14 1.5.1. Declarare si initializare ...................................................... 14 1.5.2. Tipuri de variabile ............................................................. 15 1.5.3. Atributul %TYPE ............................................................... 17 1.6.

Variabile de substitutie ........................................................... 17

1.7.

Exercitii ................................................................................ 22

2.

Partea a 2-a .............................................................................. 24

2.1. Structuri fundamentale de programare ..................................... 24 2.1.1. Structuri alternative .......................................................... 24 2.1.2. Structuri repetitive ............................................................ 29 2.2.

Tipuri de date compuse .......................................................... 36

2.3. Cursorul ............................................................................... 39 2.3.1. Cursorul implicit ............................................................... 40 2.3.2. Cursorul explicit ............................................................... 41 Cursorul cu parametru ................................................................ 47 2.4.

Exercitii ................................................................................ 50


3.

Partea a 3-a .............................................................................. 55

3.1. Tratarea exceptiilor ................................................................ 55 3.1.1. 1. Tratarea excepţiilor predefinite ale Serverului Oracle ......... 56 3.1.2. 2. Tratarea excepţiilor non-predefinite Oracle Server............. 58 3.1.3. 3. Tratarea excepţiilor definite de utilizator .......................... 60 3.2.

Exercitii ................................................................................ 65

4. Partea a 4-a .............................................................................. 69 4.1. Subprograme PL-SQL ............................................................. 69 4.1.1. Proceduri ......................................................................... 70 Exercitii .................................................................................... 71 4.1.2. Functii ............................................................................. 75 Exercitii .................................................................................... 77 5.

Partea a 5-a .............................................................................. 81

5.1.

Variabile de tip record ............................................................ 81

5.2.

Colectii ................................................................................. 83

6.

Biografie ................................................................................... 85


PLSQL – notiuni fundamentale Cerinte prealabile Inainte de inceperea cursului, avem nevoie de o baza de date care sa contine diferite tabele si inregistrari pentru exangajatilele si exercitiile ce vor urma. In scriptul atasat mai jos, puteti gasi o schema schema oferita oraclesilainregistrari instalarea bazei de date pentru studiu. Aceasta continedetabele aferente unui departament de HR.

HR Diagram:


 Construcţiile PL/SQL conţin structuri de control procedurale şi comenzi descriptive SQL;  PL/SQL este înunblocuri limbaj procedural structurat pe bloc, programele putând fi împărţite logice;  Blocurile PL/SQL sunt procesate de motorul PL/SQL care poate fi rezident pe ORACLE SERVER sau pe un instrument de dezvoltare (ex.: Oracle Forms, Reports, JDeveloper etc.) Multe instrumente ORACLE au propriul motor PL/SQL (ex.: Oracle Forms, Reports, JDeveloper etc.);  Tipurile de date din SQL pot fi folosite în PL/SQL;  Programarea în PL/SQL este modularizată – se utilizează blocurile care grupează instrucţiunile.

Orice unitate PL/SQL conţine unul sau mai multe blocuri, complet separate sau imbricate. Componentele unui bloc PL/SQL:

Observaţii: 

comenzile SQL asigură accesul la baza de date;



operaţiile efectuate cu variabilele PL/SQL în cadrul instrucţiunilor procedurale nu presupun accesarea bazei de date;



se foloseşte (;) după fiecare instrucţiune SQL sau instrucţiune de control PL/SQL;



blocul PL/SQL se termină cu (;) ;  se foloseşte (/) pentru a lansa un bloc anonim în bufferul SQL;




o eroare în PL/SQL este tratată ca o excepţie.

Tipuri de blocuri PL/SQL:

1. Blocuri anonime 2. şi funcţii de aplicaţii 3. şi proceduri de aplicaţii 4. 5. pe baza de date / de aplicaţii

   

sunt nedenumite nu sunt stocate în baza de date se declară inline, în locul în care se doreşte execuţia lor se execută în momentul rulării

Exemplu:


 

se pot imbrica mai multe blocuri; acestea se pot eticheta cu <> , iar variabilele din cadrul blocurilor se pot utiliza astfel: eticheta_bloc.variabila.


CREATE [OR REPLACE] {IS|AS} corpul_functiei;

nume_functie [(…)]

In interiorul corpului functiei declaratia este utilizatã pentru a intoarce controlul programului apelant impreuna cu o valoare. • o functie este foarte asemanatoare unei proceduri. • ambele pot fi stocate in baza de date sau declarate in interiorul unui bloc (o functie locala) • functia trebuie sa (sau poate intoarce mai multe valori prin intermediul parametrilor precum procedurile) -- ideal este sa intoarca doar o sigura valoare - pentru celelalte situatii - mai multe valori respectiv nici o valoare, se vor folosi procedurile. Altfel spus, rolul unei functii este acela de a calcula/verifica si de a returna un rezultat. In cadrul unei functii nu se pot da comenzi DDL/DML (create,alter, update, insert etc).

[CREATE [OR REPLACE]] nume_procedura [(argument [{IN | OUT | IN OUT}] tip_argument, …)] {IS | AS} Corpul_procedurii; Procedura stocata este o procedura stocata in baza de date (ea devine similara unei functii din biblioteca). • Corpul_procedurii este un bloc PL/SQL ( sectiunea BEGIN…END ) ; • Crearea unei proceduri este o operatie DDL -- se efectueaza un COMMIT implicit; • se poate folosi f ie IS fie AS; • Parametri de mod: IN (read-only), OUT (write-only), IN OUT (read-write), implicit este modul IN; • prin intermediul para,metrilor procedurile pot returna valori; • Nu se utilizeaza cuvantul cheie DECLARE; •Nu se impun constrangeri referitoare la lungime, pozitii zecimale,etc


asupra parametrilor (ex. VARCHAR2(20), NUMBER(2) ); • Parametrii pot avea valori sau => daca nu sunt specificati parametri, in antetul procedurii nu sunt necesare parantezele;

• Subprogramele locale sunt definite in sectiunea declarativa. • incuvantul acest caz suntPROCEDURE. omise cuvintele cheie CREATE [OR REPLACE], si se incepe cu cheie nume_procedura(parametrii); --din interiorul unui bloc PL/SQL ca o declaratie executabila discount(clientid, 0.15); -- 15% discount =>

Daca procedura nu are parametri aceasta va fi apelata fara parametri afisaza_total

Pachetele PL/SQL permit gruparea tipurilor PL/SQL, variabilelor , structurilor de pachetlui date, exceptiilor subprogramelor Componentele Pachetulsipoate fi creat in 2 intr-un parti: singur container.  Specificarea pachetului este interfata pentru aplicatie. Aici se declara tipurile publice, variabile, constante, exceptii, cursoare si subprograme.  Corpul pachetului defineste propriul subprogram. Corpul pachetului poate defini de asemenea constructii PL/SQL, cum ar fi variabile, constante, exceptii si cursoare.

Componenetele private sunt plasate in corpul pachetului si pot fi referite numai de constructii din acelasi pachet.

Componentele publice sunt declarate in sectiunea de specificare a pachetului. Componentele publice pot fi referite din oricare alt pachet Oracle extern.


Pentru a crea pachete, se declara constructii publice in sectiunea de definitie a pachetului

Exemplu:

Corpul pachetului trebuie sa completeze implementarea pentru toate procedurile si functiile declarate in definita pachetului.

Un declansator defineste o actiune care trebuie executata in baza de date la aparitia unui anumit eveniment ( insert, update, delete).  Declansatoarele sunt similare procedurilor - sunt tot blocuri PL/SQL denumite.  Deoarece declansatoarele apar datorita unor evnimente, se pot configura evenimente.astfel incat sa apara imediat inainte sau dupa aceste


consta in aceea ca o procedura este executata explicit dintr-un alt bloc printr-un apel care ii furnizeaza parametri in timp ce un declansator este executat implicit oridecate ori apare evenimentul pentru care a fost definit (INSERT, UPDATE, DELETE) si nu accepta argumante.



pentrua mentine constrangeri de integritate complexe sau reguli de

afacere;auditarea informatiilor intr-un tabel prin inregistrarea pentru modificarilor ;  pentru activarea automata a altor programe care trebuie sa ruleze la efectuarea anumitor modificari in tabel;  pentru colectarea si intretinerea datelor statistice. 

CREATE [OR REPLACE] TRIGGER nume_declansator {BEFORE | AFTER} {INSERT|UPDATE|DELETE} [OF coloana] ON tabel_referit [FOR EACH ROW [WHEN conditii]] [DECLARE]

-- optional, pentru declararea variabilelor locale

corpul_declansatorului

In Oracle exista doua tipuri de declansatoare: - la nivel de inregistrare (row-level triggers) - la nivel de declaratie (statement-level triggers) • se executa cate o data pentru fiecare rand dintr-o tranzactie. • sunt cele mai comune tipuri de declansatoare si sunt adesea folosite in aplicatii de audit pentru date. • sunt identificate prin clauza din comanda CREATE TRIGGER • se executa o singura data pentru fiecare tranzactie


De Exemplu, daca printr-o singura tranzactie se insereaza 500 de randuri intr-un tabel atunci declansatorul pentru tabelul respectiv se va executa o singura data. • se utilizeaza pentru a forta masuri suplimentare de securitate asupra anumitor tipuri de tranzactii care se pot executa asupra unui tabel. • sunt declansatoare create implicit - se omite clauza din comanda CREATE TRIGGER.

În cadrul instrucţiunilor descriptive sunt suportate toate tipurile de funcţii SQL (inclusiv funcţiile de grup în cadrul instrucţiunii SELECT); Detalii despre functii si toate tipurile de functii se gasesc pe site-ul: https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.ht m 



Instrucţiunile PL/SQL:  Suportă funcţii la nivel de înregistrare (single-row): numerice, caracter, data, de conversie etc.;  suportă funcţii de grup (SUM, MIN, MAX, AVG, COUNT, STDDEV) funcţia IF DECODE. De Exemplu se IFpotAVG(...) utiliza construcţii sau de forma: DECODE(...) THEN ...nusau THEN ...





PL/SQL permite folosirea comenzilor de manipulare a datelor (LMD):  SELECT  INSERT  UPDATE  DELETE PL/SQL permite folosirea comenzilor de control al tranzacţiilor:   

COMMIT ROLLBACK SAVEPOINT


Un bloc PL/SQL nu este o tranzacţie. Comenzile Commit/ Rollback/ Savepoint sunt independente de bloc, dar pot să apară în cadrul acestuia. 

PL/SQL  

suportă comenzile de definire a datelor (LDD) CREATE ALTER



DROP RENAME  TRUNCATE PL/SQL suportă comenzile din cadrul limbajului pentru controlul datelor (Data Control Language - DCL)  GRANT  REVOKE 



Comenzile LDD (Create, alter, drop etc) si DCL se pot folosi intr-un bloc pl/sql doar prin intermediul comenzii . Exemplu: Begin Execute immediate ‘drop table numetable’; End; /





declararea variabilelor se realizează în zona declarativă (delimitată prin DECLARE) a blocului (sau sub-blocului); iniţializarea se poate face la declarare sau în zona de execuţie (între BEGIN şi END);

Vauban IT RO S.R.L. Curs Training PL/SQL 











variabilele vor fi vizibile în restul blocului, respectiv şi în blocurile incluse în el, mai puţin în sub-blocurile în care numele lor este redefinit (ca în majoritatea limbajelor de programare structurate, semnificaţia unui nume definit de utilizator într-un bloc/sub-bloc este dată de cea mai apropiată declaraţie anterioară locului folosirii); toate variabilele PL/SQL au un tip de dată, restricţii şi un şir valid de valori; declararea şi iniţializarea se realizează astfel:

constantele trebuie obligatoriu iniţializate, iar ulterior nu îşi vor putea schimba valoarea; variabilele NOT NULL trebuie obligatoriu iniţializate, iar ulterior nu vor putea primi valoarea NULL; se foloseşte următoarea convenţie de notare: (variabilă globală definită în zona de specificaţii a pachetului de programe şi valabilă pentru toate subprogramele pachetului).

Scalare Compozite  Referinţă  LOB (Large Objects): NCLOB, CLOB, BLOB, BFILE  Obiect Variabile non-PL/SQL: variabile de mediu (BIND VARIABLES )  

a) Variabile Scalare:

Tipurile scalare conţin valori simple (o variabila scalară poate conţine la un moment dat o singură valoare simplă) şi corespund în principal tipurilor pe care le pot avea coloanele tabelelor. 

char (lung_max) - lungime fixă de max 32.767 bytes

Vauban IT RO S.R.L. Curs Training PL/SQL        

varchar2 (lung_max) – lungime variabilă de max 32.767 bytes long [şir de caractere de lungime variabilă 2GB] number (precizie,scală) boolean (true, false, null) date binary_integer şi pls_integer (numere întregi între -2147483647 şi 2147483647) binary_float şi binary_double (pentru numere reale în varianta Oracle 10g) timestamp (pentru fracţiuni de secundă)

Exangajatile: v_functie varchar2(9); v_numar binary_integer:=0; v_totalsal number(9,2):=0; v_datainceput date:=sysdate+7; c_taxa constant number(3,2):=8.25; v_valid boolean not null:=true;

se realizează prin intermediul funcţiei PUT_LINE din pachetului . Se poate utiliza operatorul de concatenare ( || ) pentru a afişa mai multe mesaje sau variabile pe aceeaşi linie. Afişarea variabilelor PL/SQL

‘VALOAREA VARIABILEI ESTE:’ || Popularea variabilelor cu valori din tabelele bazei de date 



Se utilizează comanda SELECT cu clauza INTO pentru popularea variabilelor PL/SQL cu valori ale atributelor din tabele; cererile SELECT din cadrul blocurilor PL/SQL trebuie să furnizeze o singură linie rezultat (în caz contrar se semnalează eroare).

Exemplu: --se afiseaza numele angajatului cu codul 100 SET SERVEROUTPUT ON DECLARE v_nume VARCHAR2(20); BEGIN SELECT nume INTO v_nume FROM angajati WHERE id_angajat = 100; DBMS_OUTPUT.PUT_LINE(‘NUMELE ANGAJATULUI ESTE:’ || v_nume);

Vauban IT RO S.R.L. Curs Training PL/SQL END; /

Atribuie unei variabile tipul altei variabile sau tipul de date specific unei coloane din tabelă. Declararea unei variabile cu %TYPE: variabila sau variabila1 variabila2

tabelă.nume_coloană%TYPE; tip_dată; variabila1%TYPE;

Exemplu: --afiseaza numele si prenumele angajatului cu codul 100. DECLARE v_nume angajati.nume%TYPE; v_prenume angajati.prenume%TYPE; BEGIN SELECT nume, prenume INTO v_nume, v_prenume FROM angajati WHERE id_angajat = 100; DBMS_OUTPUT.PUT_LINE(‘NUMELE ANGAJATULUI ESTE:’ || v_nume||’ ‘||v_prenume); END; /

Restricţia NOT NULL a unei coloane nu se aplică şi variabilei declarate prin folosirea atributului %TYPE.



de regulă, variabilele de substituţie sunt folosite pentru a transmite valori dinspre mediul SQL*Plus spre comenzile SQL sau blocurile PL/SQL, în timp ce variabilele de legătură ( bind variables) sunt folosite pentru a transmite valori însuccesiv sens invers sau bloc pentrusetează a transfera valori următorul între blocurio PL/SQL lansate (primul variabila, consultă);

Vauban IT RO S.R.L. Curs Training PL/SQL 



 

prin variabile de substituţie se pot transmite valori comenzilor SQL sau blocurilor PL/SQL lansate (folosind "&" sau "&&"); se pot invoca din comenzile SQL sau din blocurile PL/SQL prin "&nume_variabila" sau "&&nume_variabila"; sunt locale sesiunii SQL în care au fost declarate; în mediul SQL variabilele de substituţie pot fi uşor citite prin introducerea de valori de la tastatură (utilizând ACCEPT), se pot defini (cu DEFINE) sau afişa pe ecran (cu PROMPT);

Exangajatile: --se afiseaza numarul de comenzi ale angajatului al carui cod este introdus de utilizator prin intermediul variabilei de substitutie &id_angajat DECLARE v_nr_comenzi number(2); BEGIN select count(nr_comanda) into v_nr_comenzi from comenzi where id_angajat=&id_angajat; dbms_output.put_line(‘Angajatul are: ‘|| v_nr_comenzi||’ comenzi’); END; /

Observaţie:

Într-un bloc PL/SQL se pot utiliza toate tipurile de variabile, respectând însă caracteristicile şi regulile de utilizare ale acestora. În Exemplul următor se utilizază atât variabila de substituţie s_nume definită şi iniţializată prin comanda DEFINE, cât şi variabila de legătură g_salariul, dar şi variabila locală v_prenume de acelaşi tip cu coloana nume din tabela Angajati. Variabila de substituţie definită cu DEFINE va fi implicit de tipul CHAR: -- se afiseaza salariul si prenumele angajatului cu numele Abel SET SERVEROUTPUT ON VARIABLE g_salariul number DEFINE s_nume=Abel DECLARE v_prenume angajati.nume%type; BEGIN select prenume,salariul into v_prenume, :g_salariul from angajati where nume=‘&s_nume’; DBMS_OUTPUT.PUT_LINE (‘Prenumele angajatului este: ‘||v_prenume); END; / print g_salariul


EXERCIŢII Specificaţi ce se va afişa la rularea următorului bloc PL/SQL: DECLARE v_var1 NUMBER :=100; v_var2 NUMBER; v_var3 NUMBER := v_var2; v_var4 VARCHAR(20) := ‘variabila PL/SQL’; v_var5 NUMBER NOT NULL := v_var1; c_const1 CONSTANT DATE := TO_DATE(‘12/02/2007’,’dd/mm/yyyy’); c_const2 CONSTANT NUMBER NOT NULL := 2; c_const3 CONSTANT NUMBER := NULL; v_var6 NUMBER DEFAULT NULL; BEGIN DBMS_OUTPUT.PUT_LINE(‘variabila 1 = ‘||v_var1); DBMS_OUTPUT.PUT_LINE(‘variabila 2 = ‘||v_var2); DBMS_OUTPUT.PUT_LINE(‘variabila 3 = ‘||v_var3); DBMS_OUTPUT.PUT_LINE(‘variabila 4 = ‘||v_var4); DBMS_OUTPUT.PUT_LINE(‘variabila 5 = ‘||v_var5); DBMS_OUTPUT.PUT_LINE(‘constanta 1 = ‘||c_const1); DBMS_OUTPUT.PUT_LINE(‘constanta 2 = ‘||c_const2); DBMS_OUTPUT.PUT_LINE(‘constanta 3 = ‘||c_const3); DBMS_OUTPUT.PUT_LINE(‘variabila 6 = ‘||v_var6); END; /

Specificaţi ce se va afişa la rularea următorului bloc PL/SQL (care conţine blocuri imbricate, ilustrând domeniul de vizibilitate al unor variabile care au acelaşi nume) : DECLARE var NUMBER; BEGIN var := 1; DBMS_OUTPUT.PUT_LINE(var); <> DECLARE var NUMBER; BEGIN var :=2; DBMS_OUTPUT.PUT_LINE(var);

Vauban IT RO S.R.L. Curs Training PL/SQL END bloc1; DBMS_OUTPUT.PUT_LINE(var); <> DECLARE var NUMBER; BEGIN var :=3; DBMS_OUTPUT.PUT_LINE(var); <> DECLARE var NUMBER; BEGIN var :=4; DBMS_OUTPUT.PUT_LINE(var); DBMS_OUTPUT.PUT_LINE(bloc2.var); END bloc3; DBMS_OUTPUT.PUT_LINE(var); END bloc2; DBMS_OUTPUT.PUT_LINE(var); END; / Specificaţi ce se va afişa la rularea următorului bloc PL/SQL DECLARE stoc NUMBER(3):=600; mesaj VARCHAR2(50):=‘Produsul 101’; BEGIN DECLARE stoc NUMBER(3):=10; mesaj VARCHAR2(50):=‘Produsul 102’; um VARCHAR2(10):= ‘ bucati ‘; BEGIN stoc:= stoc+1; mesaj:=‘Stocul pentru ‘||mesaj||’ este de: ‘||stoc||um; DBMS_OUTPUT.PUT_LINE(mesaj); END; stoc:= stoc+100; mesaj:=‘Stocul pentru ‘||mesaj||’ este de: ‘||stoc||um; DBMS_OUTPUT.PUT_LINE(mesaj); END;

Vauban IT RO S.R.L. Curs Training PL/SQL / Să se calculeze suma a două numere, iar rezultatul să se dividă cu 3. Numerele se vor introduce de la tastatură. VARIABLE g_rezultat number ACCEPT p_num1 PROMPT ‘Introduceţi primul număr:’ ACCEPT p_num2 PROMPT ‘Introduceţi al doilea număr:’ DECLARE v_num1 number(9,2):=&p_num1; v_num2 number(9,2):=&p_num2; BEGIN :g_rezultat:=(v_num1+v_num2)/3; END; / PRINT g_rezultat Să se afişeze salariul mărit cu un procent. Salariul şi procentul se dau de la tastatură ACCEPT p_sal PROMPT ‘Introduceţi salariul:’ ACCEPT p_procent PROMPT ‘Introduceţi procentul:’ DECLARE v_sal number:=&p_sal; v_procent number:=&p_procent; BEGIN dbms_output.put_line(to_char(nvl(v_sal,0)*(1+nvl(v_procent,0)/100))); END; / Să se afişeze cota de TVA pentru o anumită valoare introdusă de la tastatură.


1. Creaţi un bloc PL/SQL prin care să afişaţi numele şi telefonul aferente fiecărui client cu id-ul număr par. 2. Creaţi un bloc PL/SQL care să afişeze codul şi denumirea pentru departamentul din localitatea al cărei cod (id_produs) este precizat (fie citit de la tastatură, fie dat drept valoare iniţială unei variabile locale). 3. Creaţi un bloc PL/SQL prin care să se afiseze numarul de salariati care au fost angajati inainte de o data (an) precizata de utilizator printr-o variabila de substitutie (spre Exemplu inanite de 1997). 4. Creaţi un bloc PL/SQL să se afişeze numele, salariul si venitul anual (salariul*12+comision*salariul*12) angajatii cu id-ul cuprins intre 150 si 170. Modificati blocul de mai sus si afisati doar angajatii care au salariul mai mare decat media (se foloseste o variabila in care se va incarca salariul mediu). Creati tabela PRODUSE2 folosind comanda “ Realizati urmatoarele blocuri PLSQL: 1. Creaţi un bloc PL/SQL ce adaugă un produs nou in tabela PRODUSE2. Folosiţi maximul dintre codurile produselor si adăugaţi 10 la aceasta valoare, folosind-o ca valoare pentru codul produsului nou introdus. Folosiţi variabile de substituţie pentru denumirea produsului si pretul de lista. Lăsaţi descrierea produsului NULL. Finalizati tranzactia de mai sus (COMMIT). 2. Creaţi un bloc PL/SQL ce selectează pretul de lista maxim pentru produsele existente in tabela PRODUSE2. Tipăriţi rezultatul pe ecran. 3. Creaţi un bloc PL/SQL care şterge din tabela PRODUSE2 produsele care au pretul de lista mai mare decat o valoare primita ca parametru (variabila de


substituţie). Afisati numarul de produse sterse cu ajutorul cursorului implicit (SQL%ROWCOUNT). Anulati stergerea (ROLLBACK). 4. Creaţi un bloc PL/SQL prin care să se dubleze preţul ( pret_lista) pentru produsele din categoriile hardware2 şi hardware3. În cazul în care actualizarea se realizează cu success (folositi cursorul implicit), afişaţi numărul de înregistrări modificate si un mesaj sugestiv. 5. Creaţi un bloc PL/SQL prin care se dublează preţul produsului ( pret_lista) al cărui cod este 3169. În cazul în care acesta nu există (comanda UPDATE nu realizează nicio modificare, vezi SQL%NOTFOUND) se va afisa un mesaj sugestiv.


Structura IF..THEN..END IF

Se poate folosi clauza ELSIF în loc de IF imbricate

Exemplu: În funcţie de valoarea stocului produsului cu codul 3133, acesta se va afişa modificat pe ecran prin intermediul variabilei v_stoc.


Atentie la variabilele de tip NULL si evaluarea in IF! De Exemplu, în următoarea situaţie se va afişa “Felicitări, sunteţi admis!” din cauza faptului că variabila nota este declarată, dar nu este iniţializată, fiind deci NULL:

Observaţi cazurile de mai jos: X:=10; Y:=NULL; IF x!=y then


--intoarce NULL si nu TRUE END IF; sau a:=NULL; b:=NULL; IF a=b then --intoarce NULL si nu TRUE END IF;

Structura CASE ... WHEN... THEN... Sunt 2 variante: 1. expresii CASE (CASE Expressions) care intorc un rezultat intr-o variabila. Se termina cu END 2. sintaxa CASE (CASE Statement) care executa o anumita instructiune. Se termina cu END CASE, iar fiecare rand se termina cu ;


Exemplu


Exemplu:


Structura LOOP……END LOOP

Exemplu: Se afişează pe ecran utilizând structura loop…end loop numerele 9,7, 4, 0.

Sa se afişeaze în ordine angajaţii cu codurile în intervalul 100-110 atât timp cât salariul acestora este mai mic decât media.


Structura WHILE…..LOOP….END LOOP

Exemplu: Se afişează pe ecran utilizând structura while loop…end loop numerele 9,7, 4, 0.


Se afişează în ordine angajaţii cu codurile în intervalul 100-110 atât timp cât salariul acestora este mai mic decât media.


Structura FOR…..LOOP….END LOOP

Valorile intervalului pot fi de orice tip, dar sa aiba valori care pot fi convertite la un intreg (de Exemplu 20/13 sau 11/5). Daca aceste 2 valori vor fi egale ca intregi atunci instructiunile din interiorul ciclului se executa o singura data.

FOR i IN 3..3 LOOP Secventa comenzi; END LOOP; Exangajatile: Se afişează pe ecran utilizând structura for…end loop numerele 9,7, 4, 0.


Se afişează în ordine angajaţii cu codurile în intervalul 100-110 atât timp cât salariul acestora este mai mic decât media:

Să se afişeze numărul de comenzi ale fiecărui angajat al cărui id este situat în intervalul 155..160, dar să se întrerupă afişarea în cazul în care se găseşte primul angajat din acest interval care nu are nici o comandă:


Să se încarce în tabela MESAJE numere de la 1…10 cu excepţia lui 6 şi 8!! Structuri LOOP imbricate (se vor eticheta loop-urile)



Inregistrarile din PL/SQL sunt similare inregistrarilor din limbajul Pascal sau structurilor din C. Sub un acelasi nume sunt inmagazinate mai multe valori de tipuri diferite care se numesc campuri. Definirea unei inregistrari se poate face in doua moduri: Folosind o descriere a fiecarei componente a inregistrarii. In primul caz o variabila de acest tip poate sa nu fie asociata unei tabele sau rezultatului unei cereri SQL. Folosind %ROWTYPE se poate defini o inregistrare compatibila cu o linie a unei tabele sau cu o linie a rezultatului unei cereri SQL.

v_ang angajati%rowtype;

select nume, prenume into v_ang from angajati where id_angajat = 101; dbms_output.put_line(v_ang.nume || ‘ ‘ || v_ang.prenume);


A. Inregistrari definite prin descrierea componentelor In acest caz sintaxa definirii unui tip este urmatoarea:

TYPE denumire_tip IS RECORD (nume_camp tip_camp [NOT NULL] [:= | DEFAULT expresie] [, nume_camp ... Tipul unui camp poate fi oricare dintre tipurile scalare sau compuse din PL/SQL in afara de REF CURSOR. Referirea unui camp al unei variabile de tip inregistrare se face cu constructia: nume_inregistrare.nume_camp Exemplu 1:

DECLARE v_salariu NUMBER(4); TYPE t_angajat IS RECORD (cod NUMBER(4) NOT NULL := 1234, nume angajati.ename%type, sal v_salariu%type, data DATE DEFAULT SYSDATE); v_angajat t_angajat; BEGIN v_angajat.cod := v_angajat.cod + 1; v_angajat.nume := ‘HERMAN’; v_angajat.sal := 1400; . . . END;

: Utilizând un tip de dată record definit de utilizator să se afişeze preţul minim al produsului cu codul 3133. DECLARE TYPE tip_produse IS RECORD (v_cod produse.id_produs%type NOT NULL:=3000, v_den produse.denumire_produs%type,

Vauban IT RO S.R.L. Curs Training PL/SQL v_pret_min produse.pret_min%type); vrec_prod tip_produse; BEGIN SELECT id_produs, denumire_produs, pret_min into vrec_prod from produse where id_produs=3133; dbms_output.put_line(‘Produsul: ‘|| vrec_prod.v_den|| ‘ are pretul minim: ‘||vrec_prod.v_pret_min); END; /

Mai sus a fost definit un tip inregistrare numit si o variabila de acest tip ( ). Se observa ca se poate defini tipul unui camp si un functie de tipul unei variabile sau al unei coloane a unei tabele. In Exemplul de mai sus toate campurile inregistrarii sunt scalare dar in cazul general ele pot fi si compuse, de Exemplu inregistrare (definite cu %ROWTYPE, descris in continuare). B. Inregistrari definite cu %ROWTYPE

In acest caz sintaxa definitiei unei variabile inregistrare este: nume_variabila

nume_tabela%ROWTYPE;

Inregistrarea va avea atatea campuri cate coloane are tabela si acestea se vor numi la fel cu aceste coloane. Aceste inregistrari se pot utiliza pe clauza INTO a unei cereri SELECT * care intoarce o singura linie.

DECLARE v_angajat angajati%ROWTYPE; BEGIN SELECT * INTO v_angajat FROM angajati WHERE angajatino=1234; v_angajat.ename := ‘HERMAN’; v_angajat.sal := 1400; . . . END;


Utilizând un tip de dată record de acelaşi tip cu tabela produse să se afişeze preţul minim al produsului cu codul 3133. DECLARE vrec_prod produse%rowtype; BEGIN SELECT * into vrec_prod from produse where id_produs=3133; dbms_output.put_line(‘Produsul: ‘|| vrec_prod.denumire_produs|| ‘ are pretul minim: ‘||vrec_prod.pret_min); END; /

Utilizând un tip de dată record de acelaşi tip cu departamente să se afişeze denumirea fiecărui departament cu id-ul: 10, 20, 30, 40, 50. DECLARE vrec_dep departamente%rowtype; i number:=10; BEGIN loop SELECT * into vrec_dep from departamente where id_departament=i; dbms_output.put_line(‘Departamentul: ‘|| vrec_dep.id_departament|| ‘ are denumirea de: ‘||vrec_dep.denumire_departament); exit when i>=50; i:=i+10; end loop; END; /

Atunci când se execută o comandă SQL, Oracle Server deschide o zonă de memorie (context area) în care comanda este executată. Cursorul este un pointer către această zonă. În PL/SQL se utilizează două tipuri de cursoare: 1. – declarat pentru toate instrucţiunile PL/SQL de tip LMD (INSERT/UPDATE/DELETE). Este gestionat de catre SGBD, utilizatorul doar il apeleaza; 2.

declarat şi gestionat de programator.


Este declarat de PL/SQL implicit pentru toate comenzile de manipulare a datelor (INSERT, UPDATE, DELETE). Dacă o instrucţiune LMD nu afectează nici o linie a tabelei nu se generează eroare, însă excepţia trebuie tratată folosind atributele speciale ale cursorilor. Atributele cursorului implicit:   

SQL%ROWCOUNT; SQL%FOUND; SQL%NOTFOUND.

Se şterge un produs din tabela PRODUSE şi se contorizează numărul de rânduri şterse.


Se încearcă modificarea denumirii produsului cu codul 3, în cazul în care acest produs nu există (comanda update nu realizează nici o modificare) va fi afişat un mesaj corespunzător.

Se şterge din tabela ANGAJATI_SAL salariatul al cărui ID este introdus de utilizator prin intermediul variabilei globale g_angid.

  

se foloseşte pentru a procesa individual fiecare linie (înregistrare) returnată de o instrucţiune SELECT ce returnează mai multe înregistrări; mulţimea înregistrărilor returnate de o instructiune SELECT este numită mulţime rezultat; cursorul păstrează un pointer către linia curentă în cadrul unei mulţimi rezultat;


Verificarea stării unui cursor explicit se realizează prin intermediul următoarelor atribute: 1. nume_cursor%ISOPEN - evaluat la TRUE în cazul în care cursorul este deschis; 2. nume_cursor%NOTFOUND - evaluat la TRUE în cazul în care cel mai recent FETCH nu a returnat nici o linie; 3. nume_cursor%FOUND - complementul lui %NOTFOUND; 4. nume_cursor%ROWCOUNT - are ca valoare numărul liniilor returnate până în momentul curent. Prelucrarea cursorului explicit presupune parcurgerea următoarelor etape: 1) se declară variabilele în corespunzătoare unei linii din cursor;

care

vor

fi

încărcate

valorile

2) se declară cursorul explicit, specificându-se un nume pentru acesta şi definindu-se interogarea de procesat în cadrul lui: DECLARE nume_cursor IS SELECT........................; 3) se deschide cursorul prin intermediul instrucţiunii OPEN, care execută interogarea şi legarea tuturor variabilelor referite. Înregistrările returnate de interogare sunt desemnate drept set activ de date, care pot fi de acum încărcate. OPEN nume_cursor; 4) utilizându-se instrucţiunea FETCH, se încarcă linia curentă din cursor în variabile. Fiecare încărcare determină mutarea pointerului cursorului la linia următoare din setul activ de date. FETCH nume_cursor INTO var1, var2,..............; 5) este închis cursorul prin instructiunea CLOSE, care dezafectează setul activ de linii. Cursorul poate fi din nou deschis pentru a stabili un nou set activ de linii. CLOSE nume_cursor; Pentru a procesa liniile unui cursor explicit se defineşte de obicei o buclă pentru executarea unui FETCH în fiecare iteraţie. În final, toate liniile


din setul activ sunt procesate şi un FETCH executat fără succes poziţionează atributul %NOTFOUND pe TRUE. Înaintea primului FETCH, %NOTFOUND se evaluează la NULL, ca şi în cazul în care FETCH nu se execută niciodată cu succes. Exangajatile: Să se afişeze lista cu numele şi salariul angajaţilor din departamentul 60 folosind un cursor explicit:

Pentru o flexibilitate mai mare se poate utiliza o variabilă de tip record pentru încărcarea valorilor din cursor. Această variabilă de tip record poate avea aceleaşi atribute ca şi cursorul prin specificarea proprietăţii %ROWTYPE. În acest caz încărcarea din cursor se va face direct prin instrucţiunea fech var_cursor into var_record. Exemplul de mai sus poate fi rescris astfel:


Să se încarce în tabela MESAJE primii 5 angajaţi (id şi nume)


Testul de ieşire din buclă în acest caz se poate face şi cu ajutorul atributului %ROWCOUNT: Delete from mesaje.

Să se afişeze primele 3 comenzi care au cele mai multe produse comandate.

Gestiunea implicită a cursorului prin utilizarea unui ciclu FOR: FOR nume_record IN nume_cursor LOOP .... END LOOP; În acest caz, tipul RECORD nu trebuie declarat. Se realizează în mod implicit deschiderea, încărcarea şi închiderea cursorului.

Sa se afişeaze printr-un ciclu FOR numele şi salariile angajaţilor din departamentul 60.


Utilizarea unui cursor direct în cadrul instrucţiunii FOR. În acest caz cursorul nu este declarat, nu are nume, este reprezentat doar de interogarea SELECT din cadrul instrucţiunii FOR, astfel: FOR NUME_RECORD IN (SELECT......) LOOP .... END LOOP; Dezavantajul în acest caz este ca nu se pot utiliza atributele cursorului din cauza faptului că acesta nu are nume.

Să se afişeze suma aferentă salariilor din fiecare department.


Cursorul cu parametru

Pentru o flexibilitate mai mare se pot declara şi utiliza cursori cu parametru care transmit valorile parametrilor actuali în cererile SQL. Declararea cursorului cu parametru se face astfel: Cursor nume_cursor (parametru1 tip_data,.....) Is select ....; Deschidere: Open nume_cursor(valoare_parametru1,......); Cursoarele parametrizate nu oferă o funcţionalitate suplimentară ci doar o modalitate simplă şi clară de a specifica valori de intrare. Tipurile parametrilor sunt scalare, dar nu li se precizează dimensiunea; ele fiind referite în interogare.

Să se afişeze produsele al căror cantitate totală comandată este mai mare decât o valoare primită drept parametru.



Să se afişeze pentru fiecare comanda produsele comandate. În acest caz se utilizează două variabile de tip cursor.


1. Rescrieti urmatoarea structura IF astfel incat sa nu mai folositi aceasta structura pentru a seta valoarea variabilei no_revenue. Care este diferenta intre codul de mai jos si noul cod scris de voi ? IF total_sales <= 0 THEN no_revenue := TRUE; ELSE no_revenue := FALSE; END IF; IF total_sales <= 0 THEN no_revenue := TRUE; ELSIF total_sales > 0 THEN no_revenue := FALSE; END IF;

2. Rescrieti urmatoarea structura IF, astlfel incat sa scapati de structurile IF imbricate IF salary < 10000 THEN bonus := 2000; ELSE IF salary < 20000 THEN bonus := 1500; ELSE IF salary < 40000 THEN bonus := 1000; ELSE bonus := 500; END IF; END IF; END IF;

3. Care din procedurile apelate mai jos, nu o sa fie niciodata apelata? IF (order_date > SYSDATE) AND order_total >= min_order_total THEN fill_order (order_id, ‘HIGH PRIORITY’); ELSIF (order_date < SYSDATE) OR (order_date = SYSDATE) THEN

Vauban IT RO S.R.L. Curs Training PL/SQL fill_order (order_id, ‘LOW PRIORITY’); ELSIF order_date <= SYSDATE AND order_total < min_order_total THEN queue_order_for_addtl_parts (order_id); ELSIF order_total = 0 THEN disp_message (‘ No items have been placed in this order!’); END IF;

4. De cate ori o sa se execute urmatorul loop? TIP: Reverse informeaza structura ca trebuia sa mearga in ordine inversa cu numaratoarea. Echivalentul lui “For year_index IN 1 .. 12” FOR year_index IN REVERSE 12 .. 1 LOOP Dbms_output.put_line(year_index); END LOOP:

5. Identificati problemele sau eficientizati codul urmatoarelor loop-uri. Cum o sa modificati loop-ul pentru imbunatatirea codului? 1. FOR i IN 1 .. 100 LOOP calc_totals (i); IF i > 75 THEN EXIT; END IF; END LOOP;

2. OPEN angajati_cur; FETCH angajati_cur INTO angajati_rec; WHILE angajati_cur%FOUND LOOP calc_totals (angajati_rec.salary); FETCH angajati_cur INTO angajati_rec; EXIT WHEN angajati_rec.salary > 100000; END LOOP; CLOSE angajati_cur;

3.

Vauban IT RO S.R.L. Curs Training PL/SQL FOR a_counter IN lo_val .. hi_val LOOP IF a_counter > lo_val * 2 THEN hi_val := lo_val; END IF; END LOOP;

4. DECLARE CURSOR angajati_cur IS SELECT salary FROM angajati; angajati_rec angajati_cur%ROWTYPE BEGIN OPEN angajati_cur; LOOP FETCH angajati_cur INTO angajati_rec; EXIT WHEN angajati_cur%NOTFOUND; calc_totals (angajati_rec.salary); END LOOP; CLOSE angajati_cur; END;

5. WHILE no_more_data LOOP read_next_line no_more_data := (text); text IS NULL; EXIT WHEN no_more_data; END LOOP;

6. FOR month_index IN 1 .. 12 LOOP UPDATE monthly_sales SET pct_of_sales = 100 WHERE company_id = 10006 AND month_number = month_index; END LOOP;

7. DECLARE CURSOR angajati_cur IS SELECT ... ; BEGIN FOR angajati_rec IN angajati_cur

Vauban IT RO S.R.L. Curs Training PL/SQL LOOP calc_totals (angajati_rec.salary); END LOOP; IF angajati_rec.salary < 10000 THEN DBMS_OUTPUT.PUT_LINE (‘Give ‘‘em a raise!’); END IF; CLOSE angajati_cur; END;

8. DECLARE CURSOR checked_out_cur IS SELECT pet_id, name, checkout_date FROM occupancy WHERE checkout_date IS NOT NULL; BEGIN FOR checked_out_rec IN checked_out_cur LOOP INSERT INTO occupancy_history (pet_id, name, checkout_date) VALUES (checked_out_rec.pet_id, checked_out_rec.name, checked_out_rec.checkout_date); END LOOP; END;

6. Rescrieti urmatorul bloc astfel incat sa nu se foloseasca structurile repetitive. FOR i IN 1 .. 2 LOOP IF i = 1 THEN give_bonus (president_id, 2000000); ELSIF i = 2 THEN give_bonus (ceo_id, 5000000); END IF; END LOOP;

7. Ce ati modifica in acest bloc PL/SQL ? De ce ? DECLARE CURSOR angajati_cur IS SELECT ename, id_departament, angajatino FROM angajati WHERE sal < 2500; angajati_rec angajati_cur%ROWTYPE; BEGIN FOR angajati_rec IN angajati_cur

Vauban IT RO S.R.L. Curs Training PL/SQL LOOP give_raise (angajati_rec.angajatino, 10000); END LOOP; END;

8. Sa se afiseze numarul de produse vandute pe fiecare departament folosind un cursor explicit. 9. Folosind un cursor explicit, afisati primii 10 angajati, in ordinea salariilor. 10. Sa se afiseze, folosind notiunea de cursor, angajatul si managerul lui direct. De forma: “Nume angajat - Manager responsabil”. Pentru cei ce nu au manager sa se afiseze in dreptul coloanei de manager, mesajul: “manager”


O excepţie este un identificator PL/SQL asociat unei condiţii anormale apărute în timpul execuţiei unui bloc PL/SQL. Invocarea unei excepţii are ca efect terminarea blocului, deci ieşirea excepţia din blocul PL/SQL. Pentrusi evitarea unor situaţii de întrerupere anormală, poate fi captată poate fi specificată o rutină de tratare a acesteia. O excepţie poate fi invocata in doua moduri: a) apare o eroare Oracle si excepţia asociata ei este automat invocată; b) excepţia poate fi invocată în mod explicit prin instrucţiunea RAISE in cadrul blocului. Captarea unei excepţii Daca excepţia este invocată în secţiunea executabilă a unui bloc se caută în cadrul secţiunii de tratare a excepţiilor o rutină de tratare asociata. Daca PL/SQL poate trata excepţia, ea nu este propagată în blocul exterior sau în mediul apelant, caz în care se consideră că execuţia blocului s-a desfăşurat cu succes. Propagarea unei excepţii Daca nu există o rutină pentru tratarea ei, excepţia este propagată în mediul apelant, caz în care execuţia blocului se termină cu eşec. Tipuri de excepţii


Tratarea tuturor excepţiilor se realizează în secţiunea EXCEPTION a blocurilor PL/SQL astfel:

Acestea nu trebuie declarate, fiind definite de către Oracle Server si invocate implicit. Lista completă a excepţiilor predefinite Oracle poate fi consultată din PL/SQL Reference. Câteva exangajatile de excepţii predefinite sunt prezentate mai jos:

Toate cele 21 de exceptii predefinite de Oracle se gasesc la adresa de mai jos, la capitolul Predefined PL/SQL Exceptions: https://docs.oracle.com/cd/A97630_01/appdev.920/a96624/07_errs.htm


Să se afişeze angajatul cu codul 10. Să se trateze eroarea apărută în cazul în care nu există nici un angajat cu acest cod.

Să se afişeze salariul angajatului cu prenumele John. Să se trateze eroare apărută în cazul în care există mai mulţi angajaţi cu acelaşi nume (interogarea SQL din bloc întoarce mai multe înregistrări).


Se poate capta o eroare a Serverului Oracle ce nu are asociata o excepţie predefinită asociindu-i un nume codului de eroare returnat sau folosind clauza WHEN OTHERS. In PL/SQL, directiva EXCEPTION_INIT determină compilatorul sa asocieze un nume de excepţie unui număr (cod) de eroare standard a Serverului Oracle. Aceasta permite referirea erorii standard prin nume şi scrierea unei rutine de tratare a ei. Tratarea acestor erori se realizează in 3 paşi: 1) Declararea excepţiei: se face în zona DECLARE a blocului NUME_EXCEPTIE EXCEPTION; 2) Asocierea codului erorii cu excepţia declarată anterior: se realizează tot în zona DECLARE prin utilizarea directivei de compilare EXCEPTION_INIT: PRAGMA EXCEPTION_INIT(NUME_EXCEPTIE, COD_EROARE); Unde COD_EROARE este un cod de eroare standard Oracle. 3) Tratarea excepţiei în zona EXCEPTION a blocului: EXCEPTION WHEN NUME_EXCEPTIE THEN .........; Se pot utiliza 2 atribute pentru a gestiona erorile apărute: SQLCODE – returnează codul numeric al erorii. Poate avea următoarele valori:    

0 – nu a apărut nici o excepţie; 1 – este o excepţie definită de utilizator; +100 – excepţia NO_DATA_FOUND; un număr negativ – o eroare Oracle Server;

SQLERRM – returnează mesajul asociat erorii. Aceste atribute pot fi încărcate în variabile şi inserate într-o tabelă de erori pentru vizualizare şi verificare ulterioară.


Exemplu Să se insereze în tabela departamente un nou departament cu ID-ul 200, fără a preciza denumirea acestuia. În acest caz va apare o eroarea cu codul ORA-01400 prin care programatorul este avertizat de încălcarea unei restricţii de integritate. Această excepţie poate fi tratată astfel:

Să se şteargă toate înregistrările din tabela PRODUSE. lucru va duce la apariţia erorii cu codul –2292, reprezentând încălcarea Acest restricţiei referenţiale. Valorile SQLCODE şi SQLERRM vor fi inserate în tabela ERORI.


In PL/SQL se pot defini excepţii ale utilizatorului. Ele trebuie declarate în secţiunea declarativa a blocului şi invocate explicit prin instrucţiunea RAISE. Etape: 1. Se declara excepţia în secţiunea declarativă: nume_exceptie EXCEPTION; 2. Prin instrucţiunea RAISE se invocă în mod explicit, în cadrul secţiunii executabile: RAISE nume_exceptie;


3. Se tratează în rutina corespunzătoare din secţiunea de tratare a excepţiilor: WHEN nume_exceptie THEN......

Să se invoce o eroare în cazul în care utilizatorul încearcă să execute blocul PL/SQL după ora 17.

Să se modifice denumirea produsului cu id-ul 3. Dacă nu se produce nici o actualizare (valoarea atributului SQL%ROWCOUNT este 0) sau dacă apare o altă eroare (OTHERS) atunci să se declanşeze o excepţie prin care să fie avertizat utilizatorul.


Erorile definite de utilizator pot fi tratate la nivelul aplicaţiilor ca şi erorile Oracle Server prin atribuirea de coduri cu ajutorul funcţiei: RAISE_APPLICATION_ERROR (NR_EROARE, MESAJ); unde NR_EROARE poate fi un număr negativ cuprins între -20000 si 20999. In acest caz tratarea se realizează asemănător cu erorile non-predefinite Oracle Server.

Să se atribuie excepţiei din Exemplul anterior un cod şi un mesaj de eroare şi să se insereze aceste valori în tabela ERORI.


Propagarea excepţiilor Odată excepţia declanşată în secţiunea executabilă a unui bloc, se caută în cadrul secţiunii de tratare a excepţiilor (EXCEPTION) o rutină de tratare asociată. Daca PL/SQL poate trata excepţia, ea nu este propagată în blocul exterior sau în mediul apelant, caz în care se consideră că execuţia blocului s-a desfăşurat cu succes. Atunci când un sub-bloc tratează o excepţie, se termină normal iar execuţia se reia în blocul ce-l cuprinde imediat după instrucţiunea END a sub-blocului. Daca apare o excepţie iar în blocul curent nu există o rutină pentru tratarea sa, execuţia blocului se termina cu eşec, iar excepţia se propagă succesiv în blocurile exterioare până este găsită într-unul din ele o rutină pentru tratarea ei. Daca nu se găseşte nici una, în mediul apelant apare o


situaţie de excepţie nerezolvată, utilizator putând observa mesajul de eroare care a întrerupt execuţia normală.

1. no_data_found—Single row SELECT returned no data. 2. too_many_rows—Single row SELECT returned more than one row. 3. invalid_cursor—Illegal cursor operation was attangajatited. 4. value_error—Arithmetic, conversion, truncation, or constraint error occurred. 5. invalid_number—Conversion of a number to a character string failed. 6. zero_divide—Attangajatited to divide by zero. 7. dup_val_on_index—Attangajatited to insert a duplicate value into a column that has a unique index. 8. cursor_already_open—Attangajatited to open a cursor that was previously opened. 9. not_logged_on—A database call was made without being logged into Oracle. 10. transaction_backed_out—Usually raised when a remote portion of a transaction is rolled back. 11. login_denied—Login to Oracle failed because of invalid username and password. 12. program_error—Raised if PL/SQL encounters an internal problem. 13. storage_error—Raised if PL/SQL runs out of memory or if memory is corrupted. 14. timeout_on_resource—Timeout occurred while Oracle was waiting for a resource. 15. value_error—Arithmetic, conversion, truncation, or constraint error occurred. 16. others—This is a catchall. If the error was not trapped in the previous exception traps, the error will be trapped by this statement


1. Creaţi o tabela numita Mesaje, având un câmp unic, de tip Varchar2. 2. Scrieţi un bloc PL/SQL pentru a selecta codul comenzilor încheiate în anul 2000. a. Dacă interogarea returnează mai mult de o valoare pentru numărul comenzii, trataţi excepţia cu o rutină de tratare corespunzătoare şi inseraţi în tabela MESAJE mesajul “Atenţie! In anul 2000 s-au încheiat mai multe comenzi!”. b. Dacă interogarea nu returnează nici o valoare pentru numărul comenzii, trataţi excepţia cu o rutină de tratare corespunzătoare şi inseraţi în tabela Mesaje mesajul “Atenţie! In anul 2000 nu s-au încheiat comenzi!”. c. Dacă se returnează o singura linie, introduceţi în tabela Mesaje numărul comenzii. d. Trataţi orice altă excepţie cu o rutină de tratare corespunzătoare şi inseraţi în tabela MESAJE mesajul “A apărut o altă eroare!”. 3. In fiecare din urmatoarele blocuri PL/SQL, este ridicata o exceptie de tip (de obicei de o incercare de atriburile a unei valori prea mari pentru variabila declarata). Identificati modalitatea de tratare a erorii (daca exista – de asemenea exceptia poate sa ramana netratata). a) DECLARE string_of_5_chars VARCHAR2(5); BEGIN string_of_5_chars := ‘Steven ’; END;

b) DECLARE string_of_5_chars VARCHAR2(5); BEGIN BEGIN string_of_5_chars := ‘Steven’; EXCEPTION WHEN VALUE_ERROR

Vauban IT RO S.R.L. Curs Training PL/SQL THEN DBMS_OUTPUT.PUT_LINE (‘Inner block’); END; EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE (‘Outer block’); END;

c) DECLARE string_of_5_chars VARCHAR2(5) := ‘Eli’; BEGIN BEGIN string_of_5_chars := ‘Steven’; EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE (‘Inner block’); END; EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE ( ‘Outer block’); END;

d) DECLARE string_of_5_chars VARCHAR2(5) := ‘Eli’; BEGIN DECLARE string_of_3_chars VARCHAR2(3) := ‘Chris’; BEGIN string_of_5_chars := ‘Veva’; EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE (‘Inner block’); END; EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE ( ‘Outer block’); END;

e) DECLARE string_of_5_chars VARCHAR2(5); BEGIN BEGIN

Vauban IT RO S.R.L. Curs Training PL/SQL string_of_5_chars := ‘Steven’; EXCEPTION WHEN VALUE_ERROR THEN RAISE NO_DATA_FOUND; WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE (‘Inner block’); END; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE (‘Outer block’); END;

5. Scrieti un bloc PL/SQL care sa permita urmatoarelor comenzi de manipulare a datelor sa se execute, chiar daca unele dintre ele o sa intre in eroare, tratand eroarea in sectiunea de erori. UPDATE angajati SET id_angajat = 100 WHERE id_angajat > 5000; DELETE FROM departamente WHERE id_departament = 10; DELETE FROM angajati WHERE id_departament = 10;

6. Scrieti un bloc PL/SQL care trateaza printr-un nume urmatoarea eroare Oracle: ORA-1014: ORACLE shutdown in progress.

Indiciu: utilizati EXCEPTION INIT pragma. 7. Cand urmatorul bloc PL/SQL o sa fie executat, care din cele doua mesaje de mai jos este afisat? Explicati alegerea 8. Message from Exception Handler Output from Unhandled Exception Predefined or programmer-defined?

Error at line 1: ORA-1403: no data found ORA-6512: at line 5

DECLARE d VARCHAR2(1); /* Create exception with a predefined name. */ no_data_found EXCEPTION; BEGIN SELECT dummy INTO d FROM dual WHERE 1=2; IF d IS NULL

Vauban IT RO S.R.L. Curs Training PL/SQL THEN /* || Raise my own exception, not the predefined || STANDARD exception of the same name. */ RAISE no_data_found; END IF; EXCEPTION /* This handler only responds to the RAISE statement. */ WHEN no_data_found THEN DBMS_OUTPUT.PUT_LINE (‘Predefined or programmer-defined?’); END;


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 ( de Exemplu, SQL*Plus, Oracle Forms, Oracle Reports etc.). Subprogramele sunt bazate pe structura de bloc PL/SQL. Similar, ele conţin o parte declarativă opţională, o parte executabilă obligatorie şi o parte de tratare de excepţii opţională. Exista 2 tipuri de subprograme: 1. proceduri; 2. funcţii (trebuie să conţină cel puţin o comandă RETURN); Subprogramele pot fi: o o

locale (în cadrul altui bloc PL/SQL sau subprogram); stocate (create cu comanda CREATE) - odată create, procedurile şi funcţiile sunt stocate stocate. în bazaSintaxa de date, motiv pentru se numesc subprograme simplificată pentru care crearea unei proceduri este următoarea:


Sintaxa simplificată pentru crearea unei proceduri este următoarea:

Lista de parametri conţine specificaţii de parametri separate prin virgulă de forma:



mod_parametru specifică dacă parametrul este:  de intrare (IN) – singurul care poate avea o valoare initial;  de intrare / ieşire (IN OUT)  de ieşire (OUT);  mod_parametru are valoarea implicită IN.

În cazul în care se modifică un obiect (vizualizare, tabel etc) de care depinde un subprogram, acesta este invalidat. Revalidarea se face fie prin recrearea subprogramului fie prin comanda:

Ştergerea unei proceduri se realizează prin comenzile:

Informaţii despre procedurile deţinute de utilizatorul curent se pot obţine interogând vizualizarea USER_OBJECTS din

Obs: STATUS – starea subprogramului (validă sau invalidă).


Codul complet al unui subprogram poate fi vizualizat folosind următoarea sintaxă:

Eroarea apărută la compilarea unui subprogram poate fi vizualizată folosind următoarea sintaxă:

Erorile pot fi vizualizate şi prin intermediul comenzii SHOW ERRORS. Descrierea specificaţiei unui subprogram se face prin comanda DESCRIBE. Când este apelată o procedură , sistemul Oracle furnizează două metode pentru definirea parametrilor actuali: o o

specificarea explicită prin nume; specificarea prin poziţie.

Exemplu: -

specificare prin poziţie: specificare prin nume:

-

specificare prin nume şi poziţie:

Exercitii 1. Să se declare o procedură locală într-un bloc PL/SQL anonim prin care să se introducă în tabelul DEPT_pnu o nouă înregistrare precizând, prin intermediul parametrilor, valori pentru toate câmpurile. Invocaţi procedura în cadrul blocului.

DECLARE PROCEDURE add_dept (p_cod departamente.id_departament %TYPE, p_nume departamente.nume_departament %TYPE, p_manager departamente.id_manager %TYPE, p_location departamente.id_locatie%TYPE) IS BEGIN INSERT INTO dept_pnu VALUES (p_cod, p_nume, p_manager, p_location); END;

Vauban IT RO S.R.L. Curs Training PL/SQL BEGIN Add_dept(45, ‘DB Administration’ , 100, 2700); END; 2.

Să se declare o procedură locală care are parametrii următori: - p_rezultat (parametru de tip OUT) de tipul coloanei ename din tabelul ANGAJATI; - p_comision (parametru de tip IN) de tipul coloanei commdin ANGAJATI, iniţializat cu NULL; - p_cod (parametru de tip IN) de tipul coloanei angajati_id din LOYEES,

iniţializat cu NULL. Dacă p_comision nu este NULL atunci în p_rezultat se va memora numele salariatului care are salariul maxim printre salariaţii având comisionul respectiv. În caz contrar, în p_rezultat se va memora numele salariatului al cărui cod are valoarea dată la apelarea procedurii. DECLARE v_nume angajati.nume%TYPE; PROCEDURE p2l4_pnu (p_rezultat OUT angajati.nume% TYPE, p_comision IN angajati.comision%TYPE:=NULL, p_cod IN angajati.id_angajat %TYPE:=NULL) IS BEGIN IF (p_comision IS NOT NULL) THEN SELECT ename INTO p_rezultat FROM angajati WHERE comision = p_comision AND salariul = (SELECT MAX(salariul) FROM angajati WHERE comision= p_comision); DBMS_OUTPUT.PUT_LINE (‘Numele salariatului care are comisionul ‘||p_comision|| ‘ este ‘||p_rezultat); ELSE SELECT nume INTO p_rezultat FROM angajati WHERE id_angajat = p_cod; DBMS_OUTPUT.PUT_LINE (‘numele salariatului avand codul ‘||p_cod|| ‘ este ‘||p_rezultat); END IF; END; BEGIN -- partea executabilă a blocului p2l4_pnu (v_nume,0.4); --specificare prin pozitie a parametrilor actuali p2l4_pnu (v_nume, p_cod=>205); -- specificare prin pozitie si nume a parametrilor actuali END;


3. Să se creeze o procedură stocată fără parametri care afişează un mesaj “Programare PL/SQL”, ziua de astăzi în formatul DD-MONTH-YYYY şi ora curentă, precum şi ziua de ieri în formatul DD-MON-YYYY.

CREATE PROCEDURE first_pnu IS azi DATE := SYSDATE; ieri azi%TYPE; BEGIN DBMS_OUTPUT.PUT_LINE (‘Programare PL/SQL’) ; DBMS_OUTPUT.PUT_LINE (TO_CHAR(azi, ‘dd-month-yyyy hh24:mi:ss’)); ieri := azi -1; DBMS_OUTPUT.PUT_LINE (TO_CHAR(ieri, ‘dd-mon-yyyy’)); END;

La promptul SQL apelam procedura astfel

;

4. a) Să se creeze o procedură stocată care calculează salariul mediu al angajaţilor, returnândul prin intermediul unui parametru de tip OUT. b) Să se apeleze procedura regăsind valoarea medie a salariilor într-o variabilă gazdă. Afişaţi valoarea variabilei.

CREATE OR REPLACE PROCEDURE problema4_pnu (p_salAvg OUT angajati.salariul%TYPE) AS BEGIN SELECT AVG(salariul) INTO p_salAvg FROM angajati; END; La promptul SQL: VARIABLE g_medie NUMBER EXECUTE problema4_pnu (:g_medie) PRINT g_medie

5. a) Creaţi o procedură numită GET_ANGAJATI_BY_ID care întoarce salariul şi

denumirea job-ului pentru un angajat al cărui cod este transmis ca parametru. b) Executaţi procedura utilizând câte o variabilă gazdă pentru cei doi parametri de tip OUT. c) Testaţi procedura atât pentru coduri existente cât şi pentru coduri inexistente din tabelul angajati. Ce se întâmplă atunci când o invocăm pentru un cod inexistent ? 6. a) Analizaţi subprogramele create anterior în vizualizarea din dicţionarul datelor USER_OBJECTS. b) Regăsiţi codul unuia dintre subprogramele create anterior în vizualizarea


USER_SOURCE. c) Aflaţi tipul parametrilor uneia dintre procedurile create anterior utilizând comanda DESCRIBE. 7. Să se creeze o procedură stocată care măreşte salariile angajaţilor care nu au comision şi au media mai mică decât cea a departamentului în care lucrează cu o valoare transmisă ca parametru.

8. Să se declare o procedură locală care are următorii parametri: -- p_job p_rezultat (parametru de ieşire) de tip (parametru de intrare) de tip jobNUMBER; din jobs_pnu, iniţializat cu NULL; - p_titlu (parametru de intrare) de tip job_title din jobs_pnu, iniţializat cu NULL. Dacă job nu este NULL atunci în rezultat se va memora numărul de angajaţi având codul jobului specificat ca parametru. În caz contrar, în rezultat se va memora numărul de angajaţi care au titlul job-ului dat de al treilea parametru din procedura.


Sintaxa simplificată pentru crearea unei funcţii este următoarea:

Lista de parametri conţine specificaţii de parametri separate prin virgulă de forma:

O functie îndeplineste urmatoarele condiţii:     

Acceptă numai parametri de tip IN; Acceptă numai tipuri de date SQL, nu şi tipuri specifice PL/SQL; Returnează valori de tipuri de date SQL; Nu modifică tabelul care este blocat pentru comanda respectiva (mutating tables); Poate fi folosită în lista de expresii a comenzii SELECT, în clauzele WHERE si HAVING, CONNECT BY, START WITH, ORDER BY, GROUP BY, clauza VALUES a comenzii INSERT, clauza SET a comenzii UPDATE.

În cazul în care se modifică un obiect (vizualizare, tabel etc) de care depinde un subprogram, acesta este invalidat. Revalidarea se face fie prin recrearea subprogramului fie prin comanda:

Ştergerea unei funcţii se realizează prin comenzile:


Informaţii despre funcţiile deţinute de utilizatorul curent se pot obţine interogând vizualizarea USER_OBJECTS din dicţionarul datelor.

Obs: STATUS – starea subprogramului (validă sau invalidă). Codul complet al unui subprogram poate fi vizualizat folosind următoarea sintaxă:

Eroarea apărută la compilarea unui subprogram poate fi vizualizată folosind următoarea sintaxă:

Erorile pot fi vizualizate şi prin intermediul comenzii SHOW ERRORS. Descrierea specificaţiei unui subprogram se face prin comanda DESCRIBE. Când este apelată o procedură PL/SQL, sistemul Oracle furnizează două metode pentru definirea parametrilor actuali: o o

specificarea explicită prin nume; specificarea prin poziţie.

Exemplu: - specificare prin poziţie: - specificare prin nume: - specificare prin nume şi poziţie:

Vauban IT RO S.R.L. Curs Training PL/SQL Exercitii

1) Să se creeze o procedură stocată care pentru un anumit cod de departament (dat ca parametru) calculează prin intermediul unor funcţii locale numărul de salariaţi care lucrează în el, suma salariilor şi numărul managerilor salariaţilor care lucrează în departamentul respectiv.

CREATE OR REPLACE PROCEDURE problema1_pnu (p_dept angajati.id_departament%TYPE) AS FUNCTION nrSal (v_dept angajati.id_departament %TYPE) RETURN NUMBER IS v_numar NUMBER(3); BEGIN SELECT COUNT(*) INTO v_numar FROM angajati WHERE id_departament = v_dept; RETURN v_numar; END nrSal; FUNCTION sumaSal(v_dept angajati.id_departament %TYPE) RETURN NUMBER IS v_suma angajati.salariul%TYPE; BEGIN SELECT SUM(sal) INTO v_suma FROM angajati WHERE id_departament = v_dept; RETURN v_suma; END sumaSal; FUNCTION nrMgr(v_dept angajati.id_departament %TYPE) RETURN NUMBER IS v_numar NUMBER(3); BEGIN SELECT COUNT(DISTINCT mgr) INTO v_numar FROM angajati WHERE id_departament = v_dept; RETURN v_numar; END nrMgr; BEGIN -- partea executabila a procedurii problema1_pnu DBMS_OUTPUT.PUT_LINE (‘Numarul salariatilor care lucreaza in departamentul ‘||p_dept|| ‘ este ‘|| nrSal(p_dept)); DBMS_OUTPUT.PUT_LINE (‘Suma salariilor angajatilor din departamentul ‘|| p_dept || ‘ este ‘|| sumaSal(p_dept)); DBMS_OUTPUT.PUT_LINE (‘Numarul de manageri din departamentul ‘|| p_dept || ‘ este ‘|| nrMgr(p_dept)); END; EXECUTE problema1_pnu(50);


3. Să se creeze două funcţii (locale) supraîncărcate (overloaded) care să calculeze media salariilor astfel: - prima funcţie va avea ca argument codul departamentului, adică funcţia calculează media salariilor din departamentul specificat; - a doua funcţie va avea două argumente, unul reprezentând codul departamentului, iar celălalt reprezentând job-ul, adică funcţia va calcula media salariilor dintr-un anumit departament şi care aparţin unui job specificat.

DECLARE medie1 NUMBER(10,2); medie2 NUMBER(10,2); FUNCTION medie (v_dept angajati.id_departament%TYPE) RETURN NUMBER IS rezultat NUMBER(10,2); BEGIN SELECT AVG(sal) INTO rezultat FROM angajati WHERE id_departament = v_dept; RETURN rezultat; END; FUNCTION medie (v_dept angajati.id_departament%TYPE, v_job angajati.id_functie%TYPE) RETURN NUMBER IS rezultat NUMBER(10,2); BEGIN SELECT AVG(sal) INTO rezultat FROM angajati WHERE id_departament = v_dept AND job = v_job; RETURN rezultat; END; BEGIN medie1:=medie(80); DBMS_OUTPUT.PUT_LINE (‘Media salariilor din departamentul 80 este ‘ || medie1); medie2 := medie(80, ‘SA_REP’); DBMS_OUTPUT.PUT_LINE (‘Media salariilor reprezentantilor de vanzari din departamentul 80 este ‘ || medie2); END;

4. Să se creeze o funcţie stocată care determină numărul de salariaţi din angajati angajaţi după 1995, într-un departament dat ca parametru. Să se apeleze aceastăbloc funcţie prin diferite modalităţi: - printr-o comandă SELECT; - într-un PL/SQL.

Vauban IT RO S.R.L. Curs Training PL/SQL CREATE OR REPLACE FUNCTION problema3_pnu (p_dept angajati.deptno%TYPE) RETURN NUMBER IS rezultat NUMBER; BEGIN SELECT COUNT(*) INTO rezultat FROM angajati WHERE deptno=p_dept AND TO_CHAR(hire_date,’yyyy’)>1995; RETURN rezultat; END problema8_pnu; 1) SELECT problema3_pnu (80) FROM dual; 2) DECLARE p_dep NUMBER :=50; nr NUMBER; v_dep angajati.deptno%TYPE; BEGIN nr := problema3_pnu (v_dep); IF nr<>0 THEN DBMS_OUTPUT.PUT_LINE (‘numarul salariatilor angajati după 1995 in departamentul ‘||v_dep || ‘ este ‘||nr); ELSE DBMS_OUTPUT.PUT_LINE (‘departamentul cu numarul ‘|| v_dep || ‘ nu are angajati’); END IF; END;

5. Să se calculeze recursiv numărul de permutări ale unei mulţimi cu n elemente, unde n va fi transmis ca parametru.

CREATE OR REPLACE FUNCTION permutari_pnu(p_n NUMBER) RETURN INTEGER IS BEGIN IF (p_n=0) THEN RETURN 1; ELSE RETURN p_n*permutari_pnu (p_n-1); END IF; END permutari_pnu; DECLARE g_n NUMBER; BEGIN g_n := permutari_pnu (5); DBMS_OUTPUT.PUT_LINE (‘permutari ‘ || g_n); END;


6. Să se afişeze numele, job-ul şi salariul angajaţilor al căror salariu este mai mare decât media salariilor din tabelul angajati.

CREATE OR REPLACE FUNCTION medie_pnu RETURN NUMBER IS medie NUMBER; BEGIN SELECT AVG(sal) INTO medie FROM angajati; RETURN medie; END; SELECT ename, job, sal FROM angajati WHERE sal >= medie_pnu;

7. a) Analizaţi subprogramele create anterior în vizualizarea din dicţionarul datelor USER_OBJECTS. b) Regăsiţi codul unuia dintre subprogramele create anterior în vizualizarea USER_SOURCE. c) Aflaţi tipul parametrilor uneia dintre procedurile create anterior utilizând comanda DESCRIBE. 8. Să se creeze o funcţie stocată care determină numărul de salariaţi care au fost angajaţi ulterior tuturor subalternilor unui manager al cărui cod este dat ca parametru. Să se apeleze această funcţie într-un bloc PL/SQL. 9. Să se creeze trei funcţii locale cu acelaşi nume care să calculeze numărul de salariaţi astfel: - prima funcţie va avea ca argument codul departamentului, adică funcţia calculează numărul de salariaţi din departamentul specificat; - a doua funcţie va avea două argumente, unul reprezentând codul departamentului, iar celălalt reprezentând anul angajării, adică funcţia va calcula numărul de salariaţi din departament şi care au fost angajaţi într-un anumit an; - a treia funcţie va avea trei argumente, unul reprezentând codul departamentului, unul reprezentând anul angajării, iar celălalt grila de salarizare, adică funcţia va calcula numărul de salariaţi din departament, care au fost angajaţi într-un anumit an şi au salariul într-o anumită grilă de salarizare. 9. Să se creeze o funcţie pentru calculul recursiv al combinărilor.


10. Să se modifice salariul unui angajat al cărui cod este introdus ca parametru astfel încât să devină media salariilor angajaţilor care câştigă comision dintr-un departament dat ca parametru. 11. Să se scrie o funcţie care să întoarcă, pentru un angajat al cărui cod este specificat ca parametru, vechimea exprimată în luni. Să se utilizeze funcţia într-o instrucţiune SELECT care să întoarcă numele angajaţilor, salariul şi numărul de luni lucrate.

Declararea tipului RECORD se face conform următoarei sintaxe:

Probleme rezolvate: 1. Să se şteargă angajatul având codul 200 din tabelul ANGAJATI. Să se reţină într-o variabilă de tip RECORD codul, numele, salariul şi departamentul acestui angajat (clauza RETURNING) . Să se afişeze înregistrarea respectivă.

DECLARE TYPE info_ang_pnu IS RECORD ( cod_ang NUMBER(4), nume VARCHAR2(20), salariu NUMBER(8), cod_dep NUMBER(4)); v_info_ang info_ang_pnu; BEGIN DELETE FROM angajati

Vauban IT RO S.R.L. Curs Training PL/SQL WHERE id_angajat = 200 RETURNING id_angajat, nume, salariul, id_departament INTO v_info_ang; DBMS_OUTPUT.PUT_LINE (‘A fost stearsa linia continand valorile ‘ || v_info_ang.cod_ang ||’ ‘||v_info_ang.nume||’ ‘ ||v_info_ang.salariu ||’ ‘ || v_info_ang.cod_dep) ; END;

2. a) Folosind tipul declarat mai sus, să se adauge o linie în tabelul angajati prin intermediul unei variabile de tip înregistrare iniţializate. Efectuaţi modificările necesare asupra tipului de date, astfel încât această inserare să fie posibilă. La iniţializarea unei variabile de tip record, ţineţi cont de constrângerile NOT NULL definite asupra tabelului angajati. b) Modificaţi valoarea unei componente a variabilei definite anterior şi actualizaţi conţinutul liniei introduse în tabel. DECLARE TYPE info_ang_pnu IS RECORD ( cod_ang NUMBER(4):=500, nume VARCHAR2(20):='abc', prenume VARCHAR2(20):='john', email angajati.email%TYPE:='abc@mail', telefon angajati.telefon%type, data angajati.data_angajare%TYPE:=SYSDATE, job angajati.id_functie%TYPE:='SA_REP', salariu NUMBER(8, 2):=1000, comision angajati.comision%TYPE, manager angajati.id_manager%TYPE, cod_dep NUMBER(4):=30 ); v_info_ang info_ang_pnu; BEGIN --inserare; nu ar fi fost posibila maparea unei variabile de tip RECORD întro lista -- explicita de coloane INSERT INTO angajati VALUES v_info_ang; DBMS_OUTPUT.PUT_LINE ('A fost introdusa linia continand valorile ' || v_info_ang.id_angajat ||' '||v_info_ang.nume||' ' ||v_info_ang.salariul ||' ' || v_info_ang.id_departament) ; --actualizare v_info_ang.nume:='smith'; UPDATE angajati SET ROW=v_info_ang WHERE id_angajat = v_info_ang.id_angajat; DBMS_OUTPUT.PUT_LINE ('A fost actualizata linia cu valorile ' || v_info_ang.id_angajat ||' '||v_info_ang.nume||' ' ||v_info_ang.salariul ||' '

Vauban IT RO S.R.L. Curs Training PL/SQL || v_info_ang.id_departament) ; END;

Colecţiile permit să fie prelucrate simultan mai multe variabile de acelaşi tip. Fiecare element are un indice unic, care determină poziţia sa în colecţie. În PL/SQL există trei tipuri de colecţii:   

tablouri indexate (index-by tables); tablouri imbricate (nested tables); vectori (varrays sau varying arrays).

Obs: -

Tipul index-by table poate fi utilizat numai în declaraţii PL/SQL. Tipurile varray şi nested table pot fi utilizate atât în declaraţii PL/SQL, cât şi în declaraţii la nivelul schemei (de exemplu, pentru definirea tipului unei coloane a unui tabel relaţional). - Singura diferenţă sintactică între tablourile indexate şi cele imbricate este clauza INDEX BY. Dacă această clauză lipseşte, atunci tipul este tablou imbricat. Atribute şi metode ale unei colecţii: Atribut sau metoda

Descriere Numarul componetelor colectiei Indicele primului element din tablou Indicele ultimului element din tablou întoarce TRUE dacă există în tablou componenta cu indexul specificat returnează indicele următoarei componente returnează indicele componentei anterioare şterge una sau mai multe componente. Adauga elemente la sfarsit Numărul maxim de elemente al unei colecţii (pentru vectori), null pentru tablouri imbricate şterge elementele de la sfârşitul unei colecţii


Ultimele 3 metode nu sunt valide pentru index-by tables. bulk bind permite ca toate liniile unei colecţii să fie transferate simultan printr-o singură operaţie.  este realizat cu ajutorul comenzii FORALL, ce poate fi folosită cu orice tip de colecţie: 

Cursorul SQL are un atribut compus %BULK_ROWCOUNT care numără liniile afectate de iteraţiile comenzii FORALL. %BULK_ROWCOUNT(i) reprezintă numărul de linii procesate de a i-a execuţie a comenzii SQL. 

Regăsirea rezultatului unei interogări în colecţii (înainte de a fi trimisă motorului PL/SQL) se poate obţine cu ajutorul clauzei BULK COLLECT:

Clauza poate să apară în:  comenzile SELECT INTO (cursoare implicite);  comenzile FETCH INTO (cursoare explicite);  clauza RETURNING INTO a comenzilor INSERT, UPDATE, DELETE. Analizaţi şi comentaţi exemplul următor. Afişaţi valorile variabilelor definite. DECLARE TYPE tab_index IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; TYPE tab_imbri IS TABLE OF NUMBER; TYPE vector IS VARRAY(15) OF NUMBER; v_tab_index tab_index; v_tab_imbri tab_imbri; v_vector vector; i INTEGER; BEGIN v_tab_index(1) := 72; v_tab_index(2) := 23; v_tab_imbri := tab_imbri(5, 3, 2, 8, 7); v_vector := vector(1, 2); -- afisati valorile variabilelor definite; exemplu dat pentru v_tab_imbri i:=v_tab_imbri.FIRST; WHILE (i <= v_tab_imbri.LAST) LOOP DBMS_OUTPUT.PUT_LINE ('v_tab_imbri: '||v_tab_imbri(i)); i:= v_tab_imbri.NEXT(i); END LOOP; END;


1. http://www.runceanu.ro/adrian/wpcontent/cursuri/pbd2015/Laborator_PBD_1(2015).pdf 2. http://bd.ase.ro/index.php?page=suport-seminar 3. http://www.eed.usv.ro/~mdanub/cursuri/Sisteme/lab7/Proc_PLSQL.html 4. http://software.ucv.ro/~cstoica/DD/Laborator9.pdf 5. http://www.eed.usv.ro/~mdanub/cursuri/Sisteme/lab8/declansatoare. html 6. http://www.runceanu.ro/adrian/wp-content/cursuri/pbd2014.php 7. http://docstore.mik.ua/orelly/oracle/advprog/appa_01.htm 8. http://www.runceanu.ro/adrian/wpcontent/cursuri/pbd2015/Laborator_PBD_10(2015).pdf

Training - PL SQL Oracle - Basics_final (1).pdf

Recommend Documents