Oracle PL / SQL Dynamic SQL Tutorial: esecuzione immediata di & DBMS_SQL

Sommario:

Anonim

Cos'è l'SQL dinamico?

Dynamic SQL è una metodologia di programmazione per la generazione e l'esecuzione di istruzioni in fase di esecuzione. Viene utilizzato principalmente per scrivere programmi generici e flessibili in cui le istruzioni SQL verranno create ed eseguite in fase di esecuzione in base ai requisiti.

In questo tutorial imparerai-

  • Modi per scrivere SQL dinamico
  • NDS (Native Dynamic SQL): esecuzione immediata
  • DBMS_SQL per SQL dinamico

Modi per scrivere SQL dinamico

PL / SQL fornisce due modi per scrivere SQL dinamico

  1. NDS - Native Dynamic SQL
  2. DBMS_SQL

NDS (Native Dynamic SQL): esecuzione immediata

Native Dynamic SQL è il modo più semplice per scrivere SQL dinamico. Utilizza il comando "ESEGUI IMMEDIATO" per creare ed eseguire l'SQL in fase di esecuzione. Ma per utilizzare questo metodo, è necessario conoscere prima il tipo di dati e il numero di variabili da utilizzare in fase di esecuzione. Offre anche prestazioni migliori e minore complessità rispetto a DBMS_SQL.

Sintassi 

EXECUTE IMMEDIATE()[INTO][USING ]
  • La sintassi precedente mostra il comando ESEGUI IMMEDIATO.
  • La clausola INTO è facoltativa e utilizzata solo se l'SQL dinamico contiene un'istruzione select che recupera i 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 una variabile di bind.

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

DECLARElv_sql VARCHAR2(500);lv_emp_name VARCHAR2(50):ln_emp_no NUMBER;ln_salary NUMBER;ln_manager NUMBER;BEGINly_sql:=;SELECT emp_name,emp_no,salary,manager FROM emp WHEREemp_no=:empmo:;EXECUTE IMMEDIATE lv_sql INTO lv_emp_name,ln_emp_no:ln_salary,ln_managerUSING 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;/

Produzione

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

Spiegazione del codice:

  • Riga di codice 2-6 : dichiarazione di variabili.
  • Riga di codice 8 : Inquadratura dell'SQL in fase di esecuzione. SQL contiene la variabile bind in where condition ': empno'.
  • Riga di codice 9 : esecuzione del testo SQL con cornice (che viene eseguita nella riga di codice 8) utilizzando il comando NDS "ESEGUI IMMEDIATO"
  • 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, salary, manager)
  • La clausola 'USING' fornisce i valori alla variabile bind 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 dell'SQL dinamico contiene il seguente processo.

  • CURSORE APERTO : L'SQL dinamico verrà eseguito allo stesso modo di un cursore. Quindi, per eseguire l'istruzione SQL, dobbiamo aprire il cursore.
  • PARSE SQL : il passaggio successivo consiste nell'analisi dell'SQL dinamico. Questo processo controllerà solo la sintassi e manterrà la query pronta per l'esecuzione.
  • BIND VARIABLE Values : Il passaggio successivo consiste nell'assegnare i valori per le variabili bind, se presenti.
  • DEFINE COLUMN : il passaggio successivo consiste nel definire la colonna utilizzando le posizioni relative nell'istruzione select.
  • ESEGUI : il passaggio successivo consiste nell'eseguire la query analizzata.
  • VALORI DI FETCH : il passaggio successivo consiste nel recuperare i valori eseguiti.
  • CHIUDI CURSORE : una volta recuperati i risultati, il cursore dovrebbe essere chiuso.

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

DECLARElv_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;BEGINlv_sql:=‘SELECT emp_name,emp_no,salary,manager FROM emp WHEREemp_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);
LOOPIF DBMS_SQL.FETCH_ROWS(ln_cursor_id)=0THENEXIT;ELSEDBMS_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:/

Produzione

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

Spiegazione del codice:

  • Riga di codice 1-9 : dichiarazione di variabile.
  • Riga di codice 10 : inquadratura dell'istruzione SQL.
  • Riga di codice 11 : apertura del cursore utilizzando DBMS_SQL.OPEN_CURSOR. Restituirà l'ID del cursore che è stato aperto.
  • Riga di codice 12 : dopo aver aperto il cursore, l'SQL viene analizzato.
  • Riga di codice 13 : la variabile di associazione '1001' viene assegnata all'id del cursore invece di ': empno'.
  • Riga di codice 14-17 : definizione del nome della colonna in base alla loro posizione relativa nell'istruzione SQL. Nel nostro caso, la posizione relativa è (1) emp_name, (2) emp_no (3) salary (4) manager. Quindi in base a questa posizione stiamo definendo la variabile di destinazione.
  • 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 ciclo e visualizzazione dello stesso.
  • 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 può recuperare le righe, restituirà 0, uscendo così dal ciclo.

Sommario

In questa sezione, abbiamo discusso di SQL dinamico e dei modi per eseguire DYNAMIC SQL. 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 in entrambi i modi NDS e DBMS_SQL per eseguire l'esecuzione in fase di esecuzione.