Exercitii date spre rezolvare: Enunt/Rezolvare pentru Luni EG305 10-14

• Introducere în PL/SQL​
• Blocuri de instrucțiuni PL/SQL​
• Rularea blocurilor PL/SQL în SQL*Plus​
• Tipuri de variabile
• Tipuri de operatori​
• Tipuri de simboluri​
• Domeniul variabilelor și constantelor​

PL/SQL este un limbaj structurat pe blocuri​. Are caracteristici de procesare procedurală și de tratare a erorilor​. Blocurile pot fi de două tipuri:​

• Blocuri anonime​
• Subprograme(proceduri, funcții sau triggere)​

Deoarece PL/SQL este o extensie pentru SQL, regulile sintactice de bază aplicabile în SQL sunt valabile și în PL/SQL:​

• Instrucțiunile pot avea mai multe linii cu excepția cuvintelor cheie;​
• Unitățile lexicale (identificatori, nume de operatori) pot fi separate de unul sau mai multe spații, sau alt delimitator care nu poate face parte din unități lexicale;​
• Cuvintele rezervate nu pot fi folosite ca identificatori decât între ghilimele duble;​
• Identificatorii (nume de variabile, functii, proceduri, etc.) trebuie să înceapă cu o literă și pot conține cel mult 30 caractere​
• Șirurile trebuie să fie marcate cu ghilimele simple;​
• Numerele pot fi reprezentate prin valoarea lor sau științifică;

PL/SQL permite o varietate de tipuri de date ce pot fi folosite pentru declararea de variabile și constante​. Variabilele pot avea, opțional, atribuite valori la declarare și pot să-și schimbe valoarea prin atribuiri ulterioare în cadrul blocului. Constantele sunt identificatori care păstrează o valoare fixă ce trebuie atribuită în momentul declarării. Instructiunile PL/SQL nu sunt case sensitive. Setul de caractere este format din literele mari și mici, cifrele de la 0-9, spații, caractere de tabulare, carriage return și simboluri special.

Blocul anonim are cea mai simplă structură și este compus din trei părți:​

• Declare - conține definirea tuturor variabilelor care vor fi folosite în procesul de execuție​
• Execuție - conține instrucțiuni DML și bucle de execuție​
• Excepții - conține rutinele de interceptare și tratare a erorilor​

Fiecare bloc începe cu unul dintre cuvintele cheie DECLARE sau BEGIN. Cuvântul BEGIN este punctul de început al blocului ​de execuție. Înainte de executarea unui bloc se golește bufferul SQL​.

Structura logică a unui bloc PL/SQL anonim este:​

Ex. 1. Să se selecteze numele si functia unui angajat introducand id-ul acestuia de la tastatura (testați cu 1, 100 și 200).

Blocurile anonime pot fi înlănțuite. Execuția se face secvențial. Declararea variabilelor pentru toate blocurile înlănțuite se face o singură data în secțiunea DECLARE.

Structura logică pentru un bloc înlănțuit este:​

[<<label>>] -- etichetă bloc (opțional)
[DECLARE]  -- (opțional)​
  -- variabile pentru blocurile partinte și copii
BEGIN ​-- start bloc parinte
    [DECLARE] -- (opțional)​
      -- variabile pentru blocul copil_1
    BEGIN -- start blocul copil_1​
      -- accesibile variabilele declarate în blocurile
      -- parinte și copil_1
    [EXEPTION] -- (opțional)​
      -- interceptarea și tratarea excepțiilor blocul copil_1​
    END; -- sfârșit bloc copil_1​
 
    [DECLARE] -- (opțional)​
      -- variabile pentru blocul copil_2
    BEGIN -- start blocul copil_2​
      -- accesibile variabilele declarate în blocurile
      -- parinte si copil_2
    [EXCEPTION] -- (opțional)​
      -- interceptarea și tratarea excepțiilor bloc 2
    END; -- sfârșit bloc copil_2​
[EXCEPTION] -- (opțional)​
  -- interceptarea și tratarea excepțiilor bloc principal 
  -- si orice alta exceptie netratata în blocurile copii 
END;​ -- sfârșit bloc parinte

Ex. 2. Să se insereze un nou angajat, astfel:

• se introduce de la tastatura un id de job, id-ul angajatului, prenumele și numele
• data angajarii este data curenta sysdate
• salariul ese 2123.85
• id-ul managerului este cel al managerului care are cei mai mulți angajați cu job introdus de la tastatura. Dacă sunt mai mulți manageri cu același număr de angajați se ia managerul care are id-ul cel mai mic.

După insert să se adauge pentru acest angajat un punctaj pentru comision de 0.5.​

Blocurile imbricate sunt blocuri care se execută în interiorul altui bloc. Se mai numesc și sub-blocuri. Variabile pot fi transmise de la bloc la sub-bloc, nu și invers.​

Structura logică pentru un bloc imbricat este:​

[<<label>>] -- etichetă bloc (opțional)
[DECLARE] -- (opțional)​
  --  variabile bloc principal, bloc 1, bloc 2, bloc 3​ 
BEGIN​
    [DECLARE] -- (opțional)​
      --  variabile bloc 1, bloc 2, bloc 3​
    BEGIN -- bloc 1​
        [DECLARE]  -- (opțional)​
          --  variabile bloc 2, bloc 3​
        BEGIN -- bloc 2​
            [DECLARE]  -- (opțional)​
              --  variabile bloc 3​
            BEGIN -- bloc 3​
            [EXCEPTION] -- (opțional)​
              -- tratare erori bloc 3
            END; -- bloc 3​
        [EXCEPTION] -- (opțional)​
          -- tratare erori bloc 2
          -- tratarea erorilor propagate din blocul 3
        END; -- bloc 2​
    [EXCEPTION] -- (opțional)​
      -- tratare erori bloc 1​
      -- tratarea erorilor propagate din blocul 2 și 3
    END; -- bloc 1​
[EXCEPTION] -- (opțional)​
  -- tratare erori bloc principal
  -- tratarea erorilor propagate din blocurile 1, 2 și 3
END;​

Ex. 3. Să se rezolve exercițiul anterior folosind blocuri imbricate.​

În cazul în care nu aveți acces la un IDE, puteți să vă conectați la un Server ORACLE folosind utilitarul în linie de comandă SQL*Plus. (Mai multe dedalii în Laboratorul 2 - Baze de date)

Pentru a vă conecta la SQL*Plus în consolă comanda care se folosește este:

sqlplus [username[/password[@bazadate]]]

Blocurile PL/SQL pot fi editate direct în SQL*Plus (nerecomandat) sau pot fi editate în orice editor de text​. Pentru a executa un bloc în SQL*Plus folositi una din comenzile (comanda intreagă sau abrevierea):

În SQL*Plus

• Un bloc rămâne în buffer până la editarea următorului bloc​.
• Un bloc poate fi scris pe mai multe rânduri (folosiți ENTER pentru a începe o nouă linie)

În SQL*Plus un bloc se poate executa astfel:​

• Se copiază blocul în editor (Copy/Paste) și se execută cu / sau RUN​
• Se execută tot scriptul (care poate să conțină mai multe blocuri) folosind:​
  • SQL> @cale_fieier\nume_fisier.sql​
  • SQL> start cale_fieier\nume_fisier.sql​

În linia de comandă se mai pot da și următoarele comenzi:

• SAVE file_path_and_name – permite salvarea într-un fișier a conținutului bufferului SQL*Plus;
• GET file_path_and_name – încarcă conținutul unui fișier salvat în prealabil în buffer;
• START file_path_and_name – lansează în execuție comenzile salvate în fișier;
• @file_path_and_name – execută comenzile din fișier;
• EXIT – ieșire din SQL*Plus.

Comenzi SQL*Plus utile pentru formatare

PL/SQL acceptă toate tipurile de date din SQL. Nu sunt permise referințe anticipate, deci toate variabilele și constantele trebuie să fie declarate în secțiunea DECLARE înainte de utilizare. În secțiunea DECLARE trebuie declarate numele și tipul fiecărei variabile utilizate în cadrul blocului. Tipul specifică formatul de memorare, restricțiile și un domeniu valid de valori. Sintaxa declarării unei variabile este următoarea:​

nume_var [CONSTANT]{tip_de_date|identificator%TYPE|identificator%ROWTYPE}[NOT NULL][{:=|DEFAULT} expresie_PL/SQL];​

Tipurile de date scalare​ nu au componente interne (conţin valori atomice).

Tipurile de date ce stochează valori numerice cuprind:​

• tipul NUMBER cu subtipurile DEC, DECIMAL, DOUBLE PRECISION, FLOAT, INTEGER, INT, NUMERIC, REAL, SMALLINT;​
• tipul BINARY_INTEGER cu subtipurile NATURAL, NATURALN, POSITIVE, POSITIVEN, SIGNTYPE; ​
• tipul PLS_INTEGER.​

Tipurile de date ce stochează caractere cuprind:​

• tipul VARCHAR2 cu subtipurile STRING, VARCHAR;​
• tipul de date CHAR cu subtipul CHARACTER;​
• tipurile LONG, RAW, LONG RAW, ROWID​

Tipurile de date ce stochează data calendaristica si ora cuprind tipurile:

• DATE
• TIMESTAMP
• TIMESTAMP WITH TIME ZONE
• TIMESTAMP WITH LOCAL TIME ZONE
• INTERVAL YEAR TO MONTH
• INTERVAL DAY TO SECOND;​

Tipurile de date ce stochează date în format unicode includ tipurile

• NCHAR
• NVARCHAR2;​

Tipul de date BOOLEAN stochează valori logice (true, false sau null)

Exemple:

v_valoare NUMBER(15) NOT NULL := 0;​
v_data_achizitie DATE DEFAULT SYSDATE;​
v_material VARCHAR2(15) := 'Matase';​
c_valoare CONSTANT NUMBER := 100000;​
v_stare VARCHAR2(20) DEFAULT 'Buna';​
v_clasificare BOOLEAN DEFAULT FALSE;​
int_an_luna INTERVAL YEAR TO MONTH :=​
INTERVAL '3-2' YEAR TO MONTH; --interval de 3 ani si 2 luni​

Ex. 4. Să se scrie un bloc PL/SQL care primește de la tastatură un id de angajat și va afișa un mesaj cu forma: 'nume_angajat are un venit orar de venit_orar și face parte din departamentul denumire_departament. A lucrat în firma un număr total de zile_lucrate zile'. În caz că angajatul nu există să se trateze eroarea.

Tipurile corelate sunt folosite pentru a declara variabile de tipul unui rând de tabel sau al unei coloane de tabel. Avantajul folosirii unor astfel de tipuri este că atunci când se modifică structura coloanelor dintr-un tabel nu mai trebuie să fie modificat și codul PL/SQL.

Tipuri corelate:​

• %TYPE - variabilă de tipul unei coloane de tabel​
• %ROWTYPE - vector de tipul unui rând de tabel

Ex. 5. Să se scrie un bloc PL/SQL care primește de la tastatură un id de angajat și afișează numele, funcția acestuia, departamentul și locația departamentului. Locația deopartamentului va cuprinde orașul, statul/județul, țara și regiunea.​

Tipuri LOB (Large OBject) sunt folosite pentru stocarea de date binare, text, imagini și fișiere video de dimensiuni mari (până la 4GB).​

Operatorii de comparație pot fi feluri: operatori logici și operatori SQL

Operatorii de negație pot fi de două feluri: operatori logici și operatori SQL.

Precedența operatorilor logici este următoarea:

1. Operatorii de comparație și operatorii SQL au precedență egală (=,<,<=,>,>=,<>,!=,^=, BETWEEN, IN, LIKE, IS NULL)
2. NOT (pentru inversarea rezultatului unei expresii logice)
3. AND
4. OR

Domeniul de valabilitate al variabilelor reprezintă zona unui program în care acestea pot fi folosite. Variabilele și constantele pot fi declarate în cadrul blocului în care au fost definite sau în orice sub-bloc inclus în acesta. Variabilele declarate într-un sub-bloc pot fi folosite doar în acesta sau în sub-blocurile acestuia. Variabilele se pot transmite de la un bloc la sub-bloc, dar nu și invers. Aceste constrângeri se aplică tuturor obiectelor declarate, inscusiv cursoare, constante și excepții definite de utilizator.​

Ex. 6. Să se afișeze informațiile despre ultimul angajat care a venit în firmă în anul 2001.​

Ex. 7. Să se găsească care angajat are o vechime de peste 19 de ani si 5 luni in firmă.​

Ex. 8. Să se facă o verificare a numărului de angajări pentru un an introdus de la tastatură. Dacă există o angajare, atunci se va afișa mesajul 'In anul [an] au avut loc [numar angajari] angajare'. Dacă nu există nicio angajare, se va declanșa o excepție no_data_found și se va afișa mesajul 'Nu exista angajari in anul [an]'. Dacă sunt mai mulți angajați, se va declanșa excepția too_many_rows și se va afișa mesajul 'Sunt [numar angajari] angajari in anul [an]'.

Ex. 9. Să să selecteze departamementul, numele, data angajării și comisionul unui angajat introducând id-ul angajatului de la tastatură.​ În cazul în care angajatul nu are departament, se afișează N/A. Testați cu valorile 100 si 178

Ex. 10. Să se calculeze suma veniturilor angajaților care au aceeași funcție și lucrează în același departament. Testați cu

• id_dep = 10 și id_job = AD_ASST
• id_dep = 10 și id_job = IT_PROG