Oracle Tutorial de SQL dinámico de PL/SQL: Ejecución inmediata y DBMS_SQL

Por: fiona marrón fiona marrón
Hours Actualizado

¿Qué es SQL dinámico?

Dynamic SQL 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 donde las declaraciones SQL se crearán y ejecutarán en tiempo de ejecución según los requisitos.

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 sencilla de escribir SQL dinámico. Utiliza el comando 'EXECUTE IMMEDIATE' para crear y ejecutar el SQL en tiempo de ejecución. Pero para utilizar este método, es necesario conocer de antemano el tipo de datos y la cantidad de variables que se utilizarán en tiempo de ejecución. También ofrece un mejor rendimiento y una menor complejidad en comparación con DBMS_SQL.

Sintaxis 

EXECUTE IMMEDIATE(<SQL>)
[INTO<variable>]
[USING <bind_variable_value>]
  • 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 declaración de selección que obtiene valores. El tipo de variable debe coincidir con el tipo de variable de la declaración de selección.
  • La cláusula USING es opcional y solo se usa si el SQL dinámico contiene alguna variable de vinculación.

Ejemplo : En este ejemplo, vamos a recuperar los datos de la tabla emp para emp_no '1001' usando la declaración NDS.

NDS - Ejecutar Inmediato

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;
/

Salida

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

Explicación del código:

  • Línea de código 2-6: Declaración de variables.
  • Línea de código 8: Enmarcar el SQL en tiempo de ejecución. SQL contiene la variable de enlace en la condición ':empno'.
  • Línea de código 9: Ejecutar el texto SQL enmarcado (que se realiza en la línea de código 8) usando el comando NDS 'EJECUTAR INMEDIATO'
  • 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, gerente)
  • La cláusula 'USING' proporciona los valores de la variable de enlace en la consulta SQL (:emp_no).
  • Línea de código 10-13: muestra los valores recuperados.

DBMS_SQL para SQL dinámico

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

  • ABRIR CURSOR: El SQL dinámico se ejecutará de la misma manera que un cursor. Entonces, para ejecutar la declaración SQL, debemos abrir el cursor.
  • ANALIZAR SQL: El siguiente paso es analizar el SQL dinámico. Este proceso simplemente verificará la sintaxis y mantendrá la consulta lista para ejecutarse.
  • VINCULAR Valores VARIABLES: El siguiente paso es asignar los valores para las variables de enlace, 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.
  • OBTENER VALORES: El siguiente paso es recuperar los valores ejecutados.
  • CERRAR CURSOR: Una vez que se obtienen los resultados, el cursor debe cerrarse.

Ejemplo : En este ejemplo, vamos a recuperar los datos de la tabla emp para emp_no '1001' usando la instrucción DBMS_SQL.

DBMS_SQL para SQL dinámico

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 para SQL dinámico

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:
/

Salida

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

Explicación del código:

  • Línea de código 1-9: Declaración de variables.
  • Línea de código 10: Encuadre de 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 a la identificación del cursor en lugar de ':empno'.
  • Línea de código 14-17: Definir el nombre de la columna en función de su posición relativa en la declaración SQL. En nuestro caso, la posición relativa es (1) emp_name, (2) emp_no (3) salario (4) gerente. 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 mostrándolos.
  • Línea de código 20: DBMS_SQL.FETCH_ROWS recuperará 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 ciclo.

Resum

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

Te podría gustar: