8 kódování dynamických příkazů SQL

Dynamic SQL je programovací technika, která umožňuje dynamicky vytvářet příkazy SQL za běhu. Můžete vytvořit obecnější účel, flexibilní aplikace pomocí dynamického SQL, protože celý text příkazu SQL může být Neznámý při kompilaci. Například dynamic SQL umožňuje vytvořit postup, který pracuje na tabulce, jejíž název není znám až do runtime.,

Oracle obsahuje dva způsoby implementace dynamického SQL v aplikaci PL / SQL:

• nativní dynamický SQL, kam umístíte dynamické příkazy SQL přímo do bloků PL/SQL.
• volání procedur v balíčku DBMS_SQL.

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

• “ co je dynamický SQL?“
• “ Proč používat dynamický SQL?,“
• „Dynamické SQL Scénář s Využitím Nativní Dynamické SQL“
• „Výběr Mezi Nativní Dynamické SQL a DBMS_SQL Balíčku“
• „Použití Dynamického SQL v Jiných Jazycích Než v PL/SQL“
• „Pomocí PL/SQL Záznamy v SQL INSERT a UPDATE Prohlášení“

můžete si najít podrobnosti o DBMS_SQL balíček v Oracle9i Dodávané PL/SQL Balíčky a Typy Reference.

co je dynamický SQL?

Dynamic SQL umožňuje psát programy, které odkazují na příkazy SQL, jejichž plný text není znám až do runtime., Před podrobným projednáním 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í od spuštění k provedení. Plné znění statické SQL příkazy jsou známé při kompilaci, které nabízí následující výhody:

• Úspěšné kompilace ověří, že SQL příkazy platné referenční objekty databáze.
• úspěšná kompilace ověřuje, že pro přístup k databázovým objektům existují potřebná oprávnění.,
• výkon statického SQL je obecně lepší než dynamický SQL.

vzhledem k těmto výhodám byste měli používat dynamický SQL pouze tehdy, pokud k dosažení svých cílů nemůžete použít statický SQL, nebo pokud je použití statického SQL těžkopádné ve srovnání s dynamickým SQL. Statický SQL má však omezení, která lze překonat dynamickým SQL. Nemusíte vždy znát celý text příkazů SQL, které musí být provedeny v postupu PL / SQL., Váš program může přijmout vstup uživatele, který definuje příkazy SQL k provedení, nebo váš program může potřebovat dokončit některé zpracování práce určit správný průběh akce. V takových případech byste měli používat dynamický SQL.

například aplikace pro podávání zpráv v prostředí datového skladu nemusí znát přesný název tabulky až do runtime., Tyto tabulky mohou být pojmenovány podle počáteční měsíc a rok, čtvrtletí, například INV_01_1997 INV_04_1997 INV_07_1997 INV_10_1997 INV_01_1998, a tak dále. Dynamic SQL můžete v aplikaci pro podávání zpráv použít k zadání názvu tabulky za běhu.

můžete také chtít spustit komplexní dotaz s uživatelsky volitelným uspořádáním řazení., Namísto kódování dotaz dvakrát, s různými ORDER BY doložky, můžete vytvořit dotaz dynamicky zahrnout zadaný ORDER BY klauzule.

dynamické SQL programy zvládnou změny v definicích dat, aniž by bylo nutné je překompilovat. Díky tomu je dynamický SQL mnohem flexibilnější než statický SQL. Dynamic SQL umožňuje psát opakovaně použitelný kód, protože SQL lze snadno přizpůsobit pro různá prostředí..,

Dynamic SQL také umožňuje provádět příkazy jazyka definice dat (DDL) a další příkazy SQL, které nejsou podporovány v čistě statických programech SQL.

Proč používat dynamický SQL?

Byste měli použít dynamické SQL 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é musí být provedeny pomocí PL/SQL procedury. Tyto příkazy SQL mohou záviset na vstupu uživatele nebo mohou záviset na zpracování práce provedené programem.,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.,

Také můžete použít pouze TABLE klauzule v SELECT prohlášení prostřednictvím dynamického SQL. Například následující kód PL/SQL blok obsahuje SELECT prohlášení, že používá TABLE klauzule a nativní dynamické SQL:

Provedení Dynamické Dotazy

můžete použít dynamické SQL k vytvoření aplikace, které spouštět dynamické dotazy, jejichž plné znění není známo, až do běhu., Mnoho typů aplikací je třeba použít dynamické dotazy, včetně:

• Aplikace, které umožňují uživatelům zadat nebo vybrat dotazu vyhledávání nebo třídění kritéria v běhu
• Aplikace, které umožňují uživatelům zadat nebo vybrat optimalizátor rady při spuštění
• Aplikace, že dotaz na databázi, kde data definice tabulky se neustále mění
• Aplikace, že dotaz na databázi, kde nové tabulky jsou vytvořeny často

Pro příklady, viz „Dotazování Pomocí Dynamického SQL: Příklad“, a podívejte se na příklady dotazu v „Dynamické SQL Scénář s Využitím Nativní Dynamické SQL“.,

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

mnoho typů aplikací musí komunikovat s daty, která jsou generována pravidelně. Například můžete znát definice tabulek v době kompilace, ale ne názvy tabulek.

dynamický SQL může tento problém vyřešit, protože umožňuje počkat až do runtime pro zadání názvů tabulek. Například v ukázkové aplikaci Data warehouse diskutované v “ co je dynamický SQL?“, nové tabulky jsou generovány každé čtvrtletí, a tyto tabulky mají vždy stejnou definici., Můžete nechat uživatele zadat název tabulky v běhu s dynamickým SQL dotaz podobná následující:

Optimalizace Výkonu Dynamicky

můžete použít dynamické SQL vytvořit SQL způsobem, který optimalizuje výkon spojením rady do SQL dynamicky. To vám umožní změnit Rady na základě vašich aktuálních statistik databáze, aniž byste museli rekompilaci.,

například, následující postup používá proměnnou s názvem a_hint umožní uživateli projít nápovědu pro možnost SELECT prohlášení:

V tomto příkladu, uživatel může projít některou z následujících hodnot pro a_hint:

a_hint = '/*+ ALL_ROWS */'a_hint = '/*+ FIRST_ROWS */'a_hint = '/*+ CHOOSE */'nebo jakékoliv jiné platné náznak možností.

Provedení Dynamické PL/SQL Bloky

můžete použít EXECUTE IMMEDIATE prohlášení k provedení anonymní PL/SQL bloky. Můžete přidat flexibilitu vytvořením obsahu bloku za běhu.

například předpokládejme, že ythroughthroughou chcete napsat aplikaci, která má číslo události a odešle psovoda pro událost. Název psovoda je ve tvaru EVENT_HANDLER_event_num, kde event_num je číslo události., Jedním z přístupů je implementovat dispečera jako příkaz switch, kde kód zpracovává každou událost statickým voláním příslušnému psovodu. 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 psovod pro novou událost.,

Pomocí nativní dynamické SQL, můžete napsat menší, pružnější event dispatcher podobná následující:

Provedení Dynamické Operace Pomocí Invoker-Práva

pomocí invoker-funkce práva s dynamickým SQL, můžete vytvořit aplikace, které problém dynamické SQL příkazy v rámci výsad a schéma vyvolávač. Tyto dvě vlastnosti, vyvolávač-práva a dynamické SQL, umožní vytvořit opakovaně použitelné aplikace dílčích součástí, které lze provozovat na, a přístup k vyvolávač dat a modulů.,

Dynamické SQL Scénář s Využitím Nativní Dynamické SQL

Tento scénář ukazuje, jak provádět následující operace pomocí nativní dynamické SQL:

• Spustit DDL a DML operace
• Spustit jeden řádek a více řádků dotazy

databáze v tomto scénáři je společnost v oblasti lidských zdrojů databáze (s názvem hr) s následující data modelu:

master tabulku s názvem offices obsahuje seznam všech společností místech., offices tabulka má následující definici:

Column Name Null? Type LOCATION NOT_NULL VARCHAR2(200)

Více emp_umístění tabulky obsahují informace o zaměstnance, kde umístění je název města, kde se kancelář nachází. Například tabulka s názvem emp_houston obsahuje informace o zaměstnancích společnosti Houston kanceláři, zatímco tabulku s názvem emp_boston obsahuje informace o zaměstnancích společnosti Boston office.,

Každý emp_umístění tabulky má následující definici:

následující oddíly popisují různé nativní dynamické SQL operace, které lze provádět na data v hr databáze.

Vzorek DML Operace Pomocí Nativní Dynamické SQL

následující nativní dynamické SQL postup dává zvýšení pro všechny zaměstnance s konkrétní pracovní zařazení:

Sample DDL Operace Pomocí Nativní Dynamické SQL

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

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

Ukázka Jednoho Řádku Dotazu Pomocí Nativní Dynamické SQL

EXECUTE IMMEDIATE prohlášení může provádět dynamické single-row dotazy. Můžete zadat bind proměnné v USING klauzule a přinést výsledný řádek do cíle uvedené v INTO klauzule prohlášení.,

následující funkce načte počet zaměstnanců na určitém místě plnění zadané práce:

Vzorek Více Řádek Dotazu Pomocí Nativní Dynamické SQL

OPEN-FOR FETCH CLOSE prohlášení může provádět dynamické několik-řadě dotazů., Například, následující postup obsahuje seznam všech zaměstnanců s konkrétní práce v zadaném umístění:

Výběr Mezi Nativní Dynamické SQL a DBMS_SQL Balení

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

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.

balíčekDBMS_SQL je knihovna PL/SQL, která nabízí API pro dynamické provádění příkazů SQL. Balíček DBMS_SQL má postupy pro otevření kurzoru, analýzu kurzoru, vázání dodávek atd. Programy, které používají balíčekDBMS_SQL, volají do tohoto balíčku a provádějí dynamické operace SQL.

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

Výhody Nativní Dynamické SQL

Nativní dynamické SQL poskytuje následující výhody DBMS_SQL balení:

Nativní Dynamické SQL je Snadné Použití

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

s balíčkemDBMS_SQL musíte volat mnoho postupů a funkcí v přísném pořadí, takže i jednoduché operace vyžadují hodně kódu. Této složitosti se můžete vyhnout použitím nativního dynamického SQL.

Tabulka 8-1 znázorňuje rozdíl v množství kódu potřebné k provedení stejné operace pomocí DBMS_SQL balíček a nativní dynamické SQL.,

Tabulka 8-1 Kód Srovnání Balíček DBMS_SQL a Nativní Dynamické SQL

Nativní Dynamické SQL je Rychlejší než DBMS_SQL

Nativní dynamické SQL v PL/SQL se provádí srovnatelně s výkonem statické SQL, protože PL/SQL tlumočník má vestavěnou podporu pro to. Programy, které používají nativní dynamický SQL, jsou mnohem rychlejší než programy, které používají balíček DBMS_SQL. Nativní dynamické příkazy SQL obvykle provádějí 1,5 až 3krát lépe než ekvivalentní DBMS_SQL volání. (Vaše zvýšení výkonu se může lišit v závislosti na vaší aplikaci.,)

Nativní dynamické SQL svazky prohlášení příprava, vázání a provedení kroků do jediné operace, která minimalizuje kopírování dat a volání procedury režijní náklady a zlepšuje výkon.

balíčekDBMS_SQL je založen na procedurálním API a vyvolává vysoké volání a kopírování dat. Pokaždé, když vázáte proměnnou, balíček DBMS_SQL zkopíruje proměnnou PL/SQL bind do svého prostoru pro použití během provádění., Pokaždé, když provedete načtení dat je kopírovat do prostoru spravovaného DBMS_SQL balíček a pak načtená data zkopírována, jeden sloupec v době, do příslušné PL/SQL, proměnné, což vede k závažným nad hlavou.

Výkon Tip: Pomocí Bind Proměnné

Při použití buď nativní dynamické SQL nebo DBMS_SQL balíček, můžete zlepšit výkon pomocí bind proměnné, protože bind proměnných povolit Oracle sdílet jeden kurzor pro více SQL příkazů.,

například, následující nativní dynamické SQL kódu nepoužívá vázat proměnné:

Pro každou jednotlivou my_deptno variabilní, nový kurzor je vytvořen, což způsobuje konflikty prostředků a špatný výkon. Místo toho, bind my_deptno jako bind proměnné:

Tady, stejné je kurzor znovu použity pro různé hodnoty bind my_deptno, zlepšení výkonu a scalabilty.,

Nativní Dynamické SQL Podporuje Uživatelem Definované Typy

Nativní dynamické SQL podporuje všechny typy podporovaných statické SQL v PL/SQL, včetně uživatelem definovaných typů, jako jsou uživatelem definované objekty, sbírky, a REFs. Balíček DBMS_SQL tyto typy definované uživatelem nepodporuje.

Nativní Dynamické SQL Podporuje Načítání Do Záznamů

Nativní dynamické SQL a statické SQL podporují načítání do záznamů, ale na DBMS_SQL součástí balení není. 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 řádky z dotazu, jsou načteny do emp_rec záznam:

Výhody Balíčku DBMS_SQL

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

DBMS_SQL je Podporován v Client-Side Programy

DBMS_SQL balíček je podporován v client-side programy, ale nativní dynamické SQL není., Každé volání DBMS_SQL balíček od client-side program, se promítá do PL/SQL remote procedure call (RPC); tyto hovory nastat, když potřebujete svázat proměnné, definovat proměnné, nebo provést prohlášení.

DBMS_SQL Podporuje POPSAT

DESCRIBE_COLUMNS postup DBMS_SQL balíček může být použit k popisu sloupce pro kurzor otevřen a analyzován pomocí DBMS_SQL. Tato funkce je podobná příkazu DESCRIBE v SQL * Plus., Nativní dynamický SQL nemáDESCRIBE zařízení.

DBMS_SQL Podporuje Více Řádek Aktualizace a Odstraní se VRACÍ Bodě

DBMS_SQL balíček podporuje prohlášení s RETURNING klauzule, že aktualizovat nebo odstranit více řádků. Nativní dynamický SQL podporuje klauzuli RETURNING, pokud je vrácen jeden řádek.,

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., Po počáteční analýze můžete příkaz použít vícekrát s různými sadami argumentů bind.

nativní dynamický SQL připravuje SQL příkaz při každém použití příkazu, který obvykle zahrnuje analýzu, optimalizaci a generování plánu. Ačkoli extra připravit operace vynakládají malý výkon trest, zpomalení je obvykle převažují výkonnostní výhody nativní dynamické SQL.,

Příklady Balíček DBMS_SQL Kód a Nativní Dynamické SQL Kódu

následující příklady ilustrují rozdíly v kódu nutné k dokončení operace s DBMS_SQL balíček a nativní dynamické SQL. Konkrétně se jedná o následující typy příkladů jsou prezentovány:

• dotaz
• DML operace
• DML návratu provoz

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

Dotazování Pomocí Dynamického SQL: Příklad

následující příklad obsahuje dynamické dotazu s jeden bind proměnné (:jobname) a vyberte dva sloupce (ename sal):

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

Tento příklad dotazy pro zaměstnance s popisem práce SALESMAN job sloupce emp tabulka. Tabulka 8-2 zobrazuje ukázkový kód, který tento dotaz provede pomocí balíčku DBMS_SQL a nativního dynamického SQL.,

Tabulka 8-2 Dotazování za Použití Balíčku DBMS_SQL a Nativní Dynamické SQL

Provedení DML Pomocí Dynamického SQL: Příklad

následující příklad obsahuje dynamické INSERT prohlášení pro tabulku se třemi sloupci:

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

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

Tabulka 8-3 DML Operace Použití Balíčku DBMS_SQL a Nativní Dynamické SQL

Provedení DML se VRACÍ Klauzule Pomocí Dynamického SQL: Příklad

následující příklad používá dynamické UPDATE příkaz aktualizovat umístění oddělení, pak vrátí název oddělení:

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

Tabulka 8-4 zobrazuje ukázkový kód, který dosáhne této operace s použitím jak DBMS_SQL balíček a nativní dynamické SQL.,

Tabulka 8-4 DML Návratu Ovládání Pomocí Balíčku DBMS_SQL a Nativní Dynamické SQL

Použití Dynamického SQL v Jiných Jazycích Než v PL/SQL

i když tato kapitola popisuje, PL/SQL, podpora pro dynamické SQL, můžete volat dynamického SQL z jiných jazyků:

• Pokud používáte C/C++, můžete volat dynamické SQL s Oracle Call Interface (OCI), nebo můžete použít Pro*C/C++ precompiler přidat dynamické SQL rozšíření váš kód v C.
• Pokud používáte COBOL, můžete použít pro * COBOL precompiler přidat dynamické rozšíření SQL do kódu COBOL.,
• Pokud používáte Java, můžete vyvíjet aplikace, které používají dynamický SQL s JDBC.

Pokud máte aplikaci, která používá OCI, Pro*C/C++, nebo Pro*COBOL, aby provádět dynamické SQL, měli byste zvážit přechod na nativní dynamické SQL uvnitř PL/SQL, uložené procedury a funkce. Síťové zpáteční cesty potřebné k provádění dynamických operací SQL z aplikací na straně klienta mohou poškodit výkon. Uložené postupy mohou být umístěny na serveru, což eliminuje režii sítě., Můžete volat uložené procedury PL/SQL a uložené funkce z aplikace OCI, Pro*C/C++ nebo pro*COBOL.,br>Viz Také:

Pro informace o volání Oracle uložené procedury a uložené funkce z různých jazyků, viz:

• Oracle Call Interface Programmer ‚s Guide
• Pro*C/C++ Precompiler Programmer‘ s Guide
• Pro*COBOL Precompiler Programmer ‚s Guide
• Oracle9i Java Uložené Procedury Developer‘ s Guide

Použití PL/SQL Záznamy v SQL INSERT a UPDATE Prohlášení

I když můžete vyjmenovat jednotlivé oblasti PL/SQL záznam při vložení nebo aktualizaci řádků v tabulce, výsledný kód není nijak zvlášť čitelné nebo udržovatelný., Místo toho můžete použít PL/SQL záznamy přímo v těchto prohlášeních. Nejvhodnější technikou je deklarovat záznam pomocí atributu % ROWTYPE, takže má přesně stejná pole jako tabulka SQL.

ačkoli tato technika pomáhá integrovat proměnné PL / SQL a typy těsněji s příkazy SQL DML, nemůžete použít záznamy PL / SQL jako vázat proměnné v dynamických příkazech SQL.