8 Coding Dynamic SQL Statements (Magyar)

A Dynamic SQL egy programozási technika, amely lehetővé teszi az SQL utasítások dinamikus felépítését futási időben. Általánosabb, rugalmasabb alkalmazásokat hozhat létre a dinamikus SQL használatával, mivel az SQL utasítás teljes szövege ismeretlen lehet az összeállításkor. Például a dynamic SQL lehetővé teszi, hogy olyan eljárást hozzon létre, amely olyan asztalon működik, amelynek neve a futásidejéig nem ismert.,

Az Oracle kétféle módon valósíthatja meg a dinamikus SQL-t egy PL/SQL alkalmazásban:

• natív dinamikus SQL, ahol a dinamikus SQL-utasításokat közvetlenül PL/SQL-blokkokba helyezi.
• hívási eljárások a DBMS_SQL csomagban.

Ez a fejezet a következő témákra terjed ki:

• “mi a dinamikus SQL?”
• ” miért használja a dinamikus SQL-t?,”
• “Egy Dinamikus SQL Forgatókönyv Segítségével Natív Dinamikus SQL”
• “Közötti Választás Natív Dinamikus SQL, valamint a DBMS_SQL Csomag”
• “A Dinamikus SQL Nyelven Más, Mint a PL/SQL”
• “A PL/SQL Rekordok SQL BESZÚRÁS, majd a FRISSÍTÉS Kimutatások”

megtalálja a részleteket arról, hogy a DBMS_SQL csomag az Oracle9i Mellékelt PL/SQL Csomagok Típusok Referencia.

mi a dinamikus SQL?

a dinamikus SQL lehetővé teszi olyan programok írását, amelyek hivatkoznak az SQL utasításokra, amelyek teljes szövege a futásidejéig nem ismert., Mielőtt részletesen megvitatnánk a dinamikus SQL-t, a statikus SQL egyértelmű meghatározása jó kiindulási pontot jelenthet a dinamikus SQL megértéséhez. A statikus SQL utasítások nem változnak a végrehajtásról a végrehajtásra. A statikus SQL utasítások teljes szövege az összeállítás során ismert, amely a következő előnyökkel jár:

• sikeres összeállítás ellenőrzi, hogy az SQL utasítások hivatkoznak-e érvényes adatbázisobjektumokra.
• a sikeres összeállítás igazolja, hogy az adatbázisobjektumok eléréséhez szükséges jogosultságok vannak érvényben.,
• a statikus SQL teljesítménye általában jobb, mint a dinamikus SQL.

ezen előnyök miatt csak akkor szabad dinamikus SQL-t használni, ha nem tudja statikus SQL-t használni a célok eléréséhez, vagy ha a statikus SQL használata nehézkes a dinamikus SQL-hez képest. A statikus SQL-nek azonban vannak korlátai, amelyeket a dinamikus SQL-vel lehet leküzdeni. Lehet, hogy nem mindig ismeri az SQL utasítások teljes szövegét, amelyeket PL/SQL eljárásban kell végrehajtani., A program elfogadhatja a felhasználói bemenetet, amely meghatározza a végrehajtandó SQL utasításokat, vagy előfordulhat, hogy a programnak bizonyos feldolgozási munkát kell elvégeznie a helyes művelet irányának meghatározásához. Ilyen esetekben dinamikus SQL-t kell használnia.

például egy adatraktári környezetben lévő jelentéskészítő alkalmazás nem ismeri a pontos táblázat nevét a futásidejéig., Ezeket a táblázatokat a negyedév kezdő hónapjának és évének megfelelően lehet megnevezni, például: INV_01_1997, INV_04_1997, INV_07_1997, INV_10_1997, INV_01_1998 stb. A dinamikus SQL-t a jelentési alkalmazásban használhatja a táblázat nevének megadásához futásidőben.

lehet, hogy egy összetett lekérdezést is futtatni szeretne egy felhasználó által választható rendezési sorrendben., A lekérdezés kétszeri kódolása helyett, különböző ORDER BY záradékokkal dinamikusan felépítheti a lekérdezést, hogy tartalmazzon egy megadott ORDER BY záradékot.

A dinamikus SQL programok képesek kezelni az adatdefiníciók változásait anélkül, hogy újra kellene írni. Ez a dinamikus SQL-t sokkal rugalmasabbá teszi, mint a statikus SQL. A dinamikus SQL segítségével újrafelhasználható kódot írhat, mivel az SQL könnyen adaptálható különböző környezetekhez..,

Dynamic SQL is lehetővé teszi, hogy végre adatdefiníciós nyelv (DDL) nyilatkozatok és egyéb SQL utasítások, amelyek nem támogatottak tisztán statikus SQL programok.

miért használjon dinamikus SQL-t?

dinamikus SQL-t kell használnia olyan esetekben, amikor a statikus SQL nem támogatja a végrehajtani kívánt műveletet, vagy olyan esetekben, amikor nem ismeri a pontos SQL-utasításokat, amelyeket PL/SQL eljárással kell végrehajtani. Ezek az SQL utasítások függhetnek a felhasználó bemenetétől, vagy függhetnek a program által végzett feldolgozási munkától.,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.,

is, akkor csak a TABLE záradék a SELECT nyilatkozat dinamikus SQL. Például, a következő PL/SQL blokk tartalmaz egy SELECT nyilatkozat arról, hogy használja a TABLE záradék, illetve natív dinamikus SQL:

Végrehajtó Dinamikus Lekérdezések

használhatja dinamikus SQL, hogy a jelentkezési végrehajtani dinamikus lekérdezések, amelynek teljes szövege nem ismert, amíg a runtime., Sok fajta alkalmazásokat kell használni dinamikus lekérdezések, beleértve:

• Alkalmazások, amelyek lehetővé teszik a felhasználók számára, hogy a bemeneti, vagy válassza a keresési lekérdezés, vagy válogatási kritériumok futásidőben
• Alkalmazások, amelyek lehetővé teszik a felhasználók számára, hogy írja be vagy válassza ki optimalizáló tippek a futási idő
• Alkalmazások lekérdezés egy adatbázis, ahol az adatok meghatározása a táblázatok folyamatosan változó
• Alkalmazások lekérdezés egy adatbázis, ahol az új táblák jönnek létre, gyakran

A példák, lásd a “Lekérdezés Segítségével Dinamikus SQL: Például” a lekérdezés példa a “Dinamikus SQL Forgatókönyv Segítségével Natív Dinamikus SQL”.,

olyan adatbázisobjektumok hivatkozása, amelyek nem léteznek a

összeállításnál, számos alkalmazásnak kölcsönhatásba kell lépnie az időszakosan generált adatokkal. Például ismerheti a táblázatok definícióit fordításkor, de nem a táblák nevét.

A dinamikus SQL megoldja ezt a problémát, mert lehetővé teszi, hogy megvárja a futásidejét a táblázat nevének megadásához. Például a minta adattárház alkalmazásban, amelyet a ” mi a dinamikus SQL?”, minden negyedévben új táblák jönnek létre, és ezeknek a tábláknak mindig ugyanaz a definíciójuk., Lehet, hogy a felhasználó adja meg a nevét, a táblázat futásidőben a dinamikus SQL lekérdezés az alábbihoz hasonló:

Optimalizálása Végrehajtás Dinamikusan

használhatja dinamikus SQL építeni egy SQL, hogy optimalizálja a kivégzés által összefűző a tippeket, egy SQL utasítás dinamikusan. Ez lehetővé teszi, hogy módosítsa a tippeket alapján a jelenlegi adatbázis statisztika, anélkül, hogy újrafordítást.,

például a következő eljárás egy a_hint nevű változót használ annak érdekében, hogy a felhasználók átadhassanak egy Tipp opciót a SELECT utasítás:

ebben a példában a felhasználó átadhatja a a_hint:

a_hint = '/*+ ALL_ROWS */'a_hint = '/*+ FIRST_ROWS */'a_hint = '/*+ CHOOSE */'vagy bármely más érvényes Tipp opció.

dinamikus PL/SQL blokkok végrehajtása

használhatja a EXECUTE IMMEDIATE utasítás névtelen PL/SQL blokkok végrehajtásához. Rugalmasságot adhat hozzá a blokk tartalmának felépítésével futási időben.

például tegyük fel, hogy ythroughthroughthrough you want to write an application that takes an event number and dispatches to a handler for the event. A kezelő neve EVENT_HANDLER_event_num formában van, ahol event_num az esemény száma., Az egyik megközelítés az, hogy végre a diszpécser, mint egy kapcsoló nyilatkozatot, ahol a kód kezeli az egyes események azáltal, hogy egy statikus hívást a megfelelő kezelő. Ez a kód nem túl bővíthető, mert a diszpécser kódot frissíteni kell, amikor egy kezelő egy új esemény hozzá.,

a natív dinamikus SQL használatával írhat egy kisebb, rugalmasabb esemény-diszpécsert, amely hasonló a következőkhöz:

dinamikus műveletek végrehajtása Invoker-Rights

A invoker-rights funkció dinamikus SQL-vel történő használatával dinamikus SQL-nyilatkozatokat állíthat ki a számlázó jogosultságai és sémája alatt. Ez a két funkció, az invoker-rights és a dynamic SQL lehetővé teszi, hogy újrafelhasználható alkalmazás-alkomponenseket építsen ki, amelyek képesek a számlázó adataihoz és moduljaihoz hozzáférni.,

A Dinamikus SQL Forgatókönyv Segítségével Natív Dinamikus SQL

Ez a forgatókönyv azt mutatja meg, hogyan hajtsa végre a következő műveletek segítségével natív dinamikus SQL:

• Execute DDL, valamint DML műveletek
• Execute egyetlen sor, több sor lekérdezések

Az adatbázis ebben az esetben egy vállalat humán erőforrás adatbázis (a neve hr) a következő adatok modell:

A mester táblázat nevű offices tartalmaz a lista az összes cég helyeken., Aoffices táblázat a következő meghatározással rendelkezik:

Column Name Null? Type LOCATION NOT_NULL VARCHAR2(200)

többszörösemp_a helytáblázatok tartalmazzák a munkavállalói információkat, ahol a hely annak a városnak a neve, ahol az iroda található. Például egy emp_houston nevű táblázat tartalmazza a vállalat Houstoni irodájának munkavállalói adatait, míg egy emp_boston nevű táblázat tartalmazza a vállalat bostoni irodájának munkavállalói adatait.,

mindegyik emp_a helytábla a következő meghatározással rendelkezik:

a következő szakaszok különböző natív dinamikus SQL műveleteket írnak le, amelyeket a hr adatbázisban lehet végrehajtani.

Minta DML művelet segítségével natív dinamikus SQL

a következő natív dinamikus SQL eljárás ad emelés minden alkalmazott egy adott munkaköri cím:

Minta DDL művelet natív dinamikus SQL

a EXECUTE IMMEDIATE utasítás végezhet DDL műveleteket., Például a következő eljárás hozzáad egy irodai helyet:

a következő eljárás törli az irodai helyet:

Minta Egysoros lekérdezés natív dinamikus SQL

a EXECUTE IMMEDIATE utasítás dinamikus egysoros lekérdezéseket hajthat végre. A kötési változókat a USING záradékban adhatja meg, majd a kapott sort a nyilatkozat INTO záradékában megadott célba helyezheti.,

a következő függvény beolvassa az alkalmazottak száma Egy adott helyen végző meghatározott munkát:

Minta többsoros lekérdezés segítségével natív dinamikus SQL

a OPEN-FOR, FETCH, és CLOSE nyilatkozatok végre dinamikus több-sor lekérdezések., Például a következő eljárás felsorolja az összes alkalmazottat, akinek egy adott munkája van egy meghatározott helyen:

A natív dinamikus SQL és a Dbms_sql csomag közötti választás

Az Oracle két módszert kínál a dinamikus SQL használatára a PL/SQL-en belül: native dynamic SQL és a DBMS_SQL csomag. A natív dinamikus SQL lehetővé teszi a dinamikus SQL utasítások közvetlen elhelyezését PL / SQL kódba. Ezek a dinamikus utasítások magukban foglalják a DML-kimutatásokat (beleértve a lekérdezéseket), a PL/SQL anonymous blokkokat, a DDL-nyilatkozatokat, a tranzakció-ellenőrzési nyilatkozatokat és a munkamenet-ellenőrzési nyilatkozatokat.,

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.

a DBMS_SQL a csomag egy PL/SQL könyvtár, amely API-t kínál az SQL utasítások Dinamikusan történő végrehajtására. ADBMS_SQL csomag eljárásokat tartalmaz a kurzor megnyitására, a kurzor elemzésére, a kínálat kötésére stb. A DBMS_SQL csomagot használó programok hívásokat kezdeményeznek erre a csomagra dinamikus SQL műveletek végrehajtásához.

a következő szakaszok részletes tájékoztatást nyújtanak mindkét módszer előnyeiről.,

Előnyei Natív Dinamikus SQL

Natív dinamikus SQL rendelkezik, a következő előnyöket a DBMS_SQL csomag:

Natív Dinamikus SQL Használata Egyszerű

Mert natív dinamikus SQL integrált SQL, akkor tudja használni, ugyanúgy, ahogy használja a statikus SQL belül PL/SQL-kód. A natív dinamikus SQL kód általában kompaktabb és olvashatóbb, mint a DBMS_SQL csomagot használó egyenértékű kód.,

a DBMS_SQL csomag segítségével számos eljárást és funkciót szigorú sorrendben kell hívnia, így még az egyszerű műveletekhez is sok kód szükséges. Ezt a bonyolultságot elkerülheti a natív dinamikus SQL használatával.

A 8-1. táblázat a DBMS_SQL csomag és natív dinamikus SQL használatával azonos művelet végrehajtásához szükséges kódmennyiség különbségét szemlélteti.,

8-1. táblázat a Dbms_sql csomag és a natív dinamikus SQL

natív dinamikus SQL összehasonlítása gyorsabb, mint a dbms_sql

a natív dinamikus SQL a PL/SQL-ben összehasonlíthatóan teljesít a statikus SQL teljesítményével, mivel a PL/SQL értelmező beépített támogatást nyújtott hozzá. A natív dinamikus SQL-t használó programok sokkal gyorsabbak, mint a DBMS_SQL csomagot használó programok. Általában a natív dinamikus SQL utasítások 1,5-3-szor jobbak, mint az egyenértékű DBMS_SQL hívások. (A teljesítménynövekedés az alkalmazástól függően változhat.,)

a natív dynamic SQL egyetlen műveletbe Csomagolja az utasításkészítési, kötési és végrehajtási lépéseket, ami minimalizálja az adatmásolási és eljáráshívási költségeket, és javítja a teljesítményt.

aDBMS_SQL csomag egy procedurális API-n alapul, és magas eljáráshívási és adatmásolási költségeket eredményez. Minden alkalommal, amikor egy változót köt, aDBMS_SQL csomag másolja a PL/SQL bind változót a helyére a végrehajtás során., Minden egyes alkalommal, amikor végre egy lekérni az adatokat másolja be a hely által kezelt, hogy a DBMS_SQL csomagot, majd a letöltésre adatok másolása, egy oszlop, egy időben, a megfelelő PL/SQL változók, ami jelentős rezsi.

Teljesítmény Tipp: A Bind Változók

Mikor segítségével akár natív dinamikus SQL vagy a DBMS_SQL csomagot, akkor a teljesítmény javítása érdekében segítségével bind változók, mert bind változók lehetővé Oracle megosztani egyetlen kurzor több SQL-utasítások.,

például a következő natív dinamikus SQL kód nem használ kötési változókat:

minden különálló my_deptno változóhoz új kurzor jön létre, ami erőforrás-ellentmondást és gyenge teljesítményt eredményez. Ehelyett bind my_deptno mint bind változó:

itt ugyanazt a kurzort újra használják a bind my_deptno különböző értékeihez, javítva a teljesítményt és a scalabilty-t.,

natív dinamikus SQL támogatja a felhasználó által definiált típusok

natív dinamikus SQL támogatja az összes típus által támogatott statikus SQL PL / SQL, beleértve a felhasználó által definiált típusok, mint a felhasználó által definiált objektumok, gyűjtemények, és REFs. ADBMS_SQL csomag nem támogatja ezeket a felhasználó által definiált típusokat.

a natív Dynamic SQL támogatja a

natív dinamikus SQL és statikus SQL rekordokba való lekérést, de a DBMS_SQL csomag nem. Natív dinamikus SQL esetén a lekérdezésből származó sorok közvetlenül lekérhetők PL / SQL rekordokba.,

a következő példában a sort a lekérdezés vagy letöltésre a emp_rec felvétel:

Előnye a DBMS_SQL Csomag

A DBMS_SQL a csomag tartalmazza a következő előnye a natív dinamikus SQL:

DBMS_SQL Támogatott Ügyfél Oldali Programok

A DBMS_SQL csomag támogatja a kliens oldali programok, de natív dinamikus SQL-nem., Minden hívás aDBMS_SQL csomag a kliens oldali program lefordítja a PL/SQL remote procedure call (RPC); ezek a hívások akkor fordulnak elő, ha meg kell kötni egy változó, meg egy változó, vagy végre egy nyilatkozatot.

DBMS_SQL támogatja leírni

a DESCRIBE_COLUMNS eljárás a DBMS_SQL csomag lehet használni, hogy leírja az oszlopokat a kurzor megnyitott és elemzett keresztül DBMS_SQL. Ez a funkció hasonló aDESCRIBE parancshoz az SQL*Plus-ban., A natív dinamikus SQL nem rendelkezik DESCRIBE lehetőséggel.

A DBMS_SQL több Sorfrissítést támogat, és a

DBMS_SQL csomag támogatja a RETURNING záradékot, amely több sor frissítését vagy törlését teszi lehetővé. A Native dynamic SQL csak akkor támogatja a RETURNING záradékot, ha egyetlen sor kerül visszaadásra.,

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., A kezdeti elemzés után többször is használhatja a nyilatkozatot különböző kötési argumentumkészletekkel.

natív dinamikus SQL készít egy SQL utasítás minden alkalommal, amikor a nyilatkozatot használják, amely általában magában foglalja az elemzés, optimalizálás, terv generációs. Bár az extra előkészítési műveletek kis teljesítmény büntetést vonnak maguk után, a lassulást általában felülmúlják a natív dinamikus SQL teljesítmény előnyei.,

példák a Dbms_sql Csomagkódra és a natív dinamikus SQL kódra

a következő példák szemléltetik a DBMS_SQL csomag és a natív dinamikus SQL használatához szükséges kódbeli különbségeket. Konkrétan a következő típusú példákat mutatjuk be:

• a lekérdezés
• A DML művelet
• A DML visszatérő művelet

általában a natív dinamikus SQL kód olvashatóbb és kompaktabb, ami javíthatja a fejlesztői termelékenységet.,

Lekérdezés Segítségével Dinamikus SQL: Példa

Az alábbi példa tartalmazza a dinamikus lekérdezési nyilatkozat egy bind változó (:jobname), valamint két oszlopok kiválasztása (ename vagy sal):

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

Ez a példa lekérdezések a munkavállalót a munkaköri leírás SALESMAN a job oszlop a emp táblázat. A 8-2. táblázat a DBMS_SQL csomag és a natív dinamikus SQL használatával megvalósítja ezt a lekérdezést.,

Táblázat 8-2 Lekérdezés Segítségével a DBMS_SQL Csomag Natív Dinamikus SQL

Teljesítő DML Segítségével Dinamikus SQL: Példa

Az alábbi példa tartalmazza a dinamikus INSERT nyilatkozat egy asztal, három oszlop:

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

Ez a példa beszúr egy új sor, amelyre az oszlop értékei a PL/SQL változók deptnumber, deptname vagy location. A 8-3. táblázat azt a mintakódot mutatja, amely ezt a DML műveletet a DBMS_SQL csomag és natív dinamikus SQL használatával valósítja meg.,

Táblázat 8-3 DML Művelet Segítségével a DBMS_SQL Csomag Natív Dinamikus SQL

Teljesítő DML VISSZATÉRŐ Záradék Segítségével Dinamikus SQL: Példa

A következő példában egy dinamikus UPDATE nyilatkozatot, hogy frissítse a helyét egy osztály, akkor visszatér a megye nevét:

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

a Táblázat 8-4 mutatja minta kód, amely megvalósítja ezt a műveletet használja mind a DBMS_SQL csomag natív dinamikus SQL.,

Táblázat 8-4 DML Visszatérő Művelet Segítségével a DBMS_SQL Csomag Natív Dinamikus SQL

A Dinamikus SQL Nyelven Más, Mint a PL/SQL

Bár ez a fejezet azt tárgyalja, PL/SQL támogatja a dinamikus SQL, akkor hívja a dinamikus SQL más nyelvek:

• Ha a C/C++, akkor hívja a dinamikus SQL az Oracle Call Interface (OCI), vagy használhatja a Pro*C/C++ precompiler hozzá dinamikus SQL fájlokat a C kódot.
• ha a COBOL-t használja, a Pro*COBOL precompiler segítségével dinamikus SQL kiterjesztéseket adhat hozzá a COBOL-kódhoz.,
• Ha Java-t használ, fejleszthet olyan alkalmazásokat, amelyek dinamikus SQL-t használnak a JDBC-vel.

Ha van olyan alkalmazás, amely OCI, Pro * c/c++, vagy Pro * COBOL végre dinamikus SQL, akkor érdemes váltani natív dinamikus SQL belül PL / SQL tárolt eljárások és funkciók. A kliens oldali alkalmazások dinamikus SQL műveleteinek végrehajtásához szükséges hálózati körutazások ronthatják a teljesítményt. A tárolt eljárások a kiszolgálón tartózkodhatnak, kiküszöbölve a hálózati költségeket., Hívhatja a PL / SQL tárolt eljárások tárolt funkciók az OCI, Pro * c / c++, vagy Pro * COBOL alkalmazás.,br>Lásd még:

további információ arról, hogy felhívja az Oracle tárolt eljárások tárolt funkciók a különböző nyelvek, lásd:

• Oracle Call Interface-programozói Útmutató
• Pro*C/C++ Precompiler programozói Útmutató
• Pro*COBOL Precompiler programozói Útmutató
• Oracle9i Java Tárolt Eljárások Fejlesztői Útmutató

A PL/SQL Rekordok SQL BESZÚRÁS, majd a FRISSÍTÉS Kimutatások

Bár lehet felsorolni minden egyes pályán a PL/SQL rekord behelyezésekor vagy frissítése sor egy táblázatban, a kapott kód nem különösen olvasható, vagy karbantartható., Ehelyett a PL / SQL rekordokat közvetlenül használhatja ezekben a kijelentésekben. A legkényelmesebb módszer a rekord %ROWTYPE attribútummal történő deklarálása, így pontosan ugyanazokkal a mezőkkel rendelkezik, mint az SQL táblázat.

bár ez a technika segít jobban integrálni a PL / SQL változókat és típusokat az SQL DML utasításokkal, a PL / SQL rekordok nem használhatók bind változókként a dinamikus SQL utasításokban.