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 ,

Anuncios

Una respuesta to “Consultas Dinámicas SQL – ORACLE”

  1. EFDZ Says:

    Hola,

    Hice una funcion dentro de un paquete, se compila sin marca errores:

    FUNCTION testdinamic(pCAMPO IN VARCHAR2, pesq IN VARCHAR2, ptc IN VARCHAR2, psaldo IN NUMBER, pmora IN NUMBER) RETURN NUMBER IS
    lResult NUMBER(18,5) := 0.00;

    ncursor INTEGER;
    Lconsulta VARCHAR2(1000);
    security_level PLS_INTEGER := 1;

    pidtipocar VARCHAR2(2);
    lmora NUMBER(18,5) := 0.00;
    BEGIN

    lmora := 0.02545;
    IF pmora = 0 THEN
    lmora := 0;
    ELSE
    lmora := ROUND( pmora/psaldo, 2);
    END IF;

    Lconsulta := ‘SELECT :c1
    INTO :r1
    FROM RHPROD.PARPROD_MES PM
    WHERE PM.TIPOCONTRATO = :w1 AND PM.IDTIPOCAR = :w2
    AND trunc(:w3, 4) between PM.PCMORAINI and PM.PCMORAFIN’;

    DBMS_SQL.PARSE(ncursor, Lconsulta, DBMS_SQL.NATIVE );
    ncursor := DBMS_SQL.OPEN_CURSOR(security_level);
    DBMS_SQL.BIND_VARIABLE(ncursor, ‘:c1’, pCAMPO);
    DBMS_SQL.BIND_VARIABLE(ncursor, ‘:w1’, pesq);
    DBMS_SQL.BIND_VARIABLE(ncursor, ‘:w2’, pidtipocar);
    DBMS_SQL.BIND_VARIABLE(ncursor, ‘:w3’, lmora);

    lResult := DBMS_SQL.EXECUTE (ncursor);

    DBMS_SQL.CLOSE_CURSOR(ncursor);

    RETURN lResult;

    END testdinamic;

    Pero al ejecutarlo, marca que no se tiene acceso a DBMS_SQL …

    SELECT RHPROD.PRUEBAS.testdinamic(‘TPCRECUP’, ‘M’, ‘AC’, 100, 50) AS TPCRECUP,
    RHPROD.PRUEBAS.OBT_PARPROD_MES(‘TCREDCOL’, ‘M’, ‘AC’, 100, 50) AS TCREDCOL
    FROM DUAL

    ORA-29471: acceso denegado a DBMS_SQL
    ORA-06512: en “SYS.DBMS_SQL”, línea 1010
    ORA-06512: en “RHPROD.PRUEBAS”, línea 158

    Revise los errores y formas de corregirlos, pero no quiero aplicar alter systems ni comprometer la seguridad en la BD

    Que me suguieres?

    Gracias!

    Saludos desde México.


Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: