sgbd3 - plsql blocuri variabile instructiuni

7/25/2019 SGBD3 - PLSQL Blocuri Variabile Instructiuni

1/19

Sisteme de Gestiune a Bazelor de Date 3-1

Copyright 2012 Lect. Dr. Gabriela Mihai. Toate drepturile rezervate.

3. PL/SQL Blocuri. Variabile. Instruciuni .

CUPRINS

3. PL/SQL Blocuri. Variabile.Instruciuni. ............................................................................ 1

3.1. Limbajul PL/SQL ............................................................................................................ 2

3.2. Structura unui bloc PL/SQL ............................................................................................ 2

3.2.1. Separator pentru instruciuni.................................................................................... 3

3.2.2. Comentarii ................................................................................................................ 3

3.3. Operatori ......................................................................................................................... 4

3.4. Variabile ......................................................................................................................... 4

3.5. Blocuri PL/SQL .............................................................................................................. 5

3.6. ComenziSQL n PL/SQL ................................................................................................ 6

3.6.1. ComandaSELECT INTO ..................................................................................... 7

3.6.2. Comenzile INSERT , UPDATE , DELETE ................................................................ 8

3.7. Instruciuni PL/SQL ........................................................................................................ 93.7.1. Instruciunea de atribuire ......................................................................................... 9

3.7.2. Instruciunea condiional IF ................................................................................... 9

3.7.3. Instruciunea condiionalCASE ........................................................................... 11

3.7.4. Instruciunea iterativ LOOP ................................................................................. 13

3.7.5. Instruciunea iterativWHILE ............................................................................... 13

3.7.6. Instruciunea iterativ FOR .................................................................................... 143.7.7. Instruciunea vid................................................................................................... 15

3.7.8. Instruciunea de salt EXIT ...................................................................................... 18

3.7.9. Instruciunea de saltCONTINUE ........................................................................... 18

3.7.10. Instruciunea de salt GOTO ................................................................................. 19

Bibliografie ............................................................................................................................... 19


2/19



3.1. Limbajul PL/SQL

Att PL/SQL , ct i server -ul Oracle utilizeaz acelai spaiu de memorie i prinurmare nu apar suprancrcri datorate comunicaiilor dintre acestea.

Este un limbajcu structur de blocuri. Pentru modularizarea codului PL/SQL se pot folosi

o blocuri anonimeo subprograme ( proceduri i funcii)

funciile pot fiinvocate direct utiliznd comenziSQL o pacheteo trigger -i

sunt un tip special de proceduri PL/SQL care se execut automat laapariia unui anumit eveniment.

Blocuri anonime versus subprograme stocate

3.2. Structura unui bloc PL/SQL

Blocul este unitatea de baz a unui program PL/SQL . Mai este denumit i modul. Blocul PL/SQL conine 3 seciuni

o seciunea declarativ (opional) constantei variabile tipuri de date locale

Blocuri anonime Subprograme stocate

Blocuri PL/SQL fr nume Blocuri PL/SQL cu nume

Compilate de fiecare datcnd aplicaia este executat Compilate o singur dat

Nu sunt stocate n BD Sunt stocate n BDNu pot fi invocate de alteaplicaii

Pot fi invocate de alteaplicaii

Nu ntorc valori Funciile trebuie sntoarc o valoare

Nu accept parametrii Accept parametrii


3/19



cursoare excepii definite de utilizator subprograme locale (vizibile doar n bloc)

o seciunea executabil (obligatorie) instruciuniSQL pentru prelucrarea datelor instruciuni PL/SQL trebuie s conin mcar o instruciune

o seciuneade tratare aexcepiilor (opional) instruciuni efectuate atunci cnd apare a numit excepie/eroare

Sintaxageneral

[]

[DECLARE

instruciuni de declarare]

BEGIN

instruciuni executabile SQL sau PL/SQL

[EXCEPTION

tratarea erorilor /excepiilor ]

END [nume_bloc];

Dac blocul PL/SQL este executat fr erori va aprea mesajul: anonymous block completed

ntr-un bloc PL/SQL sunt permise instruciuniSQL*Plus ?

3.2.1. Separator pentru instruciuni

Caracterul ; este separator pentru instruciuni.

3.2.2. Comentarii

Comentariile sunt ignorate de compilatorul PL/SQL :

o pe o singur linie sunt prefixate de simbolurile -- ncep n orice punct al liniei i se termin la sfritul acesteia

o pe mai multe linii sunt delimitate de simbolurile /* i */


4/19



3.3. Operatori

Operatorii din PL/SQL sunt identici cu cei dinSQL. n PL/SQL este introdus operatorul ** pentru ridicare la putere.

Exemplul 3.1

SELECT POWER(3,2)

FROM DUAL;

BEGIN

DBMS_OUTPUT.PUT_LINE(POWER(3,2));

END;

BEGIN

DBMS_OUTPUT.PUT_LINE(3**2);

END;

Care secven poate fi executat local? n ce condiii?

3.4. Variabile

Stocheaz datele n acelai format binar intern ca i baza de date, astfelnefiindnecesare conversii suplimentare.

Pot fi declarate doarn zona declarativ a unui bloc, unde pot fi i iniializate. Exemplul 3.2

DECLAREnume_utilizator VARCHAR2(30);data_creare_cont DATE DEFAULT SYSDATE;numar_credite NUMBER(4) NOT NULL := 1000;limita_inferioara_credite CONSTANT NUMBER := 100;limita_superioara_credite NUMBER := 5000;este_valid BOOLEAN := TRUE;

Fiecarevariabil se declar individual.

DECLARE-- declaratie-- incorectai, j INTEGER;

BEGINNULL;

END;

DECLARE/*declaratie

corecta*/i INTEGER;j INTEGER;

BEGINNULL;

END;


5/19



Li se pot atribui valori noi i pot fi utilizate n zona executabil a blocului. Pot fi transmise ca parametrii subprogramelor PL/SQL . Pot fi declarate pentru a menine rezultatul obinut de un subprogram PL/SQL . Sunt vizibile n blocul n care sunt declaratei n toate subblocurile declarate n

acesta. Dac o variabil nu este declarat local n bloc, atunci este cutat n seciunea

declarativ a blocurilor care includ blocul respectiv.

Exemplul 3.3

DECLAREv_principal VARCHAR2(50);

BEGIN

v_principal := 'variabila din blocul principal';DECLARE

v_secundar VARCHAR2(50) :='variabila din blocul secundar';

BEGINDBMS_OUTPUT.PUT_LINE('');DBMS_OUTPUT.PUT_LINE('Folosesc '||v_principal);DBMS_OUTPUT.PUT_LINE('Folosesc '||v_secundar);v_secundar := 'Modific '||v_secundar;v_principal := 'Modific '||v_principal;DBMS_OUTPUT.PUT_LINE(v_secundar);DBMS_OUTPUT.PUT_LINE(v_principal);

END;

DBMS_OUTPUT.PUT_LINE('');DBMS_OUTPUT.PUT_LINE(v_secundar);DBMS_OUTPUT.PUT_LINE(v_principal);

END;

Carecomand va genera o eroare?

3.5. Blocuri PL/SQL

Exemplul 3.4

o Bloc f r seciune declarativ, fr seciune de tratare a excepiilor .BEGIN

DBMS_OUTPUT.PUT_LINE('SGBD');END;


6/19



o Bloc cu seciune declarativ, fr seciune de tratare a excepiilor .DECLARE

v_data DATE := SYSDATE;BEGIN

DBMS_OUTPUT.PUT_LINE(v_data);END;

o Bloc cuseciune declarativ, cu seciune de tratare a excepiilor .DECLARE

x NUMBER := &p_x;y NUMBER := &p_y;

BEGINDBMS_OUTPUT.PUT_LINE(x/y);

EXCEPTIONWHEN ZERO_DIVIDE THEN

DBMS_OUTPUT.PUT_LINE('Nu poti sa imparti la 0!');WHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('Alta eroare!');

END;

3.6. Comenzi SQL n PL/SQL

ComenziSQL care pot fi utilizate direct n PL/SQL

o LMD (SELECT , INSERT , UPDATE , DELETE , MERGE )

ComandaSELECT poate fi utilizat doar cu clauza INTO o LCD (COMMIT , SAVEPOINT , ROLLBACK )

ComenziSQL care nu pot fi utilizate direct n PL/SQL

o LDD (CREATE , ALTER , DROP )

o LCD (GRANT , REVOKE )

o Aceste comenzi nu pot fi folosite direct n PL/SQL deoarece sunt construitei executate laruntime (sunt dinamice). De aceea pot fi utilizate n PL/SQL

doar cuSQL Dinamic .o SQL Static cuprinde comenzi care sunt stabilite la momentul n care

programul este compilat. Acestea pot fi utilizate direct n PL/SQL .


7/19



3.6.1. Comanda SELECT INTO

Exemplul 3.5

DECLARE

v_clasificare clasific_clienti.clasificare%TYPE;v_categorie clasific_clienti.id_categorie%TYPE;v_client clasific_clienti.id_client%TYPE

:= &p_client;BEGIN

SELECT clasificare, id_categorieINTO v_clasificare, v_categorieFROM clasific_clientiWHERE id_client = v_client;DBMS_OUTPUT.PUT_LINE(v_categorie || ' '

|| v_clasificare);EXCEPTION

WHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE('Nicio linie!');

WHEN TOO_MANY_ROWS THENDBMS_OUTPUT.PUT_LINE('Mai multe linii!');

WHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('Alta eroare!');

END;

ComandaSELECT INTO trebuie s obin exact o singur nregistrare?ComandaSELECT poate fi utilizat n PL/SQL fr clauza INTO?

Exemplul 3.6

VARIABLE h_clasificare VARCHAR2VARIABLE h_categorie NUMBER

BEGINSELECT clasificare, id_categorieINTO :h_clasificare, :h_categorieFROM clasific_clientiWHERE id_client = 82;

EXCEPTIONWHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('Clientul nu exista!');WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('Alta eroare!');END;/

PRINT h_clasificarePRINT h_categorie


8/19



ComandaSELECT poate fi utilizat ntr -o procedur Microsoft T-SQL frclauza INTO ?

n Microsoft T-SQL comandaSELECT cu clauza INTO funcioneaz la felca i n PL/SQL ?

ComandaSELECT poate fi utilizat ntr -o procedur MySQL fr clauza INTO?

Ce opiuni permite comandaSELECT INTO n MySQL?

n cadrul acestui curs vor fi considerate corecte i punctate doar soluiileimplementate n PL/SQL .

3.6.2. ComenzileI NSERT

,UPDATE

,DELETE

Exemplul 3.7

BEGINDELETE FROM clasific_clienti WHERE id_client=209;

INSERT INTO clasific_clienti VALUES (209,2,1,null);

UPDATE clasific_clientiSET clasificare = 'D'WHERE id_client = 209;

COMMIT;END;

Exemplul 3.8 vezi curs

Un bloc PL/SQL poate conine mai multe comenziCOMMIT , SAVEPOINT sau ROLLBACK ?


9/19



3.7. Instruciuni PL/SQL

Instruciunea de atribuire (:=) Instruciuni condiionale ( IF , CASE ) Instruciuni iterative ( LOOP , WHILE , FOR ) Instruciuni de salt (GOTO , EXIT, CONTINUE ) Instruciunea vid ( NULL)

3.7.1. Instruciunea de atribuire

variabila := expresie;

Variabilele care sunt declarate NOT NULL trebuie iniializatela declarare.o Codul din partea stng a exemplului de mai jos va genera eroarea

PLS-00218: a variable declared NOT NULL must have an initialization

assignment

Exemplul 3.9

DECLARE

x NUMBER(2) NOT NULL;

BEGINx:=2;DBMS_OUTPUT.PUT_LINE(x);

END;

DECLARE

x NUMBER(2) NOT NULL :=2;

BEGINDBMS_OUTPUT.PUT_LINE(x);END;

3.7.2. Instruciunea condiional I F

IF expresie_boolean THEN comand [ comand ]...

[ELSIF expresie_boolean THEN comand [ comand ]...]

[ELSIF expresie_boolean THEN comand [ comand ]...]...

[ELSE comand [ comand ]...]END IF;

Comenzile dininstruciune sunt executatedac expresia boolean corespunztoareare valoareTRUE . n caz contrar (expresia boolean are valoarea FALSE sau NULL),secvena nu este executat.

Instruciunea IF poate conine mai multe clauze ELSIF , dar o singur clauz ELSE .Aceasta se refer la ultima clauz ELSIF .


10/19



Exemplul 3.10

DECLAREv_nr NATURAL;v_clasificare CHAR(1) := UPPER('&p_clasificare');

BEGINSELECT COUNT(*) INTO v_nrFROM clasific_clientiWHERE clasificare = v_clasificare;

IF v_nr=0THEN

DBMS_OUTPUT.PUT_LINE('Nu exista clienti de '||'tipul ' || v_clasificare);

ELSEDBMS_OUTPUT.PUT_LINE('Exista ' ||v_nr ||

' clienti de tipul '|| v_clasificare);END IF;END;

Exemplul 3. 11


BEGINSELECT COUNT(*) INTO v_nrFROM clasific_clientiWHERE clasificare = v_clasificareAND id_categorie = 1;

IF v_nr=0THEN

DBMS_OUTPUT.PUT_LINE('Nu exista clienti de '||'tipul '|| v_clasificare);

ELSE

IF v_nr =1THENDBMS_OUTPUT.PUT_LINE('Exista 1 client '||

'de tipul '|| v_clasificare);ELSE

DBMS_OUTPUT.PUT_LINE('Exista ' ||v_nr || ' clienti de tipul '|| v_clasificare);

END IF;

END IF;END;

Exemplul 3. 12 vezi curs


11/19



3.7.3. Instruciunea condiional CASE

[]CASE selector

WHEN valoare_1_selector THEN secven _comenzi_1;WHEN valoare_2_selector THEN secven _comenzi_2; WHEN valoare_n_selector THEN secven _comenzi_n;[ELSE secven _comenzi;]

END CASE [eticheta];

Selectorul este o expresie a crei valoare este evaluat o singur dat i este utilizat pentru a selecta una dintre alternativele specificate prin clauzeleWHEN .

o Poate avea orice tip PL/SQL , cu excepia tipurilor BLOB , BFILE i tipuridefinite de utilizator.

Dac valoarea selectorului este egal cuvaloare_k_selector , atunci sunt executatecomenzile cuprinse n secven _comenzi_k i comandaCASE se ncheie.

o Valoare_k_selector poate avea orice tip PL/SQL , cu excepia tipurilor BLOB , BFILE i tipuri definite deutilizator.

Secvena de comenzi din clauza ELSE este executat doar dacselectorul nu areniciuna dintre valorile cuprinse n clauzeleWHEN .

o Clauza ELSE este opional. o Dac aceast clauz lipsete i selectorul nu are niciuna dintre valorile

specificate n clauzeleWHEN , atunci pare eroareaCASE_NOT_FOUND .

Exemplul 3. 13


BEGINSELECT COUNT(*) INTO v_nrFROM clasific_clientiWHERE clasificare = v_clasificareAND id_categorie = 1;

CASE v_nrWHEN 0 THEN

DBMS_OUTPUT.PUT_LINE('Nu exista clienti de ' ||'tipul '|| v_clasificare);

WHEN 1 THENDBMS_OUTPUT.PUT_LINE('Exista 1 client ' ||

'de tipul '|| v_clasificare);


12/19



ELSEDBMS_OUTPUT.PUT_LINE('Exista ' ||v_nr ||

' clienti de tipul '|| v_clasificare);

END CASE;

END;

ComandaCASE permite o form alternativ:

[]CASE

WHEN expresie_boolean_1 THEN secven _comenzi_1;WHEN expresie_boolean_2 THEN secven _comenzi_2; WHEN expresie_boolean_n THEN secven _comenzi_n;

[ELSE secven _comenzi;]END CASE [eticheta];

o Selectorul lipsete.o Fiecare clauzWHEN conine o expresie boolean.

o Dac expresie boolean expresie_boolean_k are valoareaTRUE , atunci suntexecutate comenzile cuprinse n secven_comenzi_k i comandaCASE sencheie.

o Secvena decomenzi din clauza ELSE este executat doar dacnicio expresie boolean din clauzeleWHEN nu are valoareTRUE .

o i n acest caz clauza ELSE este opional. Dac aceast clauz lipsete inicioexpresie boolean din clauzeleWHEN nu are valoareTRUE , atunci pareeroareaCASE_NOT_FOUND .

Exemplul 3. 14 vezi curs

Nu confundai comandaCASE din PL/SQL cu expresiaCASE dinSQL.

Exemplul 3.15 5 - vezi curs

ExpresiaCASE are sintaxa similar comenziiCASE , dar: clauzeleWHEN nu se termin prin caracterul ;; n clauzeleWHEN nu se realizeaz atribuiri; clauza END nu include cuvntul cheieCASE .

Exemplul 3.16 - vezi curs


13/19



3.7.4. Instruciunea iterativ LOOP

LOOPsecven _de_comenzi;

END LOOP;

Este denumit ciclare simpl. Comenzile incluse ntre cuvintele cheie LOOP i END LOOP sunt executate cel puin o dat.

Pentru a nu cicla la infinit trebuie utilizatcomanda EXIT .

Exemplul 3.17

DECLAREcod_ascii NUMBER := ASCII('A');

BEGIN

LOOPDBMS_OUTPUT.PUT(CHR(cod_ascii) || ' ');cod_ascii := cod_ascii + 1;-- EXIT;EXIT WHEN cod_ascii > ASCII('E');

/* IF cod_ascii > ASCII('E') THEN EXIT;END IF; */

END LOOP;

DBMS_OUTPUT.NEW_LINE;DBMS_OUTPUT.PUT_LINE('Iesire cand am ajuns la '||

CHR(cod_ascii));END;

Pentru atransfera controlul iteraiei urmtoarese utilizeaz comandaCONTINUE .

Exemplul 3.18 - vezi curs

3.7.5. Instruciunea iterativ W H I L E

WHILE condiie LOOP secven _de_comenzi;

END LOOP;

Estedenumit ciclare condiionat. Comenzile incluse ntre cuvintele cheie LOOP i END LOOP sunt executate atta

timp ct condiia are valoareaTRUE . Condiia este evaluat la nceputul fiecrei iteraii.


14/19



Exemplul 3.19

DECLAREi NATURAL := 1;

BEGIN

WHILE i


15/19



Exemplul 3.20

BEGIN

FOR i IN 1..10 LOOPDBMS_OUTPUT.PUT(i**2|| ' ');

END LOOP;DBMS_OUTPUT.NEW_LINE;

END;/

BEGIN

FOR i IN REVERSE 1..10 LOOPDBMS_OUTPUT.PUT(i**2|| ' ');

END LOOP;

DBMS_OUTPUT.NEW_LINE;END;/

BEGIN

FOR i IN REVERSE 1..10 LOOPDBMS_OUTPUT.PUT(i**2|| ' ');CONTINUE WHEN i


16/19



o Un bloc care nu are nicio aciune. Exemplul 3.21

DECLAREx NUMBER(2) NOT NULL :=2;

BEGIN

NULL;

END;

o Captarea unei excepii pentru care nu se realizeaz nicio aciune. Exemplul 3.22

DECLARE

v_clasificare clasific_clienti.clasificare%TYPE;v_categorie clasific_clienti.id_categorie%TYPE;v_client clasific_clienti.id_categorie%TYPE := 978;

BEGINSELECT clasificare, id_categorieINTO v_clasificare, v_categorieFROM clasific_clientiWHERE id_client = v_client;DBMS_OUTPUT.PUT_LINE(v_categorie || ' '

|| v_clasificare);

EXCEPTIONWHEN NO_DATA_FOUND THEN

-- DBMS_OUTPUT.PUT_LINE('Nicio linie!');NULL;

WHEN TOO_MANY_ROWS THENDBMS_OUTPUT.PUT_LINE('Mai multe linii!');

WHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('Alta eroare!');

END;


17/19



o Salt la o etichet dup care nu urmeaz nicio instruciune executabil (deexemplu urmeaz END sau END IF ). Urmtoarele 3 exemple ilustreaz opiuni posibile.

Exemplul 3.23

DECLAREi INT(1);

BEGINFOR i in 1..5 loop

IF i=3 THEN

GOTO eticheta;

ELSEDBMS_OUTPUT.PUT_LINE('i='||i);

END IF;END LOOP;

--instructiunea NULL nu este necesaraDBMS_OUTPUT.PUT_LINE('STOP cand i='||i);

END;

DECLAREj INT(1);

BEGINFOR i in 1..5 loop

j:=i;IF i=3 THEN

GOTO eticheta;


END IF;END LOOP;

--instructiunea NULL nu este necesaraDBMS_OUTPUT.PUT_LINE('STOP cand i='||j);

END;


18/19



BEGIN

FOR i in 1..5 loopIF i=3 THEN

DBMS_OUTPUT.PUT_LINE('STOP cand i='||i);

GOTO eticheta;ELSE

DBMS_OUTPUT.PUT_LINE('i='||i);END IF;

END LOOP;

--instructiunea NULL este necesara

NULL;

END;

Este des utilizat n instruciunile condiionale pentru a sugera cntr-un anumit caznu se ntmpl nimic.

BEGIN

FOR i in 1..5 loop

IF i=3 THENNULL;


END IF;END LOOP;

END;

3.7.8. Instruciunea de salt EXIT

EXIT [etichet ] [ WHEN condiie];

Permiteieirea dintr -un ciclu. (Exemplele3.17 i 3.18 ). Controlul trece fie la prima instruciune situat dup clauza END LOOP

corespunztoare, fie dup instruciunea LOOP avnd etichetaspecificat.

3.7.9. Instruciunea de salt CONTINUE

CONTINUE [WHEN con diie ];

Permite transferarea controlului iteraiei urmtoare. (Exemplele3.18 , 3.19 i 3.20 ).


19/19


3.7.10. Instruciunea de salt GOTO

GOTO nume_eticheta;

Permite saltulnecondiionat la o instruciune executabil sau la nceputul unui bloccare are eticheta specificat n comand. (Exemplul3.23 ).

Nu este permis saltul:

o n interiorul unui bloc (subbloc);

o n interiorul unei comenzi IF , CASE sau LOOP ;

o de la o clauz a comenziiCASE , la alt clauz a aceleai comenzi;o de la tratarea unei excepii, n blocul curent;o n exteriorul unui subprogram.

Bibliografie

1. Programare avansat n Oracle9i , I. Popescu, A. Alecu, L. Velcescu, G.Florea (Mihai), Ed. Tehnic (2004)

2. Oracle Database PL/SQL Language Reference 11g Release 2 , OracleOnline Documentation (2012)

3. Oracle Database 11g : PL/SQL Fundamentals , Student Guide , OracleUniversity (2009)

4. MySQL Online Documentation (http://dev.mysql.com ) 5. Microsoft Online Documentation (http://msdn.microsoft.com )
http://dev.mysql.com/doc/refman/5.0/en/select-into.htmlhttp://dev.mysql.com/doc/refman/5.0/en/select-into.htmlhttp://dev.mysql.com/doc/refman/5.0/en/select-into.htmlhttp://dev.mysql.com/doc/refman/5.0/en/select-into.html

sgbd3 - plsql blocuri variabile instructiuni

Documents