Los SQL’s dinámicos son una metodología de programación para generar y ejecutar sentencias SQL en tiempo de ejecución. Son muy útiles cuando se desea escribir programas que sean de uso general y flexibles, como un sistema de consultas ad hoc, cuando se escriben programas que deben ejecutar sentencias de definición de objetos (DDL), o cuando en el momento de compilación no se conoce la totalidad del texto de la sentencia SQL, el número o tipos de datos de las variables de entrada y/o salida.
El PL/SQL provee dos maneras de escribir SQL dinámicos:
- SQL dinámicos nativos, una característica del lenguaje PL/SQL (por esto el concepto de nativo) para construir y ejecutar las sentencias SQL dinámicas.
- El paquete DBMS_SQL, un API de la base de datos para construir, ejecutar y describir las sentencias SQL dinámicas.
En el caso de las sentencias dinámicas SQL nativas, el código es más fácil de leer y escribir que el utilizado por el paquete DBMS_SQL, además de ser ejecutado con mayor rapidez, especialmente cuando este está optimizado por el compilador. Sin embargo, cuando se escribe este tipo de sentencias SQL dinámicas, se debe tener conocimiento de la cantidad y tipos de variables de entrada y salida en el momento de la compilación. Si esa información no se conoce, deberá utilizar el paquete DBMS_SQL.
El proceso de las sentencias dinámicas SQL nativas en su mayoría se lleva a cabo con la sentencia EXECUTE IMMEDIATE. La distinción “nativa” en el nombre se refiere al hecho de que la ejecución de las sentencias en el lenguaje PL/SQL se realiza directamente por el motor de ese componente y no por el paquete DBMS_SQL.
Si la sentencia dinámica SQL es una sentencia SELECT que retorne múltiples filas, el motor tiene las siguientes opciones:
- Usar la sentencia EXECUTE IMMEDIATE con la cláusula BULK COLLECT INTO.
- Usar las sentencias OPEN, FOR, FETCH y CLOSE.
Un ejemplo que utiliza un SQL dinámico nativo para crear una tabla es el siguiente:
BEGIN
EXECUTE IMMEDIATE ‘create table la_table(col1 number)’;
END;
Cuando se utiliza una expresión en la cláusula USING, es necesario indicar el modo, el cual puede ser IN, OUT o IN OUT, igual que los modos de los parámetros. Por defecto el modo es IN.
Cuando se ejecutan sentencias dinámicas como SELECT, INSERT, UPDATE y DELETE, generalmente solo se usa el modo IN, ya sea indicándolo explícitamente o confiando que es el valor predeterminado. Por otro lado, los modos OUT e IN OUT se usan más comúnmente en bloques PL/SQL dinámicos. Un ejemplo de esto es el siguiente:
DECLARE
N NUMBER;
BEGIN
EXECUTE IMMEDIATE ‘BEGIN :n := 100; END;’ USING OUT n;
END;
Cuando se ejecuta un programa PL/SQL por medio de la sentencia EXECUTE IMMEDIATE, el modo que se le asigna a la variable de sustitución debe ser el mismo o cubrir más que el parámetro asociado en la sentencia. Esto es, si el modo del parámetro es IN entonces el modo de la variable de sustitución puede ser IN o IN OUT. Si el modo del parámetro es OUT, el modo de la variable de sustitución podría ser OUT o IN OUT.
Con respecto al paquete DBM_SQL, vemos que nos da la posibilidad de utilizarlo cuando se desea confeccionar código que sea mucho más flexible, sin la necesidad de conocer todos los detalles de los objetos en el momento de la compilación, pero esta flexibilidad podría tener impacto en la ejecución de las sentencias.
El detalle del paquete DBMS_SQL no se verá en esta nota, pero en general los pasos que se siguen para su utilización son los siguientes:
- Crear el cursor, utilizando un área de memoria para su uso
- Se ejecuta el proceso de parseo sobre una hilera donde se armó la sentencia a ejecutar
- Se asignan las variables para los resultados o para los datos de entrada
- Se ejecuta la sentencia
- Se procesan los resultados
- Se cierra el cursor
Para detalles del paquete DBMS_SQL, puede consultar la documentación de Oracle en la dirección:
https://docs.oracle.com/database/121/ARPLS/d_sql.htm#ARPLS68182