8 Dynamisches SQL

Dynamisches SQL ist eine Programmiertechnik, mit der Sie SQL-Anweisungen zur Laufzeit dynamisch erstellen können. Sie können allgemeinere, flexible Anwendungen mithilfe von dynamischem SQL erstellen, da der vollständige Text einer SQL-Anweisung bei der Kompilierung möglicherweise unbekannt ist. Mit dynamischem SQL können Sie beispielsweise eine Prozedur erstellen, die für eine Tabelle ausgeführt wird, deren Name erst zur Laufzeit bekannt ist.

In früheren Versionen von Oracle war die einzige Möglichkeit, dynamisches SQL in einer PL / SQL-Anwendung zu implementieren, die mithilfe des Pakets DBMS_SQL. Oracle8i führt natives dynamisches SQL ein, eine Alternative zum Paket DBMS_SQL. Mit nativem dynamischem SQL können Sie dynamische SQL-Anweisungen direkt in PL / SQL-Blöcken platzieren.

In diesem Kapitel werden die folgenden Themen behandelt:

• Was ist dynamisches SQL?
• Wann wird dynamisches SQL verwendet?
• Ein dynamisches SQL-Szenario mit nativem dynamischem SQL
• Natives dynamisches SQL im Vergleich zur DBMS_SQL-Paketanwendung
• Andere Entwicklungssprachen als PL / SQL

Was ist dynamisches SQL?

Mit dynamischem SQL können Sie Programme schreiben, die auf SQL-Anweisungen verweisen, deren vollständiger Text erst zur Laufzeit bekannt ist. Bevor auf dynamisches SQL ausführlich eingegangen wird, kann eine klare Definition von statischem SQL einen guten Ausgangspunkt für das Verständnis von dynamischem SQL bieten. Statische SQL-Anweisungen ändern sich nicht von Ausführung zu Ausführung. Der vollständige Text statischer SQL-Anweisungen ist bei der Kompilierung bekannt. Dies bietet die folgenden Vorteile:

• Bei einer erfolgreichen Kompilierung wird überprüft, ob die SQL-Anweisungen auf gültige Datenbankobjekte verweisen.
• Durch erfolgreiche Kompilierung wird überprüft, ob die erforderlichen Berechtigungen für den Zugriff auf die Datenbankobjekte vorhanden sind.
• Die Leistung von statischem SQL ist im Allgemeinen besser als die von dynamischem SQL.

Aufgrund dieser Vorteile sollten Sie dynamisches SQL nur verwenden, wenn Sie statisches SQL nicht zur Erreichung Ihrer Ziele verwenden können oder wenn die Verwendung von statischem SQL im Vergleich zu dynamischem SQL umständlich ist. Statisches SQL weist jedoch Einschränkungen auf, die mit dynamischem SQL überwunden werden können. Möglicherweise kennen Sie nicht immer den vollständigen Text der SQL-Anweisungen, die in einer PL / SQL-Prozedur ausgeführt werden müssen. Ihr Programm akzeptiert möglicherweise Benutzereingaben, die die auszuführenden SQL-Anweisungen definieren, oder Ihr Programm muss möglicherweise einige Verarbeitungsarbeiten ausführen, um die richtige Vorgehensweise zu bestimmen. In solchen Fällen sollten Sie dynamisches SQL verwenden.

Stellen Sie sich beispielsweise eine Berichtsanwendung vor, die Standardabfragen für Tabellen in einer Data Warehouse-Umgebung ausführt, in der der genaue Tabellenname bis zur Laufzeit unbekannt ist. Um die große Datenmenge im Data Warehouse effizient unterzubringen, erstellen Sie vierteljährlich eine neue Tabelle, in der die Rechnungsinformationen für das Quartal gespeichert werden. Diese Tabellen haben alle genau die gleiche Definition und sind nach dem Anfangsmonat und -jahr des Quartals benannt, z. B. INV_01_1997, INV_04_1997, INV_07_1997, INV_10_1997, INV_01_1998 usw. In einem solchen Fall können Sie dynamisch verwenden SQL in Ihrer Berichtsanwendung zur Angabe des Tabellennamens zur Laufzeit.

Bei statischem SQL müssen alle Datendefinitionsinformationen, z. B. Tabellendefinitionen, auf die in den SQL-Anweisungen in Ihrem Programm verwiesen wird, bei der Kompilierung bekannt sein. Wenn sich die Datendefinition ändert, müssen Sie das Programm ändern und neu kompilieren. Dynamische SQL-Programme können Änderungen an Datendefinitionsinformationen verarbeiten, da sich die SQL-Anweisungen zur Laufzeit „on the fly“ ändern können. Daher ist dynamisches SQL viel flexibler als statisches SQL. Mit Dynamic SQL können Sie wiederverwendbaren Anwendungscode schreiben, da der Code einen Prozess definiert, der unabhängig von den verwendeten spezifischen SQL-Anweisungen ist.

Darüber hinaus können Sie mit Dynamic SQL SQL-Anweisungen ausführen, die statisch nicht unterstützt werden SQL-Programme, z. B. DDL-Anweisungen (Data Definition Language). Durch die Unterstützung dieser Anweisungen können Sie mit Ihren PL / SQL-Programmen mehr erreichen.

Verwendung von dynamischem SQL

Sie sollten dynamisches SQL in Fällen verwenden, in denen statisches SQL das nicht unterstützt Operation, die Sie ausführen möchten, oder in Fällen, in denen Sie die genauen SQL-Anweisungen nicht kennen, die von einer PL / SQL-Prozedur ausgeführt werden müssen. Diese SQL-Anweisungen können von Benutzereingaben oder von der Verarbeitungsarbeit des Programms abhängen. In den folgenden Abschnitten werden typische Situationen beschrieben, in denen Sie dynamisches SQL verwenden sollten, sowie typische Probleme, die mit dynamischem SQL gelöst werden können.

Ausführen dynamischer DML-Anweisungen

Zum Ausführen können Sie dynamisches SQL verwenden DML-Anweisungen, bei denen die genaue SQL-Anweisung erst zur Laufzeit bekannt ist.Beispiele finden Sie in den DML-Beispielen unter „Beispiele für DBMS_SQL-Paketcode und nativen dynamischen SQL-Code“ und „Beispiel für eine DML-Operation“.

Ausführen von Anweisungen, die von statischem SQL in PL / SQL nicht unterstützt werden

In PL / SQL können Sie die folgenden Arten von Anweisungen nicht mit statischem SQL ausführen:

• DDL-Anweisungen (Data Definition Language), z. B. CREATE, DROP, GRANT und REVOKE
• SCL-Anweisungen (Session Control Language), z. B. ALTER SESSION und SET ROLE
  Informationen zu DDL- und SCL-Anweisungen finden Sie auch unter:

  Oracle8i SQL-Referenz.

Verwenden Sie dynamisches SQL, wenn Sie eine dieser Arten von Anweisungen in einem PL / ausführen müssen. SQL-Block.

Darüber hinaus erlaubt statisches SQL in PL / SQL nicht die Verwendung der Klausel TABLE in der SELECT Anweisungen. In dynamischem SQL gibt es keine solche Einschränkung. Der folgende PL / SQL-Block enthält beispielsweise eine SELECT -Anweisung, die die TABLE -Klausel und natives dynamisches SQL verwendet:

Ausführen dynamischer Abfragen

Mit dynamischem SQL können Sie Anwendungen erstellen, die dynamische Abfragen ausführen. Hierbei handelt es sich um Abfragen, deren vollständiger Text erst zur Laufzeit bekannt ist. Viele Arten von Anwendungen müssen dynamische Abfragen verwenden, einschließlich:

• Anwendungen, mit denen Benutzer zur Laufzeit Abfragesuche oder Sortierkriterien eingeben oder auswählen können
• Anwendungen, mit denen Benutzer Eingaben vornehmen können oder wählen Sie Optimierungshinweise zur Laufzeit.
• Anwendungen, die eine Datenbank abfragen, in der sich die Datendefinitionen von Tabellen ständig ändern.
• Anwendungen, die eine Datenbank abfragen, in der häufig neue Tabellen erstellt werden.

Beispiele finden Sie unter „Abfragebeispiel“ und in den Abfragebeispielen unter „Ein dynamisches SQL-Szenario mit nativem dynamischem SQL“.

Referenzieren von Datenbankobjekten, die beim Kompilieren nicht vorhanden sind

Viele Arten von Anwendungen müssen mit Daten interagieren, die regelmäßig generiert werden. Beispielsweise kann es möglich sein, die Definition der Datenbanktabellen bei der Kompilierung zu bestimmen, nicht jedoch die Namen der Tabellen, da regelmäßig neue Tabellen generiert werden. Ihre Anwendung muss auf die Daten zugreifen, aber es gibt keine Möglichkeit, die genauen Namen der Tabellen bis zur Laufzeit zu ermitteln.

Dynamic SQL kann dieses Problem lösen, da Sie mit Dynamic SQL bis zur Laufzeit warten können, um die Daten anzugeben Tabellennamen, auf die Sie zugreifen müssen. In der unter „Was ist dynamisches SQL?“ Erläuterten Beispiel-Data-Warehouse-Anwendung werden beispielsweise vierteljährlich neue Tabellen generiert, und diese Tabellen haben immer dieselbe Definition. In diesem Fall können Sie einem Benutzer erlauben, den Namen der Tabelle zur Laufzeit mit einer dynamischen SQL-Abfrage anzugeben, die der folgenden ähnelt:

Dynamische Optimierung der Ausführung

Wenn Sie statisch verwenden SQL müssen Sie bei der Kompilierung entscheiden, wie Sie Ihre SQL-Anweisungen erstellen möchten, ob Hinweise in Ihren Anweisungen enthalten sein sollen und, wenn Sie Hinweise einschließen, genau welche Hinweise enthalten sein sollen. Sie können jedoch dynamisches SQL verwenden, um eine SQL-Anweisung so zu erstellen, dass die Ausführung optimiert und / oder die Hinweise dynamisch zu einer SQL-Anweisung verkettet werden. Auf diese Weise können Sie die Hinweise basierend auf Ihren aktuellen Datenbankstatistiken ändern, ohne dass eine Neukompilierung erforderlich ist.

Bei der folgenden Prozedur wird beispielsweise eine Variable mit dem Namen a_hint verwendet, um Benutzern zu ermöglichen So übergeben Sie eine Hinweisoption an die Anweisung SELECT:

In diesem Beispiel kann der Benutzer einen der folgenden Werte für a_hint:

Aufrufen dynamischer PL / SQL-Blöcke

Sie können die EXECUTE IMMEDIATE -Anweisung zum Aufrufen anonymer PL / SQL-Blöcke. Die Möglichkeit, dynamische PL / SQL-Blöcke aufzurufen, kann für die Anwendungserweiterung und -anpassung nützlich sein, bei der das auszuführende Modul zur Laufzeit dynamisch festgelegt wird.

Angenommen, Sie möchten eine Anwendung schreiben, die ein Ereignis enthält Nummer und Versand an einen Handler für die Veranstaltung. Der Name des Handlers hat die Form EVENT_HANDLER_ event_num, wobei event_num die Nummer des Ereignisses ist. Ein Ansatz wäre, den Dispatcher wie unten gezeigt als switch-Anweisung zu implementieren, wobei der Code jedes Ereignis behandelt, indem er einen statischen Aufruf an den entsprechenden Handler ausführt.

Dieser Code ist aufgrund des Dispatcher-Codes nicht sehr erweiterbar muss aktualisiert werden, wenn ein Handler für ein neues Ereignis hinzugefügt wird. Mit nativem dynamischem SQL können Sie jedoch einen erweiterbaren Ereignis-Dispatcher schreiben, der dem folgenden ähnelt:

So führen Sie dynamische Vorgänge mit Invoker-Rechten aus

Verwenden Sie die Invoker-Rights-Funktion mit dynamic SQL können Sie Anwendungen erstellen, die dynamische SQL-Anweisungen unter den Berechtigungen und dem Schema des Aufrufers ausgeben.Mit diesen beiden Funktionen, Aufruferrechte und dynamisches SQL, können Sie wiederverwendbare Anwendungsunterkomponenten erstellen, die auf die Daten und Module des Aufrufers zugreifen und auf diese zugreifen können.

Ein dynamisches SQL-Szenario mit nativem dynamischem SQL

Das in diesem Abschnitt beschriebene Szenario veranschaulicht die Leistungsfähigkeit und Flexibilität von nativem dynamischem SQL. Dieses Szenario enthält Beispiele, die Ihnen zeigen, wie Sie die folgenden Vorgänge mit nativem dynamischem SQL ausführen:

• DDL- und DML-Vorgänge ausführen
• Abfragen einzelner Zeilen und mehrerer Zeilen ausführen

Datenmodell

Die Datenbank in diesem Szenario ist die Personaldatenbank eines Unternehmens (mit dem Namen hr) mit dem folgenden Datenmodell :

Eine Mastertabelle mit dem Namen offices enthält die Liste aller Unternehmensstandorte. Die Tabelle offices hat die folgende Definition :

Column Name Null? Type LOCATION NOT_NULL VARCHAR2(200)

Mehrere emp_ Standorttabellen enthalten die Mitarbeiterinformationen, wobei Standort der Name der Stadt ist, in der sich das Büro befindet befindet sich beispielsweise. Eine Tabelle mit dem Namen emp_houston enthält Mitarbeiterinformationen für das Büro in Houston, während eine Tabelle mit dem Namen emp_boston Mitarbeiter enthält Informationen für das Büro des Unternehmens in Boston.

Jede hat die folgende Definition:

In den folgenden Abschnitten werden verschiedene native dynamische SQL-Operationen beschrieben, die für die Daten in hr ausgeführt werden können Datenbank.

Beispiel für eine DML-Operation

Die folgende native dynamische SQL-Prozedur gibt allen Mitarbeitern mit einer bestimmten Berufsbezeichnung eine Erhöhung:

Beispiel für eine DDL-Operation

Die Anweisung EXECUTE IMMEDIATE kann DDL-Operationen ausführen. Mit der folgenden Prozedur wird beispielsweise ein Bürostandort hinzugefügt:

Mit der folgenden Prozedur wird ein Bürostandort gelöscht:

Beispiel für eine dynamische einzeilige Abfrage

Die EXECUTE IMMEDIATE -Anweisung kann dynamische einzeilige Abfragen ausführen. Sie können Bindungsvariablen in der USING -Klausel angeben und die resultierende Zeile in das in der INTO -Klausel der Anweisung angegebene Ziel abrufen.

Die folgende Funktion ruft die Anzahl der Mitarbeiter an einem bestimmten Standort ab, die einen bestimmten Job ausführen:

Beispiel für eine dynamische mehrzeilige Abfrage

Die OPEN-FOR, FETCH und CLOSE Anweisungen können dynamische mehrzeilige Abfragen ausführen. Das folgende Verfahren listet beispielsweise alle Mitarbeiter mit einem bestimmten Job an einem bestimmten Speicherort auf:

Native Dynamic SQL im Vergleich zum DBMS_SQL-Paket

Oracle bietet zwei Methoden zur Verwendung von Dynamic SQL in PL / SQL: natives dynamisches SQL und das Paket DBMS_SQL. Mit nativem dynamischem SQL können Sie dynamische SQL-Anweisungen direkt in PL / SQL-Code einfügen. Diese dynamischen Anweisungen umfassen DML-Anweisungen (einschließlich Abfragen), anonyme PL / SQL-Blöcke, DDL-Anweisungen, Transaktionssteuerungsanweisungen und Sitzungssteuerungsanweisungen.

Um die meisten nativen dynamischen SQL-Anweisungen zu verarbeiten, verwenden Sie die EXECUTE IMMEDIATE Anweisung. Um jedoch eine mehrzeilige Abfrage (SELECT -Anweisung) zu verarbeiten, verwenden Sie OPEN-FOR, FETCH und CLOSE Anweisungen.

Das Paket DBMS_SQL ist eine PL / SQL-Bibliothek, die eine programmatische API für bietet SQL-Anweisungen dynamisch ausführen. Das Paket DBMS_SQL verfügt über programmgesteuerte Schnittstellen zum Öffnen eines Cursors, Analysieren eines Cursors, Bereitstellen von Bindungen usw. Programme, die das Paket DBMS_SQL verwenden, rufen auf zu diesem Paket, um dynamische SQL-Vorgänge auszuführen.

Die folgenden Abschnitte enthalten detaillierte Informationen zu den Vorteilen beider Methoden.

-Paket. Im PL / SQL-Benutzerhandbuch und in der Referenz wird natives dynamisches SQL einfach als dynamisches SQL bezeichnet.

Vorteile von nativem dynamischem SQL

Natives dynamisches SQL bietet die folgenden Vorteile gegenüber DBMS_SQL -Paket:

Benutzerfreundlichkeit

Natives dynamisches SQL ist viel einfacher zu verwenden als das DBMS_SQL -Paket.Da natives dynamisches SQL in SQL integriert ist, können Sie es auf dieselbe Weise verwenden, wie Sie derzeit statisches SQL in PL / SQL-Code verwenden. Darüber hinaus ist nativer dynamischer SQL-Code in der Regel kompakter und lesbarer als gleichwertiger Code, der das Paket DBMS_SQL verwendet.

Die DBMS_SQL ist nicht so einfach zu verwenden wie natives dynamisches SQL. Es gibt viele Verfahren und Funktionen, die in einer strengen Reihenfolge verwendet werden müssen. Normalerweise erfordert das Ausführen einfacher Operationen eine große Menge an Code, wenn Sie das Paket DBMS_SQL verwenden. Sie können diese Komplexität vermeiden, indem Sie stattdessen natives dynamisches SQL verwenden.

Tabelle 8-1 zeigt den Unterschied in der Menge an Code, die erforderlich ist, um denselben Vorgang mit DBMS_SQL Paket und natives dynamisches SQL.

Tabelle 8-1 Codevergleich von DBMS_SQL-Paket und nativem dynamischem SQL

Leistungsverbesserungen

Die Leistung von nativem dynamischem SQL in PL / SQL ist vergleichbar mit der Leistung von statischem SQL, da der PL / SQL-Interpreter integrierte Unterstützung für natives dynamisches SQL bietet. Daher ist die Leistung von Programmen, die natives dynamisches SQL verwenden, viel besser als die von Programmen, die das Paket DBMS_SQL verwenden. In der Regel weisen native dynamische SQL-Anweisungen eine 1,5- bis 3-mal bessere Leistung auf als entsprechende Anweisungen, die das Paket DBMS_SQL verwenden. Natürlich können Ihre Leistungssteigerungen je nach Anwendung variieren.

Das DBMS_SQL -Paket basiert auf einer prozeduralen API und verursacht daher einen hohen Overhead für Prozeduraufrufe und Datenkopien. Beispielsweise kopiert das Paket DBMS_SQL jedes Mal, wenn Sie eine Variable binden, die PL / SQL-Bindungsvariable in ihren Speicherplatz, um sie später während der Ausführung zu verwenden. In ähnlicher Weise werden jedes Mal, wenn Sie einen Abruf ausführen, zuerst die Daten in den vom DBMS_SQL -Paket verwalteten Speicherplatz kopiert und dann die abgerufenen Daten spaltenweise in den entsprechenden Bereich kopiert PL / SQL-Variablen, was zu einem erheblichen Overhead beim Kopieren von Daten führt. Im Gegensatz dazu bündelt natives dynamisches SQL die Schritte zur Vorbereitung, Bindung und Ausführung von Anweisungen in einer einzigen Operation, wodurch der Aufwand für das Kopieren von Daten und Prozeduraufrufen minimiert und die Leistung verbessert wird.

Leistungstipp

Wenn Sie entweder natives dynamisches SQL oder das Paket DBMS_SQL verwenden, können Sie die Leistung durch die Verwendung von Bindungsvariablen verbessern, da Oracle durch die Verwendung von Bindungsvariablen einen einzelnen Cursor für mehrere SQL-Anweisungen freigeben kann. P. >

Beispielsweise verwendet der folgende native dynamische SQL-Code keine Bindevariablen:

Für jede einzelne my_deptno -Variable wird ein neuer Cursor erstellt, der kann zu Ressourcenkonflikten und schlechter Leistung führen. Binden Sie stattdessen my_deptno als Bindungsvariable, wie im folgenden Beispiel:

Hier wird derselbe Cursor für verschiedene Werte der Bindung my_deptno, wodurch Leistung und Skalierbarkeit verbessert werden.

Unterstützung für benutzerdefinierte Typen

Natives dynamisches SQL unterstützt alle von statischem SQL in PL / unterstützten Typen SQL. Daher bietet natives dynamisches SQL Unterstützung für benutzerdefinierte Typen wie benutzerdefinierte Objekte, Sammlungen und REFs. Das Paket DBMS_SQL unterstützt diese benutzerdefinierten Typen nicht.

Unterstützung für das Abrufen in Datensätze

Natives dynamisches SQL und statisches SQL unterstützen beide das Abrufen in Datensätze, aber die DBMS_SQL Paket nicht. Mit nativem dynamischem SQL können die aus einer Abfrage resultierenden Zeilen direkt in PL / SQL-Datensätze abgerufen werden.

Im folgenden Beispiel werden die Zeilen aus einer Abfrage in die emp_rec record:

Vorteile des DBMS_SQL-Pakets

Das DBMS_SQL -Paket bietet die folgenden Vorteile gegenüber nativem dynamischem SQL:

Unterstützung für clientseitige Programme

Derzeit wird das Paket DBMS_SQL in clientseitigen Programmen unterstützt, natives dynamisches SQL jedoch nicht. Jeder Aufruf des DBMS_SQL -Pakets vom clientseitigen Programm führt zu einem PL / SQL-Remoteprozeduraufruf (RPC). Diese Aufrufe treten auf, wenn Sie eine Variable binden, eine Variable definieren oder eine Anweisung ausführen müssen.

Unterstützung für DESCRIBE

Die Prozedur DESCRIBE_COLUMNS im Paket DBMS_SQL kann verwendet werden Beschreiben Sie die Spalten für einen Cursor, der durch DBMS_SQL geöffnet und analysiert wurde. Die Funktionalität ähnelt dem Befehl DESCRIBE in SQL * Plus. Natives dynamisches SQL verfügt nicht über eine DESCRIBE -Funktion.

Unterstützung für dynamisches Massen-SQL

Massen-SQL ist die Fähigkeit, mehrere Datenzeilen in einer einzigen DML-Anweisung zu verarbeiten. Bulk-SQL verbessert die Leistung, indem der Kontextwechsel zwischen SQL und der Host-Sprache reduziert wird. Derzeit unterstützt das Paket DBMS_SQL dynamisches Massen-SQL.

Obwohl Massenoperationen in nativem dynamischem SQL nicht direkt unterstützt werden, können Sie ein natives dynamisches Massen-SQL simulieren Anweisung durch Platzieren der Bulk-SQL-Anweisung in einem Block „BEGIN … END“ und dynamisches Ausführen des Blocks. Mit dieser Problemumgehung können Sie die Vorteile von Bulk-SQL in einem nativen dynamischen SQL-Programm nutzen. Der folgende native dynamische SQL-Code kopiert beispielsweise die Spalte ename einer Tabelle in eine andere:

Aktualisieren und Löschen mehrerer Zeilen mit einer RETURNING-Klausel

Das Paket DBMS_SQL unterstützt Anweisungen mit einer RETURNING -Klausel, mit der mehrere Zeilen aktualisiert oder gelöscht werden. Natives dynamisches SQL unterstützt nur eine RETURNING -Klausel, wenn eine einzelne Zeile zurückgegeben wird.

Unterstützung für SQL-Anweisungen, die größer als 32 KB sind

Die DBMS_SQL Paket unterstützt SQL-Anweisungen größer als 32 KB; natives dynamisches SQL nicht.

Wiederverwendung von SQL-Anweisungen

Die Prozedur PARSE in der DBMS_SQL package analysiert eine SQL-Anweisung einmal. Nach dem ersten Parsen kann die Anweisung mehrmals mit verschiedenen Bindeargumenten verwendet werden.

Im Gegensatz dazu bereitet natives dynamisches SQL bei jeder Verwendung der Anweisung eine SQL-Anweisung zur Ausführung vor. Die Vorbereitung von Anweisungen umfasst normalerweise das Parsen, Optimieren und Generieren von Plänen. Das Vorbereiten einer Anweisung bei jeder Verwendung führt zu einer geringen Leistungseinbuße. Der gemeinsam genutzte Cursormechanismus von Oracle minimiert jedoch die Kosten, und die Leistungseinbußen sind im Vergleich zu den Leistungsvorteilen von nativem dynamischem SQL in der Regel trivial.

Beispiele für DBMS_SQL-Paketcode und nativen dynamischen SQL-Code

Die folgenden Beispiele veranschaulichen die Unterschiede im Code, die zum Abschließen von Vorgängen mit dem Paket DBMS_SQL und nativem dynamischem SQL erforderlich sind. Insbesondere werden die folgenden Arten von Beispielen vorgestellt:

• Eine Abfrage
• Eine DML-Operation
• Eine DML-Rückgabeoperation

Im Allgemeinen lautet der native dynamische SQL-Code Lesbarer und kompakter, wodurch die Entwicklerproduktivität verbessert werden kann.

Abfragebeispiel

Das folgende Beispiel enthält eine dynamische Abfrageanweisung mit einer Bindungsvariablen (:jobname) und zwei ausgewählte Spalten (ename und sal):

stmt_str := "SELECT ename, sal FROM emp WHERE job = :jobname";

In diesem Beispiel werden Mitarbeiter mit dem j abgefragt ob description SALESMAN in der Spalte job der Tabelle emp. Tabelle 8-2 zeigt Beispielcode, der diese Abfrage mit dem Paket DBMS_SQL und nativem dynamischem SQL ausführt.

Tabelle 8-2 Abfragen mit dem DBMS_SQL-Paket und Native Dynamic SQL

DML-Beispiel

Die Das folgende Beispiel enthält eine dynamische INSERT -Anweisung für eine Tabelle mit drei Spalten:

stmt_str := "INSERT INTO dept_new VALUES (:deptno, :dname, :loc)";

In diesem Beispiel wird eine neue Zeile eingefügt für die sich die Spaltenwerte in den PL / SQL-Variablen deptnumber, deptname und location. Tabelle 8-3 zeigt Beispielcode, der diese DML-Operation mit dem Paket DBMS_SQL und nativem dynamischem SQL ausführt.

Tabelle 8-3 DML-Operation mit dem Paket DBMS_SQL und Native Dynamic SQL

DML-Rückgabebeispiel

Das folgende Beispiel enthält eine dynamische UPDATE Anweisung, die den Standort einer Abteilung aktualisiert, wenn die Abteilungsnummer (deptnumber) und ein neuer Standort (location) angegeben werden, und dann zurückkehrt Der Name der Abteilung:

stmt_str := "UPDATE dept_new SET loc = :newloc WHERE deptno = :deptno RETURNING dname INTO :dname";

In diesem Beispiel wird eine neue Zeile eingefügt, für die sich die Spaltenwerte in den PL / SQL-Variablen befinden. deptnumber, deptname und location. Tabelle 8-4 zeigt Beispielcode, der diese DML-Rückgabeoperation mit dem Paket DBMS_SQL und nativem dynamischem SQL ausführt.

Tabelle 8-4 DML-Rückgabeoperation mit DBMS_SQL Paket und natives dynamisches SQL

Anwendungsentwicklungssprachen außer PL / SQL

Bisher wurde in diesem Kapitel darauf eingegangen Informationen zur PL / SQL-Unterstützung für dynamisches SQL.Sie können jedoch andere Anwendungsentwicklungssprachen verwenden, um Programme zu implementieren, die dynamisches SQL verwenden. Diese Anwendungsentwicklungssprachen umfassen C / C ++, COBOL und Java.

Wenn Sie C / C ++ verwenden, können Sie Anwendungen entwickeln, die dynamisches SQL mit dem Oracle Call Interface (OCI) verwenden, oder Sie können den Pro * C / C ++ – Precompiler verwenden, um Ihrem C dynamische SQL-Erweiterungen hinzuzufügen Code. Wenn Sie COBOL verwenden, können Sie den Pro * COBOL-Precompiler verwenden, um Ihrem COBOL-Code dynamische SQL-Erweiterungen hinzuzufügen. Wenn Sie Java verwenden, können Sie mit JDBC Anwendungen entwickeln, die dynamisches SQL verwenden.

In der Vergangenheit war die einzige Möglichkeit, dynamisches SQL in PL / SQL-Anwendungen zu verwenden, die Verwendung von Paket. Die Verwendung dieses Pakets unterliegt einer Reihe von Einschränkungen, einschließlich Leistungsproblemen. Folglich haben Anwendungsentwickler möglicherweise eine der oben diskutierten Alternativen zu PL / SQL verwendet, um dynamisches SQL zu implementieren. Mit der Einführung von nativem dynamischem SQL in PL / SQL werden jedoch viele der Nachteile der Verwendung von PL / SQL für dynamisches SQL beseitigt.

Wenn Sie eine Anwendung haben, die OCI verwendet, ist Pro * C / C ++ oder Pro * COBOL für die dynamische SQL-Ausführung. Die für die Ausführung dynamischer SQL-Vorgänge erforderlichen Netzwerk-Roundtrips können die Leistung beeinträchtigen. Da sich diese Anwendungen normalerweise auf Clients befinden, sind mehr Netzwerkaufrufe erforderlich, um dynamische SQL-Vorgänge abzuschließen. Wenn Sie über diese Art von Anwendung verfügen, sollten Sie die dynamische SQL-Funktionalität auf gespeicherte Prozeduren und gespeicherte Funktionen in PL / SQL verschieben, die natives dynamisches SQL verwenden. Dies kann die Leistung Ihrer Anwendung verbessern, da sich die gespeicherten Prozeduren auf dem Server befinden können, wodurch der Netzwerk-Overhead entfällt. Sie können dann die gespeicherten PL / SQL-Prozeduren und gespeicherten Funktionen aus der Anwendung aufrufen.