Tema 2.3 - SQL Dinámico

Es una metodología de programación que ejecuta y genera sentencias SQL en tiempo de ejecución. Es útil cuando se escriben programas flexibles y de propósito general como cuando en tiempo de compilación no se sabe el texto completo de una instrucción SQL o el número o tipos de datos de sus variables de entrada/salida.

Uso de SQL Dinámico

SQL dinámico procesa la mayoría de sentencias SQL dinámicas por medio de la declaración sentencia EXECUTE IMMEDIATE.

Si la sentencia SQL dinámica es una instrucción SELECT que devuelve varias filas, SQL dinámico ofrece las siguientes opciones:

1. Utilice la sentencia EXECUTE IMMEDIATE con la cláusula BULK COLLECT INTO.
2. Utilice las sentencias OPEN-FOR, FETCH y CLOSE (cursor).

Se puede usar variables Bind como parámetros dinámicos en las sentencias EXECUTE IMMEDIATE y OPEN.

Manipulación de datos DDL y DML

En PL/SQL se puede usar directamente el Lenguaje de Manipulación de Datos (DML) de SQL, es decir, SELECT, UPDATE, DELETE e INSERT. La cláusula SELECT requiere usar INTO o estar asociado con un cursor para poder asignar el valor o valores devueltos por la consulta a nuestras variables declaradas en nuestro bloque PL/SQL.

Las sentencias de Lenguaje de Definición de Datos (DDL) que son CREATE, DROP y ALTER no se pueden utilizar dentro del bloque PL/SQL directamente, entonces para poder usarlas debemos utilizar la declaración EXECUTE IMMEDIATE.

Nota: La declaración EXECUTE IMMEDIATE también se puede utilizar para ejecutar operaciones DML.

Sintaxis EXECUTE IMMEDIATE

A continuación, se visualiza la sintaxis que tiene esta sentencia.

Donde:

1. bind_argument: Una expresión cuyo valor se pasa a la instrucción SQL dinámica o una variable que almacena un valor devuelto por la instrucción SQL dinámica.
2. define_variable: Una variable que almacena el valor de una columna seleccionada.
3. dynamic_string: La cadena literal, variable o expresión que representa una sola sentencia SQL o un bloque PL/SQL. Debe ser de tipo CHAR o VARCHAR2, no NCHAR o NVARCHAR2.
4. INTO: Utilizada sólo para consultas de una sola fila, esta cláusula especifica las variables o registros en los que se recuperan los valores de las columnas. Para cada valor recuperado por la consulta, debe haber una variable o campo correspondiente compatible con el tipo en la cláusula INTO.
5. record_name: Record %ROWTYPE (o definido por el usuario) que almacena una fila seleccionada.
6. RETURNING INTO: Utilizada sólo para sentencias DML que tienen una cláusula RETURNING (sin una cláusula BULK COLLECT), esta cláusula especifica las variables Bind en las que se devuelven los valores de columna. Para cada valor devuelto por la sentencia DML, debe haber una variable correspondiente compatible con el tipo en la cláusula RETURNING INTO.
7. USING: Especifica una lista de argumentos bind de entrada y/o salida. El modo de parámetro predeterminado es IN.

Variables BIND

Antes de explicar para que sirven las variables bind debemos preguntarnos qué pasa cuando se ejecuta una sentencia SQL (DELETE, SELECT, DROP, ALTER, etc). Todas las sentencias SQL pasan por un proceso denominado parse, el cual analiza la sentencia antes de ejecutarse.

El parseo consiste en un análisis de verificación que consta de los siguientes 5 pasos y en ese orden:

1. Análisis sintáctico.
2. Análisis semántico.
3. Optimización.
4. Generación.
5. Ejecución.

El parse se puede clasificar en dos: Soft Parse y Hard Parse.

Hard Parse

Es cuando la sentencia SQL tiene que pasar por los 5 pasos debido a que Oracle no encuentra la sentencia en el SQL Area del SGA (Área Global del Sistema).

Soft Parse

Es cuando la sentencia SQL pasa solamente por los pasos 1 y 2 debido a que Oracle encuentra la sentencia en el SQL Area del SGA y determina que no es necesario ejecutar los demás pasos.

Nota: SGA (Área Global del Sistema) es una estructura básica de memoria de Oracle que sirve para facilitar la transferencia de información entre usuarios y también almacena la información estructural de la BD más frecuentemente requerida.

Entonces lo que se quiere a la hora de ejecutar una sentencia SQL es que pase por un Soft Parse y poder optimizar el rendimiento de nuestra base de datos. Para lograr esto debemos aplicar buenas prácticas de programación usando variables bind al momento de utilizar sentencias dinámicas que se ejecutan con el comando EXECUTE IMMEDIATE.

En PL/SQL las variables bind se declaran con dos puntos (:) y seguido se coloca el nombre de la variable. Y se utilizan con el comando EXECUTE IMMEDIATE.

Los beneficios que trae el uso de variables bind son los siguientes:

1. Mejor tiempo de respuesta al ejecutar sentencias SQL porque se generan por Soft Parse al momento de analizarse.
2. Disminuye la carga de trabajo en la base de datos.
3. Menor uso de SQL Library.

Las desventajas que se presentan al no usar variables bind son:

1. Malos tiempos de respuesta.
2. Aumento de la carga de trabajo en la base de datos.
3. Mayor uso de SQL Library.

Ejemplo 1:

A continuación, se mostrará un script donde estará el código para crear la tabla y los registros que se van a utilizar para visualizar el uso de las operaciones DML.

Al ejecutar el script debe quedar la tabla EMP de esta manera:

Procedemos a escribir el siguiente código en PL/SQL.

La salida que obtenemos después de ejecutarlo es la que se muestra a continuación.

Para verificar si los cambios se hicieron, revisamos la tabla EMP que nos debió quedar de la siguiente manera.

En un script, ejecutamos la sentencia COMMIT para que los cambios que se hicieron queden guardados.

Nota: Las variables nom y salario usan el atributo %TYPE que permite heredar el mismo tipo de dato de una variable, campo, registro, tabla anidada o columna de la base de datos previamente declarada, en este caso se toma el tipo de datos de las columnas NOMBRE y SUELDO de la tabla EMP.

Ejemplo 2:

Acá se muestra el código para crear una tabla directamente en el bloque PL/SQL usando la sentencia EXECUTE IMMEDIATE.

Para verificar si se creó la tabla DEPARTAMENTO en nuestra base de datos, miramos en SQL Developer.

Ejemplo 3:

Vamos a seguir usando la misma estructura de la tabla DEPARTAMENTO del ejemplo anterior, le añadiremos y eliminaremos de manera dinámica algunos registros por medio de la sentencia EXECUTE IMMEDIATE y utilizando variables de tipo BIND.

Abrimos un script nuevo y escribimos el siguiente código.

El código mostrado anteriormente lo que hará es ingresar 3 registros a la tabla DEPARTAMENTO que son: Financiero, Recursos Humanos y Marketing. Y luego se elimina el departamento de Marketing.

Ejecutamos el script y para verificar si se hizo las operaciones esperadas miramos los registros de la tabla DEPARTAMENTO. Según el código hecho deberíamos tener solamente dos registros en esta tabla pertenecientes al departamento Financiero y de Recursos Humanos.

Por último, en un nuevo script ejecutamos la sentencia COMMIT para confirmar los cambios hechos en nuestra base de datos.

Notas:

1. :bind_1 y :bind_2 son variables de tipo BIND.
2. Al usar EXECUTE IMMEDIATE para operaciones DML como INSERT y DELETE se debe utilizar variables Bind.
3. Por medio de la cláusula USING colocamos los valores que van a ser vinculados a nuestras variables bind que en nuestro caso son :bind_1 y :bind_2, mientras que los valores que vamos a asociar son los que están en las variables cod_dept_X y nombre_dept_X. X es una valor de 1 a 3.