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

⚡ Riepilogo intelligente

SQL dinamico in Oracle PL/SQL crea ed esegue istruzioni in fase di runtime, adattando le query ai requisiti in continua evoluzione attraverso due approcci: SQL dinamico nativo con EXECUTE IMMEDIATE e OPEN-FOR, e il pacchetto flessibile DBMS_SQL per i casi complessi.

⚙️ SQL in fase di esecuzione: Il SQL dinamico genera ed esegue istruzioni quando i nomi di tabelle o colonne non sono noti in anticipo.
⚡ SQL dinamico nativo: EXECUTE IMMEDIATE crea ed esegue query SQL rapidamente con il minimo codice.
🔁 APERTO A: Gestisce query dinamiche multi-riga che EXECUTE IMMEDIATE non è in grado di eseguire da solo.
🧩 DBMS_SQL: Adatto a istruzioni il cui numero di colonne o tipi sono sconosciuti fino al momento dell'esecuzione.
🔐 Associa le variabili: La clausola USING passa i valori in modo posizionale e blocca le iniezioni SQL.
🤖 Assistenza AI: Gli strumenti di intelligenza artificiale generano query SQL dinamiche e segnalano i rischi di injection durante la fase di revisione.

Scopri di più

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:

NDS: SQL dinamico nativo (le istruzioni EXECUTE IMMEDIATE e OPEN-FOR)
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

Le variabili di bind passano l'input dell'utente come dati, mai come codice eseguibile. La clausola USING fornisce i valori in modo posizionale, quindi un testo dannoso non può alterare la struttura dell'istruzione. È sempre consigliabile utilizzare il bind per l'input non attendibile anziché concatenarlo.

No. Oracle Associa solo i valori dei dati, non i nomi degli oggetti. Concatena gli identificatori nella stringa SQL e convalidali con DBMS_ASSERT.SIMPLE_SQL_NAME per proteggerti dalle injection.

L'istruzione EXECUTE IMMEDIATE recupera una sola riga. Per più righe, apri un REF CURSOR con l'istruzione OPEN-FOR, quindi esegui un ciclo FETCH fino a %NOTFOUND e CHIUDI il cursore.

Aggiungi una clausola RETURNING all'istruzione INSERT, UPDATE o DELETE, quindi utilizza la clausola RETURNING INTO di EXECUTE IMMEDIATE per acquisire i valori delle righe interessate negli argomenti di bind.

L'SQL dinamico aggiunge un overhead di analisi perché le istruzioni vengono compilate in fase di esecuzione. Il riutilizzo delle variabili di bind consente Oracle condividi i cursori e riduci i parsing difficili, mantieniping Prestazioni simili a quelle di SQL statico.

La stringa deve essere di tipo VARCHAR2 o CHAR. I tipi di carattere nazionali come NVARCHAR2 e NCHAR non sono consentiti. Per testi di lunghezza superiore a 32 KB, DBMS_SQL accetta una raccolta di elementi di tipo VARCHAR2.

Sì. Gli assistenti basati sull'IA, come GitHub Copilot, generano blocchi EXECUTE IMMEDIATE e DBMS_SQL a partire da semplici prompt, suggeriscono segnaposto per le variabili di bind e spiegano ogni clausola, sebbene un programmatore debba comunque rivedere l'output.

Gli scanner di codice basati sull'IA segnalano l'input utente concatenato e raccomandano variabili di bind o controlli DBMS_ASSERT. Evidenziano i modelli rischiosi durante la revisione, aiutanoping I team individuano i difetti di iniezione prima della distribuzione.

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