8 Koding Dynamisk SQL-uttrykk

Dynamisk SQL er en programmering teknikk som gjør det mulig for deg å bygge SQL-setninger dynamisk under kjøring. Du kan opprette mer generelle formål, fleksible applikasjoner ved hjelp av dynamisk SQL fordi den fullstendige teksten til en SQL-setning kan være ukjent på samling. For eksempel, dynamisk SQL lar deg lage en prosedyre som opererer på et bord hvis navn er ikke kjent før kjøring.,

Oracle har to måter å implementere dynamisk SQL i en PL/SQL-programmet:

• Native dynamisk SQL, hvor du plasserer dynamisk SQL-setninger direkte i PL/SQL-blokker.
• Kalle prosedyrer i DBMS_SQL pakken.

Dette kapitlet omhandler følgende emner:

• «Hva Er Dynamisk SQL?»
• «Hvorfor Bruke Dynamisk SQL?,»
• «En Dynamisk SQL Scenariet ved å Bruke en Innfødt Dynamisk SQL»
• «å Velge Mellom Innfødte Dynamisk SQL og DBMS_SQL Pakke»
• «ved Hjelp av Dynamisk SQL i Andre Språk Enn PL/SQL -»
• «ved Hjelp av PL/SQL-Poster i SQL INSERT og UPDATE-Setninger»

Du kan finne mer informasjon om DBMS_SQL pakke i Oracle9i Følger av PL/SQL-Pakker og Typer Referanse.

Hva Er Dynamisk SQL?

Dynamisk SQL gjør det mulig for deg å skrive programmer som referanse SQL-setninger med fullstendig tekst er ikke kjent før kjøring., Før diskutere dynamisk SQL i detalj en klar definisjon av statisk SQL kan gi et godt utgangspunkt for å forstå dynamisk SQL. Statisk SQL-setninger ikke endres fra gjennomføring til gjennomføring. Full tekst av statisk SQL-setninger er kjent på samling, noe som gir følgende fordeler:

• Vellykket samling bekrefter at SQL-setninger referanse gyldig database objekter.
• Vellykket samling bekrefter at nødvendige rettigheter er på plass for å få tilgang til databaseobjekter.,
• Ytelse av statisk SQL er generelt bedre enn dynamisk SQL.

på Grunn av disse fordelene bør du bruke dynamisk SQL-bare hvis du ikke kan bruke statiske SQL å oppnå målene dine, eller hvis du bruker statisk SQL er tungvint i forhold til dynamisk SQL. Imidlertid, statisk SQL har begrensninger som kan overvinnes med dynamisk SQL. Du kan ikke alltid vite den fulle teksten til SQL-setninger som må utføres i en PL/SQL-prosedyren., Programmet kan godta inndata som definerer SQL-setninger for å utføre, eller at programmet kan være nødvendig å fullføre enkelte behandler arbeidet med å finne de riktige løpet av handlingen. I slike tilfeller bør du bruke dynamisk SQL.

For eksempel, en rapportering programmet i en data warehouse miljøet kan ikke vite nøyaktig tabell navnet til kjøring., Disse tabellene kan være oppkalt etter start-måned og år for kvartal, for eksempel INV_01_1997, INV_04_1997, INV_07_1997, INV_10_1997, INV_01_1998, og så videre. Du kan bruke dynamisk SQL i rapportering programmet til å angi navnet til bordet ved kjøring.

Du kan også være lurt å kjøre en sammensatt spørring med en valgbare sorteringsrekkefølge., I stedet for koding spørringen to ganger, med forskjellige ORDER BY klausuler, kan du lage spørring dynamisk å inkludere en bestemt ORDER BY klausulen.

Dynamisk SQL programmer kan håndtere endringer i data definisjoner, uten at du trenger å kompilere. Dette gjør dynamisk SQL mye mer fleksible enn statisk SQL. Dynamisk SQL lar deg skrive gjenbrukbar kode fordi SQL lett kan tilpasses for ulike miljøer..,

Dynamisk SQL også lar deg utføre data definition language (DDL) erklæringer og andre SQL-setninger som støttes ikke i rent statisk SQL-programmer.

Hvorfor Bruke Dynamisk SQL?

Du bør bruke dynamisk SQL i tilfeller hvor statisk SQL støtter ikke den handlingen du vil utføre, eller i tilfeller der du ikke vet den eksakte SQL-setninger som må bli utført av en PL/SQL-prosedyren. Disse SQL-setninger kan avhenge av brukerens input, eller de kan stole på behandling av arbeidet gjort 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 Også bare bruke TABLE klausulen i SELECT uttalelse gjennom dynamisk SQL. For eksempel, følgende PL/SQL-blokk inneholder en SELECT uttalelse som bruker TABLE klausulen og innfødte dynamisk SQL:

Utføre Dynamisk Spørringer

Du kan bruke dynamisk SQL til å lage applikasjoner som utfører dynamisk spørsmål, hvis fulle tekst er ikke kjent før kjøring., Mange typer applikasjoner trenger å bruke dynamisk spørsmål, blant annet:

• Programmer som lar brukere å legge inn eller velge spørring søk eller sortering kriteriene ved runtime
• Programmer som lar brukere å legge inn eller velge optimizer hint på kjøre-time
• Programmer som spørring en database der data definisjoner av tabellene er i stadig endring
• Programmer som spørring en database hvor de nye tabellene er opprettet ofte

For eksempel, se «sende en Spørring ved Hjelp av Dynamisk SQL: Eksempel på», og se spørringen eksempler i «Et Dynamisk SQL Scenariet ved å Bruke en Innfødt Dynamisk SQL».,

Referere til Database for Objekter som Ikke Finnes i Samlingen

Mange typer programmer må samhandle med data som er generert med jevne mellomrom. For eksempel, du kanskje vet tabellene definisjoner på kompilere tid, men ikke navnene på tabellene.

Dynamisk SQL kan løse dette problemet, fordi det lar deg vente til runtime for å angi tabellen navn. For eksempel, i de utvalgte data warehouse programmet diskutert i «Hva Er Dynamisk SQL?»nye tabeller er generert hvert kvartal, og disse tabellene alltid har samme definisjon., Du kan la en bruker angi navn på bordet ved kjøring med en dynamisk SQL-spørring som ligner på følgende:

Optimalisere Gjennomføring Dynamisk

Du kan bruke dynamisk SQL å bygge en SQL-setning på en måte som optimaliserer kjøring ved å slå sammen hint inn i en SQL-setning dynamisk. Dette kan du endre hint basert på din nåværende database for statistikk, uten at det er recompilation.,

For eksempel, følgende prosedyre bruker en variabel kalt a_hint for å tillate brukere å passere et hint alternativet til SELECT uttalelse:

I dette eksempelet bruker kan sende en av følgende verdier for a_hint:

a_hint = '/*+ ALL_ROWS */'a_hint = '/*+ FIRST_ROWS */'a_hint = '/*+ CHOOSE */'eller noen annen gyldig hint alternativet.

Utføre Dynamisk PL/SQL-Blokker

Du kan bruke EXECUTE IMMEDIATE statement å utføre anonym PL/SQL-blokker. Du kan legge til fleksibilitet ved å konstruere blokkere innhold på kjøring.

For eksempel, tenk at ythroughthroughou lyst til å skrive et program som tar en hendelse nummer og telegrammer til et behandlingsprogram for arrangementet. Navnet på hunden er i form EVENT_HANDLER_event_num, hvor event_num er antallet av hendelsen., En tilnærming er å implementere oppsynsmannen som en switch statement, hvor koden håndterer hver hendelse ved å lage en statisk ringe til sin riktige handler. Denne koden er ikke veldig extensible fordi koordinatoren koden må være oppdatert når du handler for et nytt arrangement er lagt til.,

ved Hjelp av innfødte dynamisk SQL, kan du skrive en mindre, mer fleksibel event oppsynsmannen som ligner på følgende:

Utføre Dynamiske Operasjoner ved Hjelp Invoker-Rettigheter

Ved hjelp av invoker-rettigheter har med dynamisk SQL, kan du lage programmer som er problemet dynamisk SQL-setninger under privilegier og skjema for invoker. Disse to funksjonene, invoker-rettigheter og dynamisk SQL, gjøre deg i stand til å bygge gjenbrukbare programmet underkomponentene som kan operere på og få tilgang til invoker data og moduler.,

En Dynamisk SQL Scenariet ved å Bruke en Innfødt Dynamisk SQL

Dette scenariet viser hvordan du skal utføre følgende operasjoner ved hjelp native dynamisk SQL:

• Utføre DDL og DML-operasjoner
• Utføre én rad og flere rad spørringer

databasen i dette scenariet er selskapets menneskelige ressurser database (kalt hr) med følgende data modell:

En master tabell som heter offices inneholder liste over alle selskapets steder., offices tabellen har følgende definisjon:

Column Name Null? Type LOCATION NOT_NULL VARCHAR2(200)

Flere emp_beliggenhet tabeller inneholder den ansatte informasjon, der plassering er navnet på byen der kontoret ligger. For eksempel, en tabell som heter emp_houston inneholder ansatt informasjon for selskapets Houston-kontoret, mens en tabell som heter emp_boston inneholder ansatt informasjon for selskapets Boston office.,

Hver emp_beliggenhet tabellen har følgende definisjon:

følgende avsnitt beskriver ulike native dynamisk SQL-operasjoner som kan utføres på data i hr database.

Eksempel DML-Drift av lokale Dynamisk SQL

følgende native dynamisk SQL-prosedyren gir en høyning til alle ansatte med en bestemt jobb tittel:

Eksempel DDL Drift av lokale Dynamisk SQL

EXECUTE IMMEDIATE uttalelse kan utføre DDL operasjoner., For eksempel, følgende prosedyre legger et office-sted:

følgende prosedyre sletter et office-sted:

Eksempel Single-Row-Spørring ved Hjelp av Innfødte Dynamisk SQL

EXECUTE IMMEDIATE uttalelse kan utføre dynamisk single-row-spørringer. Du kan angi bind variabler i USING klausulen og hente den resulterende rad inn målet som er angitt i INTO klausulen i erklæringen.,

Den følgende funksjonen henter antall ansatte på et spesielt sted for å utføre en bestemt jobb:

Eksempel Flere-Rad-Spørring ved Hjelp av Innfødte Dynamisk SQL

OPEN-FOR, FETCH, og CLOSE uttalelser kan utføre dynamisk flere-rad-spørringer., For eksempel, følgende prosedyre lister alle av de ansatte med en bestemt jobb på et angitt sted:

Velge Mellom Innfødte Dynamisk SQL og DBMS_SQL Pakken

Oracle gir to metoder for å bruke dynamisk SQL innen PL/SQL: naturlig dynamisk SQL og DBMS_SQL pakken. Native dynamisk SQL lar deg plassere dynamisk SQL-setninger direkte i PL/SQL-kode. Disse dynamiske uttalelser inkluderer DML-setninger (inkludert spørsmål), PL/SQL anonym blokker, DDL-setninger, transaksjon kontroll uttalelser, og økt kontroll uttalelser.,

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 en PL/SQL-bibliotek som tilbyr et API for å utføre SQL-setninger dynamisk. DBMS_SQL pakken har prosedyrer for å åpne en markør, parse en markør, supply-binder, og så videre. Programmer som bruker DBMS_SQL pakken ringe til denne pakken til å utføre dynamisk SQL-operasjoner.

De følgende avsnittene gir detaljert informasjon om fordelene av begge metoder.,

Fordeler av Innfødte Dynamisk SQL

Native dynamisk SQL gir følgende fordeler over DBMS_SQL pakke

Native Dynamisk SQL er Enkel å Bruke

Fordi native dynamisk SQL er integrert med SQL, kan du bruke den på samme måte som du bruker statisk SQL innen PL/SQL-kode. Native dynamisk SQL-koden er vanligvis mer kompakt og lesbar enn tilsvarende kode som bruker DBMS_SQL pakken.,

Med DBMS_SQL pakken må du ringe mange prosedyrer og funksjoner i en bestemt rekkefølge, noe som gjør selv enkle operasjoner som krever mye av koden. Du kan unngå denne kompleksiteten ved hjelp av innebygde dynamisk SQL i stedet.

Tabell 8-1 viser forskjellen i mengden av kode som er nødvendig for å utføre den samme operasjonen ved hjelp av DBMS_SQL pakken og native dynamisk SQL.,

Tabell 8-1 Kode Sammenligning av DBMS_SQL Pakke og Native Dynamisk SQL

Native Dynamisk SQL er Raskere enn DBMS_SQL

Native dynamisk SQL i PL/SQL utfører relativt til ytelse av statisk SQL, fordi PL/SQL tolk har innebygd støtte for det. Programmer som bruker native dynamisk SQL er mye raskere enn programmer som bruker DBMS_SQL pakken. Vanligvis native dynamisk SQL-setninger utføre 1,5 til 3 ganger bedre enn tilsvarende DBMS_SQL anrop. (Resultatene dine gevinster kan variere avhengig av programmet.,)

Native dynamisk SQL bunter uttalelsen forberedelse, bindende og gjennomføring skritt inn i en enkelt operasjon, noe som minimaliserer kopieringen og prosedyre anrop overhead og forbedrer ytelsen.

DBMS_SQL pakken er basert på en prosessuelle API og pådrar høy prosedyre anrop og data kopi overhead. Hver gang du binder en variabel, DBMS_SQL pakken kopier PL/SQL binde variabel i sin plassen for bruk under kjøring., Hver gang du utfører en hente dataene er kopiert inn i rommet, og forvaltes av DBMS_SQL pakken, og deretter hentes dataene som er kopiert, en kolonne i en tid, i riktig PL/SQL-variabler, noe som resulterer i betydelig overhead.

Ytelse Tips: Bruker Bind Variabler

Når du bruker enten native dynamisk SQL eller DBMS_SQL pakken, du kan forbedre ytelsen ved å bruke bind variabler, fordi bind variabler kan Oracle for å dele en enkelt markøren for flere SQL-setninger.,

For eksempel, følgende native dynamisk SQL-koden ikke bruke bind variabler:

For hver distinkt my_deptno variabel, en ny markøren er opprettet, forårsaker ressurs strid og dårlig ytelse. I stedet binde my_deptno som binder variabel:

Her, det samme markøren er nytt for ulike verdier av binde my_deptno, bedre ytelse og skalerbarhet.,

Native Dynamisk SQL Støtter brukerdefinerte Typer

Native dynamisk SQL støtter alle typer av statisk SQL i PL/SQL, inkludert brukerdefinerte typer, slik som brukerdefinerte objekter, samlinger og REFs. DBMS_SQL pakken din støtter ikke disse brukerdefinerte typer.

Native Dynamisk SQL Støtter Hente Inn Poster

Native dynamisk SQL og statisk SQL støtte både hente inn poster, men DBMS_SQL pakken ikke. Med native dynamisk SQL, radene som følge av en spørring kan være direkte hentet inn i PL/SQL-poster.,

I følgende eksempel rader fra en spørring er hentet inn emp_rec legg til:

Fordeler av DBMS_SQL Pakken

DBMS_SQL pakken gir følgende fordeler over native dynamisk SQL:

DBMS_SQL Støttes i Klient-Side-Programmer

DBMS_SQL pakken kan brukes i klient-side-programmer, men innfødte dynamisk SQL er det ikke., Alle anrop til DBMS_SQL pakken fra klient-program som oversetter til en PL/SQL-RPC (remote procedure call); disse samtalene oppstår når du trenger å binde en variabel, må du definere en variabel, eller utføre en uttalelse.

DBMS_SQL Støtter BESKRIVE

DESCRIBE_COLUMNS prosedyre i DBMS_SQL pakken kan brukes for å beskrive kolonner for en markør åpnet og lest gjennom DBMS_SQL. Denne funksjonen er lik DESCRIBE – kommandoen i SQL*Plus., Native dynamisk SQL ikke har en DESCRIBE anlegget.

DBMS_SQL Støtter Flere Rad Oppdateringer og Sletter med en RETUR Punkt

DBMS_SQL pakken støtter uttalelser med en RETURNING klausulen som oppdatere eller slette flere rader. Native dynamisk SQL støtter bare et RETURNING klausulen hvis en enkelt rad er returnert.,

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., Etter den første parsing, kan du bruke den setningen flere ganger med ulike sett av bind argumenter.

Native dynamisk SQL forbereder en SQL-setning for hver gang uttrykket er brukt, noe som vanligvis innebærer analyse, optimalisering, og planen generasjon. Selv om de ekstra forberede operasjoner medfører en liten ytelse straff, og nedgangen er vanligvis oppveies av ytelse fordeler av innfødte dynamisk SQL.,

Eksempler på DBMS_SQL Pakke Koden og Native Dynamisk SQL-Kode

følgende eksempler illustrerer forskjeller i koden er nødvendig for å fullføre operasjoner med DBMS_SQL pakken og native dynamisk SQL. Spesielt på følgende typer eksempler er presentert:

• En spørring
• EN DML-drift
• EN DML tilbake drift

generelt native dynamisk SQL-kode er mer lesbar og kompakt, noe som kan forbedre utvikler produktiviteten.,

Spørring ved Hjelp av Dynamisk SQL: Eksempel

følgende eksempel inneholder en dynamisk spørring uttalelse med ett bind variabel (:jobname) og to kolonner (ename og sal):

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

Dette eksemplet spørringer for ansatte med jobb beskrivelse SALESMAN i job kolonne av emp tabell. Tabell 8-2 viser eksempel på kode som oppnår dette spørring ved hjelp av DBMS_SQL pakken og native dynamisk SQL.,

Tabell 8-2 Spørring ved Hjelp av DBMS_SQL Pakke og Native Dynamisk SQL

Utføre DML ved Hjelp av Dynamisk SQL: Eksempel

følgende eksempel inneholder en dynamisk INSERT statement for en tabell med tre kolonner:

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

Dette eksemplet setter inn en ny rad for hvilke kolonnen verdier i PL/SQL-variabler deptnumber, deptname, og location. Tabell 8-3 viser eksempel på kode som oppnår dette DML-drift ved hjelp av DBMS_SQL pakken og native dynamisk SQL.,

Tabell 8-3 DML-Drift ved Hjelp av DBMS_SQL Pakke og Native Dynamisk SQL

Utføre DML med RETUR Punkt ved Hjelp av Dynamisk SQL: Eksempel

følgende eksempel bruker en dynamisk UPDATE statement å oppdatere plasseringen av en avdeling, og deretter returnerer navnet på avdelingen:

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

Tabell 8-4 viser eksempel på kode som fullfører denne operasjonen ved hjelp av både DBMS_SQL pakken og native dynamisk SQL.,

Tabell 8-4 DML Tilbake Drift ved Hjelp av DBMS_SQL Pakke og Native Dynamisk SQL

ved Hjelp av Dynamisk SQL i Andre Språk Enn PL/SQL

Selv om dette kapitlet drøfter PL/SQL-støtte for dynamisk SQL, kan du ringe dynamisk SQL fra andre språk:

• Hvis du bruker C/C++, kan du ringe dynamisk SQL med Oracle Call Interface (OCI), eller du kan bruke Pro*C/C++ precompiler å legge til dynamisk SQL-utvidelser til C-kode.
• Hvis du bruker COBOL, kan du bruke Pro*COBOL precompiler å legge til dynamisk SQL-utvidelser til COBOL-koden.,
• Hvis du bruker Java, kan du utvikle programmer som bruker dynamisk SQL med JDBC.

Hvis du har et program som bruker OCI, Pro*C/C++, eller Pro*COBOL å utføre dynamisk SQL, bør du vurdere å bytte til en naturlig dynamisk SQL inne i PL/SQL-lagrede prosedyrer og funksjoner. Nettverket rundt-turer som kreves for å utføre dynamisk SQL-operasjoner fra klient-side-programmer kan skade ytelse. Lagrede prosedyrer kan ligge på serveren, noe som eliminerer nettverk overhead., Du kan ringe PL/SQL-lagrede prosedyrer og funksjoner som er lagret fra OCI, Pro*C/C++, eller Pro*COBOL-programmet.,br>Se Også:

For informasjon om å ringe Oracle, lagrede prosedyrer og funksjoner som er lagret fra ulike språk, kan du se:

• Oracle Call Interface Programmer ‘s Guide
• Pro*C/C++ Precompiler Programmer’ s Guide
• Pro*COBOL Precompiler Programmer ‘s Guide
• Oracle9i Java Lagrede Prosedyrer Developer’ s Guide

ved Hjelp av PL/SQL-Poster i SQL INSERT og UPDATE Uttalelser

Selv om du kan nummerere hvert felt i en PL/SQL-posten når du setter det inn eller oppdatering av rader i en tabell, den resulterende koden ikke er spesielt lesbart eller vedlikeholde., I stedet kan du bruke PL/SQL-poster direkte i disse uttalelsene. Den mest praktiske teknikken er å forkynne spille inn ved hjelp av en %ROWTYPE attributt, slik at det har nøyaktig de samme feltene som SQL-tabellen.

Selv om denne teknikken bidrar til å integrere PL/SQL-variabler og typer tettere med SQL-DML-uttalelser, kan du ikke bruke PL/SQL-poster som bind variabler i dynamisk SQL-setninger.