Oracle PL/SQL Dynamic SQL Tutorial: Execute Immediate & DBMS_SQL

Was ist dynamisches SQL?

Dynamisch SQL ist eine Programmiermethode zum Generieren und Ausführen von Anweisungen zur Laufzeit. Es wird hauptsächlich zum Schreiben universeller und flexibler Programme verwendet, in denen die SQL-Anweisungen zur Laufzeit basierend auf den Anforderungen erstellt und ausgeführt werden.

Möglichkeiten, dynamisches SQL zu schreiben

PL/SQL bietet zwei Möglichkeiten, dynamisches SQL zu schreiben

1. NDS – Natives dynamisches SQL
2. DBMS_SQL

NDS (Native Dynamic SQL) – Sofort ausführen

Native Dynamic SQL ist die einfachere Möglichkeit, dynamisches SQL zu schreiben. Es verwendet den Befehl „EXECUTE IMMEDIATE“, um SQL zur Laufzeit zu erstellen und auszuführen. Um diese Methode zu verwenden, müssen jedoch der Datentyp und die Anzahl der Variablen, die zur Laufzeit verwendet werden sollen, vorher bekannt sein. Im Vergleich zu DBMS_SQL bietet es auch eine bessere Leistung und weniger Komplexität.

Syntax

EXECUTE IMMEDIATE(<SQL>)
[INTO<variable>]
[USING <bind_variable_value>]

• Die obige Syntax zeigt den Befehl EXECUTE IMMEDIATE.
• Die Klausel INTO ist optional und wird nur verwendet, wenn das dynamische SQL eine SELECT-Anweisung enthält, die Werte abruft. Der Variablentyp sollte mit dem Variablentyp der Select-Anweisung übereinstimmen.
• Die Klausel USING ist optional und wird nur verwendet, wenn das dynamische SQL eine Bindevariable enthält.

Beispiel 1: In diesem Beispiel werden wir die Daten aus der emp-Tabelle für emp_no „1001“ mithilfe der NDS-Anweisung abrufen.

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

Ausgang

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

Code-Erklärung:

• Codezeile 2-6: Variablen deklarieren.
• Codezeile 8: Framing der SQL zur Laufzeit. SQL enthält die Bind-Variable in der Where-Bedingung „:empno“.
• Codezeile 9: Ausführen des gerahmten SQL-Textes (was in Codezeile 8 erfolgt) mit dem NDS-Befehl „EXECUTE IMMEDIATE“
• Die Variablen in der „INTO“-Klausel (lv_emp_name, ln_emp_no, ln_salary, ln_manager) werden verwendet, um die aus der SQL-Abfrage abgerufenen Werte zu speichern (emp_name, emp_no, Gehalt, Manager).
• Die Klausel „USING“ gibt die Werte an die Bindevariable in der SQL-Abfrage (:emp_no) weiter.
• Codezeile 10-13: Anzeige der abgerufenen Werte.

DBMS_SQL für dynamisches SQL

PL/SQL stellt das DBMS_SQL-Paket bereit, mit dem Sie mit dynamischem SQL arbeiten können. Der Prozess zum Erstellen und Ausführen des dynamischen SQL umfasst den folgenden Prozess.

• CURSOR ÖFFNEN: Das dynamische SQL wird auf die gleiche Weise ausgeführt wie a Cursor. Um die SQL-Anweisung auszuführen, müssen wir also den Cursor öffnen.
• SQL PARSE: Der nächste Schritt besteht darin, das dynamische SQL zu analysieren. Dieser Prozess überprüft lediglich die Syntax und hält die Abfrage zur Ausführung bereit.
• BIND VARIABLE-Werte: Der nächste Schritt besteht darin, die Werte für Bindevariablen zuzuweisen, falls vorhanden.
• SPALTE DEFINIEREN: Der nächste Schritt besteht darin, die Spalte anhand ihrer relativen Positionen in der SELECT-Anweisung zu definieren.
• EXECUTE: Der nächste Schritt besteht darin, die analysierte Abfrage auszuführen.
• WERTE FETCH: Der nächste Schritt besteht darin, die ausgeführten Werte abzurufen.
• CURSOR SCHLIESSEN: Sobald die Ergebnisse abgerufen wurden, sollte der Cursor geschlossen werden.

Beispiel 1: In diesem Beispiel werden wir die Daten aus der emp-Tabelle für emp_no „1001“ mithilfe der DBMS_SQL-Anweisung abrufen.

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

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

Ausgang

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

Code-Erklärung:

• Codezeile 1-9: Variable Aussage.
• Codezeile 10: Rahmen der SQL-Anweisung.
• Codezeile 11: Öffnen des Cursors mit DBMS_SQL.OPEN_CURSOR. Es wird die Cursor-ID zurückgegeben, die geöffnet wird.
• Codezeile 12: Nachdem der Cursor geöffnet wurde, wird die SQL analysiert.
• Codezeile 13: Die Bind-Variable „1001“ weist der Cursor-ID statt „:empno“ zu.
• Codezeile 14-17: Definieren des Spaltennamens basierend auf ihrer relativen Position in der SQL-Anweisung. In unserem Fall ist die relative Position (1) emp_name, (2) emp_no (3) Gehalt (4) Manager. Basierend auf dieser Position definieren wir die Zielvariable.
• Codezeile 18: Ausführen der Abfrage mit DBMS_SQL.EXECUTE. Es gibt die Anzahl der verarbeiteten Datensätze zurück.
• Codezeile 19-33: Datensätze mithilfe einer Schleife abrufen und anzeigen.
• Codezeile 20: DBMS_SQL.FETCH_ROWS ruft einen Datensatz aus den verarbeiteten Zeilen ab. Es kann wiederholt aufgerufen werden, um alle Zeilen abzurufen. Wenn keine Zeilen abgerufen werden können, gibt es 0 zurück und verlässt damit die Schleife.

Zusammenfassung

In diesem Abschnitt haben wir dynamisches SQL und die Möglichkeiten zur Ausführung von DYNAMIC SQL besprochen. Wir haben auch die unterschiedlichen Schritte bei der Ausführung des dynamischen SQL auf beide Arten gesehen. Wir haben auch Beispiele gesehen, in denen das gleiche Szenario sowohl auf NDS- als auch auf DBMS_SQL-Art gehandhabt wird, um die Ausführung zur Laufzeit durchzuführen.