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

prin: Fiona Brown Fiona Brown
Hours Actualizat

Ce este SQL dinamic?

Dinamic SQL este o metodologie de programare pentru generarea și rularea instrucțiunilor în timpul execuției. Este folosit în principal pentru a scrie programe de uz general și flexibile în care instrucțiunile SQL vor fi create și executate în timpul execuției pe baza cerințelor.

Modalități de a scrie SQL dinamic

PL/SQL oferă două moduri de a scrie SQL dinamic

  1. NDS – SQL dinamic nativ
  2. DBMS_SQL

NDS (Native Dynamic SQL) – Executare imediată

Native Dynamic SQL este modalitatea mai ușoară de a scrie SQL dinamic. Utilizează comanda „EXECUTE IMMEDIATE” pentru a crea și executa SQL-ul în timpul execuției. Dar pentru a utiliza acest mod, tipul de date și numărul de variabile care vor fi utilizate la un timp de execuție trebuie să fie cunoscute înainte. De asemenea, oferă performanțe mai bune și mai puțină complexitate în comparație cu DBMS_SQL.

Sintaxă 

EXECUTE IMMEDIATE(<SQL>)
[INTO<variable>]
[USING <bind_variable_value>]
  • Sintaxa de mai sus arată comanda EXECUTE IMMEDIATE.
  • Clauza INTO este opțională și utilizată numai dacă SQL-ul dinamic conține o instrucțiune select care preia valori. Tipul variabilei trebuie să se potrivească cu tipul variabilei din instrucțiunea select.
  • Clauza USING este opțională și este utilizată numai dacă SQL-ul dinamic conține orice variabilă de legătură.

Exemplu 1: În acest exemplu, vom prelua datele din tabelul emp pentru emp_no '1001' utilizând instrucţiunea NDS.

NDS - Executare imediată

DECLARE
lv_sql VARCHAR2(500);
lv_emp_name VARCHAR2(50):
ln_emp_no NUMBER;
ln_salary NUMBER;
ln_manager NUMBER;
BEGIN
ly_sql:=;SELECT emp_name,emp_no,salary,manager FROM emp WHERE
emp_no=:empmo:;
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_salaiy);
Dbms_output.put_line('Manager ID:‘||ln_manager);
END;
/

producție

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

Explicația codului:

  • Linia de cod 2-6: Declararea variabilelor.
  • Linia de cod 8: Încadrarea SQL în timpul rulării. SQL conține variabila bind în condiția unde „:empno”.
  • Linia de cod 9: Executarea textului SQL încadrat (care se face în linia de cod 8) folosind comanda NDS „EXECUTE IMMEDIATE”
  • Variabilele din clauza „INTO” (lv_emp_name, ln_emp_no, ln_salary, ln_manager) sunt folosite pentru a păstra valorile preluate din interogarea SQL (emp_name, emp_no, salariu, manager)
  • Clauza „USING” oferă valorile variabilei de legătură în interogarea SQL (:emp_no).
  • Linia de cod 10-13: Afișarea valorilor preluate.

DBMS_SQL pentru SQL dinamic

PL/SQL oferă pachetul DBMS_SQL care vă permite să lucrați cu SQL dinamic. Procesul de creare și executare a SQL dinamic conține următorul proces.

  • CURSOR DESCHIS: SQL-ul dinamic se va executa în același mod ca a cursor. Deci, pentru a executa instrucțiunea SQL, trebuie să deschidem cursorul.
  • PARSE SQL: Următorul pas este analizarea SQL-ului dinamic. Acest proces va verifica doar sintaxa și va menține interogarea gata de executare.
  • BIND VARIABLE Valori: Următorul pas este alocarea valorilor pentru variabilele de legătură, dacă există.
  • DEFINIȚI COLONA: Următorul pas este definirea coloanei folosind pozițiile sale relative în instrucțiunea select.
  • A EXECUTA: Următorul pas este să executați interogarea analizată.
  • PRELUARE VALORI: Următorul pas este preluarea valorilor executate.
  • ÎNCHIS CURSORUL: Odată ce rezultatele sunt preluate, cursorul ar trebui să fie închis.

Exemplu 1: În acest exemplu, vom prelua datele din tabelul emp pentru emp_no '1001' utilizând instrucţiunea DBMS_SQL.

DBMS_SQL pentru SQL dinamic

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;
BEGIN
lv_sql:=‘SELECT emp_name,emp_no,salary,manager FROM emp WHERE
emp_no=:empmo’;
in_cursor_id:=DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(ln_cursor_id,lv_sql,DBMS_SQL.NATIVE);

DBMS_SQL.BIXD_VARLABLE(ln_cursor_id,:‘empno‘,1001);

DBMS_SQL.DEFINE_COLUMN(ln_cursor_ici,1,ln_emp_name);
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);

DBMS_SQL pentru SQL dinamic

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,In_salary);
DBMS_SQL.COLUMN_VALUE(ln_cursor_id,4,In_manager);
Dbms_output.put_line('Employee Name:‘||lv_emp_name); 
Dbms_output.put_line('Employee Number:l‘||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_ClIRSOR(ln_cursor_id);

END:
/

producție

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

Explicația codului:

  • Linia de cod 1-9: Declarație variabilă.
  • Linia de cod 10: Încadrarea instrucțiunii SQL.
  • Linia de cod 11: Deschiderea cursorului folosind DBMS_SQL.OPEN_CURSOR. Va returna id-ul cursorului care este deschis.
  • Linia de cod 12: După deschiderea cursorului, SQL-ul este analizat.
  • Linia de cod 13: Variabila de legătură „1001” este atribuită id-ului cursorului în loc de „:empno”.
  • Linia de cod 14-17: Definirea numelui coloanei pe baza poziției lor relative în instrucțiunea SQL. În cazul nostru, poziția relativă este (1) emp_name, (2) emp_no (3) salariu (4) manager. Deci, pe baza acestei poziții, definim variabila țintă.
  • Linia de cod 18: Executarea interogării utilizând DBMS_SQL.EXECUTE. Returnează numărul de înregistrări procesate.
  • Linia de cod 19-33: Preluarea înregistrărilor folosind o buclă și afișarea acelorași.
  • Linia de cod 20: DBMS_SQL.FETCH_ROWS va prelua o înregistrare din rândurile procesate. Poate fi apelat în mod repetat pentru a prelua toate rândurile. Dacă nu poate prelua rândurile, va returna 0, ieșind astfel din buclă.

Rezumat

În această secțiune, am discutat despre SQL dinamic și despre modalitățile de a executa SQL DYNAMIC. Am văzut, de asemenea, diferiții pași în executarea SQL-ului dinamic în ambele moduri. Am văzut, de asemenea, exemplele în care același scenariu este gestionat atât în ​​mod NDS, cât și în DBMS_SQL, pentru a efectua execuția în timpul execuției.