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.
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.
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