Oracle Tutorial SQL dinamico PL/SQL: esecuzione immediata e DBMS_SQL

Cos'è l'SQL dinamico?

Dinamico SQL È una metodologia di programmazione per generare ed eseguire istruzioni in fase di runtime. Viene utilizzata principalmente per scrivere programmi generici e flessibili in cui le istruzioni SQL vengono create ed eseguite in fase di runtime in base alle esigenze, ad esempio quando i nomi delle tabelle, gli elenchi delle colonne o le condizioni WHERE non sono noti fino all'esecuzione del programma.

Modi per scrivere SQL dinamico

PL/SQL offre due modi per scrivere SQL dinamico:

1. NDS: SQL dinamico nativo (le istruzioni EXECUTE IMMEDIATE e OPEN-FOR)
2. DBMS_SQL (un pacchetto fornito)

La regola generale è semplice: se il numero e i tipi di dati delle variabili di input e output sono noti in fase di compilazione, si utilizza SQL dinamico nativo perché è più veloce e richiede meno codice. Quando queste informazioni sono note solo in fase di esecuzione, si utilizza il pacchetto DBMS_SQL.

NDS (SQL dinamico nativo): esecuzione immediata

Il SQL dinamico nativo è il modo più semplice per scrivere SQL dinamico. Utilizza il comando EXECUTE IMMEDIATE per creare ed eseguire il codice SQL in fase di runtime. Per utilizzare questo approccio, è necessario conoscere in anticipo il tipo di dati e il numero di variabili utilizzate in fase di runtime. Offre inoltre prestazioni migliori e una minore complessità rispetto a DBMS_SQL.

Sintassi

EXECUTE IMMEDIATE dynamic_sql_string
[INTO {variable[, variable]... | record}]
[USING [IN | OUT | IN OUT] bind_argument[, ...]]
[RETURNING INTO bind_argument[, ...]];

• stringa sql dinamica: Un'espressione stringa (VARCHAR2 o CHAR, non NVARCHAR2/NCHAR) contenente una singola istruzione SQL o un blocco PL/SQL.
• Clausola INTO: Opzionale. Utilizzato solo quando l'SQL dinamico è una SELECT a riga singola; acquisisce i valori restituiti in variabili o in un record. Ogni colonna selezionata necessita di una variabile compatibile con il tipo.
• Clausola USING: Opzionale. Fornisce variabili di collegamento. La modalità predefinita è IN; OUT e IN OUT vengono utilizzati per ricevere valori.
• Clausola di RITORNO: Utilizzato con le istruzioni DML che includono una clausola RETURNING, per acquisire i valori delle righe interessate negli argomenti di bind.

Esempio 1: In questo esempio, recuperiamo i dati dalla tabella emp per emp_no '1001' utilizzando un'istruzione NDS con una variabile di bind.

DECLARE
   lv_sql       VARCHAR2(500);
   lv_emp_name  VARCHAR2(50);
   ln_emp_no    NUMBER;
   ln_salary    NUMBER;
   ln_manager   NUMBER;
BEGIN
   lv_sql := 'SELECT emp_name, emp_no, salary, manager FROM emp WHERE emp_no = :empno';
   EXECUTE IMMEDIATE lv_sql
      INTO lv_emp_name, ln_emp_no, ln_salary, ln_manager
      USING 1001;
   DBMS_OUTPUT.PUT_LINE('Employee Name: '   || lv_emp_name);
   DBMS_OUTPUT.PUT_LINE('Employee Number: ' || ln_emp_no);
   DBMS_OUTPUT.PUT_LINE('Salary: '          || ln_salary);
   DBMS_OUTPUT.PUT_LINE('Manager ID: '      || ln_manager);
END;
/

Uscita

Employee Name  : XXX
Employee Number: 1001
Salary         : 15000
Manager ID     : 1000

Code Spiegazione:

• Righe 2-6: Dichiarazione delle variabili.
• Linea 8: Definizione della query SQL in fase di esecuzione. La query SQL contiene la variabile di bind ':empno' nella clausola WHERE.
• Righe 9-11: Esecuzione della query SQL con EXECUTE IMMEDIATE. Le variabili della clausola INTO contengono i valori recuperati, mentre la clausola USING fornisce il valore per la variabile di bind :empno.
• Righe 12-15: Visualizzazione dei valori recuperati.

Utilizzo di SQL dinamico per DDL

Il PL/SQL statico non può eseguire direttamente istruzioni DDL come CREATE, ALTER o DROP. EXECUTE IMMEDIATE risolve questo problema costruendo l'istruzione come stringa, il che risulta utile anche quando il nome di un oggetto viene fornito in fase di esecuzione:

DECLARE
   l_table_name VARCHAR2(30) := 'my_table';
   l_sql_stmt   VARCHAR2(200);
BEGIN
   l_sql_stmt := 'CREATE TABLE ' || l_table_name ||
                 ' (id NUMBER, name VARCHAR2(30))';
   EXECUTE IMMEDIATE l_sql_stmt;
END;
/

I nomi degli oggetti (tabella, colonna, schema) non possono essere passati come variabili di bind, quindi devono essere concatenati nella stringa. È sempre necessario convalidare tale input, ad esempio con DBMS_ASSERT.SIMPLE_SQL_NAME, per evitare SQL injection.

DBMS_SQL per SQL dinamico

PL/SQL fornisce il pacchetto DBMS_SQL per lavorare con SQL dinamico quando la struttura dell'istruzione non è nota fino al momento dell'esecuzione. Il processo di creazione ed esecuzione di SQL dinamico prevede i seguenti passaggi:

• APRI CURSORE: L'SQL dinamico viene eseguito come un cursorePer eseguire l'istruzione SQL, dobbiamo prima aprire il cursore.
• ANALIZZA SQL: Analizza la query SQL dinamica. Questo verifica la sintassi e la prepara per l'esecuzione.
• VARIABILI DI ASSOCIAZIONE Valori: Assegna i valori alle variabili di bind, se presenti.
• DEFINIRE LA COLONNA: Definisci ciascuna colonna utilizzando la sua posizione relativa nell'istruzione SELECT.
• ESEGUIRE: Eseguire la query analizzata.
• RECUPERA I VALORI: Recupera i valori eseguiti.
• CHIUDI CURSORE: Una volta recuperati i risultati, chiudere il cursore.

Esempio 1: In questo esempio, recuperiamo i dati dalla tabella emp per emp_no '1001' utilizzando un'istruzione DBMS_SQL. Il blocco EXCEPTION chiude il cursore anche in caso di errore.

DECLARE
   lv_sql            VARCHAR2(500);
   lv_emp_name       VARCHAR2(50);
   ln_emp_no         NUMBER;
   ln_salary         NUMBER;
   ln_manager        NUMBER;
   ln_cursor_id      NUMBER;
   ln_rows_processed NUMBER;
BEGIN
   lv_sql := 'SELECT emp_name, emp_no, salary, manager FROM emp WHERE emp_no = :empno';
   ln_cursor_id := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(ln_cursor_id, lv_sql, DBMS_SQL.NATIVE);
   DBMS_SQL.BIND_VARIABLE(ln_cursor_id, ':empno', 1001);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 1, lv_emp_name, 50);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 2, ln_emp_no);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 3, ln_salary);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 4, ln_manager);
   ln_rows_processed := DBMS_SQL.EXECUTE(ln_cursor_id);
   LOOP
      IF DBMS_SQL.FETCH_ROWS(ln_cursor_id) = 0 THEN
         EXIT;
      ELSE
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 1, lv_emp_name);
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 2, ln_emp_no);
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 3, ln_salary);
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 4, ln_manager);
         DBMS_OUTPUT.PUT_LINE('Employee Name: '   || lv_emp_name);
         DBMS_OUTPUT.PUT_LINE('Employee Number: ' || ln_emp_no);
         DBMS_OUTPUT.PUT_LINE('Salary: '          || ln_salary);
         DBMS_OUTPUT.PUT_LINE('Manager ID: '      || ln_manager);
      END IF;
   END LOOP;
   DBMS_SQL.CLOSE_CURSOR(ln_cursor_id);
EXCEPTION
   WHEN OTHERS THEN
      DBMS_SQL.CLOSE_CURSOR(ln_cursor_id);
END;
/

Uscita

Employee Name  : XXX
Employee Number: 1001
Salary         : 15000
Manager ID     : 1000

Code Spiegazione:

• Righe 1-8: Dichiarazione di variabili.
• Linea 10: Definizione dell'istruzione SQL.
• Linea 11: Apertura del cursore tramite DBMS_SQL.OPEN_CURSOR, che restituisce l'ID del cursore aperto.
• Linea 12: Una volta aperto il cursore, viene analizzato il codice SQL.
• Linea 13: Il valore di binding '1001' viene assegnato al posto di ':empno'.
• Righe 14-17: Definizione delle colonne in base alla loro posizione relativa: (1) emp_name, (2) emp_no, (3) salary, (4) manager.
• Linea 18: Esecuzione della query con DBMS_SQL.EXECUTE, che restituisce il numero di record elaborati.
• Righe 19-32: Recupero dei record in un ciclo. FETCH_ROWS restituisce 0 quando non ci sono più righe, interrompendo così il ciclo.
• Blocco ECCEZIONE: Garantisce la chiusura del cursore, impedendo così che i cursori aperti perdano memoria in caso di errore.

NDS vs DBMS_SQL: quando usare l'uno o l'altro

Entrambi gli approcci eseguono query SQL in fase di runtime, ma sono adatti a situazioni diverse:

• Utilizzare SQL dinamico nativo (EXECUTE IMMEDIATE / OPEN-FOR) quando il numero e i tipi di dati degli input e degli output sono noti in fase di compilazione. È più veloce, più facile da leggere e richiede meno codice.
• Utilizzare DBMS_SQL quando la struttura è sconosciuta fino al momento dell'esecuzione, ad esempio una query il cui numero di colonne selezionate o variabili di binding varia, nota come SQL dinamico di metodo 4, oppure un'istruzione troppo grande per essere contenuta in una singola variabile VARCHAR2 da 32K.

DOMANDE FREQUENTI