Laboratorul 02 - PL/SQL Structuri de control și tipuri de date

Obiective

  • Instrucțiuni
  • Tipuri compuse și colecții

Instrucțiuni

Limbajul PL/SQL combină puterea de procesare a datelor, specifică limbajelor procedurale, cu puterea de manipulare a datelor, specifică instrucțiunilor. Structurile de control reprezintă cea mai importantă completare adusă de PL/SQL limbajului SQL prin introducerea instrucțiunilor de control condiționale, iterative, secvențiale și necondiționale. Toate structurile acceptă în clauzele lor instrucțiuni imbricate.

Instrucțiuni condiționale​

Instrucțiunea IF ... THEN​

Sintaxa instrucțiunii este:​

IF conditions​ THEN
    statements;​
END IF;​

Unde:

  • conditions – reprezintă condițiile cerute pentru execuția instrucțiunilor
  • statements – reprezintă secvența de instrucțiuni condiționale

Instrucțiunile care urmează după THEN se execută numai dacă toate condițiile din conditions sunt îndeplinite.​

Ex. 1. Pentru un angajat cu funcția 'FI_MGR' care nu primește comision să se modifice comisionul la 10% (.1). Testați cu id-ul 108.

Instrucțiunea IF ... THEN ... ELSE​

Sintaxa instrucțiunii este:​

IF conditions​ THEN 
    statements_1;​
ELSE 
    statements_2;​
END IF;​

Unde:

  • conditions – reprezintă condițiile cerute pentru execuția statements_1
  • statements_1 – reprezintă secvența de instrucțiuni atunci când conditions este TRUE
  • statements_2 – reprezintă secvența de instrucțiuni atunci când conditions este FALSE

Ex. 2. Să se acorde un comision de 10% din salariu angajaților care au o vechime de cel puțin 15 de ani în companie. (testati cu 108 si 149)​.

Instrucțiunea IF ... THEN ... ELSIF ... ELSE​

Sintaxa instrucțiunii este:​

IF conditions_1 THEN 
  statements_1;
ELSIF conditions_2​ THEN 
  statements_2;
...[ELSE statements_k;]
END IF;​

Unde:

  • conditions_1 – reprezintă setul de condiții cerute pentru execuția statements_1
  • statements_1 – reprezintă secvența de instrucțiuni executate atunci când conditions_1 = TRUE
  • conditions_2 – reprezintă setul de condiții cerute pentru execuția statements_2
  • statements_2 – reprezintă secvența de instrucțiuni executate atunci când conditions_2 = TRU​E
  • statements_k – reprezintă secvența de instrucțiuni executate dacă niciuna dintre conditions_1..k-1 nu sunt verificate

Verificarea seturilor de condiții se face secvențial, în ordinea specificată și fiecare set poate conține condiții multiple.

Se acceptă mai multe clause ELSIF dar o singură clasuză ELSE.

Clauza ELSE este opțională, aceasta poate să lipsească din construcția instrucțiunii.

Ex. 3. Să se acorde un comision de 10%, conducerii companiei care nu a primit comision, dar președintele să nu primească comision.​

Instrucțiunea CASE​

Sintaxa instrucțiunii este:​

[<<label_name>>]
CASE expression
WHEN value_1   THEN   statements_1;​
WHEN value_2   THEN   statements_2;
...
[ELSE statements_k;]
END CASE [label_name];

Unde:

  • expression – reprezintă expresia care se va evalua
  • statements_1 – reprezintă secvența de instrucțiuni executate în cazul în care expression = value_1
  • statements_2 – reprezintă secvența de instrucțiuni executate în cazul în care expression = value_2
  • statements_k – reprezintă secvența de instrucțiuni executate dacă expression <> value_1, value_2, …

Ex. 4. Să se calculeze impozitul pe salariu al unui angajat, după algoritmul:​

  • Din tabela JOBS se află salariul minim, respectiv maxim, pentru funcția pe care o deține;
  • Dacă angajatul are salariul minim, atunci impozitul este de 10% din salariu.
  • Dacă angajatul are salariul maxim, atunci impozitul este de 30% din salariu.
  • Altfel impozitul este de 20% din salariu.
  • Se afișează numele și impozitul angajatului.

Instrucțiunea SEARCHED CASE​

PL/SQL pune la dispoziția dezvoltatorilor SEARCHED CASE. Sintaxa instrucțiunii SEARCHED CASE este:

[<<label_name>>]CASE
WHEN search_condition_1 THEN sequence_of_statements_1;
WHEN search_condition_2 THEN sequence_of_statements_2;
...WHEN search_condition_N THEN sequence_of_statements_N;
[ELSE sequence_of_statements_N+1;]
END CASE [label_name];​

Ex. 5. Folosirea instrucțiunii searched CASE:​

Ex. 6. Folosirea instrucțiunii CASE pentru inițializarea unei variabile și în clauza where a unei cereri:​

Ex. 7. Exemplu de folosire a instrucțiunii CASE în clauza SELECT.​

Observații:

  • Clauza ELSE poate să lipsească din instrucțiunea CASE;
  • Dacă lipsește clauza ELSE și niciuna dintre clauzele WHEN nu se execută atunci apare eroarea CASE_NOT_FOUND.

Instrucțiuni de repetiție

Instrucțiunea LOOP​

Instrucțiunea LOOP este folosită pentru execuția ciclică a unei secvențe de instrucțiuni.

Sintaxa instrucțiunii este:​

[<<label_name>>]
LOOP
    statements;
    [EXIT [WHEN conditions];]
    [CONTINUE [WHEN conditions];]
END LOOP [label_name];

Unde:

  • statements – reprezintă secvența de instrcțiuni execuate în cadrul buclei
  • label_name – este eticheta următoarei instrucțiuni la care se forțează saltul, care poate să lipsească
  • conditions – reprezintă setul de condiții la care se forțează ieșirea din buclă și nu este obligatoriu, iar dacă lipsește atunci bucla are o singură iterație

Observații

  • După fiecare procesare a unui ciclu, controlul revine la începutul ciclului și se reia execuția instrucțiunilor
  • Instrucțiunea EXIT este folosită pentru ieșirea din ciclu, cu sau fără verificare de condiție
  • EXIT poate fi executată fie ca o acțiune dintr-un IF în cadrul blocului LOOP, fie ca o instrucțiune individuală în cadrul unui ciclu
  • O clauză WHEN poate fi înlocuită de instrucțiunea IF, cum se vede în construcția următoare:
LOOP
    statements;
    IF conditions THEN
        statements_in_if;
        EXIT;  -- exit loop immediately
    END IF;
END LOOP;

Ex. 8. În exemplul următor se dorește să se facă o listă cu data angajării și veniturile angajaților care fac parte dintr-un departament specificat.

Instrucțiunea WHILE

Instructiunea WHILE este folosită pentru realizarea unei structuri repetitive condiționate.

Sintaxa instrucțiunii este:

WHILE conditions
LOOP
    statements;
END LOOP;

Unde:

  • conditions – reprezintă setul de condiții pentru execuția buclei
  • statements – reptezintă secvența de instrucțiuni executată în cadrul buclei

Ex. 9. Să se modifice exercițiul precedent astfel încât să folosească instrucțiunea WHILE.

Instrucțiunea FOR​

Instrucțiunea FOR este folosită pentru realizarea unei structuri repetitive.

Sintaxa instrucțiunii este:

FOR contor IN [REVERSE] value1 .. value2
LOOP
    statements
END LOOP;

Unde:

  • contor – este contorul folosit pentru incrementarea (decrementarea) iterațiilor
  • value1 .. value2 – reprezintă valorile minime și maxime ale contorului (value1 ⇐ value2)
  • statements – reprezintă secvența de instrucțiuni executate în cadrul buclei​

Observații:​

  • Întotdeauna trebuie să se îndeplinească condiția value1 < value2 pentru a se intra în bucla FOR, indiferent dacă se folosește IN sau IN REVERSE
  • Incrementarea (cazul cu IN) sau decrementarea (cazul IN REVERSE) contorului este făcută automat cu 1 (cu data tipul PLS_INTEGER)

Ex. 10. Să se modifice exercițiul precedent astfel încât să folosească instrucțiunea FOR.

Observații:

  • Buclele pot fi imbricate pe mai multe niveluri
  • În general, o buclă internă nu termină execuția unui bucle care o include, exceptând cazul când este generată o excepție
  • Pentru salturi de instrucțiuni, se folosește instrucțiunea GOTO
  • GO poate fi folosit în oricare dintre instrucțiunile prezentate
  • Instrucțiunea GOTO poate fi folosită și să se iasă dintr-o buclă

Ex. 11.Exemplu de folosire a instrucțiunii GOTO.

Ex. 12. Exeplu de ieșise dintr-o buclă folosind GOTO.

Ex. 13. Să se scrie un bloc PL/SQL care face o listă cu numărul angajaților care au veniturile mai mici de 4000 (considerate venituri mici) și mai mari de 4000 (considerate venituri mari), pentru fiecare departament. Se va face o construcție folosind structuri de control și cicluri. Pentru departamentele care nu au niciun angajat se va afișa un mesaj.

Tipuri compuse și colecție

Tipul RECORD

Tipurile scalare nu au componente interne, în timp ce tipurile compuse au componente interne care se pot manipula.

RECORD este un tip compus:

  • Folosit pentru a accesa liniile tabelelor sub formă de matrice
  • Asemănător cu o înregistrare a unei tabele cu excepția faptului că numele coloanelor sunt proprii fiecărei înregistrări

Declararea tipului RECORD se face conform următoarei sintaxe:​

TYPE nume_tip IS RECORD​ (
   field_1 {datatype | variable%TYPE | TABLE.column%TYPE | table%ROWTYPE} [ [NOT NULL] [{:= | DEFAULT} expression_1] ],
   field_2 {datatype | variable%TYPE | TABLE.column%TYPE | table%ROWTYPE} [ [NOT NULL] [{:= | DEFAULT} expression_2] ],
   ...
   field_n {datatype | variable%TYPE | TABLE.column%TYPE | table%ROWTYPE} [ [NOT NULL] [{:= | DEFAULT} expression_n] ]
);​

Începând cu Oracle9i, pentru tipul RECORD sunt introduse câteva facilităţi:

  • Se poate insera (INSERT) o linie într-un tabel utilizând tipul RECORD
  • Se poate actualiza (UPDATE) o linie într-un tabel utilizând tipul RECORD (cu sintaxa SET ROW)
  • Se poate regăsi şi returna sau şterge informaţia din clauza RETURNING a comenzilor UPDATE sau DELETE
  • Dacă în comenzile UPDATE sau DELETE se modifică mai multe linii, atunci pot fi utilizate colecţii de înregistrări cu sintaxa BULK COLLECT INTO

Ex. 14. Un exemplu de utilizare a tipului RECORD.​

Ex. 15. Ex. 15. Folosindu-se tipul compus RECORD:

  • Să se creeze un record de forma tabelului dept
  • Să se insereze un nou departament folosind o variabilă de tip record
  • Să se selecteze înregistrarea inserată într-un record
  • Să se updateze înregistrarea inserată folosindu-se record și să se returneze într-un record informații
  • Să se șteargă departamentul creat și să se returneze într-un record informații.

Tipuri colecție

Tipurile colecție sunt:

  • Varrays
  • Index-by table
  • Nested tables

Tipurile colecție au atribute și metode. (În funcție de tip, atributele se comporta diferit). ​

Atribute și metode​ Descriere ​
COUNT​ Numărul de componente din colecţie
FIRST Indicele primului element din tablou​
LAST​ Indicele ultimului element din colecție
EXISTS Întoarce TRUE dacă există în coleție componenta cu indexul specificat​
NEXT​ Întoarce indicele următoarei componente​
PRIOR Întoarce indicele componentei anterioare​
DELETE Șterge una sau mai multe componente
EXTEND​ Adaugă elemente la sfârşitul colecției
LIMIT​ Întoarce numărul maxim de elemente al unei colecţii (pentru vectori), null pentru tablouri imbricate​
TRIM​ Șterge elementele de la sfârşitul unei colecţii​

Tipul VARRAY

Vectorii (varray) sunt structuri asemănătoare vectorilor din limbajele C sau Java. Vectorii au o dimensiune maximă (constantă) stabilită la declarare. Se utilizează pentru modelarea relaţiilor one-to-many, atunci când numărul maxim de elemente din partea “many” este cunoscut şi ordinea elementelor este importantă. Fiecare element are un index. Indexarea începe de la 1.

Tipul de date vector este declarat utilizând sintaxa:​

TYPE name IS {VARRAY | VARYING ARRAY} (max_length) OF element_type [NOT NULL]​;

Ex. 16. Exemplul folosire varray​.

Tipul ASSOCIATIVE ARRAY

Tipul ASSOCIATIVE ARRAY se mai numește și INDEX BY TABLE (tabel indexat).

Un table indexat în PL/SQL are două componente:

  • Coloană ce cuprinde cheia primară pentru a avea acces la liniile tabelului
  • O coloană care include valoarea efectivă a elementelor tabelului
  • Indexul pentru a avea acces la valoare
  • Valoarea efectivă a elementului

Declararea tipului ASSOCIATIVE ARRAY se face cu următoarea sintaxă:

TYPE name IS TABLE OF{data_type | variable%TYPE |TABLE.column%TYPE [NOT NULL] |​ table%ROWTYPE}INDEX BY data_type_index;​

Unde:

  • data_type_index poate fi String sau Integer

Observații:

  • Elementele unui ASSOCIATIVE ARRAY nu sunt într-o ordine particulară şi pot fi inserate cu indecși arbitrar aleși
  • Deoarece nu există constrângeri de dimensiune, dimensiunea tabelului se modifică dinamic
  • Un ASSOCIATIVE ARRAY nu poate fi iniţializat în declararea sa
  • Un ASSOCIATIVE ARRAY neiniţializat este vid (nu conţine nici valori, nici indecși)
  • Un element al unui ASSOCIATIVE ARRAY este nedefinit atâta timp cât nu are atribuită o valoare efectivă
  • Dacă se face referire la o linie care nu există, atunci se produce excepţia NO_DATA_FOUND.​

Ex. 17. Exemplu de folosire ASSOCIATIVE ARRAY.

Tipul Nested Table​

Un tablou imbricat este o mulţime neordonată de elemente de acelaşi tip. Valorile de acest tip:

  • Pot fi stocate în baza de date
  • Pot fi prelucrate direct în instrucţiuni SQL
  • Au excepţii predefinite proprii

Comanda de declarare a tipului de date tablou imbricat are sintaxa:

TYPE name IS TABLE OF {data_type | variable%TYPE |TABLE.column%TYPE [NOT NULL] |​ table%ROWTYPE}[NOT NULL];​

Observații:

  • Pentru adăugarea de linii intr-un tablou imbricat, acesta trebuie sa fie inițializat cu ajutorul constructorului
  • PL/SQL apelează un constructor numai în mod explicit
  • Tabelele indexate nu au constructori
  • Constructorul primeşte ca argumente o listă de valori numerotate în ordine, de la 1 la numărul de valori date ca parametrii constructorului
  • Dimensiunea iniţială a colecţiei este egală cu numărul de argumente date în constructor, când aceasta este iniţializată
  • Pentru vectori nu poate fi depăşită dimensiunea maximă precizată la declarare
  • Atunci când constructorul este fără argumente va crea o colecție fără niciun element (vida), dar care are valoarea not null

Ex. 18. Exemplu de folosire al unui tabel imbricat.​

Colecții stocate

Tipurile colecție pot fi definite direct în baza de date.

Collection Type​ Number of Elements​ Subscript Type​ Dense or Sparse​ Where Created​ Can Be Object Type Attribute​
Associative array (or index-by table)​ Unbounded​ String or PLS_INTEGER/BINARY_INTEGER​ Either​ Only in PL/SQL block​ No​
Nested table​ Unbounded​ Integer​ Starts dense, can become sparse​ Either in PL/SQL block or at schema level​ Yes​
Variable-size array (varray)​ Bounded​ Integer​ Always dense​ Either in PL/SQL block or at schema level​ Yes​

Ex. 19. Definirea tipurilor direct în baza de date.

Atunci când o coloană dintr-un tabel are tipul NESTED TABLE, Oracle stochează informația din această coloană în aceași zonă de stocare. De aceea trebuie să specificăm unde se zona de stocare la crearea tabelului folosind NESTED TABLE proiect STORE AS proiect_store;

BULK COLLECT

Pentru a insera mai multe valori într-o variabilă de tip colecție se poate folosi BULK COLLECT INTO.

Ex. 20. Folosirea BULK COLLECT INTO cu varray.​

Ex. 21. Folosirea BULK COLLECT INTO cu index-by table.​

Ex. 22. Folosirea BULK COLLECT INTO cu nested table.​

Colecții de înregistrări

Se poate crea un “tabel” care sa aibă coloane definite de dezvoltatori folosind un table(index-by sau nested) care să fie de tipul RECORD.

Ex. 23. Crearea unui tabel cu coloanele definite de utilizator.

bdd/laboratoare/02.txt · Last modified: 2024/10/08 01:43 by alex.petrescu
CC Attribution-Share Alike 3.0 Unported
www.chimeric.de Valid CSS Driven by DokuWiki do yourself a favour and use a real browser - get firefox!! Recent changes RSS feed Valid XHTML 1.0