Trabalhando com Views no Oracle

Figura 1: Logomarca do Oracle

View

Podemos definir uma view como uma consulta predefinida baseada em uma ou mais tabelas. As view�s podem receber consulta assim como uma tabela, para isso deve-se passar o nome da(s) view(s) na cl�usula FROM dento da consulta. As view�s oferecem algumas vantagens e est�o divididas em 2 tipos, View Simples e Complexa.

Algumas Vantagens de Usar View:

Para restringir acesso a dados;
Para fazer consultas complexas de forma mais simplificada;
Para oferecer independ�ncia de dados;

View Simpes - Uma view simples recupera linhas de uma �nica tabela base, n�o cont�m funç�es grupo e pode aceitar operaç�es DML(Linguagem de Manipulaç�o de Dados).View Complexa - Uma View complexa recupera linhas de v�rias tabelas, cont�m funç�es de grupo e nem sempre permite operaç�es DML.

Abaixo vemos a sintaxe Padr�o para criaç�o de uma view, o que est� entre colchetes([]) � opcional.

Listagem 1: Sintaxe padr�o de criaç�o de view

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW Nome_Da_View
  [(alias[, alias]...)]
 AS subquery
[WITH CHECK OPTION [CONSTRAINT nome_Constraint]]
[WITH READ ONLY [CONSTRAINT nome_Constraint]];

OR REPLACE � Significa que a view dever� ser alterada, caso ela j� exista.
FORCE � Força a criaç�o da view mesmo que as tabelas de base n�o existam.
NOFORCE � N�o permite a criaç�o da view se as tabelas de base n�o existirem, esse j� o padr�o, ou seja ,se no FORCE n�o for declarado o NOFORCE � embutido de forma impl�cita.
NOME_DA_VIEW � � nome da vis�o.
ALIAS � � o apelido de uma express�o na subconsulta. Deve haver o mesmo n�mero de apelidos do que expressç�es na subconsulta.
SUBQUERY � � a subconsulta que recupera as linhas das tabelas de base. Se voc� estiver usando alias(apelidos), pode us�-los na lista ap�s a instruç�o SELECT.
WITH CHECK OPTION � Significa que somente as linhas que seriam recuperadas na subconsulta podem ser inseridas, atualizadas ou removidas. Se voc� n�o usar essa cl�usula, as linhas n�o s�o verificadas.
NOME_CONSTRAINT � � o nome que ser� atribu�do � restriç�o WITH CHECK OPTION ou WITH READ ONLY.
WITH READ ONLY � Significa que s� podem ser lidas as linhas da tabela base.

Nota: para nossos exemplos vamos trabalhar sobre o esquema HR que vem como padr�o na instalaç�o do Oracle. Para criaç�o de view�s o usu�rio precisa ter o privil�gio CREATE VIEW liberado.

CRIANDO VIEW SIMPLES

Listagem 2: Criando a View Emp_dep

CREATE VIEW emp_dep 
 AS
  SELECT employee_id,Last_name,salary
 FROM
   employees;

A view emp_dep criada foi baseada na tabela EMPLOYEES, essa view n�o possui qualquer restriç�o.

Listagem 3: Criando a View Emp_dep_const

CREATE VIEW emp_dep_const 
 AS
  SELECT employee_id,Last_name,salary
 FROM
   employees
 WITH READ ONLY constraint apenas_leitura;

A view emp_dep_const criada foi baseada na tabela EMPLOYEES, por�m ao contr�rio da view emp_dep essa view s� aceita leitura, nenhuma outra operaç�o como delete, insert e etc.

Listagem 4: Criando a View Emp_dep_option_Const

CREATE VIEW emp_dep_option_Const 
 AS
  SELECT employee_id,Last_name,salary
 FROM
   employees
 WHERE department_id = 20  
 WITH CHECK OPTION CONSTRAINT emp_20;

A view emp_dep_option_Const criada foi baseada na tabela EMPLOYEES, essa view possui a cl�usula WITH CHECK OPTION, onde foi criada a constraint emp_20. Essa constraint se baseia no que est� na cl�usula WHERE, que nesse caso s� ir� recuperar as linhas onde o department_id seja igual a 20. Com a constraint emp_20 significa que as operaç�es DML s� poder�o ser aplicadas onde o departamento for 20.

Listagem 5: Criando a View Emp_dep_apelido

CREATE VIEW emp_dep_apelido(Cod_Func,Nome_Func,Salario) 
 AS
  SELECT employee_id,Last_name,salary
 FROM
   employees;

A view emp_dep_apelido criada foi baseada na tabela EMPLOYEES, e nesse caso foram usados apelidos para as colunas que ir�o compor a view, dessa forma na hora de aplicar operaç�es DML sobre essa view, pode-se utilizar os apelidos usado em sua criaç�o(Cod_Func, Nome_Func, Salario). O n�mero de apelidos deve corresponder ao numero de colunas envolvidas na subconsulta.

Listagem 6: Criando a View Emp_dep_apelido_as

CREATE VIEW emp_dep_apelido_as
 AS
  SELECT 
  employee_id as Cod_Func,
  Last_name as Nome,
  salary as Salario
 FROM
   employees;

A view emp_dep_apelido_as criada acima tamb�m foi baseada na tabela EMPLOYEES, e nesse caso foram usados apelidos para as colunas que ir�o compor a view, dessa forma na hora de aplicar operaç�es DML sobre essa view pode-se utilizar os apelidos usados em sua criaç�o(Cod_Func, Nome_Func, Salario). Nessa view os apelidos para as colunas foram passados direto na subconsulta, mas o resultado � mesmo da emp_dep_apelido.

Listagem 7: Criando a View V_Grupo

CREATE FORCE VIEW V_Grupo 
 AS
  SELECT grupo_id,descricao
 FROM
   Grupo;

A view V_Grupo criada foi baseada na tabela GRUPO, por�m a tabela grupo n�o existe no banco, ent�o, para que fosse poss�vel a criaç�o dessa View sem uma tabela base foi usada a opç�o FORCE.

CRIANDO VIEW COMPLEXA

Listagem 8: Criando a View Emp_e_dep

CREATE VIEW emp_e_dep 
 AS
  SELECT E.employee_id,E.Last_name,D.department_name
 FROM
   employees E,
   departments D
 WHERE
   D.DEPARTMENT_ID = E.EMPLOYEE_ID;

A view emp_e_dep criada foi baseada em duas tabelas, EMPLOYEES e DEPATMENTS. Nesse momento deixa de ser uma view simples e passa a ser uma view complexa, pois est� sendo aplicada uma subconsulta sobre duas tabelas.

Listagem 9: Criando a View Dep_mediasal

CREATE VIEW dep_mediasal 
 AS
SELECT
 department_id as Cod_Departamento,
 AVG(salary)  as Media_Salario
FROM     employees 
GROUP BY department_id;

A view dep_mediasal acima foi baseada em uma tabela: EMPLOYEES. Nesse caso, mesmo sendo utilizada apenas uma tabela, foi usada a funç�o de agrupamento AVG e os registros est�o sendo agrupados atrav�s do Group By.

ALTERANDO UMA VIEW

Para alterar uma View voc� precisar� usar OR REPLACE que � a opç�o para substituir uma view j� existente.

Listagem 10: Alterando a View Emp_dep

CREATE OR REPLACE VIEW emp_dep 
 AS
  SELECT employee_id,Last_name,email,salary
 FROM
   employees;

A view emp_dep est� sendo alterada com o OR REPLACE, nesse caso est� sendo inserida a coluna EMAIL que n�o estava na criaç�o da View no primeiro momento. A emp_dep ser� substitu�da j� com a nova coluna que foi inclu�da na subconsulta.

Listagem 11: Alterando a View Emp_e_dep

CREATE OR REPLACE VIEW emp_e_dep 
 AS
  SELECT E.Last_name,D.department_name
 FROM
   employees E,
   departments D
 WHERE
   D.DEPARTMENT_ID = E.EMPLOYEE_ID;

A view emp_e_dep est� sendo alterada com o OR REPLACE, nesse caso est� sendo exclu�da a coluna EMPLOYEE_ID que estava na criaç�o da View no primeiro momento. A emp_e_dep ser� substitu�da com a coluna EMPLOYEE_ID j� removida da subconsulta.

REMOVENDO UMA VIEW

Quando uma view n�o � mais necess�ria voc� pode remover do mesmo modo que s�o removidos outros objetos do Oracle.

Listagem 12: Removendo a View Emp_e_dep

Drop view emp_e_dep;Nota: um dica pra criaç�o de uma view � criar e testar antes a subconsulta e ap�s isso aplicar a sintaxe de criaç�o de view.

Finalizamos por aqui esse artigo, at� o pr�ximo.

Abraço.