Oracle 11g - Utilização de Parametros em VIEWs

Pessoal,

Bom dia!
Trabalhando com VIEWs no Oracle, as vezes podemos ter a necessidade de criar uma view que receba informações de parametros em sua consulta SQL e em sua criação normal não é permitido o input de parametros como fazemos no cabeçalho de procedures e funções.

Segue abaixo uma maneira de criar view que utilize parametros em sua consulta interna, utilizando variáveis de contexto:



Criamos uma tabela de funcionários, base de nossa view, onde teremos o id, nome do funcionário e o departamento que ele trabalha:



SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 29 09:59:12 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table funcionarios (idfunc number(10), nome varchar2(20), iddepto number(10));

Table created.

Cadastramos alguns funcionários:

SQL> insert into funcionarios values (1,'JOSE',10);

1 row created.

SQL> insert into funcionarios values (2,'MARIA',10);

1 row created.

SQL> insert into funcionarios values (3,'ANTONIO',10);

1 row created.

SQL> insert into funcionarios values (4,'JOAO',20);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> select * from funcionarios;

IDFUNC NOME                    IDDEPTO
---------- -------------------- ----------
1 JOSE                         10
2 MARIA                        10
3 ANTONIO                      10
4 JOAO                         20

Agora precisamos criar a variável de contexto que será utilizada como parametro na view, porem o usuário que vai criar a variável de contexto precisa ter permissão: grant CREATE ANY CONTEXT to usuario;

Abaixo criação do contexto que será utilizado como parametro na view.
Obs. Apontamos o contexto para uma procedure prc_contexto que realizará a alteração da variável ou a mudança do parametro quando necessário:

SQL> create or replace context ctx_teste USING prc_contexto;

Context created.

Criação da procedure que modifica a nossa variável de contexto

 SQL> create or replace procedure prc_contexto(p_name in varchar2, p_value in number) as 
begin 
dbms_session.set_context( 'ctx_teste', p_name, p_value ); 
end prc_contexto; 
/  
Procedure created.


Alimentamos a variável de contexto que será utilizada como parametro na view. Aqui definimos o parametro como departamento = 10.


SQL> exec prc_contexto('departamento', 10);

PL/SQL procedure successfully completed.

Criamos uma view que utiliza como parametro de consulta a variável de contexto:

SQL> create or replace view VW_TESTE as 
select idfunc, nome from funcionarios 
where iddepto = sys_context('ctx_teste', 'departamento');   
View created. 


Agora quando consultamos a view ela retorna apenas os funcionários do departamento 10:

SQL> select * from VW_TESTE;

IDFUNC NOME
---------- --------------------
1 JOSE
2 MARIA
3 ANTONIO

Mudando o contexto o resultado da view é alterado também:

SQL> exec prc_contexto('departamento', 20);

PL/SQL procedure successfully completed.

SQL> select * from VW_TESTE;

IDFUNC NOME
---------- --------------------
4 JOAO


Abraço,

Ronaldo

Nenhum comentário:

Postar um comentário