eugo.ro

 
Controlul execuţiei unui bloc PL/SQL
PL/SQL este un limbaj cu structură de bloc, adică programele sunt compuse din blocuri care pot fi complet separate sau imbricate.
Structura unui bloc poate fi obţinută combinând subprograme, pachete, blocuri imbricate.
Blocurile pot fi folosite în utilitarele Oracle.
Pentru modularizarea unui program este necesară:

• gruparea logică a instrucţiunilor în blocuri;
• imbricarea de subblocuri în blocuri mai mari;
• descompunerea unei probleme complexe într-o mulţime de module logice şi implementarea
  acestora cu ajutorul blocurilor;
• lasarea în biblioteci a codului PL/SQL reutilizabil, de unde poate fi folosit
  de aplicaţii;
• depunerea codului într-un server Oracle, de unde este accesibil oricărei aplicatii care
  interacţionează cu baza de date Oracle.

Un program PL/SQL poate cuprinde unul sau mai multe blocuri. Un bloc poate fi anonim sau neanonim.

Blocurile anonime sunt blocuri PL/SQL fără nume, care sunt construite dinamic şi sunt executate o singură dată. Acest tip de bloc nu are argumente şi nu returnează un rezultat. Ele sunt declarate într-un punct al aplicaţiei, unde vor fi executate (trimise motorului PL/SQL). În blocurile anonime pot fi declarate proceduri şi funcţii PL/SQL.

Blocurile anonime pot să apară într-un program ce lucrează cu precompilator sau în SQL*Plus.
De obicei, blocurile anonime sunt plasate într-un fişier, iar apoi fişierul este executat din SQL*Plus. De asemenea, declanşatorii din componentele Developer Suite constau din astfel de blocuri.

Blocurile neanonime sunt fie blocuri cu nume (etichetate) construite static sau dinamic şi executate o singură dată, fie subprograme, pachete sau declanşatori.
Subprogramele sunt proceduri sau funcţii depuse în baza de date. Aceste blocuri sunt executate de mai multe ori şi, în general, nu mai sunt modificate după ce au fost construite. Procedurile şi funcţiile stocate sunt depuse pe server-ul Oracle, acceptă parametri şi pot fi apelate prin nume. Procedurile şi funcţiile aplicaţie sunt depuse într-o aplicaţie Developer Suite sau într-o bibliotecă.

Pachetele (stocate sau aplicaţie) sunt blocuri neanonime care grupează proceduri, funcţii, cursoare, tipuri, constante, variabile într-o unitate logică, în baza de date.

Declanşatorii sunt blocuri PL/SQL neanonime depuse în baza de date, care pot fi asociaţi bazei, iar în acest caz sunt executaţi implicit ori de câte ori apare un anumit eveniment declanşator (de exemplu, instrucţiuni INSERT, UPDATE sau DELETE ce se execută asupra unui tabel al bazei de date) sau pot fi asociaţi uneiaplicaţii (de exemplu, declanşator SQL*Forms), ceea ce presupune că se execută automat, în funcţie de anumite condiţii sistem.

Structura unui bloc PL/SQL

Un bloc PL/SQL este compus din trei secţiuni distincte.

• Secţiunea declarativă (opţională) conţine declaraţii pentru toate variabilele, constantele, cursoarele şi erorile definite de utilizator la care se face referinţă în secţiunea executabilă sau chiar în cea declarativă. De asemenea, pot fi declarate subprograme locale care sunt vizibile doar în blocul respectiv.
• Secţiunea executabilă conţine instrucţiuni neprocedurale SQL pentru prelucrarea datelor din baza de date şi instrucţiuni PL/SQL pentru prelucrarea datelor în cadrul blocului.
• Secţiunea pentru tratarea erorilor (opţională) specifică acţiunile ce vor fi efectuate atunci când în execuţia blocului apar erori sau condiţii efectuate atunci când în execuţia blocului apar erori sau condiţii anormale.

Blocul PL/SQL are următoarea structură generală:


[bloc_name]
[DECLARE
declaration instructions]
BEGIN
executable statements(SQL sau PL/SQL)
[EXCEPTION
error handling]
END [bloc_name];


Dacă blocul PL/SQL este executat fără erori, va apărea mesajul:

PL/SQL procedure successfully completed

Example (SELECT with INTO clause)

Sa cream un bloc anonim în care se declară o variabilă v_job de tip
job_title (%TYPE) a cărei valoare va fi titlul jobului salariatului având codul 200.


SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 v_job jobs.job_title%TYPE;
3 BEGIN
4 SELECT job_title
5 INTO v_job
6 FROM employees e, jobs j
7 WHERE e.job_id=j.job_id
8 AND employee_id=200;
9 DBMS_OUTPUT.PUT_LINE('the job is '|| v_job);
10 END;
11 /
the job is Administration Assistant
PL/SQL procedure successfully completed.


Varianta 2

In exemplus de mai sus utilizam variabile de legătură. Să se afişeze rezultatul atat din bloc, cât şi din exteriorul acestuia.


SQL> VARIABLE result VARCHAR2(35)
SQL> BEGIN
2 SELECT job_title
3 INTO :result
4 FROM employees e, jobs j
5 WHERE e.job_id=j.job_id AND employee_id=200;
6 DBMS_OUTPUT.PUT_LINE('the result is '|| :result);
7 END;
8 /
the result is Administration Assistant
PL/SQL procedure successfully completed.
SQL> PRINT RESULT
RESULT
------------------------------
Administration Assistant


Compatibilitate SQL

Din punct de vedere al compatibilităţii dintre PL/SQL şi SQL, se remarcă următoarele reguli de bază:

• PL/SQL furnizează toate comenzile LMD ale lui SQL, comanda SELECT cu clauza INTO, comenzile LCD, funcţiile, pseudocoloanele şi operatorii SQL;
• PL/SQL nu furnizează comenzile LDD.

Totuşi, în ultimele sale versiuni, Oracle permite folosirea dinamică a comenzilor SQL, utilizând tehnica oferită de SQL dinamic. În felul acesta, orice comandă SQL (inclusiv comandă LDD) poate să fie utilizată în PL/SQL.

Majoritatea funcţiilor SQL sunt disponibile în PL/SQL. Există însă funcţii specifice PL/SQL, cum sunt funcţiile SQLCODE şi SQLERRM. De asemenea,există funcţii SQL care nu sunt disponibile în instrucţiuni procedurale (DECODE,funcţiile grup), dar care sunt disponibile în instrucţiunile SQL dintr-un bloc PL/SQL. SQL nu poate folosi funcţii sau atribute specifice PL/SQL.

Funcţiile grup trebuie folosite cu atenţie, deoarece clauza GROUP BY nu are sens să apară în instrucţiunea SELECT … INTO. Oracle9i introduce clauza OVER,care permite ca funcţia grup căreia îi este asociată să fie considerată o funcţie analitică (poate returna mai multe linii pentru fiecare grup).

Următoarele funcţii SQL nu sunt permise în PL/SQL: WIDTH_BUCKET,BIN_TO_NUM, COMPOSE, DECOMPOSE, TO_LOB, DECODE, DUMP,EXISTSNODE, TREAT, NULLIF, SYS_CONNECT_BY_PATH, SYS_DBURIGEN,EXTRACT.

Instrucţiuni PL/SQL

Orice program poate fi scris utilizând structuri de control de bază care sunt combinate în diferite moduri pentru rezolvarea problemei propuse. PL/SQL dispune de comenzi ce permit controlul execuţiei unui bloc. Instrucţiunile limbajului pot fi: iterative (LOOP, WHILE, FOR), de atribuire (:=), condiţionale (IF, CASE), de salt (GOTO, EXIT) şi instrucţiunea vidă (NULL).

Observaţii:

• Comentariile sunt ignorate de compilatorul PL/SQL. Există comentarii pe o singură linie, prefixate de simbolurile „–“, care încep în orice punct al liniei şi se termină la sfârşitul acesteia. De asemenea, există comentarii pe mai multe linii, care sunt delimitate de simbolurile „/*“ şi „*/“. Nu se admit comentarii imbricate.
• Caracterul „;“ este separator pentru instrucţiuni.
• Atat operatorii din PL/SQL, cât şi ordinea de execuţie a acestora, sunt identici cu cei din SQL. În PL/SQL este introdus un nou operator („**“) pentru ridicare la putere.
• Un identificator este vizibil în blocul în care este declarat şi în toate subblocurile, procedurile şi funcţiile imbricate în acesta. Dacă blocul nu găseşte identificatorul declarat local, atunci îl caută în secţiunea declarativă a blocurilor care includ blocul respectiv şi niciodată nu caută în blocurile încuibărite în acesta.
• Comenzile SQL*Plus nu pot să apară într-un bloc PL/SQL.
• În comanda SELECT trebuie specificate variabilele care recuperează rezultatul acţiunii acestei comenzi. În clauza INTO, care este obligatorie,pot fi folosite variabile PL/SQL sau variabile de legătură.
• Referirea la o variabilă de legătură se face prin prefixarea acesteia cu simbolul „:“.
• Cererea dintr-o comandă SELECT trebuie să returneze o singură linie drept rezultat. Atunci când comanda SELECT întoarce mai multe linii, apare eroarea TOO_MANY_ROWS, iar în cazul în care comanda nu găseşte date se generează eroarea NO_DATA_FOUND.
• Un bloc PL/SQL nu este o unitate tranzacţională. Într-un bloc pot fi mai multe tranzacţii sau blocul poate face parte dintr-o tranzacţie. Acţiunile COMMIT, SAVEPOINT şi ROLLBACK sunt independente de blocuri, dar instrucţiunile asociate acestor acţiuni pot fi folosite într-un bloc.
• PL/SQL nu suportă comenzile GRANT şi REVOKE, utilizarea lor fiind posibilă doar prin SQL dinamic.

Fluxul secvenţial de execuţie a comenzilor unui program PL/SQL poate fi modificat cu ajutorul structurilor de control: IF, CASE, LOOP, FOR, WHILE, GOTO, EXIT.

Instrucţiunea de atribuire

Instrucţiunea de atribuire se realizează cu ajutorul operatorului de asignare (:=) şi are forma generală clasică (variabila := expresie). Comanda respectă proprietăţile instrucţiunii de atribuire din clasa LG3. De remarcat că nu poate fi asignată valoarea null unei variabile care a fost declarată NOT NULL.

Exemplu

Următorul exemplu prezintă modul în care acţionează instrucţiunea de atribuire în cazul unor tipuri de date particulare.


DECLARE
alfa INTERVAL YEAR TO MONTH;
BEGIN
alfa := INTERVAL '200-7' YEAR TO MONTH;
DBMS_OUTPUT.PUT_LINE(alfa);
-- alpha takes the value 200 years and 7 months
alfa := INTERVAL '200' YEAR;
-- only years can be specified
alfa := INTERVAL '7' MONTH;
-- only months can be specified
alfa := '200-7';
-- implicit conversion from character
END;

SQL> declare
2 alfa interval year to month;
3 begin
4 alfa :=interval '1 - 7' year to month;
5 DBMS_OUTPUT.PUT_LINE('alfa = '|| alfa);
6 end;
7 /
alfa = +01-07
PL/SQL procedure successfully completed.

SQL> declare
2 alfa interval year to month;
3 begin
4 alfa :=interval '7' month;
5 DBMS_OUTPUT.PUT_LINE('alfa = '|| alfa);
6 end;
7 /
alfa = +00-07
PL/SQL procedure successfully completed.

SQL> declare
2 alfa interval year to month;
3 begin
4 alfa := '10 - 8';
5 DBMS_OUTPUT.PUT_LINE('alfa = '|| alfa);
6 end;
7 /
alfa = +10-08
PL/SQL procedure successfully completed.

DECLARE
beta opera%ROWTYPE;
gama opera%ROWTYPE;
cursor epsilon IS SELECT * FROM opera;
delta epsilon%ROWTYPE;
BEGIN
beta := gama; -- correct
gama := delta; -- incorrect???-test it!
END;


Instrucţiunea IF

Un program PL/SQL poate executa diferite porţiuni de cod, în funcţie de rezultatul unui test. Instrucţiunile care realizează acest lucru sunt cele condiţionale (IF, CASE).

Structura instrucţiunii IF în PL/SQL este similară instrucţiunii IF din alte limbaje procedurale, permiţând efectuarea unor acţiuni în mod selectiv, în funcţie de anumite condiţii. Instrucţiunea IF-THEN-ELSIF are următoarea formă sintactică:


IF condition1 THEN
sequence_of_commands_1
[ELSIF condition2 THEN
sequence_of_commands_2]
…
[ELSE
sequence_of_commands_n]
END IF;


O secvenţă de comenzi din IF este executată numai în cazul în care condiţia asociată este TRUE. Atunci când condiţia este FALSE sau NULL, secvenţa nu este executată. Dacă pe ramura THEN se doreşte verificarea unei alternative, se foloseşte ramura ELSIF (atenţie, nu ELSEIF) cu o nouă condiţie. Este permis un număr arbitrar de opţiuni ELSIF, dar poate apărea cel mult o clauză ELSE. Aceasta se referă la ultimul ELSIF.

Exemplu

Să se specifice dacă o galerie este mare, medie sau mica după cum numărul operelor de artă expuse în galeria respectivă este mai mare decât 200, cuprins între 100 şi 200 sau mai mic decât 100.


DEFINE p_cod_gal = 753
DECLARE
v_cod_galerie opera.cod_galerie%TYPE := &p_cod_gal;
v_numar
NUMBER(3) := 0;
v_comentariu VARCHAR2(10);
BEGIN
SELECT COUNT(*)
INTO v_numar
FROM opera
WHERE cod_galerie = v_cod_galerie;
IF v_numar < 100 THEN v_comentariu := 'mica';
ELSIF v_numar BETWEEN 100 AND 200 THEN v_comentariu := 'medie';

ELSE
v_comentariu := 'mare';

END IF;
DBMS_OUTPUT.PUT_LINE('Galeria avand codul '||
v_cod_galerie ||' este de tip '|| v_comentariu);
END;


Exemplu

Să se specifice dacă un angajat are salariu mare, mediu sau mic după cum este mai mare decât 20000, cuprins între 10000 şi 20000 sau mai mic decât 10000.


SQL> DEFINE p_cod_em = 201
SQL> DECLARE
v_cod_ang EMPLOYEES. EMPLOYEE_ID%TYPE := &p_cod_em;
v_sal EMPLOYEES.salary%type;
v_comentariu VARCHAR2(10);
BEGIN
SELECT salary
INTO v_sal
FROM EMPLOYEES
WHERE EMPLOYEE_ID = v_cod_ang;
IF v_sal < 10000 THEN v_comentariu := 'mic';
ELSIF v_sal BETWEEN 10000 AND 20000 THEN v_comentariu := 'mediu';

ELSE
v_comentariu:= 'mare';

END IF;
DBMS_OUTPUT.PUT_LINE('salariatul avand codul '|| v_cod_ang ||' are salariu '|| v_sal || ' considerat '|| v_comentariu);
END;
old 2: v_cod_ang EMPLOYEES. EMPLOYEE_ID%TYPE := &p_cod_em;
new 2: v_cod_ang EMPLOYEES. EMPLOYEE_ID%TYPE := 201;
salariatul avand codul 201 are salariu 13000 considerat mediu
PL/SQL procedure successfully completed.


Instrucţiunea CASE

to be continued…