Consultas Dinámicas SQL – ORACLE

Más de una vez se utilizan dentro de las bases de Datos consultas un poco parecidas o se necesita armar una consulta con determinados datos, algo que puede ser simplificado dentro de Oracle, con la utilización de el paquete DBMS_SQL.

Aquí un ejemplo:

Si poseemos la siguiente estructura de una tabla (muy sencilla solo para ejemplo):

create table registro (cliente  varchar2 (50),
compras  number (10,3),
ventas   number (10,3),
estado   varchar2 (25));

En donde se guardará información de ventas o compras de un cliente, tomando en cuenta que por cada transacción solo puede haber un valor en compras o ventas, pero no los dos, los datos son ingresados de la siguiente manera:

insert into registro ( cliente, compras, estado)
values ( 'Perez Juan', 123.4, 'CANCELADO');
insert into registro ( cliente, ventas, estado)
values ( 'Perez Juan', 452.8, 'PENDIENTE');

Como podemos ver la única diferencia entre estas sentencias de INSERT es la columna a la cual hacen referencia, ahora bien, que tal si creamos un pequeño procedimiento y lo almacenamos en un paquete de utilidad que luego podremos utilizar.

El procedimiento quedaría algo como esto:

PROCEDURE grabar_datos( nm_cliente     varchar2 (50),

columna        varchar2(50),

val_columna    number (10,3),

estado         number (10,3))

is

n_cursor INTEGER;

consulta VARCHAR2(250);

aux      number;

begin

consulta := 'insert into registro (cliente,:v1,estado)

values (:v2 , :v3 , v4)';

n_cursor := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(n_cursor, consulta , DBMS_SQL.NATIVE );

DBMS_SQL.BIND_VARIABLE(n_cursor, ':v1', columna);

DBMS_SQL.BIND_VARIABLE(n_cursor, ':v2', nm_cliente);

DBMS_SQL.BIND_VARIABLE(n_cursor, ':v3', val_columna);

DBMS_SQL.BIND_VARIABLE(n_cursor, ':v4', estado);

aux := DBMS_SQL.EXECUTE (n_cursor);

DBMS_SQL.CLOSE_CURSOR(n_cursor);

end grabar_datos;

En este caso el parámetro val_columna podrá solo recibir dos valores «compras»  o «ventas» que son las columnas de la tabla. Podemos observar que para crear la consulta dinámica reemplazos los valores a utilizar por un expresión del tipo :nombre (similar a las variables host en oracle) para luego reemplazarlas con los valores originales mediante la instrucción:

DBMS_SQL.BIND_VARIABLE(n_cursor, ':v1',columna);

Se pudiera también formar la consulta dinámica de forma directa concatenando las expresiones que necesitamos, pero por algún motivo esto no es lo más óptimo pues puede presentar errores al concatener expresiones entre ‘ ‘ y números reales (12.3) .

Con esto habremos reemplazado los dos insert por un solo procedimiento.

Saludos ,