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

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.

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

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.

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)​.

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

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

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.

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.​

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

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.

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 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​

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

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.

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] ]
);​

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.

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). ​

​

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

Ex. 17. Exemplu de folosire ASSOCIATIVE ARRAY.

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];​

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

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

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

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.​

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.