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

¿Qué es SQL dinámico?

Dynamic SQL SQL es una metodología de programación para generar y ejecutar sentencias en tiempo de ejecución. Se utiliza principalmente para escribir programas flexibles y de propósito general, donde las sentencias SQL se crean y ejecutan en tiempo de ejecución según los requisitos; por ejemplo, cuando los nombres de las tablas, las listas de columnas o las condiciones WHERE no se conocen hasta que se ejecuta el programa.

Formas de escribir SQL dinámico

PL/SQL ofrece dos maneras de escribir SQL dinámico:

1. NDS – SQL dinámico nativo (las instrucciones EXECUTE IMMEDIATE y OPEN-FOR)
2. DBMS_SQL (un paquete suministrado)

La regla general es sencilla: si se conocen el número y los tipos de datos de las variables de entrada y salida en tiempo de compilación, utilice SQL dinámico nativo, ya que es más rápido y requiere menos código. Cuando esta información solo se conoce en tiempo de ejecución, utilice el paquete 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. Para usar este método, es necesario conocer de antemano el tipo de datos y el número de variables que se usarán en tiempo de ejecución. Además, ofrece un mejor rendimiento y menor complejidad en comparación con DBMS_SQL.

Sintaxis

EXECUTE IMMEDIATE dynamic_sql_string
[INTO {variable[, variable]... | record}]
[USING [IN | OUT | IN OUT] bind_argument[, ...]]
[RETURNING INTO bind_argument[, ...]];

• cadena SQL dinámica: Una expresión de cadena (VARCHAR2 o CHAR, no NVARCHAR2/NCHAR) que contiene una única instrucción SQL o un bloque PL/SQL.
• Cláusula INTO: Opcional. Se utiliza únicamente cuando la consulta SQL dinámica es una consulta SELECT de una sola fila; captura los valores devueltos en variables o en un registro. Cada columna seleccionada necesita una variable compatible con el tipo de dato.
• USO de la cláusula: Opcional. Proporciona variables de enlace. El modo predeterminado es ENTRADA; SALIDA y ENTRADA/SALIDA se utilizan para recibir valores.
• REGRESO A LA cláusula: Se utiliza con sentencias DML que incluyen una cláusula RETURNING para capturar los valores de las filas afectadas en los argumentos de enlace.

Ejemplo 1: En este ejemplo, obtenemos los datos de la tabla emp para el emp_no '1001' utilizando una instrucción NDS con una variable de enlace.

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

Resultado

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

Code Explicación:

• Líneas 2-6: Declarando las variables.
• Línea 8: Estructuración del SQL en tiempo de ejecución. El SQL contiene la variable de enlace ':empno' en la condición WHERE.
• Líneas 9-11: Ejecutando la consulta SQL enmarcada con EXECUTE IMMEDIATE. Las variables de la cláusula INTO contienen los valores obtenidos, y la cláusula USING proporciona el valor para la variable de enlace :empno.
• Líneas 12-15: Mostrando los valores obtenidos.

Uso de SQL dinámico para DDL

PL/SQL estático no puede ejecutar DDL como CREATE, ALTER o DROP directamente. EXECUTE IMMEDIATE resuelve esto construyendo la instrucción como una cadena, lo cual también es útil cuando se proporciona un nombre de objeto en tiempo de ejecución:

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

Los nombres de objetos (tabla, columna, esquema) no se pueden pasar como variables de enlace, por lo que deben concatenarse en la cadena. Valide siempre esta entrada, por ejemplo, con DBMS_ASSERT.SIMPLE_SQL_NAME, para evitar la inyección SQL.

DBMS_SQL para SQL dinámico

PL/SQL proporciona el paquete DBMS_SQL para trabajar con SQL dinámico cuando la estructura de la sentencia no se conoce hasta el tiempo de ejecución. El proceso de creación y ejecución del SQL dinámico implica los siguientes pasos:

• ABRIR CURSOR: El SQL dinámico se ejecuta como un cursorPara ejecutar la sentencia SQL, primero debemos abrir el cursor.
• ANALIZAR SQL: Analiza el SQL dinámico. Esto comprueba la sintaxis y mantiene la consulta lista para su ejecución.
• Valores de VARIABLE DE ENLACE: Asigne los valores a las variables de enlace, si las hay.
• DEFINIR COLUMNA: Defina cada columna utilizando su posición relativa en la instrucción select.
• EJECUTAR: Ejecutar la consulta analizada.
• OBTENER VALORES: Obtener los valores ejecutados.
• CERRAR EL CURSOR: Una vez obtenidos los resultados, cierre el cursor.

Ejemplo 1: En este ejemplo, recuperamos los datos de la tabla emp para el emp_no '1001' mediante una instrucción DBMS_SQL. El bloque EXCEPTION cierra el cursor incluso si se produce un error.

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

Resultado

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

Code Explicación:

• Líneas 1-8: Declaración de variables.
• Línea 10: Cómo formular la sentencia SQL.
• Línea 11: Se abre el cursor mediante DBMS_SQL.OPEN_CURSOR, que devuelve el ID del cursor abierto.
• Línea 12: Una vez abierto el cursor, se analiza la consulta SQL.
• Línea 13: El valor de enlace '1001' se asigna en lugar de ':empno'.
• Líneas 14-17: Definiendo las columnas por su posición relativa: (1) emp_name, (2) emp_no, (3) salary, (4) manager.
• Línea 18: Ejecutar la consulta con DBMS_SQL.EXECUTE, que devuelve el número de registros procesados.
• Líneas 19-32: La función FETCH_ROWS recupera los registros en un bucle. Cuando no quedan filas, devuelve 0, lo que finaliza el bucle.
• Bloque de EXCEPCIÓN: Garantiza que el cursor esté cerrado para que los cursores abiertos no generen fugas de memoria si se produce un error.

NDS vs DBMS_SQL: ¿Cuándo usar cuál?

Ambos enfoques ejecutan SQL en tiempo de ejecución, pero se adaptan a situaciones diferentes:

• Utilice SQL dinámico nativo (EXECUTE IMMEDIATE / OPEN-FOR) Cuando se conocen el número y los tipos de datos de las entradas y salidas en tiempo de compilación, es más rápido, más fácil de leer y requiere menos código.
• Utilice DBMS_SQL cuando la estructura es desconocida hasta el tiempo de ejecución, por ejemplo, una consulta cuyo número de columnas seleccionadas o variables de enlace varía, conocido como SQL dinámico de método 4, o una instrucción demasiado grande para caber en una sola variable VARCHAR2 de 32K.

Preguntas Frecuentes