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: ORCL, 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.
____________________________________________________________________________________
Soporte.
El procedimiento usa SQL Dinámico. Para mas detalles sobre el mismo, ver las siguientes publicaciones:
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.
____________________________________________________________________________________
Soporte.
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.
/* 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_select, v_query, v_primary, v_output, v_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.
*/
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;
/
/
• 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_select, v_query, v_primary, v_output, v_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.
---OUTPUT:
---OUTPUT:
---OUTPUT:
---OUTPUT:
---OUTPUT:
Ejemplos de Implementación.
SET SERVEROUTPUT ON
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:
SET SERVEROUTPUT ON
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:
SET SERVEROUTPUT ON
BEGIN
proc_dynamic_table_query('COUNTRIES','AR');
END;
/
/*En este ejemplo usamos la tabla COUNTRIES.*/---OUTPUT:
SET SERVEROUTPUT ON
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:
SET SERVEROUTPUT ON
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:
