Folosirea dinamica a comenzilor SQL

Folosirea dinamica a comenzilor SQL

SQL dinamic este o parte integranta a limbajului SQL care permite folosirea dinamica a comenzilor sale in proceduri stocate sau in blocuri anonime. Spre deosebire de comenzile statice, care nu se schimba in timp real, comenzile dinamice se schimba de la o executie la alta. Comenzile dinamice SQL pot depinde de anumite valori de intrare furnizate de utilizator sau de procesarea realizata in programul aplicatie. Ele nu sunt incorporate in programul sursa, ci sunt depuse in siruri de caractere.

SQL dinamic este o tehnica de programare care permite construirea dinamica a comenzilor la momentul executiei (adica, direct in faza de executie a blocului PL/SQL). Textul comenzii nu este cunoscut la compilare. De exemplu, se creeaza o procedura care opereaza asupra unui tabel al carui nume este cunoscut doar cand se executa procedura. In momentul compilarii este cunoscuta definitia tabelelor, dar nu si numele acestora. Exista aplicatii (de exemplu, legate de data warehouse) in care la fiecare unitate de timp (de exemplu, sfert de ora) sunt generate noi tabele, toate avand aceeasi structura.

Utilitatea tehnicii SQL dinamic este justificata de motive majore, dintre care se remarca:

necesitatea de a executa in PL/SQL, comenzi SQL care nu pot fi apelate in codul PL/SQL (de exemplu, CREATE, DROP, GRANT, REVOKE, ALTER SESSION, SET ROLE);

necesitatea unei flexibilitati in tratarea comenzilor (de exemplu, posibilitatea de a avea diferite conditii in clauza WHERE a comenzii SELECT);

necunoasterea completa, la momentul implementarii, a comenzii SQL care trebuie executata.

Pentru executia dinamica a comenzilor SQL in PL/SQL exista doua tehnici:

utilizarea pachetului DBMS_SQL;

SQL dinamic nativ.

Daca s-ar face o comparatie intre SQL dinamic nativ si functionalitatea pachetului DBMS_SQL, se poate sublinia ca SQL dinamic nativ:

este mai usor de utilizat,

solicita mai putin cod,

este mai rapid,

poate incarca direct linii in recorduri PL/SQL,

suporta toate tipurile acceptate de SQL static in PL/SQL, inclusiv tipuri definite de utilizator.

Pachetul DBMS_SQL, in raport cu SQL dinamic nativ:

poate fi folosit in programe client-side;

suporta comenzi SQL mai mari de 32 KB;

permite incarcarea inregistrarilor (procedura FETCH_ROWS);

accepta comenzi cu clauza RETURNING pentru reactualizarea si stergerea de linii multiple;

suporta posibilitatile oferite de comanda DESCRIBE (procedura DESCRIBE_COLUMNS);

analizeaza validitatea unei comenzi SQL o singura data (procedura PARSE), permitand ulterior mai multe utilizari ale comenzii pentru diferite multimi de argumente.

SQL dinamic nativ a fost introdus in Oracle8i, asigurand plasarea de comenzi SQL dinamic in codul PL/SQL. Comanda de baza utilizata pentru procesarea dinamica nativa a comenzilor SQL si a blocurilor PL/SQL anonime este EXECUTE IMMEDIATE. Comanda are urmatoarea sintaxa:

EXECUTE IMMEDIATE sir_dinamic

  [INTO ]

  [USING [IN | OUT | IN OUT] argument_bind

          [, [IN | OUT | IN OUT] argument_bind .] ]

          INTO argument_bind [, argument_bind .] ];

sir_dinamic este o expresie (sir de caractere) care reprezinta o comanda SQL (fara caracter de terminare) sau un bloc PL/SQL (avand caracter de terminare);

def_variabila reprezinta variabila in care se stocheaza valoarea coloanei selectate;

record reprezinta inregistrarea in care se depune o linie selectata;

argument_bind, daca se refera la valori de intrare (IN) este o expresie (comanda SQL sau bloc PL/SQL), iar daca se refera la valori de iesire (OUT) este o variabila ce va contine valoarea selectata de comanda SQL sau de blocul PL/SQL.

INTO este folosita pentru cereri care intorc o singura linie, iar clauza USING pentru a retine argumentele de legatura.

Pentru procesarea unei cereri care returneaza mai multe linii sunt necesare instructiunile OPEN.FOR, FETCH si CLOSE.

Prin clauza RETURNING sunt precizate variabilele care contin rezultatele.

Observatii

SQL dinamic suporta toate tipurile SQL, dar nu accepta tipuri de date specifice PL/SQL (unica exceptie este tipul RECORD, care poate sa apara in clauza INTO).

In subprogramele PL/SQL pot sa fie executate dinamic comenzi SQL care se refera la obiecte apartinand unei baze de date distante.

In anumite situatii, o comanda LDD poate crea o interblocare. De exemplu, o procedura poate genera o interblocare daca in corpul procedurii exista o comanda care sterge chiar procedura respectiva. Prin urmare, niciodata nu pot fi utilizate comenzile ALTER sau DROP referitoare la un subprogram sau pachet in timp ce se lucreaza cu pachetul sau subprogramul respectiv.

Exemplu

Sa se construiasca o procedura care poate sterge orice tabel din baza de date. Numele tabelului sters este transmis ca parametru acestei proceduri.

CREATE PROCEDURE sterge_tabel (nume_tabel IN VARCHAR2)

AS

BEGIN

EXECUTE IMMEDIATE 'DROP TABLE :tab'

USING nume_tabel;

END;

La executia acestei proceduri va fi semnalata o eroare, deoarece nu pot fi utilizate variabile de legatura ca argument, pentru a transmite numele obiectelor dintr-o schema. Prin urmare comanda corecta este:

EXECUTE IMMEDIATE 'DROP TABLE ' || nume_tabel;

Exemplu

Inserarea unei linii intr-un tabel cu doua coloane.

CREATE PROCEDURE add_linie (nume_tabel VARCHAR2, id NUMBER,

nume VARCHAR2)

IS

BEGIN

EXECUTE IMMEDIATE 'INSERT INTO ' || nume_tabel ||

'VALUES (:1, :2)' USING id, nume;

END;

Exemplu

Valoarea null nu poate sa apara in clauza USING. Comanda urmatoare este incorecta.

EXECUTE IMMEDIATE 'UPDATE opera SET valoare = :x'

USING null;

Totusi, daca este necesara folosirea valorii null, pot fi utilizate variabile neinitializate. O solutie pentru corectarea erorii anterioare este data de secventa:

DECLARE

val_null NUMBER;

BEGIN

EXECUTE IMMEDIATE 'UPDATE opera SET valoare = :x'

USING val_null;

END;

Exemplu

Sa se utilizeze SQL dinamic pentru adaugarea unei coloane intr-un tabel.

CREATE PROCEDURE adaug_col (nume_tabel VARCHAR2,

col_spec VARCHAR2)

IS

instr VARCHAR2(100) := 'ALTER TABLE ' || nume_tabel

||' ADD ' || col_spec;

BEGIN

EXECUTE IMMEDIATE instr;

END;

Adaugarea efectiva a unei noi coloane se face prin:

EXECUTE adaug_col ('carte', val_actual NUMBER(7,2)')

Exemplu

Sa se obtina numarul operelor de arta din muzeu a caror valoare depaseste o limita precizata (cerere single-row).

CREATE OR REPLACE FUNCTION numar_opera (val_opera NUMBER)

RETURN NUMBER AS

sir_cerere VARCHAR2(500);

num_oper NUMBER;

BEGIN

sir_cerere :=

'SELECT COUNT(*) FROM opera ' ||

'WHERE valoare >= :alfa';

EXECUTE IMMEDIATE sir_cerere

INTO num_oper

USING val_opera;

RETURN num_oper;

END;

Exemplu

Exemplul care urmeaza furnizeaza o modalitate de utilizare corecta a argumentelelor in clauza USING.

a)      Pentru comenzi SQL, asocierea cu argumentele de legatura (bind) din clauza USING este prin pozitie.

sql_com := 'INSERT INTO alfa VALUES (:x, :x, :y, :x)';

EXECUTE IMMEDIATE sql_com USING a, a, b, a

b)      Pentru blocuri PL/SQL executate dinamic, asocierea cu argumentele bind din clauza USING se face prin nume.

DECLARE

x NUMBER := 7;

y NUMBER := 23;

v_bloc VARCHAR2(200);

BEGIN

v_bloc := 'BEGIN calcule(:a, :a, :b, :a); END;';

EXECUTE IMMEDIATE v_bloc USING x, y;

END;

In exemplul care urmeaza va fi ilustrat modul de utilizare a comenzii EXECUTE IMMEDIATE pentru executarea comenzilor LDD, comenzilor LMD de reactualizare si a blocurilor PL/SQL anonime. Sirul care trebuie executat poate fi un literal inclus intre apostrofuri (de exemplu, CREATE TABLE sau DROP TABLE) sau poate fi un sir de caractere (de exemplu, blocuri anonime). Caracterul ";" nu trebuie inclus decat pentru blocurile anonime.

Exemplu

DECLARE

v_sql_sir VARCHAR2(200);

v_plsql_bloc VARCHAR2(200);

BEGIN

-- creare tabel

EXECUTE IMMEDIATE

'CREATE TABLE model_tabel (col1 VARCHAR2(30))';

FOR contor IN 1..10 LOOP

v_sql_sir :=

'INSERT INTO model_tabel

VALUES (''Linia '' || contor)';

EXECUTE IMMEDIATE v_sql_sir;

END LOOP;

-- tipareste continut tabel utilizand un bloc anonim

v_plsql_bloc :=

'BEGIN

FOR cont IN (SELECT * FROM model_tabel) LOOP

DBMS_OUTPUT.PUT_LINE (cont.col1);

END LOOP;

END;';

-- executie bloc anonim

EXECUTE IMMEDIATE v_plsql_bloc;

-- sterge tabel

EXECUTE IMMEDIATE 'DROP TABLE model_tabel';

END;

Comanda EXECUTE IMMEDIATE poate fi utilizata si pentru executia unor comenzi in care intervin variabile de lagatura. Exemplul urmator ilustreaza aceasta situatie, marcand si modul de folosire a clauzei USING.

Exemplu:

DECLARE

v_sql_sir VARCHAR2(200);

v_plsql_bloc VARCHAR2(200);

BEGIN

v_sql_sir :=

'INSERT INTO opera (cod_opera, titlu, valoare)

VALUES (:cod, :descriere, :val)';

EXECUTE IMMEDIATE v_sql_sir USING 'c17', 'Modista', 15;

v_plsql_bloc :=

'BEGIN

UPDATE artist SET nume = ''Gauguin''

WHERE cod_artist = :xx;

END;';

EXECUTE IMMEDIATE v_plsql_bloc USING 'a37';

END;

Pentru executarea cererilor multiple (care intorc mai multe linii) este necesara o abordare similara celei descrise in cazul cursoarelor dinamice, prin utilizarea:

declaratiei tipului REF CURSOR si a unei variabile cursor bazata pe acest tip,

comenzilor OPEN - FOR, FETCH si CLOSE.

Exemplul care urmeaza prezinta maniera in care pot fi executate diferite cereri, utilizand SQL dinamic nativ.

Exemplu:

CREATE OR REPLACE PACKAGE nativ AS

TYPE t_ref IS REF CURSOR;

FUNCTION opera_cerere (p_clauza IN VARCHAR2)

RETURN t_ref;

FUNCTION opera_alta_cerere (p_stil IN VARCHAR2)

RETURN t_ref;

END nativ;

CREATE OR REPLACE PACKAGE BODY nativ AS

FUNCTION opera_cerere (p_clauza IN VARCHAR2)

RETURN t_ref IS

v_retur_cursor t_ref;

v_sql_comanda VARCHAR2(500);

BEGIN

v_sql_comanda := 'SELECT * FROM opera ' || p_clauza;

OPEN v_retur_cursor FOR v_sql_comanda;

RETURN v_retur_cursor;

END opera_cerere;

FUNCTION opera_alta_cerere (p_stil IN VARCHAR2)

RETURN t_ref IS

v_retur_cursor t_ref;

v_sql_comanda VARCHAR2(500);

BEGIN

v_sql_comanda := 'SELECT * FROM opera WHERE stil = :s';

OPEN v_retur_cursor FOR v_sql_comanda USING p_stil;

RETURN v_retur_cursor;

END opera_alta_cerere;

END nativ;

DECLARE

v_opera opera%ROWTYPE;

v_opera_cursor nativ.t_ref;

BEGIN

-- deschide cursor

v_opera_cursor :=

nativ.opera_cerere ('WHERE valoare < 1000000');

-- parcurge cursor si tipareste rezultate

DBMS_OUTPUT.PUT_LINE ('Urmatoarele opere au valoarea mai

mica de un milion de dolari');

LOOP

FETCH v_opera_cursor INTO v_opera;

EXIT WHEN v_opera_cursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(v_opera.titlu || ' ' ||

v_opera.stil);

END LOOP;

CLOSE v_opera_cursor;

-- se procedeaza similar pentru functia opera_alta_cerere

-- deschide cursor

v_opera_cursor :=

nativ.opera_alta_cerere ('impresionism');

-- parcurge cursor si tipareste rezultate

DBMS_OUTPUT.PUT_LINE ('Urmatoarele opere de arta apartin

impresionismului');

LOOP

FETCH v_opera_cursor INTO v_opera;

EXIT WHEN v_opera_cursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE (v_opera.titlu || ' ' ||

v_opera.valoare);

END LOOP;

CLOSE v_opera_cursor;

END;

Comanda EXECUTE IMMEDIATE poate fi utilizata pentru cereri care intorc o singura linie, cu sau fara variabile bind. Urmatorul exemplu prezinta, in doua cazuri, modul de implementare a acestei posibilitati.

Exemplu

DECLARE

v_sql_cerere VARCHAR2(200);

v_galerie galerie%ROWTYPE;

v_nume_galerie galerie.nume_galerie%TYPE;

BEGIN

-- selectare in variabila

v_sql_cerere :=

'SELECT nume_galerie ' ||

'FROM galerie ' ||

'WHERE cod_galerie = ''g73''';

EXECUTE IMMEDIATE v_sql_cerere INTO v_nume_galerie;

DBMS_OUTPUT.PUT_LINE (v_nume_galerie);

-- selectare in record, utilizand variabile bind

v_sql_cerere :=

'SELECT * ' ||

'FROM galerie ' ||

'WHERE nume_galerie = :nume_galerie';

EXECUTE IMMEDIATE v_sql_cerere INTO v_galerie

USING v_nume_galerie;

DBMS_OUTPUT.PUT_LINE (v_galerie.cladire);

END;

Structura Bulk bind permite executarea cererilor care returneaza mai multe linii, toate liniile returnate putand fi obtinute printr-o singura operatie. Puterea acestei structuri poate fi combinata cu facilitatile oferite de SQL dinamic. Utilizand comenzile FETCH, EXECUTE IMMEDIATE, FORALL, clauzele RETURNING INTO, COLLECT INTO si atributul cursor %BULK_ROWCOUNT se pot construi comenzi SQL care se executa dinamic utilizand tehnica bulk bind. Comenzile vor avea o structura adaptata pentru rezolvarea dinamica a comenzilor SQL.

In acest caz, comanda FETCH are forma sintactica:

FETCH cursor_dinamic

  BULK COLLECT INTO variabila [, variabila .];

De remarcat ca daca numarul variabilelor este mai mare decat numarul de coloane, Oracle va declansa o eroare.

Comanda FORALL va avea urmatoarea structura modificata:

FORALL index IN lim_inf .. lim_sup

  EXECUTE IMMEDIATE sir_dinamic

    USING argument_bind | argument_bind(index)

           [, argument_bind | argument_bind(index) .]

    [ BULK COLLECT

      INTO argument_bind [, argument_bind .] ];

Atributul sir_dinamic accepta comenzile INSERT, UPDATE si DELETE, dar nu comanda SELECT.

Liniile de valori returnate de comanda EXECUTE IMMEDIATE pot fi depuse intr-o colectie. Comanda isi modifica structura in urmatoarea maniera:

EXECUTE IMMEDIATE sir_dinamic

  [ [BULK COLLECT] INTO variabila [, variabila .]

  [USING argument_bind [, argument_bind .] ]

      BULK COLLECT INTO argument_bind [, argument_bind .] ];

Exemplu

Exemplul care urmeaza arata modul de utilizare a clauzei BULK COLLECT in comenzile FETCH si EXECUTE IMMEDIATE.

DECLARE

TYPE opera_ref IS REF CURSOR;

TYPE tab_num IS TABLE OF NUMBER;

TYPE car_tab IS TABLE OF VARCHAR2(30);

oper opera_ref;

num1 tab_num;

car1 car_tab;

num2 tab_num;

BEGIN

OPEN oper FOR 'SELECT cod_opera, valoare FROM opera';

FETCH oper BULK COLLECT INTO car1, num1;

CLOSE oper;

EXECUTE IMMEDIATE 'SELECT valoare FROM opera'

BULK COLLECT INTO num2;

END;

Numai comenzile INSERT, UPDATE si DELETE pot avea variabile bind ca argumente OUT.

Exemplu

DECLARE

TYPE tab IS TABLE OF VARCHAR2(60);

v_val tab;

bun NUMBER := 100000;

com_sql VARCHAR2(200);

BEGIN

com_sql := 'UPDATE opera SET valoare = :1

RETURNING titlu INTO :2';

EXECUTE IMMEDIATE com_sql

USING bun RETURNING BULK COLLECT INTO v_val;

END;

Pentru a utiliza variabile bind ca intrari intr-o comanda SQL (diferita de SELECT) se pot folosi comanda FORALL si clauza USING.

Exemplu

DECLARE

TYPE num IS TABLE OF NUMBER;

TYPE car IS TABLE OF VARCHAR2(30);

num1 num;

car1 car;

BEGIN

num1 := num(1, 2, 3, 4, 5);

FORALL i IN 1..5

EXECUTE IMMEDIATE

'UPDATE opera SET val = val*1.1

WHERE cod_opera = :1

RETURNING titlu INTO :2'

USING num1(i) RETURNING BULK COLLECT INTO car1;

.

END;

SQL dinamic poate fi utilizat pentru a compila cod PL/SQL.

Exemplu

CREATE PROCEDURE compilare_plsql (name VARCHAR2,

tip_plsql VARCHAR2,

optiune VARCHAR2 := NULL)

IS

instr VARCHAR2(200) := 'ALTER '|| tip_plsql ||

' ' || name || ' COMPILE';

BEGIN

IF optiune IS NOT NULL THEN

instr := instr || ' ' || optiune;

END IF;

EXECUTE IMMEDIATE instr;

END;

Exemple de apelare:

EXECUTE compilare_plsql ('listare', 'procedure')

EXECUTE compilare_plsql ('pachet', 'package', body')

Efectul este:

ALTER PROCEDURE listare COMPILE

ALTER PACKAGE pachet COMPILE BODY

SQL dinamic poate fi apelat si din cadrul altor limbaje.

Limbajele C/C++ apeleaza SQL dinamic prin OCI (Oracle Call Interface) sau poate fi utilizat un precompilator Pro*C/C++ pentru adaugarea comenzilor dinamice SQL la codul C.

Limbajul Cobol poate utiliza comenzi dinamice SQL folosind un precompilator Pro*Cobol.

Limbajul Java prin intermediul lui JDBC (interfata pentru conectarea limbajului la baze de date relationale) poate utiliza comenzi dinamice SQL.

Exemplu

Sa se stearga toti indecsii din schema personala.

BEGIN

FOR j IN (SELECT INDEX_NAME

FROM USER_INDEXES) LOOP

EXECUTE IMMEDIATE

'DROP INDEX' || j.INDEX_NAME ||' ';

END LOOP;

END;

Presupunand ca in schema sunt 500 indecsi, executia blocului va genera eroarea ORA - 01555, deoarece nu exista suficient spatiu de lucru.

De ce? Raspunsul este simplu: COMMIT-ul generat de LDD (stergerea unui index apartine de LDD) si faptul ca LDD modifica vizualizarile DD prin stergerea indecsilor.

Cum trebuie procedat in acest caz? Trebuie ca sa nu fie deschis cursorul in timpul permanentizarii.

DECLARE

i_index VARCHAR2(30);

BEGIN

LOOP

SELECT INDEX_NAME INTO i_index

FROM USER_INDEXES

WHERE ROWNUM = 1;

EXECUTE IMMEDIATE 'DROP INDEX' || i_index;

END LOOP;

EXCEPTION

WHEN NO_DATA _FOUND THEN NULL;

END;