Domingo, 07 Junio 2020 22:29

Procedimiento: Dynamic Table Query

Rate this item
(0 votes)
Objetivos:
• Crear un procedimiento que consulte (dinámicamente) cualquier tabla en tu esquema.
• Familiarizarnos con el SQL Dinámico.
• Ver ejemplos de uso práctico.

NOTA: Usamos como ejemplo la Base de Datos: ORCLE, la cual viene por defecto en cualquier versión de ORACLE.

Introducción
La intención fue crear un procedimiento que reciba el nombre de una tabla mas un valor con el cual se filtrara un consulta. El procedimiento espera el nombre de una tabla que pertenezca al esquema actual; En él, se busca la columna PRIMARY KEY de la tabla recibida para luego usarla como columna de filtro, de no existir un PRIMARY KEY el filtro se realizaría por la primera columna de la tabla. Es prudente destacar que no importa la cantidad de columnas que contenga la tabla, la consulta debe realizarse sin problemas y luego mostrar los resultados por pantalla.

Nota: El procedimiento está pensado para tablas que tengan máximo un PRIMARY KEY (o ninguno), de tener mas de uno, el filtro puede ejecutarse por una columna no deseada.

Los detalles de las técnicas usadas están al final de la definición del procedimiento.

Soportes:
El procedimiento usa SQL Dinámico. Para mas detalles sobre el mismo, ver las siguientes publicaciones:

• SQL Dinámico y Sentencias DDL en PL/SQL


Procedimiento proc_dynamic_table_query
 
CREATE OR REPLACE PROCEDURE   proc_dynamic_table_query
                                                       (
                                                        p_table     IN  VARCHAR2,
                                                        p_filter    IN  VARCHAR2
                                                       )    IS
    e_wrong_table   EXCEPTION;
 
    CURSOR  cur_check_cols IS
        SELECT
                tc.column_name
        FROM    user_tab_columns tc
        WHERE   tc.table_name  =   UPPER(p_table)
        ORDER BY    tc.column_id    ASC;
 
    CURSOR  cur_check_pk    IS
        SELECT
                cc.column_name
        FROM    user_cons_columns cc, user_constraints c
        WHERE   cc.table_name       =   UPPER(p_table)
        AND     c.constraint_type   =   'P'
        AND     cc.table_name       =   c.table_name
        AND     cc.constraint_name  =   c.constraint_name;
 
    v_select        VARCHAR2(2000)   :=  'SELECT ';
    v_query         VARCHAR2(2000);
    v_primary       VARCHAR2(30);
    v_dynamic_type  VARCHAR2(2000)    :=  'TYPE   typ_dynamic IS RECORD (';
    v_count_cols    NUMBER           :=  0;
    v_output        VARCHAR2(2000)   :=  'DBMS_OUTPUT.PUT_LINE(';
    v_execute       VARCHAR2(4000);
 
    TYPE    typ_tab_cols      IS
                    TABLE   OF  VARCHAR2(30)
                        INDEX   BY BINARY_INTEGER;
    v_tab_cols      typ_tab_cols;
BEGIN
 
    FOR rec_cols    IN  cur_check_cols  LOOP
        v_count_cols    :=  v_count_cols+1;
 
        v_tab_cols(v_count_cols)    :=  rec_cols.column_name;
 
        v_select        :=  v_select||rec_cols.column_name||',';
        v_dynamic_type  :=  v_dynamic_type||rec_cols.column_name||' '||p_table||'.'||
rec_cols.column_name||'%TYPE,';
        v_output        :=  v_output||'v_tab(i).'||rec_cols.column_name||q'{||','||}';
 
    END LOOP;
 
    IF  LENGTH(v_select) =   7 THEN
        RAISE   e_wrong_table;
    END IF;
 
    v_select     :=  SUBSTR(v_select, 1 , LENGTH(v_select)-1);
    v_dynamic_type     :=  SUBSTR(v_dynamic_type, 1 , LENGTH(v_dynamic_type)-1)||');';
    v_output            :=  SUBSTR(v_output, 1 , LENGTH(v_output)-7)||');';
 
    OPEN    cur_check_pk;
    FETCH   cur_check_pk  INTO  v_primary;
    CLOSE   cur_check_pk;
 
    DBMS_OUTPUT.PUT_LINE('Table: '||p_table||CHR(10)||'Columns:');
    DBMS_OUTPUT.PUT_LINE(SUBSTR(v_select, 8 , LENGTH(v_select)));
 
    v_query     :=  CHR(10)||'FROM '||p_table||CHR(10)||'WHERE TO_CHAR('||
NVL(v_primary, v_tab_cols(1))||q'{) = :v_filter}';

    v_execute   :=      'DECLARE
                            '||v_dynamic_type||'
                            TYPE    typ_tab IS
                                TABLE   OF typ_dynamic
                                    INDEX BY BINARY_INTEGER;
                            v_tab   typ_tab;
                        BEGIN
                            '||v_select||' BULK COLLECT INTO v_tab'||v_query||';
 
                            FOR i IN NVL(v_tab.FIRST, 1)..NVL(v_tab.LAST,0) LOOP
                                '||v_output||'
                            END LOOP;
                        END;
                        ';
 
    EXECUTE IMMEDIATE   v_execute
                          USING   p_filter;
 
    DBMS_OUTPUT.PUT_LINE(CHR(10)||'Filter Applied: '||
 NVL(v_primary, v_tab_cols(1))||' = '||p_filter);

    EXCEPTION
        WHEN    e_wrong_table   THEN
            DBMS_OUTPUT.PUT_LINE('Invalid Table Name!');
END proc_dynamic_table_query;
/* En el anterior ejemplo:
• Se usó el CURSOR cur_check_cols para extraer todas la columnas de tabla recibida como parámetro.
• El CURSOR cur_check_pk busca la columna PRIMARY KEY por la cual se realizará el filtro.
• Las variables(v_selectv_queryv_primaryv_outputv_execute) son usadas para almacenar la sentencia SELECT usada en la sentencia dinámica.
• Usamos v_dynamic_type para almacenar la definición de un tipo Record usado en la sentencia dinámica.
• El CURSOR cur_check_cols es usado en forma de CURSOR FOR LOOP; Dentro de dicho LOOP asignamos a las variables sus valores correspondientes.
• El IF  LENGTH(v_select) =   7 tiene como fin validar que la variable v_select tenga una cadena mas extensa a la que se le asignó al momento de su declaración, si el CURSOR cur_check_cols  no retorna registros dicha variable tendría la longitud de 7 caracteres. Si dicho IF resulta TRUE la excepción e_wrong_table es levantada y las demás sentencias del procedimiento no se ejecutan.
• Si el anterior IF retorna FALSE en las siguientes sentencias usamos la función SUBSTR para eliminar algunos caracteres no necesarios de las variables ya mencionadas.
• Las demás sentencias se encargan de arma el bloque dinámico de forma adecuada para luego ejecutarlo con EXECUTE IMMEDIATE.
*/

Ejemplos de Implementación

BEGIN
    proc_dynamic_table_query('employees','100');
END;
/*En este ejemplo usamos el procedimiento proc_dynamic_table_query pasandole el nombre de la tabla EMPLOYEES y el código de empleado 100, mas abajo podemos ver el OUTPUT.*/
---OUTPUT:
EMPLOYEEES
 
BEGIN
    proc_dynamic_table_query('JOBS','AD_VP');
END;
/*En este ejemplo vemos como el procedimiento proc_dynamic_table_query  funciona de la manera esperada con una tabla (JOBS) con menos campos y con un PRIMARY KEY tipo carácter.*/
---OUTPUT:
JOBS
 
BEGIN
    proc_dynamic_table_query('COUNTRIES','AR');
END;
/*En este ejemplo usamos la tabla COUNTRIES.*/
---OUTPUT:
 
BEGIN
    proc_dynamic_table_query('EMP_COPY','201');
END;
/*En esta ocasión usamos como ejemplo la tabla EMP_COPY la cual es una copia de EMPLOYEES (Los mismos campos) pero sin PRIMARY KEY.*/
---OUTPUT:
EMP COPY
 
BEGIN
    proc_dynamic_table_query('TABLE1','2');
END;
/*Ahora usamos la tabla TABLE1 la cual no tiene PRIMARY KEY y contiene varios registros en los cuales coinciden los valores de su primera columna. El fin de este ejemplo es mostrar como el procedimiento imprime varios registros si los mismos cumplen con el criterio de búsqueda.*/
---OUTPUT:
Read 693 times Last modified on Jueves, 13 Agosto 2020 00:34

Leave a comment

Make sure you enter all the required information, indicated by an asterisk (*). HTML code is not allowed.

Magic PL/SQL

Blog orientado al desarrollo de PL / SQL en el "Maravilloso Mundo ORACLE". Cursos Online y Tutoriales Gratis de Manejo de Base de Datos. Aprende a programar, a crear aplicaciones para empresas y negocios y gana dinero. Hosting y Tecnología.