Oracle Tutorial PL/SQL Dynamic SQL: Executați imediat și DBMS_SQL

Ce este SQL dinamic?

Dinamic SQL este o metodologie de programare pentru generarea și rularea instrucțiunilor la momentul execuției. Este utilizată în principal pentru a scrie programe flexibile și de uz general, în care instrucțiunile SQL sunt create și executate la momentul execuției pe baza cerințelor, de exemplu, atunci când numele tabelelor, listele de coloane sau condițiile WHERE nu sunt cunoscute până la rularea programului.

Modalități de a scrie SQL dinamic

PL/SQL oferă două modalități de scriere SQL dinamic:

1. NDS – SQL dinamic nativ (instrucțiunile EXECUTE IMMEDIATE și OPEN-FOR)
2. DBMS_SQL (un pachet furnizat)

Regula generală este simplă: dacă numărul și tipurile de date ale variabilelor de intrare și ieșire sunt cunoscute la momentul compilării, utilizați SQL dinamic nativ deoarece este mai rapid și necesită mai puțin cod. Când aceste informații sunt cunoscute doar la momentul execuției, utilizați pachetul DBMS_SQL.

NDS (Native Dynamic SQL) – Executare imediată

SQL dinamic nativ este cea mai ușoară modalitate de a scrie SQL dinamic. Folosește comanda EXECUTE IMMEDIATE pentru a crea și executa codul SQL la momentul execuției. Pentru a utiliza această abordare, tipul de date și numărul de variabile utilizate la momentul execuției trebuie cunoscute în prealabil. De asemenea, oferă performanțe mai bune și o complexitate mai mică în comparație cu DBMS_SQL.

Sintaxă

EXECUTE IMMEDIATE dynamic_sql_string
[INTO {variable[, variable]... | record}]
[USING [IN | OUT | IN OUT] bind_argument[, ...]]
[RETURNING INTO bind_argument[, ...]];

• șir_sql_dinamic: O expresie de tip șir de caractere (VARCHAR2 sau CHAR, nu NVARCHAR2/NCHAR) care conține o singură instrucțiune SQL sau un bloc PL/SQL.
• Clauza INTO: Opțional. Se utilizează numai când SQL-ul dinamic este un SELECT pe un singur rând; capturează valorile returnate în variabile sau într-o înregistrare. Fiecare coloană selectată necesită o variabilă compatibilă cu tipul.
• Clauza USING: Opțional. Furnizează variabile de legătură. Modul implicit este IN; OUT și IN OUT sunt folosite pentru a primi valori înapoi.
• Clauza RETURNING INTO: Se utilizează cu instrucțiuni DML care conțin o clauză RETURNING, pentru a captura valorile rândurilor afectate în argumente de legare.

Exemplu 1: În acest exemplu, preluăm datele din tabela emp pentru emp_no '1001' folosind o instrucțiune NDS cu o variabilă bind.

DECLARE
   lv_sql       VARCHAR2(500);
   lv_emp_name  VARCHAR2(50);
   ln_emp_no    NUMBER;
   ln_salary    NUMBER;
   ln_manager   NUMBER;
BEGIN
   lv_sql := 'SELECT emp_name, emp_no, salary, manager FROM emp WHERE emp_no = :empno';
   EXECUTE IMMEDIATE lv_sql
      INTO lv_emp_name, ln_emp_no, ln_salary, ln_manager
      USING 1001;
   DBMS_OUTPUT.PUT_LINE('Employee Name: '   || lv_emp_name);
   DBMS_OUTPUT.PUT_LINE('Employee Number: ' || ln_emp_no);
   DBMS_OUTPUT.PUT_LINE('Salary: '          || ln_salary);
   DBMS_OUTPUT.PUT_LINE('Manager ID: '      || ln_manager);
END;
/

producție

Employee Name  : XXX
Employee Number: 1001
Salary         : 15000
Manager ID     : 1000

Code Explicaţie:

• Liniile 2-6: Declararea variabilelor.
• Linia 8: Încadrarea comenzii SQL la momentul execuției. Comanda SQL conține variabila de legătură ':empno' în condiția WHERE.
• Liniile 9-11: Executarea comenzii SQL încadrată cu EXECUTE IMMEDIATE. Variabilele clauzei INTO conțin valorile extrase, iar clauza USING furnizează valoarea pentru variabila de legătură :empno.
• Liniile 12-15: Afișarea valorilor obținute.

Utilizarea SQL dinamic pentru DDL

PL/SQL static nu poate rula DDL precum CREATE, ALTER sau DROP direct. EXECUTE IMMEDIATE rezolvă această problemă prin construirea instrucțiunii ca șir de caractere, ceea ce este util și atunci când un nume de obiect este furnizat la momentul execuției:

DECLARE
   l_table_name VARCHAR2(30) := 'my_table';
   l_sql_stmt   VARCHAR2(200);
BEGIN
   l_sql_stmt := 'CREATE TABLE ' || l_table_name ||
                 ' (id NUMBER, name VARCHAR2(30))';
   EXECUTE IMMEDIATE l_sql_stmt;
END;
/

Numele obiectelor (tabel, coloană, schemă) nu pot fi transmise ca variabile de legătură, deci trebuie concatenate în șir. Validați întotdeauna astfel de intrări, de exemplu cu DBMS_ASSERT.SIMPLE_SQL_NAME, pentru a evita injecția SQL.

DBMS_SQL pentru SQL dinamic

PL/SQL oferă pachetul DBMS_SQL pentru lucrul cu SQL dinamic atunci când structura instrucțiunii nu este cunoscută până la momentul execuției. Procesul de creare și executare a SQL-ului dinamic implică următorii pași:

• DESCHIDE CURSORUL: SQL-ul dinamic se execută ca un cursorPentru a executa instrucțiunea SQL, trebuie mai întâi să deschidem cursorul.
• PARSE SQL: Analizează codul SQL dinamic. Aceasta verifică sintaxa și menține interogarea gata de execuție.
• Valori VARIABILE BIND: Atribuiți valorile pentru variabilele de legare, dacă există.
• DEFINIRE COLOANĂ: Definiți fiecare coloană folosind poziția sa relativă în instrucțiunea select.
• A EXECUTA: Executați interogarea analizată.
• VALORI DE PRELUARE: Preia valorile executate.
• ÎNCHIDE CURSOR: După ce rezultatele sunt obținute, închideți cursorul.

Exemplu 1: În acest exemplu, preluăm datele din tabela emp pentru emp_no '1001' folosind o instrucțiune DBMS_SQL. Blocul EXCEPTION închide cursorul chiar dacă apare o eroare.

DECLARE
   lv_sql            VARCHAR2(500);
   lv_emp_name       VARCHAR2(50);
   ln_emp_no         NUMBER;
   ln_salary         NUMBER;
   ln_manager        NUMBER;
   ln_cursor_id      NUMBER;
   ln_rows_processed NUMBER;
BEGIN
   lv_sql := 'SELECT emp_name, emp_no, salary, manager FROM emp WHERE emp_no = :empno';
   ln_cursor_id := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(ln_cursor_id, lv_sql, DBMS_SQL.NATIVE);
   DBMS_SQL.BIND_VARIABLE(ln_cursor_id, ':empno', 1001);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 1, lv_emp_name, 50);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 2, ln_emp_no);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 3, ln_salary);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 4, ln_manager);
   ln_rows_processed := DBMS_SQL.EXECUTE(ln_cursor_id);
   LOOP
      IF DBMS_SQL.FETCH_ROWS(ln_cursor_id) = 0 THEN
         EXIT;
      ELSE
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 1, lv_emp_name);
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 2, ln_emp_no);
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 3, ln_salary);
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 4, ln_manager);
         DBMS_OUTPUT.PUT_LINE('Employee Name: '   || lv_emp_name);
         DBMS_OUTPUT.PUT_LINE('Employee Number: ' || ln_emp_no);
         DBMS_OUTPUT.PUT_LINE('Salary: '          || ln_salary);
         DBMS_OUTPUT.PUT_LINE('Manager ID: '      || ln_manager);
      END IF;
   END LOOP;
   DBMS_SQL.CLOSE_CURSOR(ln_cursor_id);
EXCEPTION
   WHEN OTHERS THEN
      DBMS_SQL.CLOSE_CURSOR(ln_cursor_id);
END;
/

producție

Employee Name  : XXX
Employee Number: 1001
Salary         : 15000
Manager ID     : 1000

Code Explicaţie:

• Liniile 1-8: Declararea variabilelor.
• Linia 10: Încadrarea instrucțiunii SQL.
• Linia 11: Deschiderea cursorului folosind DBMS_SQL.OPEN_CURSOR, care returnează id-ul cursorului deschis.
• Linia 12: După ce cursorul este deschis, codul SQL este analizat.
• Linia 13: Valoarea de legare „1001” este atribuită în locul lui „:empno”.
• Liniile 14-17: Definirea coloanelor după poziția lor relativă: (1) nume_angajat, (2) număr_angajat, (3) salariu, (4) manager.
• Linia 18: Executarea interogării cu DBMS_SQL.EXECUTE, care returnează numărul de înregistrări procesate.
• Liniile 19-32: Preluarea înregistrărilor într-o buclă. FETCH_ROWS returnează 0 când nu mai rămân rânduri, ceea ce iese din buclă.
• Bloc EXCEPȚII: Asigură că cursorul este închis, astfel încât cursoarele deschise să nu aibă scurgeri dacă este generată o eroare.

NDS vs DBMS_SQL: Când să folosiți care

Ambele abordări execută SQL în timpul execuției, dar se potrivesc situațiilor diferite:

• Folosește SQL dinamic nativ (EXECUTE IMMEDIATE / OPEN-FOR) când numărul și tipurile de date ale intrărilor și ieșirilor sunt cunoscute la momentul compilării. Este mai rapid, mai ușor de citit și necesită mai puțin cod.
• Utilizați DBMS_SQL când structura este necunoscută până la momentul execuției, de exemplu o interogare al cărei număr de coloane selectate sau variabile de legătură variază, cunoscută sub numele de SQL dinamic metoda 4, sau o instrucțiune prea mare pentru a încăpea într-o singură variabilă VARCHAR2 de 32K.

Întrebări frecvente