O que devemos conhecer sobre SQL/Oracle Parte 10: Criando outros objetos de schema

Criando e usando VIEWS simples e complexas

Uma VIEW (visão) nada mais é que um comando SELECT nomeado, e armazenado na base de dados, sendo acessado da mesma maneira que uma tabela comum. Possui como objetivo ocultar a complexidade da consulta que o forma, seja apenas para méritos de facilidade de uso ou segurança.

A criação de uma VIEW é muito simples, basta utilizar o comando de criação seguido da consulta, vale ressaltar que expressões devem ser nomeadas caso contrário um erro ocorrerá, sua sintaxe é a seguinte:

CREATE VIEW VW_FUNCIONARIOS AS SELECT F.COD, F.NOME FROM FUNCIONARIOS F;

Também é possível criar VIEWs capazes de atualizar a tabela na qual ela é baseada, para tal devemos ter em mente:

• As restrições da tabela devem ser respeitadas;
• Se a consulta utilizar a cláusula GROUP BY ou qualquer outro tipo de agregação ela não será gerada;
• Se a cláusula DISTINCT for utilizada também não será possível à criação;
• Se na cláusula FROM houver referencia a outras tabelas, ou subconsultas, também não será possível;

Não podemos deixar de mencionar que subconsultas utilizadas na cláusula FROM de um comando SELECT são consideradas VIEWs, conhecidas por INLINE VIEWS.

Outra questão importante é a possibilidade de uma VIEW passar para o estado de inválido, nessa situação ela não poderá ser utilizada, para torna-la valida novamente basta utilizar o comando:

ALTER VIEW VW_FUNCIONARIOS COMPILE;

Resumindo:

• VIEW é um comando SELECT armazenado no banco de dados e possui um nome;
• Colunas e expressões utilizadas no comando SELECT se tornam as colunas da VIEW;
• É possível usar VIEWS em comandos SELECT, do mesmo modo que uma tabela é usada;
• É possível fazer INSERT, UPDATE ou DELETE em VIEWS, dependendo das restrições referentes a tabela da VIEW, colunas requeridas (NOT NULL) devem estar listadas, o comando SELECT não pode conter funções agregadas, não pode conter a clausula DISTINCT e também não funciona quando existem mais de uma tabela na clausula FROM (exceto se a chave primaria for preservada no resultado da consulta e as restrições anteriores forem obedecidas);
• INLINE VIEW é uma subconsulta que substitui a referencia de uma tabela na clausula FROM;
• Uma VIEW pode ser tratada como uma tabela, salvo algumas restrições;
• Uma VIEW baseada em uma tabela que posteriormente é alterada devera ser recompilada com o comando ALTER VIEW NOME_VIEW COMPILE;

Criando, mantendo e usando SEQUENCES

Uma SEQUENCE é capaz de gerar números sequenciais, tendo como principal uso a geração de números para chaves primarias, lembrando que não existe nada em sua estrutura que impeça seu uso em outras circunstancias. Uma SEQUENCE possui as seguintes opções:

• INCREMENT BY: valor utilizado para incrementar ou decrementar (caso o valor seja negativo) a cada requisição da SEQUENCE, se omitido assume o valor um;
• START WITH: primeiro numero da SEQUENCE, se omitido assume MINVALUE para SEQUENCES ascendentes, MAXVALUE para descendentes, e no caso de NOMAXVALUE ou NOMINVALUE terem sido escolhidos e valor default passa a ser um;
• MAXVALUE: define o maior valor para a SEQUENCE, se omitido é assumido NOMAXVALUE;
• NOMAXVALUE: define que não existe numero máximo para a SEQUENCE;
• MINVALUE: define o menor numero da SEQUENCE, se omitido assume NOMINVALUE, a menos que ele seja requerido pela presença do CYCLE, em outras situações assume um;
• NOMINVALUE: indica que não existe valor mínimo;
• CYCLE: quando o limite da SEQUENCE é atingido a geração de valores reinicia, quando ascendente e atinge MAXVALUE recebe MINVALUE, quando descendente e atinge MINVALUE recebe MAXVALUE;
• NOCYCLE: quando a SEQUENCE atinge seu limite a geração de números é interrompida, ele é default;

A sintaxe de criação de uma SEQUENCE é a seguinte:

CREATE SEQUENCE SEQ_FUNCIONARIOS START WITH 10 INCREMENT BY 1;

Uma SEQUENCE só pode ser usada para recuperar o próximo valor (NEXTVAL) ou o atual (CURRVAL), quando solicitado o próximo valor a SEQUENCE pega o valor atual incrementa de acordo com suas regras e devolve o novo valor, veja um exemplo de uso:

INSERT INTO FUNCIONARIOS(COD, NOME) VALUES(SEQ_FUNCIONARIOS.NEXTVAL, ‘MARIA’);

Resumindo:

• SEQUENCE é um objeto que gera números de acordo com suas regras;
• Uma SEQUENCE especifica um numero inicial, um numero de incremento (positivo ou negativo), e limites opcionais;
• SEQUENCES são normalmente usadas para alimentar PRIMARY KEY;
• A pseudocoluna NEXTVAL retorna o próximo valor disponível da SEQUENCE, independente da sessão;
• A pseudocoluna CURRVAL retorna o valor atual existente, já definido pelo NEXTVAL, referenciado por sessão e após o uso do NEXTVAL;
• As pseudocolunas são expressões validas e podem ser usadas em qualquer local permitido para expressões, como a clausula SELECT;
• A pseudocoluna NEXTVAL sempre gera um novo valor, mesmo que o comando onde a SEQUENCE esteja falhe;

Criando e mantendo INDEXES

O INDEX é utilizado para agilizar consultas nas tabelas, sendo sempre considerado nas cláusulas WHERE e ORDER BY, logo uma coluna indexada presente nessas cláusulas irá resultar em uma execução mais rápida.

Esse objeto é criado de maneira implícita a cada geração de uma restrição de chave primaria ou única. Ele pode fazer referencia a uma ou mais colunas, quando possui mais de uma coluna é considerado composto. Veja a seguir como criar ou remover um índice:

CREATE INDEX IX_FUNCIONARIO_DEP ON FUNCIONARIOS(COD, DEPARTAMENTO);

DROP INDEX IX_FUNCIONARIO_DEP;

Resumindo:

• Um INDEX é baseado em uma ou mais colunas de uma tabela;
• O INDEX copia os dados da coluna que faz referencia a ele para realizar uma pré-seleção com o intuito de deixar mais rápido as consultas futuras;
• Quando um comando SELECT, UPDATE ou DELETE é executado em uma tabela indexada, e a coluna que possui o INDEX é alterada, ocorre um WORKLOAD automático para atualizar seus dados;
• O INDEX pode ser usado com uma ou mais colunas, quando varias colunas são usadas o INDEX é considerado composto;

Criando SYNONYMS privados e públicos

Um SYNONYM é um apelido que pode ser concedido para tabelas, sequências ou mesmo outros sinônimos. Seu uso se dá principalmente em casos onde a diminuição da complexidade dos nomes se faz necessária. Um sinônimo pode ser considerado publico ou privado, sendo os públicos criados com a cláusula PUBLIC, nesse caso o sinônimo passa a pertencer a uma conta especial do Oracle chamada PUBLIC, onde todos os usuários da base possuem acesso. Se a criação do sinônimo não contiver o PUBLIC então ele passa a pertencer ao usuário da sessão atual, sendo visível apenas a este. É importante salientar que é perfeitamente possível termos, por exemplo, sinônimos com o mesmo nome, sendo um publico e outro privado, a precedência aplicada nesse caso será sempre a do objeto do usuário local. Veja um exemplo de criação:

CREATE PUBLIC SYNONYM VWF FOR VW_FUNCIONARIOS;

Devemos ter em mente que o objeto criado acima passa a ser visível a todos os usuários da base, porem seu uso efetivo se dará apenas para aqueles que tiverem direito de acesso a VIEW em questão.

Resumindo:

• SYNONYM é um apelido para outros objetos do banco de dados;
• Um SYNONYM pode ser criado para objetos que ainda não existem;
• Um SYNONYM privado é criado com o comando CREATE SYNONYM para o usuário da sessão atual;
• O SYNONYM público é criado com o comando CREATE PUBLIC SYNONYM, pertencendo ao usuário PUBLIC;
• SYNONYMS públicos se tornam automaticamente visíveis para todos os usuários do banco, porem para poder usa-los o usuário da sessão atual deve possuir privilégios sobre o objeto ao qual o SYNONYM esta referenciando;