Oracle PL/SQL dynamische SQL-zelfstudie: onmiddellijk uitvoeren en DBMS_SQL

Door: Fiona Bruin Fiona Bruin
Hours Bijgewerkt

Wat is dynamische SQL?

Dynamisch SQL is een programmeermethodologie voor het genereren en uitvoeren van instructies tijdens runtime. Het wordt voornamelijk gebruikt om algemene en flexibele programma's te schrijven, waarbij de SQL-instructies tijdens runtime worden gemaakt en uitgevoerd op basis van de vereisten.

Manieren om dynamische SQL te schrijven

PL/SQL biedt twee manieren om dynamische SQL te schrijven

  1. NDS – Native dynamische SQL
  2. DBMS_SQL

NDS (Native Dynamic SQL) – Direct uitvoeren

Native Dynamic SQL is de eenvoudigere manier om dynamische SQL te schrijven. Het gebruikt de opdracht 'EXECUTE IMMEDIATE' om de SQL tijdens runtime te maken en uit te voeren. Maar om deze manier te gebruiken, moeten het datatype en het aantal variabelen dat tijdens runtime moet worden gebruikt, vooraf bekend zijn. Het biedt ook betere prestaties en minder complexiteit in vergelijking met DBMS_SQL.

Syntaxis 

EXECUTE IMMEDIATE(<SQL>)
[INTO<variable>]
[USING <bind_variable_value>]
  • De bovenstaande syntaxis toont de opdracht EXECUTE IMMEDIATE.
  • Clausule INTO is optioneel en wordt alleen gebruikt als de dynamische SQL een select-instructie bevat die waarden ophaalt. Het variabeletype moet overeenkomen met het variabeletype van de select-instructie.
  • Clausule USING is optioneel en wordt alleen gebruikt als de dynamische SQL een bindvariabele bevat.

Voorbeeld 1: In dit voorbeeld gaan we de gegevens ophalen uit de emp-tabel voor emp_no '1001' met behulp van de NDS-instructie.

NDS - Direct uitvoeren

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;
/

uitgang

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

Code Verklaring:

  • Coderegel 2-6: Variabelen declareren.
  • Coderegel 8: Het framen van de SQL tijdens runtime. SQL bevat de bindvariabele in de voorwaarde ':empno'.
  • Coderegel 9: Het uitvoeren van de ingekaderde SQL-tekst (wat wordt gedaan in coderegel 8) met behulp van het NDS-commando 'EXECUTE IMMEDIATE'
  • De variabelen in de 'INTO'-clausule (lv_emp_name, ln_emp_no, ln_salary, ln_manager) worden gebruikt om de opgehaalde waarden uit de SQL-query (emp_name, emp_no, salaris, manager) op te slaan
  • De 'USING'-clausule geeft de waarden aan de bindvariabele in de SQL-query (:emp_no).
  • Coderegel 10-13: Weergave van de opgehaalde waarden.

DBMS_SQL voor dynamische SQL

PL/SQL biedt het DBMS_SQL-pakket waarmee u met dynamische SQL kunt werken. Het proces van het maken en uitvoeren van de dynamische SQL omvat het volgende proces.

  • CURSOR OPENEN: De dynamische SQL wordt op dezelfde manier uitgevoerd als a cursor. Om de SQL-instructie uit te voeren, moeten we dus de cursor openen.
  • PARSE-SQL: De volgende stap is het parseren van de dynamische SQL. Dit proces controleert alleen de syntaxis en houdt de query gereed voor uitvoering.
  • BIND VARIABELE Waarden: De volgende stap is het toewijzen van de waarden voor eventuele bindvariabelen.
  • DEFINIEER KOLOM: De volgende stap is het definiëren van de kolom met behulp van hun relatieve posities in de select-instructie.
  • UITVOEREN: De volgende stap is het uitvoeren van de geparseerde query.
  • WAARDEN OPHALEN: De volgende stap is het ophalen van de uitgevoerde waarden.
  • SLUIT CURSOR: Zodra de resultaten zijn opgehaald, moet de cursor gesloten zijn.

Voorbeeld 1: In dit voorbeeld gaan we de gegevens ophalen uit de emp-tabel voor emp_no '1001' met behulp van de DBMS_SQL-instructie.

DBMS_SQL voor dynamische SQL

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 voor dynamische SQL

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:
/

uitgang

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

Code Verklaring:

  • Coderegel 1-9: Variabele declaratie.
  • Coderegel 10: Het formuleren van de SQL-instructie.
  • Coderegel 11: De cursor openen met DBMS_SQL.OPEN_CURSOR. Het retourneert de cursor-ID die is geopend.
  • Coderegel 12: Nadat de cursor is geopend, wordt de SQL geparseerd.
  • Coderegel 13: Bindvariabele '1001' wijst aan de cursor-ID toe in plaats van ':empno'.
  • Coderegel 14-17: De kolomnaam definiëren op basis van hun relatieve positie in de SQL-instructie. In ons geval is de relatieve positie (1) werk_naam, (2) werk_nr (3) salaris (4) manager. Op basis van deze positie definiëren we dus de doelvariabele.
  • Coderegel 18: De query uitvoeren met DBMS_SQL.EXECUTE. Het retourneert het aantal verwerkte records.
  • Coderegel 19-33: De records ophalen met behulp van een lus en deze weergeven.
  • Coderegel 20: DBMS_SQL.FETCH_ROWS haalt één record op uit de verwerkte rijen. Het kan herhaaldelijk worden aangeroepen om alle rijen op te halen. Als het geen rijen kan ophalen, retourneert het 0 en verlaat daarmee de lus.

Samenvatting

In deze sectie hebben we dynamische SQL besproken en de manieren om DYNAMISCHE SQL uit te voeren. We hebben ook de verschillende stappen gezien bij het uitvoeren van de dynamische SQL op beide manieren. We hebben ook de voorbeelden gezien waarin hetzelfde scenario wordt afgehandeld op zowel NDS- als DBMS_SQL-manieren om uitvoering tijdens runtime uit te voeren.