8 kodning af dynamiske s .l-sætninger

dynamisk s .l er en programmeringsteknik, der giver dig mulighed for at opbygge s .l-sætninger dynamisk ved kørsel. Du kan oprette mere generelle, fleksible applikationer ved hjælp af dynamisk s .l, fordi den fulde tekst i en s .l-sætning kan være ukendt ved kompilering. For eksempel giver dynamic s .l dig mulighed for at oprette en procedure, der fungerer på et bord, hvis navn ikke er kendt før runtime.,Oracle indeholder to måder at implementere dynamisk s .l i en PL/S .l-applikation:

• Native dynamic s .l, hvor du placerer dynamiske s .l-sætninger direkte i PL/s .l-blokke.
• Opkaldsprocedurer iDBMS_SQL pakken.

dette kapitel dækker følgende emner:

• “hvad er Dynamic s ?l?”
• ” Hvorfor bruge dynamisk s ?l?,”
• “En Dynamisk SQL Scenario ved Hjælp af Indfødt Dynamisk SQL”
• “Valg Mellem de Indfødte Dynamisk SQL og DBMS_SQL Pakke”
• “Brug af Dynamiske SQL-på Andre Sprog End PL/SQL”
• “Brug af PL/SQL-Poster i SQL INSERT og UPDATE Erklæringer”

Du kan finde oplysninger om DBMS_SQL pakke i Oracle9i Leveret af PL/SQL-Pakker og Typer Reference.

Hvad er dynamisk s ?l?

dynamisk s .l giver dig mulighed for at skrive programmer, der refererer til S .l-sætninger, hvis fulde tekst ikke er kendt før runtime., Før diskuterer dynamisk SQL i detaljer, med en klar definition af statisk SQL kan give et godt udgangspunkt for at forstå dynamisk SQL. Statiske s .l-sætninger ændres ikke fra udførelse til udførelse. Den fulde tekst af statiske s .l-sætninger er kendt ved kompilering, hvilket giver følgende fordele:

• vellykket kompilering verificerer, at s .l-sætningerne refererer til gyldige databaseobjekter.
• vellykket kompilering verificerer, at de nødvendige privilegier er på plads for at få adgang til databaseobjekter.,
• ydelse af statisk s .l er generelt bedre end dynamisk s .l.

på Grund af disse fordele, du bør bruge dynamiske SQL kun hvis du ikke kan bruge statisk SQL til at nå dine mål, eller hvis du bruger statisk SQL er besværlig i forhold til dynamisk SQL. Statisk s .l har dog begrænsninger, der kan overvindes med dynamisk s .l. Du kender muligvis ikke altid den fulde tekst til S .l-sætningerne, der skal udføres i en PL/S .l-procedure., Dit program accepterer muligvis brugerinput, der definerer de S .l-sætninger, der skal udføres, eller dit program skal muligvis gennemføre noget behandlingsarbejde for at bestemme det korrekte handlingsforløb. I sådanne tilfælde skal du bruge dynamic s .l.

for eksempel kan en rapporteringsapplikation i et data warehousearehouse-miljø muligvis ikke kende det nøjagtige Tabelnavn før runtime., Disse tabeller kan være navngivet efter den startmåned og-år af det kvartal, for eksempel INV_01_1997 INV_04_1997 INV_07_1997 INV_10_1997 INV_01_1998 og så videre. Du kan bruge dynamisk s .l i din rapportering program til at angive tabellen navn på runtime.

Du kan også køre en kompleks forespørgsel med en brugervalgbar sorteringsrækkefølge., I stedet for at kode forespørgslen to gange, med forskellige ORDER BY klausuler, kan du konstruere forespørgslen dynamisk for at inkludere en specificeret ORDER BY klausul.

dynamiske s .l-programmer kan håndtere ændringer i datadefinitioner uden at skulle kompilere igen. Dette gør dynamisk s .l meget mere fleksibel end statisk s .l. Dynamisk s .l lader dig skrive genanvendelig kode, fordi S .l let kan tilpasses til forskellige miljøer..,dynamisk s .l giver dig også mulighed for at udføre data definition language (DDL) – udsagn og andre s .l-sætninger, der ikke understøttes i rent statiske s .l-programmer.

Hvorfor bruge dynamisk s ?l?

Du bør bruge dynamiske SQL i de tilfælde, hvor statisk SQL understøtter ikke den handling, du vil udføre, eller i tilfælde, hvor du ikke kender den nøjagtige SQL-sætninger, der skal udføres ved et PL/SQL procedure. Disse s .l-sætninger kan afhænge af brugerinput, eller de kan afhænge af behandlingsarbejde udført af 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 kun bruge TABLE klausul i SELECT erklæring gennem dynamisk SQL. For eksempel, efter PL/SQL block indeholder et SELECT erklæring, der bruger koden TABLE klausul og indfødte dynamisk SQL:

Udførelse af Dynamiske Forespørgsler

Du kan bruge dynamisk SQL til at skabe applikationer, der udføre dynamiske forespørgsler, hvis fulde tekst er ikke kendt før runtime., Mange typer af applikationer er nødt til at bruge dynamiske forespørgsler, herunder:

• Applikationer, der tillader brugere at indtaste eller vælge forespørgsel søgning eller sortering kriterier på runtime
• Applikationer, der tillader brugere at indtaste eller vælge optimizer tips på run-time
• Applikationer, der sender en forespørgsel til en database, hvor data definitioner af tabeller, der er i konstant forandring
• Applikationer, der sender en forespørgsel til en database, hvor nye borde er skabt ofte

eksempler: se “Søgninger ved Hjælp af Dynamisk SQL: Eksempel”, og se forespørgslen eksempler i “En Dynamisk SQL Scenario ved Hjælp af Indfødt Dynamisk SQL”.,

henvisninger til databaseobjekter, der ikke findes ved kompilering

mange typer applikationer skal interagere med data, der genereres periodisk. For eksempel kender du måske tabellernes definitioner på kompileringstidspunktet, men ikke navnene på tabellerne.

dynamisk s .l kan løse dette problem, fordi det lader dig vente til runtime for at angive tabelnavne. For eksempel i eksempeldata warehousearehouse-applikationen diskuteret i ” Hvad er dynamisk s ?l?”, nye tabeller genereres hvert kvartal, og disse tabeller har altid den samme definition., Du kan lade en bruger, skal du angive navnet på den tabel på runtime med en dynamisk SQL-forespørgsel, der ligner følgende:

Optimering Udførelse Dynamisk

Du kan bruge dynamisk SQL til at bygge en SQL-sætning på en måde, der optimerer udførelsen ved at sammenkæde de hints ind i en SQL-sætning dynamisk. Dette lader dig ændre hints baseret på din nuværende database statistik, uden at kræve omkompilering.,

For eksempel følgende procedure bruger en variabel kaldet a_hint at give brugerne mulighed for at passere en antydning mulighed for at SELECT erklæring:

I dette eksempel, kan brugeren passerer en af følgende værdier for a_hint:

a_hint = '/*+ ALL_ROWS */'a_hint = '/*+ FIRST_ROWS */'a_hint = '/*+ CHOOSE */'eller ethvert andet gyldigt tip indstilling.

Dynamisk Udførelse af PL/SQL-Blokke

Du kan bruge EXECUTE IMMEDIATE sætningen til at udføre anonym PL/SQL-blokke. Du kan tilføje fleksibilitet ved at konstruere blokindholdet ved kørsel.Antag f.eks. atgennemgangdu vil skrive et program, der tager et begivenhedsnummer og sender til en håndterer for begivenheden. Handlerens navn er i form EVENT_HANDLER_event_num, hvor event_num er nummeret på begivenheden., En tilgang er at implementere afsenderen som en s .itch-erklæring, hvor koden håndterer hver begivenhed ved at foretage et statisk opkald til sin passende håndterer. Denne kode er ikke meget udvidelig, fordi afsenderkoden skal opdateres, når en håndterer til en ny begivenhed tilføjes.,

ved Hjælp af indfødt dynamisk SQL, kan du skrive en mindre, mere fleksible hændelse, afsender, der ligner følgende:

Udføre Dynamiske Operationer ved Hjælp af Invoker-Rettigheder

Ved hjælp af invoker-rettigheder funktion med dynamisk SQL, du kan bygge applikationer, der udsteder dynamisk SQL-sætninger i henhold til de privilegier og skema af invoker. Disse to funktioner, invoker-rettigheder og dynamic s .l, giver dig mulighed for at opbygge genanvendelige applikationssubkomponenter, der kan fungere på og få adgang til invoker ‘ s data og moduler.,

En Dynamisk SQL Scenario ved Hjælp af Indfødt Dynamisk SQL

Dette scenarie viser, hvordan du udfører følgende handlinger ved hjælp af indfødt dynamisk SQL:

• Udføre DDL og DML operationer
• Execute enkelt række og flere række forespørgsler

databasen i dette scenario er en virksomheds menneskelige ressourcer database (opkaldt hr) med følgende data model:

En master tabel, der hedder offices indeholder en liste over alle virksomhedens lokationer., offices tabellen har følgende definition:

Column Name Null? Type LOCATION NOT_NULL VARCHAR2(200)

Flere emp_placering tabeller indeholder information, hvor placering er navnet på byen, hvor kontoret ligger. For eksempel indeholder en tabel med navnet emp_houston medarbejderoplysninger til virksomhedens Houston-kontor, mens en tabel med navnet emp_boston indeholder medarbejderoplysninger til virksomhedens Boston-Kontor.,

Hvert emp_placering tabellen har følgende definition:

i De følgende afsnit beskrives forskellige indfødte dynamisk SQL operationer, der kan udføres på data i hr database.

Prøve DML Drift ved Hjælp af Indfødt Dynamisk SQL

følgende native dynamisk SQL procedure giver en rejse til alle medarbejdere med et bestemt job title:

Prøve DDL Drift ved Hjælp af Indfødt Dynamisk SQL

EXECUTE IMMEDIATE erklæring kan udføre DDL operationer., For eksempel, den følgende procedure tilføjer et kontor beliggenhed:

Den følgende procedure sletter et kontor beliggenhed:

Prøve Single-Rækken Forespørgsel ved Hjælp af Indfødt Dynamisk SQL

EXECUTE IMMEDIATE erklæring kan udføre dynamisk single-række forespørgsler. Du kan angive bind-variabler i USING – klausulen og hente den resulterende række ind i det mål, der er angivet i INTO – klausulen i erklæringen.,

følgende funktion henter antallet af medarbejdere på en bestemt lokalitet, der udfører en bestemt opgave:

Prøve Flere-Rækken Forespørgsel ved Hjælp af Indfødt Dynamisk SQL

OPEN-FOR FETCH og CLOSE udsagn kan udføre dynamisk flere-række forespørgsler., For eksempel, den følgende procedure viser alle medarbejdere med et bestemt job i en bestemt beliggenhed:

Valg Mellem Indfødte Dynamisk SQL og DBMS_SQL Pakke

Oracle giver to metoder til at bruge dynamisk SQL inden for PL/SQL: native dynamisk SQL og DBMS_SQL pakke. Native dynamic s .l giver dig mulighed for at placere dynamiske s .l-sætninger direkte i PL/s .l-kode. Disse dynamiske koncernregnskabet omfatter DML udtalelser (herunder forespørgsler), PL/SQL anonym blokke, DDL-statements, transaktion kontrol-erklæringerne, og session kontrol-erklæringerne.,

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.

DBMS_SQL pakken er et PL / S .l-bibliotek, der tilbyder en API til at udføre s .l-sætninger dynamisk. Pakken DBMS_SQL har procedurer til at åbne en markør, analysere en markør, forsyningsbindinger og så videre. Programmer ,der brugerDBMS_SQL – pakken, foretager opkald til denne pakke for at udføre dynamiske s .l-operationer.

de følgende afsnit giver detaljerede oplysninger om fordelene ved begge metoder.,

Fordele ved Native Dynamisk SQL

Native dynamisk SQL giver følgende fordele i forhold til den DBMS_SQL package:

Native Dynamisk SQL er Nemme at Bruge

Fordi native dynamisk SQL er integreret med SQL, kan du bruge den på samme måde, som du bruger statisk SQL inden for PL/SQL-kode. Native dynamisk s .l-kode er typisk mere kompakt og læsbar end tilsvarende kode, der bruger DBMS_SQL pakke.,

med DBMS_SQL pakken skal du ringe til mange procedurer og funktioner i en streng rækkefølge, hvilket gør selv enkle operationer kræver meget kode. Du kan undgå denne kompleksitet ved at bruge native dynamic s .l i stedet.tabel 8-1 illustrerer forskellen i den mængde kode, der kræves for at udføre den samme handling ved hjælp af DBMS_SQL – pakken og native dynamic s .l.,

Tabel 8-1 Kode Sammenligning af DBMS_SQL Pakke og Indfødte Dynamisk SQL

Native Dynamisk SQL er Hurtigere end DBMS_SQL

Native dynamisk SQL PL/SQL udfører sammenligneligt til udførelsen af statisk SQL, fordi PL/SQL tolk har indbygget understøttelse for det. Programmer, der bruger native dynamic s .l, er meget hurtigere end programmer, der bruger DBMS_SQL – pakken. Typisk, indfødte dynamisk SQL-sætninger udføre 1,5 til 3 gange bedre end tilsvarende DBMS_SQL opkald. (Dine præstationsgevinster kan variere afhængigt af din ansøgning.,)

Native dynamic s .l bundter sætningen forberedelse, binding og udførelse trin i en enkelt operation, hvilket minimerer data kopiering og procedure opkald overhead og forbedrer ydeevnen.

DBMS_SQL pakken er baseret på en proceduremæssig API og pådrager sig høje procedureopkald og datakopi overhead. Hver gang du binder en variabel, kopierer DBMS_SQL pakken Pl / S .l bind variablen til dens plads til brug under udførelse., Hver gang du udfører en hentning, kopieres dataene til det rum, der administreres afDBMS_SQL – pakken, og derefter kopieres de hentede data, en kolonne ad gangen, til de relevante PL/S .l-variabler, hvilket resulterer i betydelig overhead.

Ydeevne Tip: Brug Bind Variabler

Når du bruger enten indfødt dynamisk SQL eller DBMS_SQL pakke, du kan forbedre ydeevnen ved hjælp af binde variabler, fordi binde variabler tillader Oracle at dele en enkelt markør for flere SQL-sætninger.,

for eksempel bruger følgende native dynamiske s .l-kode ikke bind-variabler:

for hver særskilt my_deptno – variabel oprettes en ny markør, der forårsager ressourcekonkurrence og dårlig ydelse. I stedet bind my_deptno som en bind-variabel:

Her genbruges den samme markør til forskellige værdier af bind my_deptno, hvilket forbedrer ydeevnen og skalabilty.,

Native Dynamisk SQL Understøtter brugerdefinerede Typer

Native dynamisk SQL understøtter alle de typer, der understøttes af statisk SQL, PL/SQL, herunder bruger-definerede typer, såsom bruger-definerede objekter, samlinger og REFs. Pakken DBMS_SQL understøtter ikke disse brugerdefinerede typer.

Native Dynamisk SQL Understøtter Hentning Ind Records

Native dynamisk SQL og statisk SQL både støtte at hente i poster, men DBMS_SQL pakke ikke. Med native dynamic s .l kan rækkerne som følge af en forespørgsel hentes direkte i PL/s .l-poster.,

I det følgende eksempel, der rækker fra en forespørgsel, der er ude i emp_rec optag:

Fordele ved DBMS_SQL Pakke

DBMS_SQL pakken giver følgende fordele i forhold til native dynamisk SQL:

DBMS_SQL er Understøttet i Klient-Side-Programmer

DBMS_SQL pakke er understøttet i klient-side-programmer, men native dynamisk SQL er det ikke., Alle opkald til DBMS_SQL pakke fra klient-side-program oversætter til en PL/SQL-remote procedure call (RPC); disse opkald opstå, når du har brug for til at binde en variabel, definere en variabel, eller udføre en sætning.

DBMS_SQL Understøtter BESKRIVE

DESCRIBE_COLUMNS procedure i DBMS_SQL pakke kan bruges til at beskrive kolonner til en markør åbnet og analyseres gennem DBMS_SQL. Denne funktion ligner kommandoen DESCRIBE I S .l*Plus., Native dynamic s .l har ikke en DESCRIBE facilitet.

DBMS_SQL Understøtter Flere Række Opdateringer og Sletter med en TILBAGEVENDENDE Klausul

DBMS_SQL pakke understøtter udsagn med en RETURNING klausul, at opdatere eller slette flere rækker. Native dynamic s .l understøtter kun en RETURNING klausul, hvis en enkelt række returneres.,

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ørste parsing kan du bruge sætningen flere gange med forskellige sæt bindeargumenter.

Native dynamic s .l udarbejder en s .l-sætning, hver gang sætningen bruges, hvilket typisk involverer parsing, optimering og plangenerering. Selvom de ekstra forberedelsesoperationer pådrager sig en lille præstationsstraf, opvejes afmatningen typisk af ydelsesfordelene ved native dynamic s .l.,

Eksempler på DBMS_SQL Pakke Kode og Indfødte Dynamisk SQL-Kode

De følgende eksempler illustrerer forskelle i den kode, der er nødvendige for at gennemføre operationer med DBMS_SQL pakke og indfødte dynamisk SQL. Konkret er følgende typer af eksempler:

• En forespørgsel
• EN DML operation
• EN DML vender tilbage operation

I almindelighed, de indfødte dynamisk SQL-kode, der er mere læsevenlig og kompakt, som kan forbedre udvikler produktiviteten.,

Forespørgsler ved Hjælp af Dynamisk SQL: Eksempel

følgende eksempel indeholder en dynamisk forespørgsel erklæring med en bind-variable (:jobname) og to vælg kolonner (ename og sal):

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

Dette eksempel forespørgsler til medarbejdere med job beskrivelse SALESMAN job kolonne af emp tabel. Tabel 8-2 viser prøvekode, der udfører denne forespørgsel ved hjælp af DBMS_SQL pakken og native dynamic s .l.,

Tabel 8-2 Forespørge Hjælp DBMS_SQL Pakke og Indfødte Dynamisk SQL

Udfører DML ved Hjælp af Dynamisk SQL: Eksempel

følgende eksempel indeholder en dynamisk INSERT erklæring om en tabel med tre kolonner:

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

Dette eksempel indsætter en ny række, som den kolonne af værdier i PL/SQL variabler deptnumber deptname og location. Tabel 8-3 viser prøvekode, der udfører denne DML-operation ved hjælp af DBMS_SQL – pakken og native dynamic s .l.,

Tabel 8-3 DML Betjening via DBMS_SQL Pakke og Indfødte Dynamisk SQL

Udfører DML med RETURNERING af Bestemmelse ved Hjælp af Dynamisk SQL: Eksempel

Det følgende eksempel bruger en dynamisk UPDATE erklæring for at opdatere placeringen af en afdeling, så returnerer navnet på den afdeling:

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

Tabel 8-4 viser sample-kode, der udfører denne operation, ved hjælp af både DBMS_SQL pakke og indfødte dynamisk SQL.,

Tabel 8-4 DML vender Tilbage Betjening via DBMS_SQL Pakke og Indfødte Dynamisk SQL

ved Hjælp af Dynamisk SQL på Andre Sprog End PL/SQL

Selv om dette kapitel diskuterer, PL/SQL understøttelse af dynamisk SQL, kan du ringe til dynamisk SQL fra andre sprog:

• Hvis du bruger C/C++, kan du ringe til dynamisk SQL Oracle Call Interface (OCI), eller du kan bruge Pro*C/C++ – præprocessoren for at tilføje dynamisk SQL udvidelser til din C-kode.hvis du bruger COBOL ,kan du bruge Pro*COBOL precompiler til at tilføje dynamiske s .l-udvidelser til din COBOL-kode.,
• hvis du bruger Java, kan du udvikle applikationer, der bruger dynamisk s .l med JDBC.

Hvis du har et program, der bruger OCI, Pro*C/C++, eller Pro*COBOL til at udføre dynamisk SQL, bør du overveje at skifte til native dynamisk SQL inde i PL/SQL stored procedures og funktioner. Netværkets rundture, der kræves for at udføre dynamiske s .l-operationer fra applikationer på klientsiden, kan skade ydeevnen. Lagrede procedurer kan opholde sig på serveren, fjerne netværket overhead., Du kan ringe til PL/s .l-lagrede procedurer og lagrede funktioner fra Oci, Pro*C/C++ eller Pro*COBOL-applikationen.,br / >Se Også:

For oplysninger om at ringe til Oracle stored procedures og opbevares funktioner fra forskellige sprog, henvises til:

• Oracle Call Interface programmer ‘s Guide
• Pro*C/C++ – Præprocessoren programmer’ s Guide
• Pro*COBOL Præprocessoren programmer ‘s Guide
• Oracle9i Java Stored Procedures Developer’ s Guide

Anvendelse af PL/SQL-Poster i SQL INSERT og UPDATE Erklæringer

Selvom du kan optælles hvert felt af et PL/SQL-registrering, når du isætter eller opdatere rækker i en tabel, skal den resulterende kode er ikke særlig læsbar eller vedligeholde., I stedet kan du bruge PL/s .l-poster direkte i disse udsagn. Den mest hensigtsmæssige teknik er at erklære posten ved hjælp af en %ro .type-attribut, så den har nøjagtigt de samme felter som s .l-tabellen.selvom denne teknik hjælper med at integrere PL/S .l-variabler og-typer tættere med S .l DML-sætninger, kan du ikke bruge PL/s .l-poster som bindevariabler i dynamiske s .l-sætninger.