Controlul executiei unui bloc PL/SQL

Controlul executiei unui bloc PL/SQL

PL/SQL este un limbaj cu structura de bloc, adica programele sunt compuse din blocuri care pot fi complet separate sau imbricate. Structura unui bloc poate fi obtinuta combinand subprograme, pachete, blocuri imbricate. Blocurile pot fi folosite in utilitarele Oracle.

Pentru modularizarea unui program este necesara:

gruparea logica a instructiunilor in blocuri;

imbricarea de subblocuri in blocuri mai mari;

descompunerea unei probleme complexe intr-o multime de module logice si implementarea acestora cu ajutorul blocurilor;

plasarea in biblioteci a codului PL/SQL reutilizabil, de unde poate fi folosit de aplicatii;

depunerea codului intr-un server Oracle, de unde este accesibil oricarei aplicatii care interactioneaza 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 fara nume, care sunt construite dinamic si sunt executate o singura data. Acest tip de bloc nu are argumente si nu returneaza un rezultat. Ele sunt declarate intr-un punct al aplicatiei, unde vor fi executate (trimise motorului PL/SQL). In blocurile anonime pot fi declarate proceduri si functii PL/SQL.

Blocurile anonime pot sa apara intr-un program ce lucreaza cu precompilator sau in SQL*Plus. De obicei, blocurile anonime sunt plasate intr-un fisier, iar apoi fisierul este executat din SQL*Plus. De asemenea, declansatorii din componentele Developer Suite constau din astfel de blocuri.

Blocurile neanonime sunt fie blocuri cu nume (etichetate) construite static sau dinamic si executate o singura data, fie subprograme, pachete sau declansatori.

Subprogramele sunt proceduri sau functii depuse in baza de date. Aceste blocuri sunt executate de mai multe ori si, in general, nu mai sunt modificate dupa ce au fost construite. Procedurile si functiile stocate sunt depuse pe server-ul Oracle, accepta parametri si pot fi apelate prin nume. Procedurile si functiile aplicatie sunt depuse intr-o aplicatie Developer Suite sau intr-o biblioteca.

Pachetele (stocate sau aplicatie) sunt blocuri neanonime care grupeaza proceduri, functii, cursoare, tipuri, constante, variabile intr-o unitate logica, in baza de date.

Declansatorii sunt blocuri PL/SQL neanonime depuse in baza de date, care pot fi asociati bazei, iar in acest caz sunt executati implicit ori de cate ori apare un anumit eveniment declansator (de exemplu, instructiuni INSERT, UPDATE sau DELETE ce se executa asupra unui tabel al bazei de date) sau pot fi asociati unei aplicatii (de exemplu, declansator SQL*Forms), ceea ce presupune ca se executa automat, in functie de anumite conditii sistem.

Structura unui bloc PL/SQL

Un bloc PL/SQL este compus din trei sectiuni distincte.

Sectiunea declarativa (optionala) contine declaratii pentru toate variabilele, constantele, cursoarele si erorile definite de utilizator la care se face referinta in sectiunea executabila sau chiar in cea declarativa. De asemenea, pot fi declarate subprograme locale care sunt vizibile doar in blocul respectiv.

Sectiunea executabila contine instructiuni neprocedurale SQL pentru prelucrarea datelor din baza de date si instructiuni PL/SQL pentru prelucrarea datelor in cadrul blocului.

Sectiunea pentru tratarea erorilor (optionala) specifica actiunile ce vor fi efectuate atunci cand in executia blocului apar erori sau conditii anormale.

Blocul PL/SQL are urmatoarea structura generala

[<<nume_bloc>>]

[DECLARE

instructiuni de declarare

BEGIN

instructiuni executabile (SQL sau PL/SQL)

[EXCEPTION

tratarea erorilor

END [nume_bloc];

Daca blocul PL/SQL este executat fara erori, invariant va aparea mesajul:

PL/SQL procedure successfully completed

Compatibilitate SQL

Din punct de vedere al compatibilitatii dintre PL/SQL si SQL, se remarca urmatoarele reguli de baza:

PL/SQL furnizeaza toate comenzile LMD ale lui SQL, comanda SELECT cu clauza INTO, comenzile LCD, functiile, pseudocoloanele si operatorii SQL;

PL SQL nu furnizeaza comenzile LDD.

Totusi, in ultimele sale versiuni, Oracle permite folosirea dinamica a comenzilor SQL, utilizand tehnica oferita de SQL dinamic. In felul acesta, orice comanda SQL (inclusiv comanda LDD) poate sa fie utilizata in PL SQL

Majoritatea functiilor SQL sunt disponibile in PL SQL. Exista insa functii specifice PL/SQL, cum sunt functiile SQLCODE si SQLERRM. De asemenea, exista functii SQL care nu sunt disponibile in instructiuni procedurale (DECODE, functiile grup), dar care sunt disponibile in instructiunile SQL dintr-un bloc PL/SQL. SQL nu poate folosi functii sau atribute specifice PL SQL

Functiile grup trebuie folosite cu atentie, deoarece clauza GROUP BY nu are sens sa apara in instructiunea SELECT . INTO. Oracle9i introduce clauza OVER, care permite ca functia grup careia ii este asociata sa fie considerata o functie analitica (poate returna mai multe linii pentru fiecare grup).

Urmatoarele functii SQL nu sunt permise in PL/SQL: WIDTH_BUCKET, BIN_TO_NUM, COMPOSE, DECOMPOSE, TO_LOB, DECODE, DUMP, EXISTSNODE, TREAT, NULLIF, SYS_CONNECT_BY_PATH, SYS_DBURIGEN, EXTRACT.

Instructiuni PL/SQL

Orice program poate fi scris utilizand structuri de control de baza care sunt combinate in diferite moduri pentru rezolvarea problemei propuse. PL/SQL dispune de comenzi ce permit controlul executiei unui bloc. Instructiunile limbajului pot fi: iterative (LOOP, WHILE, FOR), de atribuire (:=), conditionale (IF, CASE), de salt (GOTO, EXIT) si instructiunea vida (NULL).

Observatii

Comentariile sunt ignorate de compilatorul PL/SQL. Exista comentarii pe o singura linie, prefixate de simbolurile "--", care incep in orice punct al liniei si se termina la sfarsitul acesteia. De asemenea, exista comentarii pe mai multe linii, care sunt delimitate de simbolurile "/*" si "*/". Nu se admit comentarii imbricate.

Caracterul este separator pentru instructiuni.

Atat operatorii din PL/SQL, cat si ordinea de executie a acestora, sunt identici cu cei din SQL. In PL/SQL este introdus un nou operator ("**") pentru ridicare la putere.

Un identificator este vizibil in blocul in care este declarat si in toate subblocurile, procedurile si functiile imbricate in acesta. Daca blocul nu gaseste identificatorul declarat local, atunci il cauta in sectiunea declarativa a blocurilor care includ blocul respectiv si niciodata nu cauta in blocurile incuibarite in acesta.

Comenzile SQL*Plus nu pot sa apara intr-un bloc PL/SQL.

In comanda SELECT trebuie specificate variabilele care recupereaza rezultatul actiunii acestei comenzi. In clauza INTO, care este obligatorie, pot fi folosite variabile PL/SQL sau variabile de legatura.

Referirea la o variabila de legatura se face prin prefixarea acesteia cu simbolul ":".

Cererea dintr-o comanda SELECT trebuie sa returneze o singura linie drept rezultat. Atunci cand comanda SELECT intoarce mai multe linii, apare eroarea TOO_MANY_ROWS, iar in cazul in care comanda nu gaseste date se genereaza eroarea NO_DATA_FOUND.

Un bloc PL/SQL nu este o unitate tranzactionala. Intr-un bloc pot fi mai multe tranzactii sau blocul poate face parte dintr-o tranzactie. Actiunile COMMIT, SAVEPOINT si ROLLBACK sunt independente de blocuri, dar instructiunile asociate acestor actiuni pot fi folosite intr-un bloc.

PL/SQL nu suporta comenzile GRANT si REVOKE, utilizarea lor fiind posibila doar prin SQL dinamic.

Fluxul secvential de executie a comenzilor unui program PL/SQL poate fi modificat cu ajutorul structurilor de control: IF, CASE, LOOP, FOR, WHILE, GOTO, EXIT.

Instructiunea de atribuire

Instructiunea de atribuire se realizeaza cu ajutorul operatorului de asignare (:=) si are forma generala clasica (variabila := expresie) Comanda respecta proprietatile instructiunii de atribuire din clasa LG3. De remarcat ca nu poate fi asignata valoarea null unei variabile care a fost declarata NOT NULL.

Exemplu

Urmatorul exemplu prezinta modul in care actioneaza instructiunea de atribuire in cazul unor tipuri de date particulare.

DECLARE

alfa INTERVAL YEAR TO MONTH;

BEGIN

alfa := INTERVAL '200-7' YEAR TO MONTH;

-- alfa ia valoarea 200 de ani si 7 luni

alfa := INTERVAL '200' YEAR;

-- pot fi specificati numai anii

alfa := INTERVAL '7' MONTH;

-- pot fi specificate numai lunile

alfa := '200-7';

-- conversie implicita din caracter

END;

DECLARE

beta opera%ROWTYPE;

gama opera%ROWTYPE;

cursor epsilon IS SELECT * FROM opera;

delta epsilon%ROWTYPE;

BEGIN

beta := gama; -- corect

gama := delta; -- incorect???-testati!

END;

Instructiunea IF

Un program PL/SQL poate executa diferite portiuni de cod, in functie de rezultatul unui test (predicat). Instructiunile care realizeaza acest lucru sunt cele conditionale (IF, CASE).

Structura instructiunii IF in PL/SQL este similara instructiunii IF din alte limbaje procedurale, permitand efectuarea unor actiuni in mod selectiv, in functie de anumite conditii. Instructiunea IF-THEN-ELSIF are urmatoarea forma sintactica:

IF conditie1 THEN

secventa_de_comenzi_1

[ELSIF conditie2 THEN

secventa_de_comenzi_2

[ELSE

secventa_de_comenzi_n

END IF

O secventa de comenzi din IF este executata numai in cazul in care conditia asociata este TRUE. Atunci cand conditia este FALSE sau NULL, secventa nu este executata. Daca pe ramura THEN se doreste verificarea unei alternative, se foloseste ramura ELSIF (atentie, nu ELSEIF) cu o noua conditie. Este permis un numar arbitrar de optiuni ELSIF, dar poate aparea cel mult o clauza ELSE. Aceasta se refera la ultimul ELSIF.

Exemplu

Sa se specifice daca o galerie este mare, medie sau mica dupa cum numarul operelor de arta expuse in galeria respectiva este mai mare decat 200, cuprins intre 100 si 200 sau mai mic decat 100.

SET SERVEROUTPUT ON

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;

SET SERVEROUTPUT OFF

Instructiunea CASE

Oracle9i furnizeaza o noua comanda (CASE) care permite implementarea unor conditii multiple. Instructiunea are urmatoarea forma sintactica:

[<<eticheta>>]

CASE test_var

WHEN valoare_1 THEN secventa_de_comenzi_1

WHEN valoare_2 THEN secventa_de_comenzi_2

.

WHEN valoare_k THEN secventa_de_comenzi_k

[ELSE alta_secventa;]

END CASE [eticheta];

Se va executa secventa_de_comenzi_p, daca valoarea selectorului test_var este valoare_p. Dupa ce este executata secventa de comenzi, controlul va trece la urmatoarea instructiune dupa CASE. Selectorul test_var poate fi o variabila sau o expresie complexa care poate contine chiar si apeluri de functii.

Clauza ELSE este optionala. Daca aceasta clauza este necesara in implementarea unei probleme, dar totusi lipseste, iar test_var nu ia nici una dintre valorile ce apar in clauzele WHEN, atunci se declanseaza eroarea predefinita CASE_NOT_FOUND (ORA - 06592).

Comanda CASE poate fi etichetata si, in acest caz, eticheta poate sa apara la sfarsitul clauzei END CASE. De remarcat ca eticheta dupa END CASE este permisa numai in cazul in care comanda CASE este etichetata.

Selectorul test_var poate sa lipseasca din structura comenzii CASE, care in acest caz va avea urmatoarea forma sintactica:

[<<eticheta>>]

CASE

WHEN conditie_1 THEN secventa_de_comenzi_1

WHEN conditie_2 THEN secventa_de_comenzi_2

.

WHEN conditie_k THEN secventa_de_comenzi_k

[ELSE alta_secventa;]

END CASE [eticheta];

Fiecare clauza WHEN contine o expresie booleana. Daca valoarea lui conditie_p este TRUE, atunci este executata secventa_de_comenzi_p

Exemplu

In functie de o valoare introdusa de utilizator, care reprezinta abrevierea zilelor unei saptamani, sa se afiseze (in cele doua variante) un mesaj prin care este specificata ziua saptamanii corespunzatoare abrevierii respective.

Varianta 1

SET SERVEROUTPUT ON

DEFINE p_zi = x

DECLARE

v_zi CHAR(2) := UPPER('&p_zi');

BEGIN

CASE v_zi

WHEN 'L' THEN DBMS_OUTPUT.PUT_LINE('Luni');

WHEN 'M' THEN DBMS_OUTPUT.PUT_LINE('Marti');

WHEN 'MI' THEN DBMS_OUTPUT.PUT_LINE('Miercuri');

WHEN 'J' THEN DBMS_OUTPUT.PUT_LINE('Joi');

WHEN 'V' THEN DBMS_OUTPUT.PUT_LINE('Vineri');

WHEN 'S' THEN DBMS_OUTPUT.PUT_LINE('Sambata');

WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Duminica');

ELSE DBMS_OUTPUT.PUT_LINE('este o eroare!');

END CASE;

END;

SET SERVEROUTPUT OFF

Varianta

SET SERVEROUTPUT ON

DEFINE p_zi = x

DECLARE

v_zi CHAR(2) := UPPER('&p_zi');

BEGIN

CASE

WHEN v_zi = 'L' THEN

DBMS_OUTPUT.PUT_LINE('Luni');

WHEN v_zi = 'M' THEN

DBMS_OUTPUT.PUT_LINE('Marti');

WHEN v_zi = 'MI' THEN

DBMS_OUTPUT.PUT_LINE('Miercuri');

WHEN v_zi = 'J' THEN

DBMS_OUTPUT.PUT_LINE('Joi');

WHEN v_zi = 'V' THEN

DBMS_OUTPUT.PUT_LINE('Vineri');

WHEN v_zi = 'S' THEN

DBMS_OUTPUT.PUT_LINE('Sambata');

WHEN v_zi = 'D' THEN

DBMS_OUTPUT.PUT_LINE('Duminica');

ELSE DBMS_OUTPUT.PUT_LINE('Este o eroare!');

END CASE;

END;

SET SERVEROUTPUT OFF

Oracle8i a implementat suportul pentru expresii CASE care sunt permise numai in comenzi SQL. In Oracle9i poate fi utilizata o constructie CASE intr-o comanda SQL a unui bloc PL/SQL.

Expresia CASE are sintaxa similara comenzii CASE, dar clauzele WHEN nu se termina prin caracterul ";", clauza END nu include cuvantul cheie CASE si nu se fac atribuiri in clauza WHEN.

Exemplu

BEGIN

FOR j IN (SELECT

CASE valoare

WHEN 1000 THEN 1100

WHEN 10000 THEN 11000

WHEN 100000 THEN 110000

ELSE valoare

END

FROM opera)

.

END LOOP;

END;

Instructiuni iterative

Exista trei tipuri de comenzi iterative: ciclarea simpla LOOP, ciclarea WHILE si ciclarea FOR.

Acestea permit repetarea (conditionata sau neconditionata) executiei uneia sau mai multor instructiuni. Ciclurile pot fi imbricate pe mai multe niveluri. Ele pot fi etichetate, iar iesirea din ciclu se poate realiza cu ajutorul comenzii EXIT.

Se utilizeaza:

comanda LOOP, daca instructiunile din cadrul ciclului trebuie sa se execute cel putin o data;

comanda WHILE, in cazul in care conditia trebuie evaluata la inceputul fiecarei iteratii;

comanda FOR, daca numarul de iteratii este cunoscut.

Instructiunea LOOP are urmatoarea forma sintactica:

LOOP

secventa_de_comenzi

END LOOP

Ciclarea simpla cuprinde o multime de comenzi incluse intre cuvintele cheie LOOP si END LOOP. Aceste comenzi se executa cel putin o data. Daca nu este utilizata comanda EXIT, ciclarea poate continua la infinit.

Exemplu

Se presupune ca a fost creata structura tabelului org_tab, constand din doua coloane: cod_tab de tip INTEGER, ce contine un contor al inregistrarilor si text_tab de tip VARCHAR2, ce contine un text asociat fiecarei inregistrari. Sa se introduca 70 de inregistrari in tabelul org_tab.

DECLARE

v_contor BINARY_INTEGER := 1;

BEGIN

LOOP

INSERT INTO org_tab

VALUES (v_contor, 'indicele ciclului');

v_contor := v_contor + 1;

EXIT WHEN v_contor > 70;

END LOOP; COMMIT;

END;

Instructiunea repetitiva WHILE permite repetarea unei secvente de instructiuni, atata timp cat o anumita conditie specificata este adevarata.

Comanda WHILE are urmatoarea sintaxa:

WHILE conditie LOOP

secventa_de_comenzi

END LOOP

Daca variabilele care apar in conditie nu se schimba in interiorul ciclului, atunci conditia ramane adevarata si ciclul nu se termina.

Cand conditia este evaluata ca fiind FALSE sau NULL, atunci secventa de comenzi nu este executata si controlul trece la prima instructiune dupa END LOOP.

Exemplu

DECLARE

v_contor BINARY_INTEGER := 1;

BEGIN

WHILE v_contor <= 70 LOOP

INSERT INTO org_tab

VALUES (v_contor, 'indicele ciclului');

v_contor := v_contor + 1;

END LOOP;

END;

Instructiunea repetitiva FOR (ciclare cu pas) permite executarea unei secvente de instructiuni pentru valori ale variabilei contor cuprinse intre doua limite, lim_inf si lim_sup. Daca este prezenta optiunea REVERSE, iteratia se face (in sens invers) de la lim_sup la lim_inf.

Comanda FOR are sintaxa:

FOR contor_ciclu IN [REVERSE] lim_inf..lim_sup LOOP

secventa_de_comenzi

END LOOP

Variabila contor_ciclu nu trebuie declarata. Ea este neidentificata in afara ciclului si implicit de tip BINARY_INTEGER. Pasul are implicit valoarea 1 si nu poate fi modificat. Limitele domeniului pot fi variabile sau expresii, care sa poata fi convertite la intreg.

Exemplu

In structura tabelului opera se va introduce un nou camp (stea). Sa se creeze un bloc PL/SQL care va reactualiza acest camp, introducand o steluta pentru fiecare 10000$ din valoarea unei opere de arta al carei cod este specificat.

ALTER TABLE opera

ADD stea  VARCHAR2(20);

DEFINE p_cod_opera = 7777

DECLARE

v_cod_opera opera.cod_opera%TYPE := &p_cod_opera;

v_valoare opera.valoare%TYPE;

v_stea opera.stea%TYPE := NULL;

BEGIN

SELECT NVL(ROUND(valoare/10000),0)

INTO v_valoare

FROM opera

WHERE cod_opera = v_cod_opera;

IF v_valoare > 0 THEN

FOR i IN 1..v_valoare LOOP

v_stea := v_stea || '*';

END LOOP;

END IF;

UPDATE opera

SET stea = v_stea

WHERE cod_opera = v_cod_opera;

COMMIT;

END;

Instructiuni de salt

Instructiunea EXIT permite iesirea dintr-un ciclu. Ea are o forma neconditionala (iesire fara conditii) si una conditionala. Controlul trece fie la prima instructiune situata dupa clauza END LOOP corespunzatoare, fie dupa instructiunea LOOP avand eticheta nume_eticheta.

EXIT [nume_eticheta] [WHEN conditie];

Numele etichetelor urmeaza aceleasi reguli ca cele definite pentru identificatori. Eticheta se plaseaza inaintea comenzii, fie pe aceeasi linie, fie pe o linie separata. In PL/SQL etichetele se definesc prin intercalarea numelui etichetei intre caracterele "<<" si ">>" (<<eticheta>>).

Exemplu

DECLARE

v_contor BINARY_INTEGER := 1;

raspuns  VARCHAR2(10);

alt_raspuns VARCHAR2(10);

BEGIN

.

<<exterior>>

LOOP

v_contor := v_contor + 1;

EXIT WHEN v_contor > 70;

<<interior>>

LOOP

.

EXIT exterior WHEN raspuns = 'DA';

-- se parasesc ambele cicluri

EXIT WHEN alt_raspuns = 'DA';

-- se paraseste ciclul interior

.

END LOOP interior;

.

END LOOP exterior;

END;

Instructiunea GOTO determina un salt neconditionat la o instructiune executabila sau la inceputul unui bloc care are eticheta specificata in comanda. Instructiunea are urmatoarea forma sintactica:

GOTO nume_eticheta

Nu este permis saltul:

in interiorul unui bloc (subbloc);

in interiorul unei comenzi IF, CASE sau LOOP;

de la o clauza a comenzii CASE, la alta clauza a aceleasi comenzi;

de la tratarea unei exceptii, in blocul curent;

in exteriorul unui subprogram.

Instructiunea vida

Instructiunea vida (NULL) este folosita pentru o mai buna lizibilitate a programului. NULL este instructiunea care nu are nici un efect, marcand faptul ca nu trebuie intreprinsa nici o actiune. Nu trebuie confundata instructiunea NULL cu valoarea null!

Uneori instructiunea NULL este folosita intr-o comanda IF, indicand faptul ca pentru o anumita clauza ELSIF nu se executa nici o actiune.