miércoles, 1 de abril de 2009

Cómo crear una vista parametrizada

En Oracle, podemos crear vistas que retornen resultados dependientes de parámetros previamente seteados. La forma de lograr esto es usando un feature llamado Application Contexts.

¿Qué es un contexto de aplicación?
El contexto de aplicación es simplemente un espacio en memoria que nos permite almacenar valores para luego utilizarlos en SQL o PL/SQL, como cualquier otra variable definida en el entorno. De forma transparente, mis objetos pueden ser 'manipulados' externamente sin necesidad que mis aplicaciones o procesos batch se enteren.
El contexto puede ser definido tanto localmente (privado para cada sesión) como globalmente, compartiendo sus valores para todas las sesiones de la instancia.
Para poder alterar los valores del contexto, debemos crear un paquete especialmente autorizado para ese fin. Esto es un requerimiento por razones de seguridad.
También necesitamos tener el permiso especial de sistema CREATE ANY CONTEXT.

Ejemplo (con sqlplus)

Vamos a ver un sencillo ejemplo de cómo implementar una vista parametrizada con contextos de aplicación, usando el popular usuario SCOTT. El parámetro para la vista en este caso será el número de departamento.

Paso 1: Crear el contexto de aplicación
CREATE CONTEXT app_ctx_scott USING pk_scott_app_context
/
Paso 2: Crear el paquete para manipular el contexto
CREATE OR REPLACE PACKAGE pk_scott_app_context AS
-- El contexto tendra un unico valor deptno
PROCEDURE set_dept (p_deptno IN NUMBER);
END;
/

CREATE OR REPLACE PACKAGE BODY pk_scott_app_context AS
PROCEDURE set_dept (p_deptno IN NUMBER) IS
BEGIN
DBMS_SESSION.SET_CONTEXT('app_ctx_scott', 'deptno', p_deptno);
END;
END;
/
Paso 3: Setear el parámetro de contexto deptno para el departamento de ventas
BEGIN
pk_scott_app_context.set_dept(30);
END;
/
Paso 4: Crear la vista parametrizada
CREATE VIEW empleados AS
SELECT e.empno, e.ename, e.job, d.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno
AND d.deptno = sys_context('app_ctx_scott','deptno');
Paso 5: Obtener los resultados consultando la vista
SELECT * FROM empleados;

EMPNO ENAME JOB DNAME
---------- ---------- --------- --------------
7499 ALLEN SALESMAN SALES
7521 WARD SALESMAN SALES
7654 MARTIN SALESMAN SALES
7698 BLAKE MANAGER SALES
7844 TURNER SALESMAN SALES
7900 JAMES CLERK SALES

6 rows selected.
Actualmente la vista retorna los empleados del departamento de ventas, ya que así está definida la variable en el contexto. Ahora cambiaré el valor del parámetro para que la vista retorne resultados únicamente del departamento contable:
BEGIN
pk_scott_app_context.set_dept(10);
END;
/

PL/SQL procedure successfully completed.

SELECT * FROM empleados;


EMPNO ENAME JOB DNAME
---------- ---------- --------- --------------
7782 CLARK MANAGER ACCOUNTING
7839 KING PRESIDENT ACCOUNTING
7934 MILLER CLERK ACCOUNTING

3 rows selected.

De la misma forma podemos aplicar esta técnica a procedimientos y funciones, pudiendo manipular valores utilizados internamente o inclusive introduciendo fragmentos de código como sql dinámico.