8 Dynamisk SQL (Svenska)

Dynamisk SQL är en programmeringsteknik som gör att du kan bygga SQL-uttalanden dynamiskt vid körning. Du kan skapa mer allmänna ändamål, flexibla applikationer genom att använda dynamisk SQL eftersom den fullständiga texten i en SQL-sats kan vara okänd vid kompileringen. Med dynamisk SQL kan du till exempel skapa en procedur som fungerar på en tabell vars namn inte är känt förrän körtid.

I tidigare versioner av Oracle var det enda sättet att implementera dynamisk SQL i en PL / SQL-applikation med paketet DBMS_SQL. Oracle8i introducerar inbyggd dynamisk SQL, ett alternativ till DBMS_SQL -paketet. Med hjälp av inbyggd dynamisk SQL kan du placera dynamiska SQL-uttalanden direkt i PL / SQL-block.

Detta kapitel täcker följande ämnen:

• Vad är dynamisk SQL?
• När ska man använda dynamisk SQL
• Ett dynamiskt SQL-scenario med hjälp av Native Dynamic SQL
• Native Dynamic SQL kontra DBMS_SQL-paketet
• Application Utvecklingsspråk annat än PL / SQL

Vad är dynamisk SQL?

Med dynamisk SQL kan du skriva program som refererar till SQL-uttalanden vars fullständiga text inte är känd förrän körtid. Innan vi 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-uttalanden ändras inte från körning till körning. Hela texten för statiska SQL-uttalanden är känd vid sammanställning, vilket ger följande fördelar:

• Framgångsrik kompilering verifierar att SQL-uttalanden refererar till giltiga databasobjekt.
• Framgångsrik kompilering verifierar att nödvändiga behörigheter 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 bara använda dynamisk SQL om du inte kan använda statisk SQL för att uppnå dina mål, eller om att använda statisk SQL är besvärlig 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 för SQL-uttalanden som måste köras i en PL / SQL-procedur. Ditt program kan acceptera användarinmatning som definierar de SQL-uttalanden som ska köras, eller så kan ditt program behöva slutföra något bearbetningsarbete för att fastställa rätt åtgärd. I sådana fall bör du använda dynamisk SQL.

Tänk till exempel på ett rapporteringsprogram som utför standardfrågor på tabeller i en datalagermiljö där det exakta tabellnamnet är okänt fram till körtid. För att effektivt rymma den stora mängden data i datalagret skapar du en ny tabell varje kvartal för att lagra fakturainformationen för kvartalet. Dessa tabeller har alla exakt samma definition och namnges enligt startmånaden och året för kvartalet, till exempel INV_01_1997, INV_04_1997, INV_07_1997, INV_10_1997, INV_01_1998, etc. I ett sådant fall kan du använda dynamisk SQL i din rapporteringsapplikation för att ange tabellnamnet vid körning.

Med statisk SQL måste all datadefinitionsinformation, såsom tabelldefinitioner, som refereras av SQL-satserna i ditt program vara känd vid sammanställningen. Om datadefinitionen ändras måste du ändra och kompilera om programmet. Dynamiska SQL-program kan hantera ändringar i datadefinitionsinformation, eftersom SQL-uttalanden kan ändras ”i farten” vid körning. Därför är dynamisk SQL mycket mer flexibel än statisk SQL. Dynamisk SQL gör att du kan skriva applikationskod som kan återanvändas eftersom koden definierar en process som är oberoende av de specifika SQL-uttalanden som används.

Dessutom låter dynamisk SQL dig köra SQL-uttalanden som inte stöds i statisk SQL-program, till exempel DDL-uttalanden (Data Definition Language). Stöd för dessa uttalanden gör att du kan åstadkomma mer med dina PL / SQL-program.

När ska du använda dynamisk SQL

Du bör använda dynamisk SQL i fall där statisk SQL inte stöder operation som du vill utföra, eller i fall där du inte känner till de exakta SQL-satserna som måste köras med en PL / SQL-procedur. Dessa SQL-uttalanden kan bero på användarnas inmatning, eller de kan bero på bearbetningsarbete som utförts av programmet. Följande avsnitt beskriver typiska situationer där du ska använda dynamisk SQL och typiska problem som kan lösas med dynamisk SQL.

Så här kör du dynamiska DML-uttalanden

Du kan använda dynamisk SQL för att utföra DML-satser där den exakta SQL-satsen inte är känd förrän körning.För exempel, se DML-exemplen i ”Exempel på DBMS_SQL-paketkod och Native Dynamic SQL-kod” och ”Exempel på DML-operation”.

Att utföra uttalanden som inte stöds av statisk SQL i PL / SQL

I PL / SQL kan du inte köra följande typer av uttalanden med statisk SQL:

• DDL-uttalanden för datadefinitionsspråk, såsom CREATE, DROP, GRANT och REVOKE
• Sessionsstyrningsspråk (SCL) uttalanden, såsom ALTER SESSION och SET ROLE
  Se även:

  Oracle8i SQL-referens för information om DDL- och SCL-uttalanden.

Använd dynamisk SQL om du behöver utföra någon av dessa typer av uttalanden i en PL / SQL-block.

Dessutom tillåter inte statisk SQL i PL / SQL användning av TABLE -satsen i SELECT uttalanden. Det finns ingen sådan begränsning i dynamisk SQL. Till exempel innehåller följande PL / SQL-block ett SELECT -uttalande som använder TABLE -satsen och inbyggd dynamisk SQL:

Så här kör du dynamiska frågor

Du kan använda dynamisk SQL för att skapa applikationer som kör dynamiska frågor, vilket är frågor vars fullständiga text inte är känd förrän körtid. Många typer av applikationer behöver använda dynamiska frågor, inklusive:

• Program som tillåter användare att mata in eller välja sök- eller sorteringskriterier vid körning
• Program som tillåter användare att mata in eller välj optimeringstips vid körningstid
• Program som frågar efter en databas där datadefinitionerna för tabeller ändras ständigt
• Program som frågar efter en databas där nya tabeller skapas ofta

För exempel, se ”Frågeexempel” och se frågeexemplen i ”Ett dynamiskt SQL-scenario med hjälp av Native Dynamic SQL”.

För att referera till databasobjekt som inte finns vid sammanställning

Många typer av applikationer måste interagera med data som genereras regelbundet. Det kan till exempel vara möjligt att bestämma definitionen av databastabellerna vid sammanställning, men inte namnen på tabellerna, eftersom nya tabeller genereras regelbundet. Din applikation behöver komma åt data, men det finns inget sätt att veta tabellernas exakta namn förrän körtid.

Dynamisk SQL kan lösa detta problem, eftersom dynamisk SQL gör att du kan vänta tills körtid för att ange tabellnamn du behöver komma åt. I exempelvis datalagerapplikationen som diskuteras i ”Vad är dynamisk SQL?” Skapas nya tabeller varje kvartal och dessa tabeller har alltid samma definition. I det här fallet kan du tillåta en användare att ange namnet på tabellen under körning med en dynamisk SQL-fråga som liknar följande:

För att optimera utförandet dynamiskt

Om du använder statisk SQL, du måste bestämma vid sammanställningen hur du vill konstruera dina SQL-uttalanden, om du vill ha tips i dina uttalanden och, om du inkluderar tips, exakt vilka tips du ska ha. Du kan dock använda dynamisk SQL för att bygga en SQL-sats på ett sätt som optimerar körningen och / eller sammanfogar tipsen till en SQL-sats dynamiskt. Detta låter dig ändra tips baserat på din nuvarande databasstatistik utan att behöva kompileras igen.

Till exempel använder följande procedur en variabel som heter a_hint för att tillåta användare för att skicka ett tipsalternativ till SELECT uttalande:

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

Att anropa dynamiska PL / SQL-block

Du kan använda EXECUTE IMMEDIATE uttalande för att åberopa anonyma PL / SQL-block. Möjligheten att åberopa dynamiska PL / SQL-block kan vara användbar för applikationstillägg och anpassning där modulen som ska köras bestäms dynamiskt vid körning.

Anta till exempel att du vill skriva ett program som tar en händelse nummer och sändningar till en hanterare för evenemanget. Hanterarens namn har formen EVENT_HANDLER_ event_num, där event_num är händelsens nummer. Ett tillvägagångssätt skulle vara att implementera sändaren som en switch-sats, som visas nedan, där koden hanterar varje händelse genom att ringa ett statiskt samtal till lämplig hanterare. måste uppdateras när en hanterare för en ny händelse läggs till. Med hjälp av inbyggd dynamisk SQL kan du dock skriva en utdragbar händelsedispenser som liknar följande:

Att utföra dynamiska operationer med Invoker-Rights

Genom att använda invoker-rights-funktionen med dynamisk SQL, du kan bygga applikationer som utfärdar dynamiska SQL-uttalanden under invokers privilegier och schema.Dessa två funktioner, invoker-rättigheter och dynamisk SQL, gör det möjligt för dig att bygga återanvändbara applikationskomponenter som kan fungera på och få åtkomst till invokers data och moduler.

Ett dynamiskt SQL-scenario med hjälp av Native Dynamic SQL

Scenariot som beskrivs i detta avsnitt illustrerar kraften och flexibiliteten av inbyggd dynamisk SQL. Det här scenariot innehåller exempel som visar hur du utför följande åtgärder med hjälp av inbyggd dynamisk SQL:

• Kör DDL- och DML-operationer
• Kör enstaka rader och flera radfrågor

Datamodell

Databasen i detta scenario är ett företags databas för mänskliga resurser (med namnet hr) med följande datamodell :

En mastertabell med namnet offices innehåller listan över alla företagsplatser. Tabellen offices har följande definition :

Column Name Null? Type LOCATION NOT_NULL VARCHAR2(200)

Flera emp_ platstabeller innehåller anställd information, där plats är namnet på staden där kontoret finns till exempel. En tabell med namnet emp_houston innehåller anställd information för företagets Houston-kontor, medan en tabell med namnet emp_boston innehåller anställd information för företagets Boston-kontor.

Varje emp_ platstabell har följande definition:

Följande avsnitt beskriver olika inbyggda dynamiska SQL-operationer som kan utföras på data i hr databas.

Exempel på DML-operation

Följande inbyggda dynamiska SQL-procedur ger en höjning till alla anställda med en viss jobbtitel:

Exempel på DDL-operation

Uttrycket EXECUTE IMMEDIATE kan utföra DDL-operationer. Följande procedur lägger till exempel till en kontorsplats:

Följande procedur raderar en kontorsplats:

Exempel på dynamisk enradig fråga

EXECUTE IMMEDIATE kan utföra dynamiska enradiga frågor. Du kan ange bindningsvariabler i USING -satsen och hämta den resulterande raden till målet som anges i INTO -satsen i uttalandet.

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

Exempel på dynamisk fråga om flera rader

OPEN-FOR, FETCH och CLOSE kan utföra dynamiska frågor med flera rader. I följande procedur listas till exempel alla anställda med ett visst jobb på en viss plats:

Native Dynamic SQL vs. DBMS_SQL Package

Oracle tillhandahåller två metoder för att använda dynamisk SQL inom PL / SQL: inbyggd dynamisk SQL och DBMS_SQL -paketet. Med inbyggd dynamisk SQL kan du placera dynamiska SQL-uttalanden direkt i PL / SQL-kod. Dessa dynamiska uttalanden inkluderar DML-uttalanden (inklusive frågor), PL / SQL-anonyma block, DDL-uttalanden, transaktionskontrolluttalanden och sessionskontrolluttalanden.

För att bearbeta de flesta inbyggda dynamiska SQL-uttalanden använder du EXECUTE IMMEDIATE uttalande. För att bearbeta en flerradig fråga (SELECT uttalande) använder du OPEN-FOR, FETCH och CLOSE uttalanden.

DBMS_SQL -paketet är ett PL / SQL-bibliotek som erbjuder ett programmatiskt API till köra SQL-uttalanden dynamiskt. DBMS_SQL -paketet har programmatiska gränssnitt för att öppna en markör, analysera en markör, leveransbindningar etc. Program som använder DBMS_SQL -paketet ringer samtal till detta paket för att utföra dynamiska SQL-operationer.

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

-paket. I PL / SQL-användarhandboken och referens kallas native dynamisk SQL helt enkelt dynamisk SQL.

Fördelar med Native Dynamic SQL

Native dynamisk SQL ger följande fördelar jämfört med DBMS_SQL -paket:

Användarvänlighet

Inbyggd dynamisk SQL är mycket enklare att använda än DBMS_SQL -paketet.Eftersom integrerad dynamisk SQL är integrerad med SQL kan du använda den på samma sätt som du för närvarande använder statisk SQL inom PL / SQL-kod. Dessutom är inbyggd dynamisk SQL-kod vanligtvis mer kompakt och läsbar än motsvarande kod som använder DBMS_SQL -paketet.

DBMS_SQL -paketet är inte lika lätt att använda som inbyggd dynamisk SQL. Det finns många procedurer och funktioner som måste användas i en strikt sekvens. Vanligtvis kräver enkla åtgärder en stor mängd kod när du använder paketet DBMS_SQL. Du kan undvika denna komplexitet genom att använda inbyggd dynamisk SQL istället.

Tabell 8-1 illustrerar skillnaden i mängden kod som krävs för att utföra samma operation med DBMS_SQL paket och inbyggd dynamisk SQL.

Tabell 8-1 Kodjämförelse av DBMS_SQL-paket och Native Dynamic SQL

Prestandaförbättringar

Prestanda för native dynamisk SQL i PL / SQL är jämförbar med prestanda för statisk SQL eftersom PL / SQL-tolk har inbyggt stöd för native dynamisk SQL. Därför är prestanda för program som använder inbyggd dynamisk SQL mycket bättre än för program som använder paketet DBMS_SQL. Normalt utför inbyggda dynamiska SQL-uttalanden 1,5 till 3 gånger bättre än motsvarande uttalanden som använder DBMS_SQL -paketet. Naturligtvis kan dina prestationsvinster variera beroende på din ansökan.

DBMS_SQL -paketet är baserat på ett procedur-API och som ett resultat medför höga proceduranrops- och datakopieringskostnader. Till exempel, varje gång du binder en variabel kopierar paketet DBMS_SQL PL / SQL-bindningsvariabeln till sitt utrymme för senare användning under körningen. På samma sätt, varje gång du kör en hämtning, kopieras data först till utrymmet som hanteras av DBMS_SQL -paketet och sedan kopieras de hämtade uppgifterna, en kolumn i taget, till lämplig PL / SQL-variabler, vilket resulterar i betydande omkostnader till följd av datakopiering. Däremot buntar inbyggd dynamisk SQL satsens förberedande, bindande och exekveringssteg i en enda operation, vilket minimerar datakopiering och proceduranropskostnad och förbättrar prestanda.

Prestandatips

När du använder antingen inbyggd dynamisk SQL eller DBMS_SQL -paketet kan du förbättra prestanda genom att använda bindningsvariabler, eftersom användning av bindningsvariabler gör att Oracle kan dela en enda markör för flera SQL-satser. >

Till exempel använder följande inbyggda dynamiska SQL-kod inte bindningsvariabler:

För varje distinkt my_deptno -variabel skapas en ny markör som kan orsaka resurskonflikter och dålig prestanda. Bind istället my_deptno som en bindningsvariabel, som i följande exempel:

Här återanvänds samma markör för olika värden för bindningen my_deptno och därigenom förbättrar prestanda och skalbarhet.

Stöd för användardefinierade typer

Naturlig dynamisk SQL stöder alla typer som stöds av statisk SQL i PL / SQL. Därför tillhandahåller inbyggd dynamisk SQL stöd för användardefinierade typer, som användardefinierade objekt, samlingar och REFs. DBMS_SQL -paketet stöder inte dessa användardefinierade typer.

Stöd för att hämta till poster

Inbyggd dynamisk SQL och statisk SQL stöder båda hämtning i poster, men DBMS_SQL -paketet gör det inte. Med inbyggd dynamisk SQL kan raderna från en fråga hämtas direkt till PL / SQL-poster.

I följande exempel hämtas raderna från en fråga till emp_rec -post:

Fördelar med DBMS_SQL-paketet

DBMS_SQL -paketet ger följande fördelar jämfört med inbyggd dynamisk SQL:

Stöd för klientprogram

För närvarande stöds paketet DBMS_SQL i program på klientsidan, men inte inbyggd dynamisk SQL. Varje samtal till DBMS_SQL -paketet från klientsidan kan översättas till ett PL / SQL fjärrproceduranrop (RPC); dessa samtal inträffar när du behöver binda en variabel, definiera en variabel eller utföra ett uttalande.

Stöd för DESCRIBE

DESCRIBE_COLUMNS -proceduren i DBMS_SQL -paketet kan användas för att beskriv kolumnerna för en markör som öppnas och analyseras genom DBMS_SQL. Funktionaliteten liknar kommandot DESCRIBE i SQL * Plus. Inbyggd dynamisk SQL har ingen DESCRIBE -anläggning.

Stöd för bulkdynamisk SQL

Bulk SQL är möjligheten att bearbeta flera rader med data i en enda DML-sats. Bulk SQL förbättrar prestanda genom att minska mängden sammanhangsbyte mellan SQL och värdspråket. För närvarande stöder DBMS_SQL -paketet dynamisk SQL i bulk.

Även om det inte finns något direkt stöd för bulkoperationer i native dynamisk SQL kan du simulera en native dynamisk bulk SQL uttalande genom att placera SQL-satsen i bulk i ett ”BEGIN … END” block och köra blocket dynamiskt. Denna lösning gör att du kan förverkliga fördelarna med bulk-SQL inom ett inbyggt dynamiskt SQL-program. Till exempel kopierar följande inbyggda dynamiska SQL-kod kolumnen ename i en tabell till en annan:

Flera raduppdateringar och raderas med ett RETURNING-avsnitt

DBMS_SQL -paketet stöder uttalanden med en RETURNING -sats som uppdaterar eller tar bort flera rader. Inbyggd dynamisk SQL stöder endast en RETURNING -sats om en enstaka rad returneras.

Stöd för SQL-uttalanden större än 32 KB

DBMS_SQL paketet stöder SQL-uttalanden större än 32KB; inbyggd dynamisk SQL gör inte det.

Återanvändning av SQL-uttalanden

PARSE -proceduren i DBMS_SQL paket analyserar en SQL-sats en gång. Efter den första tolkningen kan påståendet användas flera gånger med olika uppsättningar bindargument.

Däremot förbereder native dynamisk SQL ett SQL-uttalande för körning varje gång uttalandet används. Uttalningsförberedelse innebär vanligtvis analysering, optimering och plangenerering. Att förbereda ett uttalande varje gång det används medför en liten prestationsstraff. Orakles delade markörmekanism minimerar dock kostnaden och prestationsstraffet är vanligtvis trivialt jämfört med prestandafördelarna med inbyggd dynamisk SQL.

Exempel på DBMS_SQL-paketkod och Native Dynamic SQL-kod

Följande exempel illustrerar skillnaderna i koden som krävs för att slutföra operationerna med DBMS_SQL -paketet och inbyggd dynamisk SQL. Specifikt presenteras följande typer av exempel:

• En fråga
• En DML-åtgärd
• En DML-återvändande åtgärd

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

Frågexempel

Följande exempel innehåller en dynamisk frågesats med en bindningsvariabel (:jobname) och två valda kolumner (ename och sal):

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

Detta exempel frågar för anställda med j ob beskrivning SALESMAN i job kolumnen i emp tabellen. Tabell 8-2 visar exempelkod som utför denna fråga med DBMS_SQL -paketet och inbyggd dynamisk SQL.

Tabell 8-2 Fråga med DBMS_SQL-paketet och Native Dynamic SQL

DML-exempel

följande exempel innehåller ett dynamiskt INSERT uttalande för en tabell med tre kolumner:

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

Detta exempel infogar en ny rad för vilka kolumnvärdena finns i PL / SQL-variablerna deptnumber, deptname och location. Tabell 8-3 visar exempelkod som utför denna DML-operation med DBMS_SQL -paketet och inbyggd dynamisk SQL.

Tabell 8-3 DML-användning med DBMS_SQL-paketet och Native Dynamic SQL

DML Returexempel

Följande exempel innehåller ett dynamiskt UPDATE uttalande som uppdaterar platsen för en avdelning när den får avdelningsnumret (deptnumber) och en ny plats (location), och returnerar sedan avdelningens namn:

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

Detta exempel infogar en ny rad för vilken kolumnvärdena finns i PL / SQL-variablerna deptnumber, deptname och location. Tabell 8-4 visar exempelkod som åstadkommer denna DML-returoperation med DBMS_SQL -paketet och inbyggd dynamisk SQL.

Tabell 8-4 DML-återvändande med DBMS_SQL Package and Native Dynamic SQL

Application Development Languages Other Than PL / SQL

Hittills har diskussionen i detta kapitel varit om PL / SQL-stöd för dynamisk SQL.Du kan dock använda andra applikationsutvecklingsspråk för att implementera program som använder dynamisk SQL. Dessa applikationsutvecklingsspråk inkluderar C / C ++, COBOL och Java.

Om du använder C / C ++ kan du utveckla applikationer som använder dynamisk SQL med Oracle Call Interface (OCI), eller så kan du använda Pro * C / C ++ förkompilatorn för att lägga till dynamiska SQL-tillägg till din C koda. På samma sätt, om du använder COBOL kan du använda Pro * COBOL-förkompilatorn för att lägga till dynamiska SQL-tillägg till din COBOL-kod. Om du använder Java kan du utveckla applikationer som använder dynamisk SQL med JDBC.

Tidigare var det enda sättet att använda dynamisk SQL i PL / SQL-applikationer genom att använda DBMS_SQL -paket. Det finns ett antal begränsningar för att använda detta paket, inklusive prestationsproblem. Följaktligen kan applikationsutvecklare ha använt ett av alternativen till PL / SQL som diskuterats ovan för att implementera dynamisk SQL. Med introduktionen av inbyggd dynamisk SQL i PL / SQL elimineras nu många av nackdelarna med att använda PL / SQL för dynamisk SQL.

Om du har ett program som använder OCI, Pro * C / C ++ eller Pro * COBOL för dynamisk SQL-körning, de nätverksresor som krävs för att utföra dynamiska SQL-operationer kan skada prestandan. Eftersom dessa applikationer vanligtvis finns på klienter krävs fler nätverkssamtal för att slutföra dynamiska SQL-operationer. Om du har den här typen av applikationer, överväg att flytta den dynamiska SQL-funktionen till lagrade procedurer och lagrade funktioner i PL / SQL som använder inbyggd dynamisk SQL. Om du gör det kan det förbättra din applikations prestanda eftersom de lagrade procedurerna kan finnas på servern och därmed eliminera nätverkskostnaderna. Du kan sedan ringa PL / SQL-lagrade procedurer och lagrade funktioner från applikationen.