Oracle PL / SQL Dynamic SQL-Lernprogramm: Sofort ausführen & DBMS_SQL

Inhaltsverzeichnis:

Anonim

Was ist dynamisches SQL?

Dynamic SQL ist eine Programmiermethode zum Generieren und Ausführen von Anweisungen zur Laufzeit. Es wird hauptsächlich zum Schreiben von universellen und flexiblen Programmen verwendet, in denen die SQL-Anweisungen je nach Anforderung zur Laufzeit erstellt und ausgeführt werden.

In diesem Tutorial lernen Sie:

  • Möglichkeiten zum Schreiben von dynamischem SQL
  • NDS (Native Dynamic SQL) - Sofort ausführen
  • DBMS_SQL für Dynamic SQL

Möglichkeiten zum Schreiben von dynamischem SQL

PL / SQL bietet zwei Möglichkeiten zum Schreiben von dynamischem SQL

  1. NDS - Native Dynamic SQL
  2. DBMS_SQL

NDS (Native Dynamic SQL) - Sofort ausführen

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

Syntax 

EXECUTE IMMEDIATE()[INTO][USING ]
  • 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 Bindungsvariable enthält.

Beispiel 1 : In diesem Beispiel werden die Daten mit der NDS-Anweisung aus der emp-Tabelle für emp_no '1001' abgerufen.

DECLARElv_sql VARCHAR2(500);lv_emp_name VARCHAR2(50):ln_emp_no NUMBER;ln_salary NUMBER;ln_manager NUMBER;BEGINly_sql:=;SELECT emp_name,emp_no,salary,manager FROM emp WHEREemp_no=:empmo:;EXECUTE IMMEDIATE lv_sql INTO lv_emp_name,ln_emp_no:ln_salary,ln_managerUSING 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;/

Ausgabe

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

Code Erläuterung:

  • Codezeile 2-6 : Variablen deklarieren.
  • Codezeile 8 : Framing der SQL zur Laufzeit. SQL enthält die Bindevariable in der Bedingung ': empno'.
  • Codezeile 9 : Ausführen des gerahmten SQL-Textes (in Codezeile 8) 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 (emp_name, emp_no, Gehalt, Manager) zu speichern.
  • Die 'USING'-Klausel gibt die Werte für die Bindevariable in der SQL-Abfrage an (: emp_no).
  • Codezeile 10-13 : Anzeigen der abgerufenen Werte.

DBMS_SQL für Dynamic SQL

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

  • OPEN CURSOR : Das dynamische SQL wird wie ein Cursor ausgeführt. Um die SQL-Anweisung auszuführen, müssen wir den Cursor öffnen.
  • PARSE SQL : Der nächste Schritt ist das Parsen des dynamischen SQL. 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 Bindungsvariablen zuzuweisen, falls vorhanden.
  • SPALTE DEFINIEREN : Der nächste Schritt besteht darin, die Spalte anhand ihrer relativen Positionen in der select-Anweisung zu definieren.
  • AUSFÜHREN : Der nächste Schritt besteht darin, die analysierte Abfrage auszuführen.
  • FETCH VALUES : Der nächste Schritt ist das Abrufen der ausgeführten Werte.
  • CURSOR SCHLIESSEN : Sobald die Ergebnisse abgerufen wurden, sollte der Cursor geschlossen werden.

Beispiel 1 : In diesem Beispiel werden die Daten mit der DBMS_SQL-Anweisung aus der emp-Tabelle für emp_no '1001' abgerufen.

DECLARElv_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;BEGINlv_sql:=‘SELECT emp_name,emp_no,salary,manager FROM emp WHEREemp_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);
LOOPIF DBMS_SQL.FETCH_ROWS(ln_cursor_id)=0THENEXIT;ELSEDBMS_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:/

Ausgabe

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

Code Erläuterung:

  • Codezeile 1-9 : Variablendeklaration.
  • Codezeile 10 : Rahmen der SQL-Anweisung.
  • Codezeile 11 : Öffnen des Cursors mit DBMS_SQL.OPEN_CURSOR. Es wird die geöffnete Cursor-ID zurückgegeben.
  • Codezeile 12 : Nach dem Öffnen des Cursors wird die SQL analysiert.
  • Codezeile 13 : Die Bindungsvariable '1001' wird stattdessen der Cursor-ID ': empno' zugewiesen.
  • 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 also 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 : Abrufen der Datensätze mit einer Schleife und Anzeigen derselben.
  • 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, wird 0 zurückgegeben, wodurch die Schleife verlassen wird.

Zusammenfassung

In diesem Abschnitt haben wir dynamisches SQL und die Möglichkeiten zur Ausführung von DYNAMIC SQL erläutert. Wir haben auch die verschiedenen Schritte beim Ausführen des dynamischen SQL auf beide Arten gesehen. Wir haben auch die Beispiele gesehen, in denen dasselbe Szenario sowohl auf NDS- als auch auf DBMS_SQL-Weise behandelt wird, um die Ausführung zur Laufzeit durchzuführen.