Tutorial de SQL dinámico de Oracle PL / SQL: ejecución inmediata & DBMS_SQL

Tabla de contenido:

Anonim

¿Qué es SQL dinámico?

El SQL dinámico es una metodología de programación para generar y ejecutar declaraciones en tiempo de ejecución. Se utiliza principalmente para escribir programas flexibles y de propósito general en los que las sentencias SQL se crearán y ejecutarán en tiempo de ejecución según los requisitos.

En este tutorial, aprenderá:

  • Formas de escribir SQL dinámico
  • NDS (SQL dinámico nativo): ejecución inmediata
  • DBMS_SQL para SQL dinámico

Formas de escribir SQL dinámico

PL / SQL proporciona dos formas de escribir SQL dinámico

  1. NDS: SQL dinámico nativo
  2. DBMS_SQL

NDS (SQL dinámico nativo): ejecución inmediata

El SQL dinámico nativo es la forma más fácil de escribir SQL dinámico. Utiliza el comando 'EXECUTE INMEDIATE' para crear y ejecutar el SQL en tiempo de ejecución. Pero para usarlo de esta manera, es necesario conocer antes el tipo de datos y el número de variable que se usará en un tiempo de ejecución. También ofrece un mejor rendimiento y menos complejidad en comparación con DBMS_SQL.

Sintaxis 

EXECUTE IMMEDIATE()[INTO][USING ]
  • La sintaxis anterior muestra el comando EJECUTAR INMEDIATO.
  • La cláusula INTO es opcional y se usa solo si el SQL dinámico contiene una instrucción de selección que obtiene valores. El tipo de variable debe coincidir con el tipo de variable de la instrucción de selección.
  • La cláusula USING es opcional y se usa solo si el SQL dinámico contiene alguna variable de vinculación.

Ejemplo 1 : En este ejemplo, vamos a obtener los datos de la tabla emp para emp_no '1001' usando la declaración 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;/

Producción

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

Explicación del código:

  • Línea de código 2-6 : Declaración de variables.
  • Línea de código 8 : Enmarcando el SQL en tiempo de ejecución. SQL contiene la variable de vinculación en la condición donde ': empno'.
  • Línea de código 9 : Ejecutando el texto SQL enmarcado (que se hace en la línea de código 8) usando el comando NDS 'EXECUTE INMEDIATE'
  • Las variables en la cláusula 'INTO' (lv_emp_name, ln_emp_no, ln_salary, ln_manager) se utilizan para contener los valores obtenidos de la consulta SQL (emp_name, emp_no, salario, manager)
  • La cláusula 'USING' da los valores a la variable de vinculación en la consulta SQL (: emp_no).
  • Línea de código 10-13 : Visualización de los valores obtenidos.

DBMS_SQL para SQL dinámico

PL / SQL proporciona el paquete DBMS_SQL que le permite trabajar con SQL dinámico. El proceso de creación y ejecución del SQL dinámico contiene el siguiente proceso.

  • ABRIR CURSOR : El SQL dinámico se ejecutará de la misma forma que un cursor. Entonces, para ejecutar la instrucción SQL, debemos abrir el cursor.
  • PARSE SQL : el siguiente paso es analizar el SQL dinámico. Este proceso simplemente verificará la sintaxis y mantendrá la consulta lista para ejecutarse.
  • BIND VARIABLE Valores : El siguiente paso es asignar los valores para las variables de vinculación, si las hay.
  • DEFINIR COLUMNA : El siguiente paso es definir la columna usando sus posiciones relativas en la declaración de selección.
  • EJECUTAR : El siguiente paso es ejecutar la consulta analizada.
  • BUSCAR VALORES : El siguiente paso es buscar los valores ejecutados.
  • CURSOR CERRADO : Una vez que se obtienen los resultados, el cursor debe cerrarse.

Ejemplo 1 : En este ejemplo, vamos a obtener los datos de la tabla emp para emp_no '1001' usando la declaración 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:/

Producción

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

Explicación del código:

  • Línea de código 1-9 : Declaración de variable.
  • Línea de código 10 : Enmarcando la declaración SQL.
  • Línea de código 11 : Abriendo el cursor usando DBMS_SQL.OPEN_CURSOR. Devolverá la identificación del cursor que se abre.
  • Línea de código 12 : después de abrir el cursor, se analiza el SQL.
  • Línea de código 13 : La variable de enlace '1001' se asigna al identificador del cursor en lugar de ': empno'.
  • Línea de código 14-17 : Definición del nombre de la columna en función de su posición relativa en la instrucción SQL. En nuestro caso, la posición relativa es (1) emp_name, (2) emp_no (3) salario (4) manager. Entonces, en base a esta posición, estamos definiendo la variable objetivo.
  • Línea de código 18 : Ejecutando la consulta usando DBMS_SQL.EXECUTE. Devuelve el número de registros procesados.
  • Línea de código 19-33 : Obteniendo los registros usando un bucle y mostrando los mismos.
  • Línea de código 20: DBMS_SQL.FETCH_ROWS buscará un registro de las filas procesadas. Se puede llamar repetidamente para recuperar todas las filas. Si no puede recuperar filas, devolverá 0, saliendo así del bucle.

Resumen

En esta sección, hemos discutido SQL dinámico y las formas de ejecutar SQL DINÁMICO. También hemos visto los diferentes pasos en la ejecución del SQL dinámico en ambos sentidos. También hemos visto los ejemplos en los que se maneja el mismo escenario en formas NDS y DBMS_SQL para realizar la ejecución en tiempo de ejecución.