8 Coding Dynamic SQL Statements

Dynamic SQL is een programmeertechniek waarmee u SQL statements dynamisch kunt bouwen tijdens runtime. U kunt meer algemene, flexibele toepassingen maken met behulp van dynamische SQL omdat de volledige tekst van een SQL-statement onbekend kan zijn bij het compileren. Met dynamic SQL kunt u bijvoorbeeld een procedure maken die werkt op een tabel waarvan de naam tot runtime niet bekend is.,

Oracle bevat twee manieren om dynamische SQL te implementeren in een PL/SQL-toepassing:

• Native dynamic SQL, waarbij u dynamische SQL-statements rechtstreeks in PL / SQL-blokken plaatst.
• Aanroepprocedures in hetDBMS_SQL pakket.

Dit hoofdstuk behandelt de volgende onderwerpen:

• “Wat Is dynamische SQL?”
• ” waarom dynamische SQL gebruiken?,”
• ” een dynamisch SQL-Scenario met Native Dynamic SQL “
• ” kiezen tussen Native Dynamic SQL en het Dbms_sql-pakket “
• ” dynamische SQL gebruiken in andere talen dan PL/SQL “
• ” PL/SQL-Records gebruiken in SQL INSERT and UPDATE Statements”

u kunt details vinden over de DBMS_SQL pakket in de Oracle9i geleverde PL/SQL pakketten en types referentie.

Wat Is dynamische SQL?

Dynamic SQL stelt u in staat om programma ‘ s te schrijven die verwijzen naar SQL-statements waarvan de volledige tekst niet bekend is tot runtime., Alvorens dynamische SQL in detail te bespreken, kan een duidelijke definitie van statische SQL een goed uitgangspunt zijn voor het begrijpen van dynamische SQL. Statische SQL-statements veranderen niet van uitvoering naar uitvoering. De volledige tekst van statische SQL-statements is bekend bij de compilatie, wat de volgende voordelen biedt:

• succesvolle compilatie controleert of de SQL-statements verwijzen naar geldige databaseobjecten.
• succesvolle compilatie controleert of de benodigde rechten aanwezig zijn om toegang te krijgen tot de database-objecten.,
• de prestaties van statische SQL zijn over het algemeen beter dan dynamische SQL.

vanwege deze voordelen moet u dynamische SQL alleen gebruiken als u geen statische SQL kunt gebruiken om uw doelen te bereiken, of als het gebruik van statische SQL omslachtig is in vergelijking met dynamische SQL. Statische SQL heeft echter beperkingen die kunnen worden overwonnen met dynamische SQL. U kent misschien niet altijd de volledige tekst van de SQL statements die uitgevoerd moeten worden in een PL/SQL procedure., Uw programma kan gebruikersinvoer accepteren die de uit te voeren SQL-statements definieert, of uw programma moet mogelijk wat verwerkingswerk voltooien om de juiste koers te bepalen. In dergelijke gevallen moet u dynamische SQL gebruiken.

bijvoorbeeld, een reporting applicatie in een data warehouse omgeving kan de exacte tabelnaam niet kennen tot runtime., Deze tabellen kunnen worden genoemd volgens de beginmaand en het jaar van het kwartaal, bijvoorbeeld INV_01_1997, INV_04_1997, INV_07_1997, INV_10_1997, INV_01_1998, en zo verder. U kunt dynamic SQL gebruiken in uw rapportagetoepassing om de tabelnaam tijdens runtime op te geven.

u kunt ook een complexe query uitvoeren met een door de gebruiker te selecteren sorteervolgorde., In plaats van de query tweemaal te coderen, met verschillende ORDER BY clausules, kunt u de query dynamisch construeren om een opgegeven ORDER BY clausule op te nemen.

dynamische SQL-programma ‘ s kunnen veranderingen in gegevensdefinities verwerken, zonder de noodzaak om opnieuw te compileren. Dit maakt dynamische SQL veel flexibeler dan statische SQL. Met Dynamic SQL kunt u herbruikbare code schrijven, omdat de SQL gemakkelijk kan worden aangepast aan verschillende omgevingen..,met

Dynamic sql kunt u ook DDL-statements (data definition language) en andere SQL-statements uitvoeren die niet worden ondersteund in puur statische SQL-programma ‘ s.

waarom dynamische SQL gebruiken?

u moet dynamische SQL gebruiken in gevallen waarin statische SQL de operatie die u wilt uitvoeren niet ondersteunt, of in gevallen waarin u niet precies weet welke SQL-statements moeten worden uitgevoerd door een PL/SQL-procedure. Deze SQL statements kunnen afhangen van de input van de gebruiker, of ze kunnen afhangen van het verwerken van werk gedaan door het programma.,DL and SCL Statements in PL/SQL

In PL/SQL, you can only execute the following types of statements using dynamic SQL, rather than static SQL:

• Data definition language (DDL) statements, such as CREATE, DROP, GRANT, and REVOKE
• Session control language (SCL) statements, such as ALTER SESSION and SET ROLE
  
  See Also:

  Oracle9i SQL Reference for information about DDL and SCL statements.,

ook kunt u alleen de TABLE clausule gebruiken in de SELECT statement via dynamische SQL. Het volgende PL/SQL-blok bevat bijvoorbeeld eenSELECT statement dat deTABLE – clausule gebruikt en native dynamische SQL:

dynamische query ’s uitvoeren

u kunt dynamische SQL gebruiken om toepassingen te maken die dynamische query’ s uitvoeren, waarvan de volledige tekst niet bekend is tot runtime., Veel soorten toepassingen moeten dynamische query ‘ s gebruiken, waaronder:

• toepassingen die gebruikers toestaan om query-zoek-of sorteercriteria in runtime in te voeren of te kiezen
• toepassingen die gebruikers toestaan om optimizer-hints in te voeren of te kiezen tijdens runtime
• toepassingen die een database opvragen waar de gegevensdefinities van tabellen voortdurend veranderen
• toepassingen die een database opvragen waar vaak nieuwe tabellen worden aangemaakt

voor voorbeelden, zie “Querying Using Dynamic SQL: example”, en zie de query voorbeelden in “a dynamic SQL scenario using native Dynamic SQL”.,

Referentiebestandobjecten die niet bestaan bij de compilatie

veel typen toepassingen moeten interageren met gegevens die periodiek worden gegenereerd. U kent bijvoorbeeld de tabeldefinities tijdens het compileren, maar niet de namen van de tabellen.

dynamische SQL kan dit probleem oplossen, omdat het u laat wachten tot runtime om de tabelnamen op te geven. Bijvoorbeeld, in de sample data warehouse applicatie besproken in ” Wat is Dynamic SQL?”, nieuwe tabellen worden gegenereerd elk kwartaal, en deze tabellen hebben altijd dezelfde definitie., U kunt een gebruiker de naam van de tabel laten opgeven tijdens runtime met een dynamische SQL-query die vergelijkbaar is met de volgende:

dynamisch optimaliseren

u kunt dynamische SQL gebruiken om een SQL-statement te bouwen op een manier die de uitvoering optimaliseert door de hints dynamisch samen te voegen in een SQL-statement. Hiermee kunt u de hints wijzigen op basis van uw huidige database statistieken, zonder dat hercompilatie nodig is.,

bijvoorbeeld, de volgende procedure gebruikt een variabele genaamd a_hint om gebruikers toe te staan een hint optie door te geven aan de SELECT statement:

In dit voorbeeld kan de gebruiker een van de volgende waarden doorgeven voor a_hint:

a_hint = '/*+ ALL_ROWS */'a_hint = '/*+ FIRST_ROWS */'a_hint = '/*+ CHOOSE */'of een andere geldige hint optie.

dynamische PL/SQL-blokken uitvoeren

u kunt de opdracht EXECUTE IMMEDIATE gebruiken om anonieme PL / SQL-blokken uit te voeren. U kunt Flexibiliteit toevoegen door de inhoud van het blok tijdens runtime te construeren.

bijvoorbeeld, stel dat y through throughou een toepassing wilt schrijven die een gebeurtenisnummer neemt en naar een handler voor de gebeurtenis stuurt. De naam van de handler is in de vorm EVENT_HANDLER_event_num, waarbij event_num het nummer van de gebeurtenis is., Een aanpak is om de dispatcher te implementeren als een switch statement, waar de code behandelt elke gebeurtenis door het maken van een statische oproep aan de juiste handler. Deze code is niet erg uitbreidbaar omdat de dispatcher code moet worden bijgewerkt wanneer een handler voor een nieuwe gebeurtenis wordt toegevoegd.,

met behulp van native dynamic sql, kunt u een kleinere, meer flexibele gebeurtenis dispatcher schrijven, vergelijkbaar met de volgende:

dynamische bewerkingen uitvoeren met behulp van Invoker-rechten

met behulp van de invoker-rechten functie met dynamische SQL, kunt u toepassingen bouwen die dynamische SQL-statements uitgeven onder de privileges en het schema van de invoker. Deze twee functies, invoker-rechten en dynamic SQL, kunt u herbruikbare toepassing subcomponenten die kunnen werken op en toegang tot de gegevens en modules van de invoker te bouwen.,

een dynamisch SQL-Scenario met Native Dynamic SQL

dit scenario laat u zien hoe u de volgende bewerkingen met native dynamic sql kunt uitvoeren:

• DDL-en DML-bewerkingen uitvoeren
• enkele rij-en queries met meerdere rijen uitvoeren

de database in dit scenario is de personeelsdatabase van een bedrijf (genaamd hr) met het volgende gegevensmodel:

een Mastertabel genaamd offices bevat de lijst van alle bedrijfslocaties., De tabel offices heeft de volgende definitie:

Column Name Null? Type LOCATION NOT_NULL VARCHAR2(200)

meerdere emp_locatietabellen bevatten de werknemersinformatie, waarbij locatie de naam is van de plaats waar het kantoor zich bevindt. Een tabel met de naam emp_houston bevat bijvoorbeeld informatie over werknemers voor het kantoor van het bedrijf in Houston, terwijl een tabel met de naam emp_boston informatie over werknemers voor het kantoor van het bedrijf in Boston bevat.,

elke emp_locatietabel heeft de volgende definitie:

de volgende secties beschrijven verschillende native dynamische SQL-bewerkingen die kunnen worden uitgevoerd op de gegevens in de hr database.

Sample DML-bewerking met Native Dynamic SQL

de volgende native dynamic SQL-procedure geeft een verhoging aan alle werknemers met een bepaalde functie:

Sample DDL-bewerking met Native Dynamic SQL

de instructie EXECUTE IMMEDIATE kan DDL-bewerkingen uitvoeren., De volgende procedure voegt bijvoorbeeld een kantoorlocatie toe:

de volgende procedure verwijdert een kantoorlocatie:

Sample Single-Row Query Using Native Dynamic SQL

het EXECUTE IMMEDIATE statement kan dynamische Single-row query ‘ s uitvoeren. U kunt bind-variabelen opgeven in deUSING clausule en de resulterende rij ophalen in het doel dat is gespecificeerd in deINTO clausule van het statement.,

de volgende functie haalt het aantal werknemers op een bepaalde locatie die een bepaalde taak uitvoeren:

Sample Multiple-Row Query met behulp van Native Dynamic SQL

De OPEN-FOR, FETCH, en CLOSE statements kunnen dynamische query ‘ s met meerdere rijen uitvoeren., De volgende procedure toont bijvoorbeeld alle werknemers met een bepaalde taak op een bepaalde locatie:

kiezen tussen Native Dynamic SQL en het Dbms_sql-pakket

Oracle biedt twee methoden voor het gebruik van dynamische SQL binnen PL/SQL: native dynamic SQL en het DBMS_SQL – pakket. Met Native dynamic sql kunt u dynamische SQL-statements rechtstreeks in PL/SQL-code plaatsen. Deze dynamische statements omvatten DML statements (inclusief queries), PL/SQL anonieme blokken, DDL statements, transaction control statements en Session control statements.,

To process most native dynamic SQL statements, you use the EXECUTE IMMEDIATE statement. To process a multi-row query (SELECT statement), you use OPEN-FOR, FETCH, and CLOSE statements.

hetDBMS_SQL pakket is een PL/SQL-bibliotheek die een API biedt om SQL-statements dynamisch uit te voeren. HetDBMS_SQL pakket heeft procedures om een cursor te openen, een cursor te ontleden, te leveren bindt, enzovoort. Programma ‘ s die het DBMS_SQL pakket gebruiken, roepen dit pakket aan om dynamische SQL-bewerkingen uit te voeren.

de volgende paragrafen geven gedetailleerde informatie over de voordelen van beide methoden.,

voordelen van Native Dynamic SQL

Native dynamic SQL biedt de volgende voordelen ten opzichte van het DBMS_SQL pakket:

Native Dynamic SQL is eenvoudig te gebruiken

omdat native dynamic SQL is geïntegreerd met SQL, kunt u het gebruiken op dezelfde manier als u statische SQL gebruikt binnen PL / SQL-code. Native dynamische SQL-code is doorgaans compacter en leesbaar dan equivalente code die het DBMS_SQL pakket gebruikt.,

met hetDBMS_SQL pakket moet u veel procedures en functies in een strikte volgorde aanroepen, waardoor zelfs eenvoudige operaties veel code vereisen. U kunt deze complexiteit voorkomen door in plaats daarvan native dynamic sql te gebruiken.

tabel 8-1 illustreert het verschil in de hoeveelheid code die nodig is om dezelfde bewerking uit te voeren met behulp van het DBMS_SQL pakket en native dynamic SQL.,

tabel 8-1 Code vergelijking van Dbms_sql pakket en Native dynamische SQL

Native dynamische SQL is sneller dan Dbms_sql

Native dynamische SQL in PL/SQL presteert vergelijkbaar met de prestaties van statische SQL, omdat de PL / SQL interpreter heeft ingebouwde ondersteuning voor het. Programma ’s die native dynamic SQL gebruiken zijn veel sneller dan Programma’ s die het DBMS_SQL pakket gebruiken. Typisch, native dynamische SQL statements presteren 1,5 tot 3 keer beter dan equivalent DBMS_SQL aanroepen. (Uw prestatiewinst kan variëren afhankelijk van uw toepassing.,)

Native dynamic SQL bundelt de stappen voor het voorbereiden, binden en uitvoeren van statements in één enkele bewerking, waardoor het kopiëren van gegevens en de aanroep overhead van de procedure worden geminimaliseerd en de prestaties worden verbeterd.

het DBMS_SQL pakket is gebaseerd op een procedurele API en maakt gebruik van high procedure call en data copy overhead. Elke keer dat u een variabele bindt, kopieert hetDBMS_SQL pakket de PL/SQL bind variabele in zijn ruimte voor gebruik tijdens het uitvoeren., Elke keer dat u een fetch uitvoert, worden de gegevens gekopieerd naar de ruimte die wordt beheerd door het DBMS_SQL pakket en vervolgens worden de opgehaalde gegevens één kolom per keer gekopieerd naar de juiste PL/SQL variabelen, wat resulteert in aanzienlijke overhead.

Prestatietip: Bind-variabelen

bij gebruik van native dynamic sql of het DBMS_SQL pakket, kunt u de prestaties verbeteren door bind-variabelen te gebruiken, omdat bind-variabelen Oracle toestaan om een enkele cursor te delen voor meerdere SQL-statements.,

bijvoorbeeld, de volgende standaard dynamische SQL-code gebruikt bind-variabelen niet:

voor elke afzonderlijke my_deptno variabele wordt een nieuwe cursor aangemaakt, waardoor resource contention en slechte prestaties worden veroorzaakt. Bind my_deptno als bind-variabele:

Hier wordt dezelfde cursor hergebruikt voor verschillende waarden van de bind my_deptno, wat de prestaties en schaalbaarheid verbetert.,

Native Dynamic SQL ondersteunt door de gebruiker gedefinieerde typen

Native dynamic SQL ondersteunt alle typen die door statische SQL in PL/SQL worden ondersteund, inclusief door de gebruiker gedefinieerde typen zoals door de gebruiker gedefinieerde objecten, collecties enREFs. Het pakket DBMS_SQL ondersteunt deze door de gebruiker gedefinieerde types niet.

Native dynamische SQL ondersteunt het ophalen in Records

Native dynamische SQL en statische SQL ondersteunen beide het ophalen in records, maar het DBMS_SQL pakket niet. Met native dynamic SQL kunnen de rijen die voortvloeien uit een query direct worden opgehaald in PL/SQL-records.,

in het volgende voorbeeld worden de rijen van een query opgehaald in de emp_rec record:

voordelen van het Dbms_sql-pakket

het DBMS_SQL biedt de volgende voordelen ten opzichte van native dynamische SQL:

DBMS_SQL wordt ondersteund in Client-Side programma ‘ s

het DBMS_SQL pakket wordt ondersteund in programma ‘ s aan de clientzijde, maar native Dynamic sql niet., Elke aanroep naar hetDBMS_SQL pakket van het client-side programma vertaalt zich naar een PL/SQL remote procedure call (RPC); deze aanroep vindt plaats wanneer u een variabele moet binden, een variabele moet definiëren of een statement moet uitvoeren.

DBMS_SQL ondersteunt DESCRIBE

deDESCRIBE_COLUMNS procedure in hetDBMS_SQL pakket kan worden gebruikt om de kolommen te beschrijven voor een cursor geopend en ontleed doorDBMS_SQL. Deze functie is vergelijkbaar met het DESCRIBE commando in SQL*Plus., Native dynamische SQL heeft geenDESCRIBE faciliteit.

DBMS_SQL Ondersteunt meerdere rij-Updates en verwijdert met een terugkerende Clausule

het DBMS_SQL pakket ondersteunt statements met een RETURNING clausule die meerdere rijen bijwerken of verwijderen. Native dynamic SQL ondersteunt alleen eenRETURNING clausule als een enkele rij wordt geretourneerd.,

DBMS_SQL Supports SQL Statements Larger than 32KB

The DBMS_SQL package supports SQL statements larger than 32KB; native dynamic SQL does not.

DBMS_SQL Lets You Reuse SQL Statements

The PARSE procedure in the DBMS_SQL package parses a SQL statement once., Na de eerste parsing kunt u het statement meerdere keren gebruiken met verschillende sets bind-argumenten.

Native dynamic SQL bereidt elke keer dat het statement wordt gebruikt een SQL-statement voor, wat meestal gepaard gaat met parsing, optimalisatie en het genereren van plannen. Hoewel de extra prepare-operaties een kleine prestatiefout veroorzaken, wordt de vertraging meestal gecompenseerd door de prestatievoordelen van native dynamic SQL.,

voorbeelden van Dbms_sql pakketcode en Native dynamische SQL-Code

de volgende voorbeelden illustreren de verschillen in de code die nodig is om bewerkingen met de DBMS_SQL pakket en native dynamische SQL te voltooien. In het bijzonder worden de volgende soorten voorbeelden gepresenteerd:

• een query
• een DML-operatie
• een DML-retouroperatie

in het algemeen is de native dynamic SQL-code leesbaarder en compacter, wat de productiviteit van ontwikkelaars kan verbeteren.,

het Opvragen van het Gebruik van Dynamische SQL: Voorbeeld

in Het volgende voorbeeld bevat een dynamische query statement met één binden variabele (:jobname) en twee kolommen selecteren (ename en sal):

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

in Dit voorbeeld query ‘ s voor werknemers met de functieomschrijving SALESMAN in de job kolom van de emp tabel. Tabel 8-2 toont voorbeeldcode die deze query uitvoert met behulp van hetDBMS_SQL pakket en native dynamic SQL.,

Table 8-2 Querying Using the Dbms_sql Package and Native Dynamic SQL

uitvoeren van DML met behulp van dynamische SQL: voorbeeld

het volgende voorbeeld bevat een dynamisch INSERT statement voor een tabel met drie kolommen:

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

dit voorbeeld voegt een nieuwe rij in waarvoor de kolom waarden zijn in de PL/SQL variabelen deptnumber, deptname, en location. Tabel 8-3 toont de voorbeeldcode die deze DML operatie uitvoert met behulp van hetDBMS_SQL pakket en native dynamic SQL.,

Tabel 8-3 DML Bediening met Behulp van de DBMS_SQL Pakket en eigen Dynamische SQL

het Uitvoeren van DML met het TERUGSTUREN van het Component met Behulp van Dynamische SQL: Voorbeeld

in Het volgende voorbeeld wordt een dynamische UPDATE statement voor een update van de locatie van een afdeling, dan geeft de naam van de afdeling:

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

Tabel 8-4 voorbeeld van code die volbrengt deze bewerking met zowel de DBMS_SQL pakket en eigen dynamische SQL.,

tabel 8-4 DML retournering met behulp van het Dbms_sql-pakket en Native Dynamic SQL

met behulp van dynamische SQL in andere talen dan PL/SQL

hoewel dit hoofdstuk PL/SQL-ondersteuning voor dynamische SQL bespreekt, kunt u dynamische SQL vanuit andere talen aanroepen:

• Als u C/c++ gebruikt, kunt u dynamische SQL aanroepen met de Oracle Call Interface (OCI), of u kunt de Pro*C/C++ PRECOMPILER gebruiken om dynamische SQL-extensies aan uw C-code toe te voegen.
• als u COBOL gebruikt, kunt u de pro*COBOL precompiler gebruiken om dynamische SQL extensies aan uw COBOL code toe te voegen.,
• Als u Java gebruikt, kunt u toepassingen ontwikkelen die dynamische SQL met JDBC gebruiken.

Als u een toepassing hebt die OCI, Pro*C/C++ of Pro*COBOL gebruikt om dynamische SQL uit te voeren, moet u overwegen om over te schakelen naar native dynamische SQL binnen PL/SQL opgeslagen procedures en functies. De netwerk round-trips die nodig zijn om dynamische SQL-bewerkingen uit te voeren vanuit client-side applicaties kunnen de prestaties schaden. Opgeslagen procedures kunnen zich op de server bevinden, waardoor de netwerkoverhead wordt geëlimineerd., U kunt de PL/SQL opgeslagen procedures en opgeslagen functies bellen vanuit de OCI, Pro*C/C++, of Pro*COBOL applicatie.,br>Zie Ook:

Voor informatie over het aanroepen van opgeslagen procedures van Oracle worden opgeslagen en functies van de verschillende talen refereer je naar:

• Oracle Call Interface Programmer ’s Guide
• Pro*C/C++ Precompiler Programmer’ s Guide
• Pro*COBOL Precompiler Programmer ’s Guide
• Oracle9i Stored Procedures Developer’ s Guide

het Gebruik van PL/SQL Records in SQL INSERT en UPDATE-Instructies

Hoewel u kunt opsommen elk veld van een PL/SQL-record bij het plaatsen of bijwerken van de rijen in een tabel, de resulterende code is niet bijzonder leesbaar of te onderhouden., In plaats daarvan kun je PL/SQL records direct gebruiken in deze statements. De meest handige techniek is om de record te declareren met behulp van een % ROWTYPE attribuut, zodat het precies dezelfde velden heeft als de SQL tabel.

hoewel deze techniek helpt om PL/SQL variabelen en types nauwer te integreren met SQL DML statements, kunt u PL/SQL records niet gebruiken als bind variabelen in dynamische SQL statements.