8 Dynamic SQL (Čeština)

Dynamic SQL je programovací technika, která umožňuje dynamické vytváření příkazů SQL za běhu. Pomocí dynamického jazyka SQL můžete vytvořit obecnější a flexibilnější aplikace, protože při kompilaci může být neznámý celý text příkazu SQL. Například dynamický SQL umožňuje vytvořit proceduru, která pracuje s tabulkou, jejíž název není znám až za běhu.

V minulých vydáních Oracle byl jediný způsob implementace dynamického SQL v aplikaci PL / SQL pomocí balíčku DBMS_SQL. Oracle8i představuje nativní dynamický SQL, alternativu k balíčku DBMS_SQL. Pomocí nativního dynamického SQL můžete umisťovat dynamické příkazy SQL přímo do bloků PL / SQL.

Tato kapitola se zabývá následujícími tématy:

• Co je to dynamický SQL?
• Kdy použít dynamický SQL
• Scénář dynamického SQL využívající nativní dynamický SQL
• Nativní dynamický SQL vs. balíček DBMS_SQL
• aplikace Vývojové jazyky jiné než PL / SQL

Co je to Dynamic SQL?

Dynamic SQL vám umožňuje psát programy, které odkazují na příkazy SQL, jejichž plný text není znám až do běhu. Před podrobným popisem dynamického SQL může jasná definice statického SQL poskytnout dobrý výchozí bod pro pochopení dynamického SQL. Statické příkazy SQL se nemění z provedení na provedení. Celý text statických příkazů SQL je znám při kompilaci, což poskytuje následující výhody:

• Úspěšná kompilace ověří, že příkazy SQL odkazují na platné databázové objekty.
• Úspěšná kompilace ověří, že jsou k dispozici potřebná oprávnění pro přístup k databázovým objektům.
• Výkon statického SQL je obecně lepší než dynamický SQL.

Kvůli těmto výhodám byste měli používat dynamický SQL pouze v případě, že k dosažení svých cílů nemůžete použít statický SQL, nebo pokud je používání statického SQL v porovnání s dynamickým SQL těžkopádné. Statický SQL má však omezení, která lze překonat pomocí dynamického SQL. Možná nebudete vždy znát plný text příkazů SQL, které je nutné provést v proceduře PL / SQL. Váš program může přijmout vstup uživatele, který definuje příkazy SQL, které se mají provést, nebo může být nutné, aby váš program dokončil nějaké zpracování, aby určil správný postup. V takových případech byste měli použít dynamický SQL.

Zvažte například aplikaci pro vytváření sestav, která provádí standardní dotazy na tabulky v prostředí datového skladu, kde je přesný název tabulky neznámý až do doby běhu. Chcete-li efektivně přizpůsobit velké množství dat v datovém skladu, vytvářejte každé čtvrtletí novou tabulku pro uložení fakturačních údajů za dané čtvrtletí. Všechny tyto tabulky mají přesně stejnou definici a jsou pojmenovány podle počátečního měsíce a roku čtvrtletí, například INV_01_1997, INV_04_1997, INV_07_1997, INV_10_1997, INV_01_1998 atd. V takovém případě můžete použít dynamický SQL ve vaší aplikaci pro vytváření sestav k určení názvu tabulky za běhu.

U statického SQL musí být při kompilaci známy všechny informace o definici dat, jako jsou definice tabulek, na které odkazují příkazy SQL ve vašem programu. Pokud se definice dat změní, musíte program změnit a znovu zkompilovat. Programy Dynamic SQL dokážou zpracovat změny v informacích o definici dat, protože příkazy SQL se mohou za běhu měnit „za běhu“. Proto je dynamický SQL mnohem flexibilnější než statický SQL. Dynamický SQL umožňuje psát aplikační kód, který je opakovaně použitelný, protože kód definuje proces, který je nezávislý na konkrétních použitých příkazech SQL.

Kromě toho vám dynamický SQL umožňuje provádět příkazy SQL, které nejsou podporovány ve statickém formátu Programy SQL, jako jsou příkazy jazyka definice dat (DDL). Podpora těchto příkazů vám umožní dosáhnout s vašimi programy PL / SQL více.

Kdy použít dynamický SQL

Dynamický SQL byste měli používat v případech, kdy statický SQL nepodporuje operaci, kterou chcete provést, nebo v případech, kdy neznáte přesné příkazy SQL, které je třeba provést procedurou PL / SQL. Tyto příkazy SQL mohou záviset na vstupu uživatele nebo mohou záviset na zpracování provedeném programem. Následující části popisují typické situace, kdy byste měli používat dynamický SQL a typické problémy, které lze vyřešit pomocí dynamického SQL.

Provádění dynamických příkazů DML

Ke spuštění můžete použít dynamický SQL Příkazy DML, ve kterých není přesný příkaz SQL znám až do běhu.Příklady najdete v příkladech DML v částech „Příklady kódu balíku DBMS_SQL a nativního dynamického kódu SQL“ a „Ukázková operace DML“.

Provádění příkazů, které statický SQL nepodporuje v PL / SQL

V PL / SQL nemůžete pomocí statického SQL provádět následující typy příkazů:

• Příkazy jazyka DDL (Data Definition Language), například CREATE, DROP, GRANT a REVOKE
• Příkazy jazyka pro řízení relací (SCL), například ALTER SESSION a SET ROLE
  Viz také:

  Reference Oracle8i SQL Reference pro informace o příkazech DDL a SCL.

Použijte dynamický SQL, pokud potřebujete provést některý z těchto typů příkazů v rámci PL / Blok SQL.

Navíc statický SQL v PL / SQL neumožňuje použití klauzule TABLE v SELECT prohlášení. V dynamickém SQL takové omezení neexistuje. Například následující blok PL / SQL obsahuje příkaz SELECT, který používá klauzuli TABLE a nativní dynamický SQL:

Provádět dynamické dotazy

Pomocí dynamického jazyka SQL můžete vytvářet aplikace, které provádějí dynamické dotazy, což jsou dotazy, jejichž plný text není znám až za běhu. Mnoho typů aplikací musí používat dynamické dotazy, včetně:

• Aplikace, které uživatelům umožňují zadávat nebo volit kritéria vyhledávání dotazů nebo řazení za běhu
• Aplikace, které uživatelům umožňují zadávat nebo vyberte tipy pro optimalizaci za běhu
• Aplikace, které se dotazují na databázi, kde se definice dat tabulek neustále mění
• Aplikace, které se dotazují na databázi, kde se často vytvářejí nové tabulky

Příklady najdete v části „Příklad dotazu“ a v příkladech dotazu v části „Dynamický scénář SQL pomocí nativního dynamického jazyka SQL“.

Odkaz na databázové objekty, které při kompilaci neexistují

Mnoho typů aplikací musí interagovat s daty, která se generují pravidelně. Například může být možné určit definici databázových tabulek při kompilaci, ale ne názvy tabulek, protože se pravidelně generují nové tabulky. Vaše aplikace potřebuje přístup k datům, ale do runtime neexistuje způsob, jak znát přesné názvy tabulek.

Dynamický SQL může tento problém vyřešit, protože dynamický SQL umožňuje počkat, až runtime specifikuje názvy tabulek, ke kterým potřebujete získat přístup. Například v ukázkové aplikaci datového skladu popsané v části „Co je to dynamický SQL?“ Se každé čtvrtletí generují nové tabulky a tyto tabulky mají vždy stejnou definici. V tomto případě můžete uživateli umožnit zadat název tabulky za běhu pomocí dynamického dotazu SQL podobného následujícímu:

Chcete-li dynamicky optimalizovat provádění

Pokud používáte statický SQL, musíte se při kompilaci rozhodnout, jak chcete sestavit své příkazy SQL, zda mají mít ve vašich příkazech nápovědu, a pokud zahrnete nápovědu, přesně, jaké nápovědy mají mít. Můžete však použít dynamický SQL k sestavení příkazu SQL způsobem, který optimalizuje provádění a / nebo zřetězí rady do příkazu SQL dynamicky. To vám umožňuje měnit tipy na základě vašich aktuálních statistik databáze, aniž byste vyžadovali rekompilaci.

Následující postup například používá proměnnou nazvanou a_hint, která uživatelům umožňuje předat možnost nápovědy k příkazu SELECT:

V tomto příkladu může uživatel předat kteroukoli z následujících hodnot pro a_hint:

K vyvolání dynamických bloků PL / SQL

Můžete použít EXECUTE IMMEDIATE příkaz k vyvolání anonymních bloků PL / SQL. Schopnost vyvolat dynamické bloky PL / SQL může být užitečná pro rozšíření a přizpůsobení aplikace, kde se modul, který má být spuštěn, určuje dynamicky za běhu.

Předpokládejme například, že chcete napsat aplikaci, která provede událost číslo a odešle obslužné rutině události. Název obslužné rutiny je ve tvaru EVENT_HANDLER_ event_num, kde event_num je číslo události. Jedním z přístupů by bylo implementovat dispečera jako příkaz switch, jak je znázorněno níže, kde kód zpracovává každou událost provedením statického volání příslušného handleru.

Tento kód není příliš rozšiřitelný, protože kód dispečera musí být aktualizován vždy, když je přidán obslužný program pro novou událost. Pomocí nativního dynamického jazyka SQL však můžete napsat rozšiřitelný dispečer událostí podobný tomuto:

Provádění dynamických operací pomocí práv volajícího

Pomocí funkce práv volajícího s dynamickým SQL, můžete vytvářet aplikace, které vydávají dynamické příkazy SQL na základě oprávnění a schématu vyvolávače.Tyto dvě funkce, práva volajícího a dynamický SQL, vám umožňují vytvářet opakovaně použitelné dílčí součásti aplikace, které mohou pracovat s daty a moduly volajícího a přistupovat k nim.

Scénář dynamického SQL pomocí nativního dynamického SQL

Scénář popsaný v této části ilustruje sílu a flexibilitu nativního dynamického SQL. Tento scénář obsahuje příklady, které vám ukáží, jak provádět následující operace pomocí nativního dynamického jazyka SQL:

• Provádět operace DDL a DML
• Provádět dotazy na jeden řádek a více řádků

Datový model

Databáze v tomto scénáři je databází lidských zdrojů společnosti (s názvem hr) s následujícím datovým modelem :

Hlavní tabulka s názvem offices obsahuje seznam všech umístění společnosti. Tabulka offices má následující definici :

Column Name Null? Type LOCATION NOT_NULL VARCHAR2(200)

Více emp_ tabulek umístění obsahuje informace o zaměstnancích, kde location je název města, kde je kancelář je umístěn. Například tabulka s názvem emp_houston obsahuje informace o zaměstnancích kanceláře společnosti Houston, zatímco tabulka s názvem emp_boston obsahuje zaměstnance informace o bostonské kanceláři společnosti.

Každý emp_ Tabulka umístění má následující definici:

Následující části popisují různé nativní dynamické operace SQL, které lze provést s daty v hr databáze.

Ukázková operace DML

Následující nativní dynamická procedura SQL zvýší všechny zaměstnance s konkrétním názvem úlohy:

Ukázková operace DDL

Příkaz EXECUTE IMMEDIATE může provádět operace DDL. Například následující postup přidá umístění kanceláře:

Následující postup odstraní umístění kanceláře:

Ukázkový dynamický jednorázový dotaz

EXECUTE IMMEDIATE příkaz může provádět dynamické jednorázové dotazy. V klauzuli USING můžete určit vázané proměnné a výsledný řádek načíst do cíle uvedeného v klauzuli INTO příkazu.

Následující funkce načte počet zaměstnanců na konkrétním místě provádějících zadanou úlohu:

Ukázkový dynamický víceřádkový dotaz

, FETCH a CLOSE mohou provádět dynamické víceřádkové dotazy. Například následující postup uvádí seznam všech zaměstnanců s konkrétní úlohou na zadaném místě:

Nativní dynamický SQL vs. balíček DBMS_SQL

Oracle poskytuje dvě metody pro použití dynamického SQL v rámci PL / SQL: nativní dynamický SQL a balíček DBMS_SQL. Nativní dynamický SQL umožňuje umístit dynamické příkazy SQL přímo do kódu PL / SQL. Mezi tyto dynamické příkazy patří příkazy DML (včetně dotazů), anonymní bloky PL / SQL, příkazy DDL, příkazy řízení transakcí a příkazy řízení relací.

Ke zpracování většiny nativních dynamických příkazů SQL použijte EXECUTE IMMEDIATE prohlášení. Ke zpracování víceřádkového dotazu (SELECT) však použijete OPEN-FOR, FETCH a CLOSE prohlášení.

Balíček DBMS_SQL je knihovna PL / SQL, která nabízí programové API pro spouštět příkazy SQL dynamicky. Balíček DBMS_SQL obsahuje programová rozhraní pro otevření kurzoru, analýzu kurzoru, vazbu vazeb atd. Programy, které používají balíček DBMS_SQL, provádějí hovory k tomuto balíčku provádět dynamické operace SQL.

Následující části poskytují podrobné informace o výhodách obou metod.

Výhody nativního dynamického SQL

Nativní dynamický SQL poskytuje oproti :

Snadné použití

Nativní dynamický SQL se používá mnohem jednodušší než balíček DBMS_SQL.Protože nativní dynamický SQL je integrován s SQL, můžete jej použít stejným způsobem, jakým aktuálně používáte statický SQL v kódu PL / SQL. Nativní dynamický kód SQL je navíc obvykle kompaktnější a čitelnější než ekvivalentní kód, který používá balíček DBMS_SQL.

DBMS_SQL . Této složitosti se můžete vyhnout tím, že místo toho použijete nativní dynamický SQL.

Tabulka 8-1 ukazuje rozdíl v množství kódu potřebného k provedení stejné operace pomocí DBMS_SQL balíček a nativní dynamický SQL.

Tabulka 8-1 Porovnání kódu balíku DBMS_SQL a nativního dynamického SQL

Vylepšení výkonu

Výkon nativního dynamického SQL v PL / SQL je srovnatelný s výkonem statického SQL, protože tlumočník PL / SQL má integrovanou podporu nativního dynamického SQL. Proto je výkon programů, které používají nativní dynamický SQL, mnohem lepší než výkon programů, které používají balíček DBMS_SQL. Nativní dynamické příkazy SQL obvykle fungují 1,5 až 3krát lépe než ekvivalentní příkazy, které používají balíček DBMS_SQL. Zvýšení výkonu se samozřejmě může lišit v závislosti na vaší aplikaci.

Balíček DBMS_SQL je založen na procedurálním API a ve výsledku přináší vysoké volání procedury a režii kopírování dat. Například při každém svázání proměnné balíček DBMS_SQL zkopíruje proměnnou vazby PL / SQL do jejího prostoru pro pozdější použití během provádění. Podobně pokaždé, když provedete načtení, nejprve se data zkopírují do prostoru spravovaného balíčkem DBMS_SQL a poté se načtená data zkopírují po jednom sloupci do příslušného Proměnné PL / SQL, což má za následek značnou režii vyplývající z kopírování dat. Naproti tomu nativní dynamický SQL spojuje kroky přípravy, vazby a provádění příkazů do jedné operace, což minimalizuje kopírování dat a režii volání procedur a zvyšuje výkon.

Tip pro výkon

Když používáte buď nativní dynamický SQL, nebo balíček DBMS_SQL, můžete zlepšit výkon pomocí proměnných vazby, protože použití proměnných vazby umožňuje Oracle sdílet jeden kurzor pro více příkazů SQL.

Například následující nativní dynamický kód SQL nepoužívá vázané proměnné:

Pro každou odlišnou proměnnou my_deptno je vytvořen nový kurzor, který může způsobit spor o prostředky a špatný výkon. Místo toho svázat my_deptno jako proměnnou vazby, jako v následujícím příkladu:

Zde je stejný kurzor znovu použit pro různé hodnoty bind my_deptno, čímž se zlepší výkon a škálovatelnost.

Podpora typů definovaných uživatelem

Nativní dynamický SQL podporuje všechny typy podporované statickým SQL v PL / SQL. Nativní dynamický SQL proto poskytuje podporu pro uživatelem definované typy, jako jsou například uživatelem definované objekty, kolekce a REFs. Balíček DBMS_SQL tyto uživatelem definované typy nepodporuje.

Podpora načítání záznamů

Nativní dynamický SQL i statický SQL podporují načítání do záznamů, ale Balíček DBMS_SQL nikoli. S nativním dynamickým SQL lze řádky vyplývající z dotazu přímo načíst do záznamů PL / SQL.

V následujícím příkladu se řádky z dotazu načtou do emp_rec záznam:

Výhody balíčku DBMS_SQL

Balíček DBMS_SQL poskytuje oproti nativnímu dynamickému SQL následující výhody:

Podpora programů na straně klienta

V současné době je balíček DBMS_SQL podporován v programech na straně klienta, ale nativní dynamický SQL nikoli. Každé volání balíčku DBMS_SQL z programu na straně klienta se promítne do vzdáleného volání procedury (RPC) PL / SQL; k těmto voláním dochází, když potřebujete vázat proměnnou, definovat proměnnou nebo provést příkaz.

Podpora pro DESCRIBE

Postup DESCRIBE_COLUMNS v balíčku DBMS_SQL lze použít k popište sloupce pro kurzor otevřený a analyzovaný pomocí DBMS_SQL. Funkčnost je podobná příkazu DESCRIBE v SQL * Plus. Nativní dynamický SQL nemá DESCRIBE zařízení.

Podpora hromadného dynamického SQL

Hromadný SQL je schopnost zpracovávat více řádků dat v jednom prohlášení DML. Hromadné SQL zvyšuje výkon tím, že snižuje množství přepínání kontextu mezi SQL a hostitelským jazykem. V současné době balíček DBMS_SQL podporuje hromadné dynamické SQL.

Ačkoli neexistuje přímá podpora hromadných operací v nativním dynamickém SQL, můžete simulovat nativní dynamický hromadný SQL příkaz umístěním hromadného příkazu SQL do bloku „BEGIN … END a jeho dynamickým provedením. Toto řešení umožňuje realizovat výhody hromadného SQL v rámci nativního dynamického programu SQL. Například následující nativní dynamický kód SQL zkopíruje sloupec ename jedné tabulky do druhé:

Více aktualizací a mazání řádků s klauzulí RETURNING

Balíček DBMS_SQL podporuje příkazy s klauzulí RETURNING, které aktualizují nebo odstraňují více řádků. Nativní dynamický SQL podporuje klauzuli RETURNING, pokud je vrácen jeden řádek.

Podpora příkazů SQL větších než 32 kB

DBMS_SQL balíček podporuje příkazy SQL větší než 32 kB; nativní dynamický SQL nikoli.

Opětovné použití příkazů SQL

Procedura PARSE v DBMS_SQL analyzuje příkaz SQL jednou. Po počáteční analýze lze příkaz použít několikrát s různými sadami argumentů vazby.

Naproti tomu nativní dynamický SQL připravuje příkaz SQL k provedení při každém použití příkazu. Příprava výpisu obvykle zahrnuje analýzu, optimalizaci a generování plánu. Příprava prohlášení pokaždé, když se použije, způsobí malý výkonnostní trest. Mechanismus sdíleného kurzoru společnosti Oracle však minimalizuje náklady a výkonnostní trest je obvykle triviální ve srovnání s výhodami výkonu nativního dynamického SQL.

Příklady kódu balíčku DBMS_SQL a nativního dynamického kódu SQL

Následující příklady ilustrují rozdíly v kódu nezbytném k dokončení operací s balíčkem DBMS_SQL a nativním dynamickým SQL. Konkrétně jsou uvedeny následující typy příkladů:

• Dotaz
• Operace DML
• Operace vracení DML

Nativní dynamický kód SQL je obecně čitelnější a kompaktnější, což může zlepšit produktivitu vývojářů.

Příklad dotazu

Následující příklad obsahuje příkaz dynamického dotazu s jednou proměnnou vazby (:jobname) a dva vybrané sloupce (ename a sal):

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

Tento příklad dotazy pro zaměstnance s j ob description SALESMAN ve sloupci job tabulky emp. Tabulka 8-2 ukazuje ukázkový kód, který splňuje tento dotaz pomocí balíčku DBMS_SQL a nativního dynamického SQL.

Tabulka 8-2 Dotazování pomocí balíčku DBMS_SQL a nativního dynamického SQL

Příklad DML

následující příklad obsahuje dynamický příkaz INSERT pro tabulku se třemi sloupci:

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

Tento příklad vloží nový řádek pro které jsou hodnoty sloupců v proměnných PL / SQL deptnumber, deptname a location. Tabulka 8-3 ukazuje ukázkový kód, který provádí tuto operaci DML pomocí balíčku DBMS_SQL a nativního dynamického jazyka SQL.

Tabulka 8-3 Provoz DML pomocí balíčku DBMS_SQL a Nativní dynamický SQL

Příklad vracení DML

Následující příklad obsahuje dynamický UPDATE prohlášení, které aktualizuje umístění oddělení po zadání čísla oddělení (deptnumber) a nového umístění (location) a poté se vrátí název oddělení:

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

Tento příklad vloží nový řádek, jehož hodnoty sloupce jsou v proměnných PL / SQL deptnumber, deptname a location. Tabulka 8-4 zobrazuje ukázkový kód, který provádí tuto návratovou operaci DML pomocí balíčku DBMS_SQL a nativního dynamického SQL.

Tabulka 8-4 Návratová operace DML pomocí DBMS_SQL Balíček a nativní dynamický SQL

Jazyky pro vývoj aplikací jiné než PL / SQL

Diskuse v této kapitole zatím proběhla o podpoře PL / SQL pro dynamické SQL.K implementaci programů, které používají dynamický SQL, však můžete použít jiné jazyky pro vývoj aplikací. Mezi tyto vývojové jazyky aplikací patří C / C ++, COBOL a Java.

Pokud používáte C / C ++, můžete s aplikací Oracle Call Interface (OCI) vyvíjet aplikace, které používají dynamický SQL, nebo můžete pomocí předkompilátoru Pro * C / C ++ přidat do svého C dynamické rozšíření SQL kód. Podobně, pokud používáte COBOL, můžete pomocí předkompilátoru Pro * COBOL přidat do svého kódu COBOL dynamická rozšíření SQL. Pokud používáte Javu, můžete pomocí JDBC vyvíjet aplikace, které používají dynamický SQL.

V minulosti byl jediným způsobem, jak používat dynamický SQL v aplikacích PL / SQL, použití DBMS_SQL balíček. Existuje několik omezení používání tohoto balíčku, včetně problémů s výkonem. V důsledku toho vývojáři aplikací mohli k implementaci dynamického SQL použít jednu z výše popsaných alternativ k PL / SQL. Se zavedením nativního dynamického SQL v PL / SQL je však nyní odstraněna řada nevýhod používání PL / SQL pro dynamické SQL.

Pokud máte aplikaci, která používá OCI, Pro * C / C ++ nebo Pro * COBOL pro dynamické provádění SQL, síťové zpáteční trasy potřebné k provádění dynamických operací SQL mohou výkon snížit. Protože se tyto aplikace obvykle nacházejí na klientech, je k dokončení dynamických operací SQL zapotřebí více síťových volání. Pokud máte tento typ aplikace, zvažte přesunutí funkce dynamického SQL na uložené procedury a uložené funkce v PL / SQL, které používají nativní dynamický SQL. Pokud tak učiníte, může se zlepšit výkon vaší aplikace, protože uložené procedury se mohou nacházet na serveru, čímž se eliminuje síťová režie. Poté můžete z aplikace volat uložené procedury a uložené funkce PL / SQL.