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

By: Fiona Marrone Fiona Marrone
Hours aggiornato

Cos'è l'SQL dinamico?

Dinamico SQL è una metodologia di programmazione per generare ed eseguire istruzioni in fase di esecuzione. Viene utilizzato principalmente per scrivere programmi flessibili e di uso generale in cui le istruzioni SQL verranno create ed eseguite in fase di esecuzione in base ai requisiti.

Modi per scrivere SQL dinamico

PL/SQL fornisce due modi per scrivere SQL dinamico

  1. NDS: SQL dinamico nativo
  2. DBMS_SQL

NDS (SQL dinamico nativo): esecuzione immediata

Native Dynamic SQL è il modo più semplice per scrivere SQL dinamico. Utilizza il comando 'EXECUTE IMMEDIATE' per creare ed eseguire SQL in fase di esecuzione. Ma per utilizzare questo modo, il tipo di dati e il numero di variabili da utilizzare in fase di esecuzione devono essere noti in anticipo. Offre anche prestazioni migliori e meno complessità rispetto a DBMS_SQL.

Sintassi 

EXECUTE IMMEDIATE(<SQL>)
[INTO<variable>]
[USING <bind_variable_value>]
  • La sintassi precedente mostra il comando EXECUTE IMMEDIATE.
  • La clausola INTO è facoltativa e viene utilizzata solo se l'SQL dinamico contiene un'istruzione select che recupera valori. Il tipo di variabile deve corrispondere al tipo di variabile dell'istruzione select.
  • La clausola USING è facoltativa e viene utilizzata solo se l'SQL dinamico contiene variabili di collegamento.

esempio 1: In questo esempio, recupereremo i dati dalla tabella emp per emp_no '1001' utilizzando l'istruzione NDS.

NDS: esecuzione immediata

DECLARE
lv_sql VARCHAR2(500);
lv_emp_name VARCHAR2(50):
ln_emp_no NUMBER;
ln_salary NUMBER;
ln_manager NUMBER;
BEGIN
ly_sql:=;SELECT emp_name,emp_no,salary,manager FROM emp WHERE
emp_no=:empmo:;
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_salaiy);
Dbms_output.put_line('Manager ID:‘||ln_manager);
END;
/

Uscita

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

Spiegazione del codice:

  • Riga di codice 2-6: Dichiarazione di variabili.
  • Riga di codice 8: Framing dell'SQL in fase di esecuzione. SQL contiene la variabile di collegamento nella condizione where ':empno'.
  • Riga di codice 9: Esecuzione del testo SQL con cornice (operazione eseguita nella riga di codice 8) utilizzando il comando NDS 'EXECUTE IMMEDIATE'
  • Le variabili nella clausola 'INTO' (lv_emp_name, ln_emp_no, ln_salary, ln_manager) vengono utilizzate per contenere i valori recuperati dalla query SQL (emp_name, emp_no, stipendio, manager)
  • La clausola 'USING' fornisce i valori alla variabile di collegamento nella query SQL (:emp_no).
  • Riga di codice 10-13: Visualizzazione dei valori recuperati.

DBMS_SQL per SQL dinamico

PL/SQL fornisce il pacchetto DBMS_SQL che consente di lavorare con SQL dinamico. Il processo di creazione ed esecuzione di SQL dinamico contiene il seguente processo.

  • APRIRE IL CURSORE: L'SQL dinamico verrà eseguito allo stesso modo di a cursore. Quindi per eseguire l'istruzione SQL, dobbiamo aprire il cursore.
  • ANALISI SQL: Il passaggio successivo consiste nell'analizzare l'SQL dinamico. Questo processo controllerà semplicemente la sintassi e manterrà la query pronta per l'esecuzione.
  • BIND VARIABILE Valori: Il passaggio successivo consiste nell'assegnare i valori per le variabili di associazione, se presenti.
  • DEFINIRE COLONNA: Il passaggio successivo consiste nel definire la colonna utilizzando le relative posizioni nell'istruzione select.
  • ESEGUIRE: Il passaggio successivo consiste nell'eseguire la query analizzata.
  • RECUPERA VALORI: Il passo successivo è recuperare i valori eseguiti.
  • CHIUDI CURSORE: Una volta recuperati i risultati, il cursore deve essere chiuso.

esempio 1: In questo esempio, recupereremo i dati dalla tabella emp per emp_no '1001' utilizzando l'istruzione DBMS_SQL.

DBMS_SQL per SQL dinamico

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;
BEGIN
lv_sql:=‘SELECT emp_name,emp_no,salary,manager FROM emp WHERE
emp_no=:empmo’;
in_cursor_id:=DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(ln_cursor_id,lv_sql,DBMS_SQL.NATIVE);

DBMS_SQL.BIXD_VARLABLE(ln_cursor_id,:‘empno‘,1001);

DBMS_SQL.DEFINE_COLUMN(ln_cursor_ici,1,ln_emp_name);
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);

DBMS_SQL per SQL dinamico

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,In_salary);
DBMS_SQL.COLUMN_VALUE(ln_cursor_id,4,In_manager);
Dbms_output.put_line('Employee Name:‘||lv_emp_name); 
Dbms_output.put_line('Employee Number:l‘||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_ClIRSOR(ln_cursor_id);

END:
/

Uscita

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

Spiegazione del codice:

  • Riga di codice 1-9: Dichiarazione di variabile.
  • Riga di codice 10: Framing dell'istruzione SQL.
  • Riga di codice 11: apertura del cursore utilizzando DBMS_SQL.OPEN_CURSOR. Restituirà l'ID del cursore aperto.
  • Riga di codice 12: Dopo che il cursore è stato aperto, l'SQL viene analizzato.
  • Riga di codice 13: La variabile di collegamento '1001' viene assegnata all'ID del cursore invece di ':empno'.
  • Riga di codice 14-17: definizione del nome della colonna in base alla posizione relativa nell'istruzione SQL. Nel nostro caso, la posizione relativa è (1) dip_name, (2) dip_no (3) stipendio (4) manager. Quindi in base a questa posizione stiamo definendo la variabile target.
  • Riga di codice 18: esecuzione della query utilizzando DBMS_SQL.EXECUTE. Restituisce il numero di record elaborati.
  • Riga di codice 19-33: Recupero dei record utilizzando un loop e visualizzazione degli stessi.
  • Riga di codice 20: DBMS_SQL.FETCH_ROWS recupererà un record dalle righe elaborate. Può essere chiamato ripetutamente per recuperare tutte le righe. Se non riesce a recuperare le righe, restituirà 0, uscendo così dal ciclo.

Sintesi

In questa sezione abbiamo discusso l'SQL dinamico e le modalità per eseguire l'SQL DINAMICO. Abbiamo anche visto i diversi passaggi nell'esecuzione dell'SQL dinamico in entrambi i modi. Abbiamo anche visto gli esempi in cui lo stesso scenario viene gestito sia in modalità NDS che DBMS_SQL per eseguire l'esecuzione in fase di esecuzione.

Riassumi questo post con: