8 Coding Dynamic SQL Statements (Italiano)

Dynamic SQL è una tecnica di programmazione che consente di creare statements SQL dinamicamente in fase di runtime. È possibile creare applicazioni più generiche e flessibili utilizzando dynamic SQL perché il testo completo di un’istruzione SQL potrebbe essere sconosciuto alla compilazione. Ad esempio, Dynamic SQL consente di creare una procedura che opera su una tabella il cui nome non è noto fino al runtime.,

Oracle include due modi per implementare SQL dinamico in un’applicazione PL/SQL:

• SQL dinamico nativo, in cui si inseriscono istruzioni SQL dinamiche direttamente nei blocchi PL/SQL.
• Procedure di chiamata nel pacchettoDBMS_SQL.

Questo capitolo copre i seguenti argomenti:

• “Che cos’è Dynamic SQL?”
• ” Perché usare SQL dinamico?,”
• “Un SQL Dinamico Scenario Utilizzando Nativo di SQL Dinamico”
• “Scelta Tra Nativo di SQL Dinamico e il DBMS_SQL Pacchetto”
• “Utilizzo di SQL Dinamico in Altre Lingue Oltre PL/SQL”
• “Utilizzo di PL/SQL Record in SQL INSERT e UPDATE”

È possibile trovare i dettagli sui DBMS_SQL pacchetto in Oracle9i Dotazione di PL/SQL i Pacchetti e Tipi di Riferimento.

Che cos’è SQL dinamico?

Dynamic SQL consente di scrivere programmi che fanno riferimento a istruzioni SQL il cui testo completo non è noto fino al runtime., Prima di discutere SQL dinamico in dettaglio, una chiara definizione di SQL statico può fornire un buon punto di partenza per comprendere SQL dinamico. Le istruzioni SQL statiche non cambiano da esecuzione a esecuzione. Il testo completo delle istruzioni SQL statiche è noto alla compilazione, il che fornisce i seguenti vantaggi:

• La compilazione riuscita verifica che le istruzioni SQL facciano riferimento a oggetti di database validi.
• La compilazione corretta verifica che siano presenti i privilegi necessari per accedere agli oggetti del database.,
• Le prestazioni di SQL statico sono generalmente migliori di SQL dinamico.

A causa di questi vantaggi, è necessario utilizzare SQL dinamico solo se non è possibile utilizzare SQL statico per raggiungere i propri obiettivi o se l’utilizzo di SQL statico è ingombrante rispetto a SQL dinamico. Tuttavia, SQL statico ha limitazioni che possono essere superate con SQL dinamico. È possibile che non si conosca sempre il testo completo delle istruzioni SQL che devono essere eseguite in una procedura PL/SQL., Il programma potrebbe accettare l’input dell’utente che definisce le istruzioni SQL da eseguire o potrebbe essere necessario completare alcuni lavori di elaborazione per determinare la linea di condotta corretta. In questi casi, è necessario utilizzare SQL dinamico.

Ad esempio, un’applicazione di reporting in un ambiente di data warehouse potrebbe non conoscere il nome esatto della tabella fino al runtime., Queste tabelle possono essere denominate in base al mese iniziale e all’anno del trimestre, ad esempio INV_01_1997, INV_04_1997, INV_07_1997, INV_10_1997, INV_01_1998, e così via. È possibile utilizzare SQL dinamico nell’applicazione di reporting per specificare il nome della tabella in fase di runtime.

Si potrebbe anche voler eseguire una query complessa con un ordinamento selezionabile dall’utente., Invece di codificare la query due volte, con diverse clausole ORDER BY, è possibile costruire la query dinamicamente per includere una clausola ORDER BY specificata.

I programmi SQL dinamici possono gestire le modifiche nelle definizioni dei dati, senza la necessità di ricompilare. Questo rende SQL dinamico molto più flessibile di SQL statico. Dynamic SQL consente di scrivere codice riutilizzabile perché SQL può essere facilmente adattato per ambienti diversi..,

Dynamic SQL consente inoltre di eseguire istruzioni DDL (Data Definition Language) e altre istruzioni SQL non supportate in programmi SQL puramente statici.

Perché usare SQL dinamico?

È necessario utilizzare SQL dinamico nei casi in cui SQL statico non supporta l’operazione che si desidera eseguire o nei casi in cui non si conoscono le istruzioni SQL esatte che devono essere eseguite da una procedura PL/SQL. Queste istruzioni SQL possono dipendere dall’input dell’utente o possono dipendere dal lavoro di elaborazione svolto dal programma.,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.,

Inoltre, è possibile utilizzare solo la clausolaTABLE nell’istruzioneSELECT tramite SQL dinamico. Ad esempio, il seguente blocco PL/SQL contiene un’istruzione SELECT che utilizza la clausola TABLE e SQL dinamico nativo:

Esecuzione di query dinamiche

È possibile utilizzare SQL dinamico per creare applicazioni che eseguono query dinamiche, il cui testo completo non è noto fino al runtime., Molti tipi di applicazioni che hanno bisogno di utilizzare le query dinamiche, tra cui:

• Applicazioni che consentono agli utenti di immettere o selezionare query di ricerca o criteri di ordinamento in fase di runtime
• Applicazioni che consentono agli utenti di immettere o selezionare optimizer suggerimenti in fase di esecuzione
• Applicazioni che la query di un database in cui i dati definizioni di tabelle che sono in costante evoluzione
• Applicazioni che la query di un database in cui le nuove tabelle vengono create spesso

Per esempio, vedere “esecuzione di Query Utilizzando SQL Dinamico: ad Esempio”, e vedere gli esempi di query in “Un SQL Dinamico Scenario Utilizzando Nativo di SQL Dinamico”.,

Riferimento agli oggetti del database che non esistono alla compilazione

Molti tipi di applicazioni devono interagire con i dati generati periodicamente. Ad esempio, è possibile conoscere le definizioni delle tabelle in fase di compilazione, ma non i nomi delle tabelle.

Dynamic SQL può risolvere questo problema, perché consente di attendere fino al runtime per specificare i nomi delle tabelle. Ad esempio, nell’applicazione di esempio data warehouse discussa in ” Cos’è SQL dinamico?”, nuove tabelle vengono generate ogni trimestre, e queste tabelle hanno sempre la stessa definizione., È possibile consentire a un utente di specificare il nome della tabella in fase di runtime con una query SQL dinamica simile alla seguente:

Ottimizzazione dell’esecuzione dinamicamente

È possibile utilizzare SQL dinamico per creare un’istruzione SQL in modo da ottimizzare l’esecuzione concatenando dinamicamente i suggerimenti in un’istruzione SQL. Ciò consente di modificare i suggerimenti in base alle statistiche del database corrente, senza richiedere la ricompilazione.,

Per esempio, la seguente procedura utilizza una variabile chiamata a_hint per consentire agli utenti di passare da un opzione di suggerimento per il SELECT dichiarazione:

In questo esempio, l’utente può passare qualsiasi dei seguenti valori a_hint:

a_hint = '/*+ ALL_ROWS */'a_hint = '/*+ FIRST_ROWS */'a_hint = '/*+ CHOOSE */'o qualsiasi altra valida opzione di suggerimento.

Esecuzione di blocchi PL/SQL dinamici

È possibile utilizzare l’istruzioneEXECUTE IMMEDIATE per eseguire blocchi PL / SQL anonimi. È possibile aggiungere flessibilità costruendo il contenuto del blocco in fase di runtime.

Ad esempio, supponiamo ythroughthroughtou desidera scrivere un’applicazione che prende un numero di evento e invia a un gestore per l’evento. Il nome del gestore è nella formaEVENT_HANDLER_event_num, dove event_num è il numero dell’evento., Un approccio consiste nell’implementare il dispatcher come istruzione switch, in cui il codice gestisce ogni evento effettuando una chiamata statica al suo gestore appropriato. Questo codice non è molto estensibile perché il codice dispatcher deve essere aggiornato ogni volta che viene aggiunto un gestore per un nuovo evento.,

Utilizzando SQL dinamico nativo, è possibile scrivere un dispatcher di eventi più piccolo e più flessibile simile al seguente:

Esecuzione di operazioni dinamiche utilizzando Invoker-Rights

Utilizzando la funzione invoker-rights con dynamic SQL, è possibile creare applicazioni che emettono istruzioni SQL dinamiche sotto i privilegi e lo schema dell’invoker. Queste due funzionalità, invoker-rights e dynamic SQL, consentono di creare sottocomponenti di applicazioni riutilizzabili che possono operare e accedere ai dati e ai moduli dell’invoker.,

Un SQL Dinamico Scenario Utilizzando Nativo di SQL Dinamico

Questo scenario viene illustrato come eseguire le seguenti operazioni utilizzando nativo di SQL dinamico:

• Esegui DDL e DML operazioni
• Eseguire singola riga e riga più query

Il database in questo scenario è una società di risorse umane database, denominato hr) con il seguente modello di dati:

Una tabella master denominato offices contiene l’elenco di tutti i siti della società., La tabellaoffices ha la seguente definizione:

Column Name Null? Type LOCATION NOT_NULL VARCHAR2(200)

Multipleemp_le tabelle location contengono le informazioni sui dipendenti, dove location è il nome della città in cui si trova l’ufficio. Ad esempio, una tabella denominata emp_houston contiene informazioni sui dipendenti per l’ufficio di Houston della società, mentre una tabella denominata emp_boston contiene informazioni sui dipendenti per l’ufficio di Boston della società.,

Ogniemp_tabella di posizione ha la seguente definizione:

Le sezioni seguenti descrivono varie operazioni SQL dinamiche native che possono essere eseguite sui dati nel hr database.

Esempio di operazione DML Utilizzando SQL dinamico nativo

La seguente procedura SQL dinamico nativo fornisce un aumento a tutti i dipendenti con un particolare titolo di lavoro:

Esempio di operazione DDL Utilizzando SQL dinamico nativo

L’istruzioneEXECUTE IMMEDIATE può eseguire operazioni DDL., Ad esempio, la seguente procedura aggiunge una posizione di office:

La seguente procedura elimina una posizione di office:

Esempio di query a riga singola Utilizzando SQL dinamico nativo

L’istruzione EXECUTE IMMEDIATE può eseguire query dinamiche a riga singola. È possibile specificare le variabili di associazione nella clausola USING e recuperare la riga risultante nella destinazione specificata nella clausola INTO dell’istruzione.,

La seguente funzione recupera il numero di dipendenti in una posizione particolare l’esecuzione di un determinato lavoro:

Esempio di Più Righe Query Utilizzando Nativo di SQL Dinamico

OPEN-FOR FETCH e CLOSE dichiarazioni possono eseguire dinamica più righe query., Ad esempio, la seguente procedura elenca tutti i dipendenti con un particolare lavoro in una posizione specificata:

Scegliendo tra SQL dinamico nativo e il pacchetto DBMS_SQL

Oracle fornisce due metodi per l’utilizzo di SQL dinamico all’interno di PL/SQL: SQL dinamico nativo e il pacchetto DBMS_SQL. Native dynamic SQL consente di inserire istruzioni SQL dinamiche direttamente nel codice PL / SQL. Queste istruzioni dinamiche includono istruzioni DML (incluse le query), blocchi anonimi PL/SQL, istruzioni DDL, istruzioni di controllo delle transazioni e istruzioni di controllo della sessione.,

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.

Il pacchetto DBMS_SQL è una libreria PL / SQL che offre un’API per eseguire istruzioni SQL in modo dinamico. Il pacchettoDBMS_SQL ha procedure per aprire un cursore, analizzare un cursore, fornire binding e così via. I programmi che utilizzano il pacchettoDBMS_SQL effettuano chiamate a questo pacchetto per eseguire operazioni SQL dinamiche.

Le sezioni seguenti forniscono informazioni dettagliate sui vantaggi di entrambi i metodi.,

Vantaggi di Native Dynamic SQL

Native dynamic SQL offre i seguenti vantaggi rispetto al pacchetto DBMS_SQL:

Native Dynamic SQL è facile da usare

Poiché native dynamic SQL è integrato con SQL, è possibile utilizzarlo nello stesso modo in cui si utilizza static SQL all’interno del codice PL / SQL. Il codice SQL dinamico nativo è in genere più compatto e leggibile del codice equivalente che utilizza il pacchettoDBMS_SQL.,

Con il pacchettoDBMS_SQL è necessario chiamare molte procedure e funzioni in una sequenza rigorosa, rendendo anche semplici operazioni richiedono molto codice. È possibile evitare questa complessità utilizzando invece SQL dinamico nativo.

La tabella 8-1 illustra la differenza nella quantità di codice necessaria per eseguire la stessa operazione utilizzando il pacchettoDBMS_SQL e l’SQL dinamico nativo.,

Tabella 8-1 Confronto di codice del pacchetto DBMS_SQL e SQL dinamico nativo

SQL dinamico nativo è più veloce di DBMS_SQL

SQL dinamico nativo in PL/SQL esegue in modo comparabile alle prestazioni di SQL statico, perché l’interprete PL / SQL ha il supporto integrato per esso. I programmi che utilizzano SQL dinamico nativo sono molto più veloci dei programmi che utilizzano il pacchetto DBMS_SQL. In genere, le istruzioni SQL dinamiche native eseguono da 1,5 a 3 volte meglio delle chiamate DBMS_SQL equivalenti. (I tuoi guadagni in termini di prestazioni possono variare a seconda dell’applicazione.,)

Native dynamic SQL raggruppa le fasi di preparazione, binding ed esecuzione delle istruzioni in una singola operazione, riducendo al minimo il sovraccarico delle chiamate di copia e procedura dei dati e migliorando le prestazioni.

Il pacchettoDBMS_SQL è basato su un’API procedurale e comporta un elevato overhead di chiamata di procedura e copia dei dati. Ogni volta che si associa una variabile, il pacchetto DBMS_SQL copia la variabile bind PL/SQL nel suo spazio per l’utilizzo durante l’esecuzione., Ogni volta che si esegue un recupero, i dati vengono copiati nello spazio gestito dal pacchetto DBMS_SQL e quindi i dati recuperati vengono copiati, una colonna alla volta, nelle variabili PL/SQL appropriate, con conseguente sovraccarico sostanziale.

Suggerimento per le prestazioni: Utilizzo delle variabili Bind

Quando si utilizza SQL dinamico nativo o il pacchettoDBMS_SQL, è possibile migliorare le prestazioni utilizzando le variabili bind, poiché le variabili bind consentono a Oracle di condividere un singolo cursore per più istruzioni SQL.,

Ad esempio, il seguente codice SQL dinamico nativo non utilizza variabili di bind:

Per ogni variabile distinta my_deptno, viene creato un nuovo cursore, causando contesa delle risorse e prestazioni scadenti. Invece, bindmy_deptno come variabile bind:

Qui, lo stesso cursore viene riutilizzato per valori diversi del bindmy_deptno, migliorando le prestazioni e la scalabilità.,

Native Dynamic SQL supporta i tipi definiti dall’utente

Native dynamic SQL supporta tutti i tipi supportati da static SQL in PL/SQL, inclusi i tipi definiti dall’utente come oggetti definiti dall’utente, raccolte eREFs. Il pacchettoDBMS_SQL non supporta questi tipi definiti dall’utente.

Native Dynamic SQL Supporta il recupero nei record

Native dynamic SQL e static SQL supportano entrambi il recupero nei record, ma il pacchetto DBMS_SQL non lo fa. Con SQL dinamico nativo, le righe risultanti da una query possono essere recuperate direttamente nei record PL / SQL.,

Nell’esempio seguente, le righe da una query vengono scaricate nel emp_rec record

Vantaggi della DBMS_SQL Pacchetto

DBMS_SQL pacchetto fornisce i seguenti vantaggi rispetto nativo di SQL dinamico:

DBMS_SQL è Supportato in Programmi Lato Client

DBMS_SQL pacchetto è supportato in programmi lato client, ma nativo di SQL dinamico non è., Ogni chiamata al pacchettoDBMS_SQL dal programma lato client si traduce in una chiamata di procedura remota PL/SQL (RPC); queste chiamate si verificano quando è necessario associare una variabile, definire una variabile o eseguire un’istruzione.

DBMS_SQL Supporta DESCRIBE

IlDESCRIBE_COLUMNSprocedura nelDBMS_SQLpacchetto può essere utilizzato per descrivere le colonne per un cursore aperto e analizzato attraversoDBMS_SQL. Questa funzione è simile al comandoDESCRIBE in SQL * Plus., L’SQL dinamico nativo non ha una funzioneDESCRIBE.

DBMS_SQL supporta più aggiornamenti di riga ed elimina con una clausola di RITORNO

Il pacchettoDBMS_SQL supporta istruzioni con una clausolaRETURNING che aggiorna o elimina più righe. L’SQL dinamico nativo supporta solo una clausolaRETURNING se viene restituita una singola riga.,

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., Dopo l’analisi iniziale, è possibile utilizzare l’istruzione più volte con diversi set di argomenti di bind.

Native dynamic SQL prepara un’istruzione SQL ogni volta che viene utilizzata l’istruzione, che in genere comporta l’analisi, l’ottimizzazione e la generazione del piano. Anche se le operazioni di preparazione extra comportano una piccola penalizzazione delle prestazioni, il rallentamento è in genere superato dai vantaggi in termini di prestazioni di SQL dinamico nativo.,

Esempi di codice del pacchetto DBMS_SQL e codice SQL dinamico nativo

I seguenti esempi illustrano le differenze nel codice necessario per completare le operazioni con ilDBMS_SQL pacchetto e SQL dinamico nativo. In particolare, vengono presentati i seguenti tipi di esempi:

• Una query
• Un’operazione DML
• Un’operazione di ritorno DML

In generale, il codice SQL dinamico nativo è più leggibile e compatto, il che può migliorare la produttività degli sviluppatori.,

la Query Tramite SQL Dinamica: Esempio

L’esempio seguente include una istruzione di query dinamiche con un bind variabile (:jobname) e due colonne di selezione (ename e sal):

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

in Questo esempio di query per i dipendenti con la descrizione del lavoro SALESMAN nel job colonna emp tabella. La tabella 8-2 mostra il codice di esempio che esegue questa query utilizzando il pacchettoDBMS_SQL e SQL dinamico nativo.,

Tabella 8-2 Query Tramite l’DBMS_SQL Pacchetto e Nativo di SQL Dinamico

Esecuzione di DML Utilizzando SQL Dinamica: Esempio

L’esempio seguente include una dinamica INSERT dichiarazione di una tabella con tre colonne:

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

in Questo esempio viene inserita una nuova riga per i quali i valori di una colonna in PL/SQL variabili deptnumber deptname e location. La tabella 8-3 mostra il codice di esempio che esegue questa operazione DML utilizzando il pacchettoDBMS_SQL e l’SQL dinamico nativo.,

Tabella 8-3 Operazione DML Utilizzando il DBMS_SQL Pacchetto e Nativo di SQL Dinamico

Esecuzione di DML con il RITORNO di Clausola di Utilizzo di SQL Dinamico: Esempio

L’esempio seguente utilizza una dinamica UPDATE istruzione per aggiornare la posizione di un reparto, quindi restituisce il nome del dipartimento:

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

Tabella 8-4 mostra un esempio di codice che esegue questa operazione, utilizzando sia il DBMS_SQL pacchetto e nativo di SQL dinamico.,

Tabella 8-4 DML Ritorno Operazione Utilizzando il DBMS_SQL Pacchetto e Nativo di SQL Dinamico

Utilizzo di SQL Dinamico in Altre Lingue Oltre PL/SQL

anche se in questo capitolo viene illustrato PL/SQL il supporto per SQL dinamico, è possibile chiamare SQL dinamico da altre lingue:

• Se si utilizza C/C++, è possibile chiamare dynamic SQL con Oracle Call Interface (OCI), oppure è possibile utilizzare il Pro*C/C++ precompilatore per aggiungere SQL dinamico estensioni per il tuo C codice.
• Se si utilizza COBOL, è possibile utilizzare il precompilatore Pro*COBOL per aggiungere estensioni SQL dinamiche al codice COBOL.,
• Se si utilizza Java, è possibile sviluppare applicazioni che utilizzano SQL dinamico con JDBC.

Se si dispone di un’applicazione che utilizza SQL, Pro*C/C++ o Pro*COBOL per eseguire SQL dinamico, è consigliabile passare a SQL dinamico nativo all’interno di stored procedure e funzioni PL/SQL. I round-trip di rete necessari per eseguire operazioni SQL dinamiche da applicazioni lato client potrebbero compromettere le prestazioni. Le stored procedure possono risiedere sul server, eliminando il sovraccarico di rete., È possibile chiamare le stored procedure PL/SQL e le funzioni memorizzate dall’applicazione CO, Pro*C/C++ o Pro*COBOL.,br>Vedi Anche:

Per informazioni sulla chiamata di stored procedure di Oracle e le funzioni memorizzate da varie lingue, fare riferimento a:

• Oracle Call Interface Manuale del Programmatore
• Pro*C/C++ Precompilatore Programmatore di Guida
• Pro*COBOL Precompilatore Programmatore di Guida
• Oracle9i Java Stored Procedure Guida per Sviluppatori

Utilizzo di PL/SQL Record in SQL INSERT e UPDATE

Anche se è possibile enumerare ogni campo di PL/SQL record durante l’inserimento o l’aggiornamento delle righe in una tabella, il codice risultante non è particolarmente leggibile e mantenibile., Invece, è possibile utilizzare i record PL/SQL direttamente in queste istruzioni. La tecnica più conveniente consiste nel dichiarare il record utilizzando un attributo % ROWTYPE, in modo che abbia esattamente gli stessi campi della tabella SQL.

Sebbene questa tecnica aiuti a integrare le variabili e i tipi PL/SQL più strettamente con le istruzioni SQL DML, non è possibile utilizzare i record PL / SQL come variabili bind nelle istruzioni SQL dinamiche.