Views no RDBMS Oracle
Um recurso muito simples em tese no SGBD Oracle (e que existe em muitos outros SGBDs) são as views: no Oracle porém quando você cria uma view não há uma sintaxe para se passar Parâmetros em tempo de runtime para ela (ao contrário de uma função, por exemplo), e como recentemente tive essa necessidade, vou ilustrar aqui essa técnica, e aproveitarei também para conceituar melhor as views…
O que são as views : falando das views SIMPLES (que criamos com CREATE VIEW, views Materializadas, Snapshots e Updateable views são animaizinhos diferentes), ao fim e ao cabo uma view NADA MAIS É do que um SELECT , uma query, cujo texto fica Armazenado no database e quando necessário é lido e executado, nada mais… Ainda é possível se encontrar livros ruins e sites piores ainda que sugerem que a view funcionaria como uma cópia dos dados (ie, os dados lidos pela view seriam ‘copiados’ para outro local do disco, de alguma maneira), o que é uma simples falácia, não é nem de longe assim que uma view simples funciona… Vamos a um exemplo: Digamos que tenho uma tabela EMP (que contém os Empregados da Empresa), com a seguinte estrutura
scott@DESENV:SQL>desc emp
Nome Nulo? Tipo
------------------------------------------ -------- -------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)E desejo criar uma view que só mostre os Empregados do departamento 10, eu criaria a view assim (vou colocar o texto do SELECT da view em amarelo apenas para o destacar):
scott@DESENV:SQL> create view V_EMPS_DEPTO_10 as select empno, ename, sal from emp where deptno = 10;
View criada.Pronto, a partir desse momento em qualquer SQL onde eu referencie esse objeto V_EMPS_DEPTO_10 essa referência VAI ser substituída pelo SELECT da view.. Por exemplo, se eu escrever:
SELECT * FROM V_EMPS_DEPTO_10;O que o banco na verdade vai executar é:
SELECT * FROM (select empno, ename, sal from emp where deptno = 10);Isso é muito fácil de comprovar – podia usar N outros métodos, vou usar o AUTOTRACE do sqlplus…
OBSERVAÇÃO : – num ambiente de teste/desenv essa ferramenta já deve muito provavelmente estar configurada, mas se não estiver E você queira a usar para reproduzir o exemplo , é simples : conectado como usuário SYS no sqlplus no servidor Oracle, no local onde o Oracle foi instalado(que nesta máquina de exemplo foi E:\oracle\product\10.2.0\db_1) haverá um subdiretório sqlplus e nesse subdiretório haverá ainda outro sub-diretório admin, nele está o script que cria a role necessária para usar o AUTOTRACE , basta executar o script (que nesta máquina:
SYS@DESENV:SQL> @E:\oracle\product\10.2.0\db_1\sqlplus\admin\plustrce.sql
Feito isso é so permissionar o usuário que vai usar a ferramenta, o SCOTT no meu caso:
SYS@DESENV:SQL>GRANT PLUSTRACE TO SCOTT;
Continuando, é ativado o autotrace e executada a consulta referenciando a view:
scott@DESENV:SQL>set autotrace on
scott@DESENV:SQL>select * from V_EMPS_DEPTO_10;
EMPNO ENAME SAL
---------- ---------- ----------
7782 CLARK 2450
7839 KING 5000
7934 MILLER 1300
Plano de Execução
----------------------------------
Plan hash value: 3956160932
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 51 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 3 | 51 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
-----------------------------------------------------
1 - filter("DEPTNO"=10)
Estatística
----------------------------------------------------------
300 recursive calls
0 db block gets
66 consistent gets
6 physical reads
0 redo size
729 bytes sent via SQL*Net to client
488 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
8 sorts (memory)
0 sorts (disk)
3 rows processed Vou desativar o AUTOTRACE pois não precisarei mais dele:
scott@DESENV:SQL>set autotrace off Veja que a tabela efetivamente lida foi a EMP, E que as colunas retornadas foram as colunas da query na EMP estabelecida na view…
Esse fato de que a query indicada na criação da view é executada como uma “sub-query”, como uma fonte Interna de dados, leva diretamente à uma questão : se o SELECT “externo” que vai ler os dados retornado pela SELECT ‘interno’ da view tiver predicados, condições de filtro e complementos do tipo, nem sempre vai ser possível para o RDBMS “importar” esses itens externos para a sub-query ‘interna’, conforme este link mostra….
Exemplificando, digamos então que a nossa view exemplo necessite ser usada dentro dum aplicativo onde o usuário (através duma interface gráfica, certamente) popula uma variável V_DEPTNO com o número do departamento a pesquisar, não sendo mais fixo sempre depto 10 – o RDBMS Oracle não aceita ainda você ter um parâmetro na criação da view (ao contrário de outros objetos programáticos, como Procedures, Funções, etc.) mas há várias opções para se criar algo nesse sentido : a mais simples seria vc simplesmente ter uma tabela ‘de trabalho’, inicialmente vazia, onde o Aplicativo iria inserir os dados necessários e a query da view iria ler esses dados através de um operador IN ou EXISTS….
Porém, Outra possibilidade é colocar os valores desejados em Variáveis Globais, preferencialmente Independentes entre sessões, de modo que cada sessão possa ter seus valores próprios informados por cada sessão usando o aplicativo : dentro de um database gerenciado pelo RDBMS Oracle, entre os vários mecanismos que são possíveis (como CONTEXTS , Global Temporary Tables, variáveis globais definidas numa declaração de Package, etc ), um dos mais antigos e simples é chamada ao PL/SQL, com um método de GET e outro de PUT, será o usado neste exemplo…
Primeiro declaramos o conteúdo da package – vai haver uma Procedure para popular a variável interna G_DEPTNO e uma função que retorna o conteúdo:
scott@DESENV:SQL> create or replace PACKAGE PKG_CONSULTA_DEPTOS AS
-- Procedure a ser Chamada Antes de usar a view
PROCEDURE P_SET_DEPTNO (P_DEPTNO NUMBER);
-- Função a ser chamada na view para Recuperar o conteúdo da variável Global packaged
FUNCTION F_GET_DEPTNO RETURN NUMBER;
END;
* /
Pacote criado. Depois criamos o corpo da package, onde haverá o código fonte das duas rotinas declaradas E será definida e populada a variável – como a variável foi definida FORA das rotinas da package pode ser referenciada/usada em qualquer uma das rotinas dessa package… Exemplo:
scott@DESENV:SQL> create or replace PACKAGE BODY PKG_CONSULTA_DEPTOS AS
g_DEPTNO NUMBER;
--
PROCEDURE P_SET_DEPTNO(P_DEPTNO NUMBER) is
Begin
g_DEPTNO := p_DEPTNO;
End;
--
FUNCTION F_GET_DEPTNO RETURN NUMBER is
Begin
RETURN g_DEPTNO;
End;
-- Fim do Package Body
END;
/
Corpo de Pacote criado. Com os objetos no lugar, eles podem ser referenciados, vou criar a view:
scott@DESENV:SQL> create view V_EMPS_DEPTOS as select deptno, empno, ename, sal from emp where deptno = PKG_CONSULTA_DEPTOS.F_GET_DEPTNO;
View criada. Ok, com isto a Aplicação que vai consumir a view já pode Popular a variável e depois consultar a view – no exemplo vou usar o sqlplus como se fosse a Aplicação e chamar os métodos necessários mas Obviamente isso seria feito na Aplicação… Digamos que numa sessão 1 um usuário queira parametrizar a view para retornar empregados do depto 10 :
scott@DESENV:SQL#1>exec PKG_CONSULTA_DEPTOS.P_SET_DEPTNO(10);
scott@DESENV:SQL#1> select * from V_EMPS_DEPTOS;
DEPTNO EMPNO ENAME SAL
---------- ---------- ---------- ----------
10 7782 CLARK 2450
10 7839 KING 5000
10 7934 MILLER 1300 E então simultaneamente outra sessão queira consultar outro depto:
scott@DESENV:SQL#2>exec PKG_CONSULTA_DEPTOS.P_SET_DEPTNO(20);
Procedimento PL/SQL concluído com sucesso.
scott@DESENV:SQL#2>select * from V_EMPS_DEPTOS;
DEPTNO EMPNO ENAME SAL
---------- ---------- ---------- ----------
20 7369 SMITH 800
20 7566 JONES 2975
20 7788 SCOTT 3000
20 7876 ADAMS 1100
20 7902 FORD 3000
scott@DESENV:SQL#2>
muito legal, não sabia que dava para fazer assim 🙂