8 kodning dynamiska SQL-satser

dynamisk SQL är en programmeringsteknik som gör att du kan bygga SQL-satser dynamiskt vid körning. Du kan skapa mer allmänna ändamål, flexibla applikationer med hjälp av dynamisk SQL eftersom den fullständiga texten i en SQL-sats kan vara okänd vid sammanställning. Till exempel kan dynamisk SQL du skapa en procedur som fungerar på en tabell vars namn inte är känt förrän runtime.,

Oracle innehåller två sätt att implementera dynamisk SQL i en PL/SQL-applikation:

• Native dynamic SQL, där du placerar dynamiska SQL-satser direkt i PL / SQL-block.
• Anropsprocedurer iDBMS_SQL – paketet.

detta kapitel omfattar följande ämnen:

• ”Vad är dynamisk SQL?”
• ” Varför använda dynamisk SQL?,”
• ” ett dynamiskt SQL-Scenario med Native Dynamic SQL ”
• ” välja mellan Native Dynamic SQL och DBMS_SQL-paketet ”
• ” använda dynamisk SQL på andra språk än PL/SQL ”
• ” använda PL/SQL-poster i SQL INSERT och UPDATE-satser”

Du kan hitta information om paketetDBMS_SQL I Oracle9i levererade PL / SQL-paket och typer referens.

Vad är dynamisk SQL?

dynamisk SQL gör att du kan skriva program som refererar SQL-satser vars fullständiga text inte är känd förrän runtime., Innan du diskuterar dynamisk SQL i detalj, kan en tydlig definition av statisk SQL ge en bra utgångspunkt för att förstå dynamisk SQL. Statiska SQL-satser ändras inte från körning till körning. Den fullständiga texten i statiska SQL-satser är kända vid sammanställning, vilket ger följande fördelar:

• framgångsrik sammanställning verifierar att SQL-satserna refererar till giltiga databasobjekt.
• framgångsrik sammanställning verifierar att de nödvändiga behörigheterna finns för att komma åt databasobjekten.,
• prestanda för statisk SQL är i allmänhet bättre än dynamisk SQL.

på grund av dessa fördelar bör du endast använda dynamisk SQL om du inte kan använda statisk SQL för att uppnå dina mål, eller om det är besvärligt att använda statisk SQL jämfört med dynamisk SQL. Statisk SQL har dock begränsningar som kan övervinnas med dynamisk SQL. Du kanske inte alltid känner till den fullständiga texten i SQL-satserna som måste utföras i en PL / SQL-procedur., Ditt program kan acceptera användarinmatning som definierar SQL-satser att utföra, eller ditt program kan behöva slutföra vissa bearbetningsarbete för att bestämma rätt åtgärd. I sådana fall bör du använda dynamisk SQL.

till exempel kan det hända att ett rapporteringsprogram i en datalagermiljö inte känner till det exakta tabellnamnet förrän körtid., Dessa tabeller kan namnges efter kvartalets startmånad och år, till exempel INV_01_1997, INV_04_1997, INV_07_1997, INV_10_1997, INV_01_1998 och så vidare. Du kan använda dynamisk SQL i rapportprogrammet för att ange tabellnamnet vid körning.

Du kanske också vill köra en komplex fråga med en användarvalbar sorteringsordning., Istället för att koda frågan två gånger, med olika klausuler ORDER BY, kan du konstruera frågan dynamiskt för att inkludera en angiven klausul ORDER BY.

dynamiska SQL-program kan hantera ändringar i datadefinitioner, utan att behöva kompilera om. Detta gör dynamisk SQL mycket mer flexibel än statisk SQL. Dynamisk SQL kan du skriva återanvändbar kod eftersom SQL kan enkelt anpassas för olika miljöer..,

Dynamic SQL låter dig också köra Data definition language (DDL) – satser och andra SQL-satser som inte stöds i rent statiska SQL-program.

Varför använda dynamisk SQL?

Du bör använda dynamisk SQL i de fall där statisk SQL inte stöder den åtgärd du vill utföra, eller i de fall där du inte känner till de exakta SQL-satser som måste utföras av en PL/SQL-procedur. Dessa SQL-satser kan bero på användarinmatning, eller de kan bero på bearbetningsarbete som utförs av programmet.,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.,

Du kan också bara användaTABLE – klausulen iSELECT – satsen genom dynamisk SQL. Följande PL / SQL-block innehåller till exempel enSELECT – sats som använder klausulenTABLE och native dynamic SQL:

exekvera dynamiska sökfrågor

Du kan använda dynamisk SQL för att skapa program som utför dynamiska sökfrågor, vars fullständiga text inte är känd förrän runtime., Många typer av program måste använda dynamiska sökfrågor, inklusive:

• program som tillåter användare att mata in eller välja sökfråga eller sorteringskriterier vid körning
• program som tillåter användare att mata in eller välja optimizer tips vid körtid
• program som frågar en databas där datadefinitionerna av tabeller ständigt förändras
• program som frågar en databas där nya tabeller skapas ofta

till exempel, se ”fråga med dynamisk SQL: exempel”, och se exempel i ”ett dynamiskt SQL-scenario med native dynamic SQL”.,

referera databasobjekt som inte finns vid sammanställning

många typer av program måste interagera med data som genereras regelbundet. Du kanske till exempel känner till tabelldefinitionerna vid kompileringstid, men inte tabellens namn.

dynamisk SQL kan lösa detta problem, eftersom det låter dig vänta tills runtime för att ange tabellnamn. Till exempel i sample Data warehouse ansökan diskuteras i ” vad är dynamisk SQL?”, nya tabeller genereras varje kvartal, och dessa tabeller har alltid samma definition., Du kan låta en användare ange namnet på tabellen vid körning med en dynamisk SQL-fråga som liknar följande:

optimera körningen dynamiskt

Du kan använda dynamisk SQL för att bygga en SQL-sats på ett sätt som optimerar körningen genom att sammanfoga tipsen i en SQL-sats dynamiskt. Detta gör att du kan ändra tips baserat på din nuvarande databas statistik, utan att kräva omkompilering.,

Följande procedur använder till exempel en variabel som heter a_hint för att tillåta användare att skicka ett tips till SELECT – satsen:

i det här exemplet kan användaren skicka något av följande värden för a_hint:

a_hint = '/*+ ALL_ROWS */'a_hinta_hint = '/*+ ALL_ROWS */'a_hint = '/*+ CHOOSE */'eller något annat giltigt tips alternativ.

exekvera dynamiska PL/SQL-Block

Du kan användaEXECUTEIMMEDIATE – satsen för att utföra anonyma PL / SQL-block. Du kan lägga till flexibilitet genom att bygga blockinnehållet vid körning.

Antag till exempel att genomroughou vill skriva ett program som tar ett händelsenummer och skickar till en handler för evenemanget. Hanterarens namn finns i formuläret EVENT_HANDLER_ event_num, där event_num är händelsens nummer., Ett tillvägagångssätt är att implementera avsändaren som en switch-sats, där koden hanterar varje händelse genom att göra ett statiskt samtal till sin lämpliga hanterare. Denna kod är inte särskilt utbyggbar eftersom sändarkoden måste uppdateras när en hanterare för en ny händelse läggs till.,

med hjälp av native dynamic SQL kan du skriva en mindre, mer flexibel händelseförsändare som liknar följande:

utföra dynamiska operationer med Invoker-Rights

genom att använda invoker-rights-funktionen med dynamic SQL kan du bygga program som utfärdar dynamiska SQL-satser under invokerns privilegier och schema. Dessa två funktioner, invokerrättigheter och dynamisk SQL, gör att du kan bygga återanvändbara applikationssubkomponenter som kan fungera på och komma åt invokerns data och moduler.,

ett dynamiskt SQL-Scenario med Native Dynamic SQL

det här scenariot visar hur du utför följande operationer med native dynamic SQL:

• utför DDL-och DML-operationer
• kör en rad och flera radfrågor

databasen i det här scenariot är ett företags personaldatabas (heterhr) med följande datamodell:

en huvudtabell som heteroffices innehåller listan över alla företagsplatser., Tabellenoffices har följande definition:

Column Name Null? Type LOCATION NOT_NULL VARCHAR2(200)

fleraemp_platstabeller innehåller information om anställda, där platsen är namnet på den stad där kontoret är beläget. Till exempel innehåller en tabell med namnet emp_houston information om anställda för företagets kontor i Houston, medan en tabell med namnet emp_boston innehåller information om anställda för företagets kontor i Boston.,

varjeemp_ platstabellen har följande definition:

följande avsnitt beskriver olika inhemska dynamiska SQL-operationer som kan utföras på data ihr – databasen.

prov DML Operation med Native Dynamic SQL

följande native dynamic SQL procedur ger en höjning till alla anställda med en viss jobbtitel:

prov DDL Operation med Native Dynamic SQL

EXECUTE IMMEDIATE uttalande kan utföra DDL operationer., Till exempel lägger följande procedur till en kontorsplats:

Följande procedur tar bort en kontorsplats:

Sample Single-Row Query Using Native Dynamic SQL

EXECUTE IMMEDIATE-satsen kan utföra dynamiska enkelradsfrågor. Du kan ange bindvariabler i klausulen USING och hämta den resulterande raden i det mål som anges i satsenINTO.,

följande funktion hämtar antalet anställda på en viss plats som utför ett angivet jobb:

Sample-Row Query Using Native Dynamic SQL

OPEN-FOR,FETCH, ochCLOSE uttalanden kan utföra dynamiska flerradsfrågor., Följande procedur listar till exempel alla anställda med ett visst jobb på en viss plats:

välja mellan Native Dynamic SQL och DBMS_SQL-paketet

Oracle ger två metoder för att använda dynamisk SQL inom PL/SQL: native dynamic SQL och paketet DBMS_SQL. Native dynamic SQL kan du placera dynamiska SQL-satser direkt i PL / SQL-kod. Dessa dynamiska satser inkluderar DML-satser (inklusive frågor), PL/SQL-anonyma block, DDL-satser, transaktionskontrollutdrag och sessionskontrollutdrag.,

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.

paketet DBMS_SQL är ett PL / SQL-bibliotek som erbjuder ett API för att köra SQL-satser dynamiskt. Paketet DBMS_SQL har procedurer för att öppna en markör, tolka en markör, leverera bindningar och så vidare. Program som använder paketetDBMS_SQL ringer till det här paketet för att utföra dynamiska SQL-operationer.

följande avsnitt ger detaljerad information om fördelarna med båda metoderna.,

fördelar med Native Dynamic SQL

Native dynamic SQL ger följande fördelar jämfört med paketetDBMS_SQL:

Native Dynamic SQL är lätt att använda

eftersom native dynamic SQL är integrerad med SQL kan du använda den på samma sätt som du använder statisk SQL inom PL / SQL-kod. Native dynamisk SQL-kod är vanligtvis mer kompakt och läsbar än motsvarande kod som använder paketet DBMS_SQL.,

med DBMS_SQL – paketet måste du ringa många procedurer och funktioner i en strikt sekvens, vilket gör att även enkla operationer kräver mycket kod. Du kan undvika denna komplexitet genom att använda native dynamic SQL istället.

tabell 8-1 visar skillnaden i mängden kod som krävs för att utföra samma åtgärd med paketetDBMS_SQL och native dynamic SQL.,

tabell 8-1-kod jämförelse av DBMS_SQL-paket och Native Dynamic SQL

Native Dynamic SQL är snabbare än DBMS_SQL

Native dynamic SQL i PL/SQL presterar jämförbart med prestanda för statisk SQL, eftersom PL/SQL-tolken har inbyggt stöd för den. Program som använder native dynamic SQL är mycket snabbare än program som använder paketetDBMS_SQL. Vanligtvis utför inbyggda dynamiska SQL-satser 1,5 till 3 gånger bättre än motsvarandeDBMS_SQL samtal. (Dina prestationsvinster kan variera beroende på din ansökan.,)

Native dynamic SQL buntar uttalandet förberedelse, bindning och utförande steg i en enda operation, vilket minimerar datakopiering och förfarande samtal overhead och förbättrar prestanda.

DBMS_SQL paketet är baserat på en procedur API och ådrar sig hög procedur samtal och datakopiering overhead. Varje gång du binder en variabel kopierar paketet DBMS_SQL PL/SQL bind-variabeln i sitt utrymme för användning under körning., Varje gång du utför en hämtning kopieras data till det utrymme som hanteras avDBMS_SQL – paketet och sedan kopieras de hämtade data, en kolumn i taget, till lämpliga PL / SQL-variabler, vilket resulterar i betydande omkostnader.

Performance Tips: använda Bind-variabler

När du använder antingen native dynamic SQL eller paketetDBMS_SQL kan du förbättra prestanda genom att använda bind-variabler, eftersom bind-variabler tillåter Oracle att dela en enda markör för flera SQL-satser.,

följande dynamiska SQL-kod använder till exempel inte bind-variabler:

för varje distinktmy_deptno – variabel skapas en ny markör, vilket orsakar resursbegränsning och dålig prestanda. Bind istället my_deptno som bind-variabel:

här återanvänds samma markör för olika värden för bind my_deptno, vilket förbättrar prestanda och skalbarhet.,

Native Dynamic SQL stöder användardefinierade typer

Native dynamic SQL stöder alla typer som stöds av statisk SQL i PL / SQL, inklusive användardefinierade typer som användardefinierade objekt, samlingar och REFs. PaketetDBMS_SQL stöder inte dessa användardefinierade typer.

Native Dynamic SQL stöder hämtning i poster

Native dynamic SQL och statisk SQL stöder båda hämtning i poster, men paketetDBMS_SQL gör det inte. Med native dynamic SQL kan raderna som härrör från en fråga hämtas direkt i PL / SQL-poster.,

i följande exempel hämtas raderna från en fråga tillemp_rec-posten:

fördelarna med DBMS_SQL-paketet

DBMS_SQL – paketet ger följande fördelar jämfört med native dynamic SQL:

DBMS_SQL stöds i Klientsidprogrammen

DBMS_SQL – paketet innehåller följande fördelar jämfört med native dynamic SQL:

DBMS_SQL stöds i Klientsidprogrammen

paketet stöds i klientsidan program, men native Dynamic SQL är inte., Varje samtal till DBMS_SQL – paketet från klientsidan översätts till ett PL / SQL remote procedure call (RPC); dessa samtal uppstår när du behöver binda en variabel, definiera en variabel eller utföra ett uttalande.

DBMS_SQL stöder DESCRIBE

DESCRIBE_COLUMNS – proceduren iDBMS_SQL – paketet kan användas för att beskriva kolumnerna för en markör som öppnas och analyseras genomDBMS_SQL. Den här funktionen liknar kommandot DESCRIBE I SQL*Plus., Native dynamic SQL har inte en DESCRIBE – anläggning.

DBMS_SQL stöder flera Raduppdateringar och raderar med en RETURKLAUSUL

paketetDBMS_SQL stöder uttalanden med enRETURNING – klausul som uppdaterar eller tar bort flera rader. Native dynamic SQL stöder endast en RETURNING klausul om en enda rad returneras.,

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., Efter den första parsningen kan du använda uttalandet flera gånger med olika uppsättningar bindningsargument.

Native dynamic SQL förbereder en SQL-sats varje gång uttrycket används, vilket vanligtvis innebär parsning, optimering och plangenerering. Även om de extra prepareoperationerna medför ett litet prestandastraff, uppvägs av avmattningen vanligtvis av prestandafördelarna med inbyggd dynamisk SQL.,

exempel på DBMS_SQL Paketkod och inbyggd dynamisk SQL-kod

följande exempel illustrerar skillnaderna i koden som krävs för att slutföra verksamheten med paketetDBMS_SQL och native dynamic SQL. Specifikt presenteras följande typer av exempel:

• en fråga
• en DML-operation
• en DML-återkommande operation

i allmänhet är den inbyggda dynamiska SQL-koden mer läsbar och kompakt, vilket kan förbättra utvecklarens produktivitet.,

fråga med dynamisk SQL: exempel

följande exempel innehåller ett dynamiskt frågeutdrag med en bind-variabel (:jobname) och två valda kolumner (ename ochsal):

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

det här exemplet frågar du med dynamisk SQL: exempel

för anställda med arbetsbeskrivningenSALESMAN I kolumnenjob I tabellen emp. Tabell 8-2 visar provkod som utför denna fråga med hjälp av paketet DBMS_SQL och native dynamic SQL.,

tabell 8-2 frågar med DBMS_SQL-paketet och Native Dynamic SQL

utför DML med dynamisk SQL: exempel

följande exempel inkluderar en dynamisk INSERT – sats för en tabell med tre kolumner:

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

i det här exemplet infogar du en ny rad för vilken kolumnvärdena finns i PL/SQL-variablerna deptnumber, deptname och location. Tabell 8-3 visar provkod som utför denna DML-åtgärd med hjälp av paketet DBMS_SQL och native dynamic SQL.,

tabell 8-3 DML-Operation med DBMS_SQL-paketet och Native Dynamic SQL

utför DML med RETURKLAUSUL med dynamisk SQL: exempel

följande exempel använder en dynamiskUPDATE – sats för att uppdatera en avdelnings plats, returnerar sedan namnet på avdelningen:

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

tabell 8-4 visar provkod som åstadkommer denna operation med bådeDBMS_SQL – paketet och Native Dynamic SQL.,

tabell 8-4 DML Returoperation med DBMS_SQL-paketet och Native Dynamic SQL

använda dynamisk SQL på andra språk än PL/SQL

även om det här kapitlet diskuterar PL/SQL-stöd för dynamisk SQL kan du ringa dynamisk SQL från andra språk:

• Om du använder C/C++ kan du ringa dynamisk SQL med Oracle Call Interface (OCI), eller så kan du använda Pro*C/C++ precompiler för att lägga till dynamisk SQL SQL-tillägg till din C-kod.
• Om du använder COBOL kan du använda Pro*COBOL precompiler för att lägga till dynamiska SQL-tillägg till din COBOL-kod.,
• Om du använder Java kan du utveckla program som använder dynamisk SQL med JDBC.

om du har ett program som använder OCI, Pro*C/C++ eller Pro*COBOL för att utföra dynamisk SQL, bör du överväga att byta till native dynamic SQL inside PL / SQL lagrade procedurer och funktioner. Nätverks rundturer som krävs för att utföra dynamiska SQL-operationer från klientsidan program kan skada prestanda. Lagrade procedurer kan bo på servern, vilket eliminerar nätverksöverhead., Du kan ringa PL / SQL lagrade procedurer och lagrade funktioner från Oci, Pro*c/c++ eller Pro*COBOL ansökan.,br>Se även:

För information om att ringa Oracle lagrade procedurer och lagrade funktioner från olika språk, Se:

• Oracle Call Interface programmerare Guide
• Pro*C/C++ Precompiler programmerarens Guide
• Pro*COBOL Precompiler programmerarens Guide
• Oracle9i Java lagrade procedurer utvecklarens Guide

använda PL/SQL-poster i SQL infoga och uppdatera satser

Även om du kan räkna upp varje fält i en PL/SQL-post när du infogar eller uppdaterar rader i en tabell, är den resulterande koden inte särskilt läsbar eller underhållbar., Istället kan du använda PL/SQL-poster direkt i dessa uttalanden. Den mest praktiska tekniken är att deklarera posten med ett %rowtype-attribut, så att det har exakt samma fält som SQL-tabellen.

även om denna teknik hjälper till att integrera PL/SQL-variabler och typer närmare med SQL DML-satser, kan du inte använda PL / SQL-poster som bind-variabler i dynamiska SQL-satser.