VIEWS
- É um objeto lógico que se baseia em uma SELECT, armazenado no banco de dados;
- Pode ser simples (apenas utilizando uma tabela) ou complexa (podendo ter vários relacionamentos e tipos de joins, grupos, funções de grupo, etc);
- Podem ter colunas virtuais;
- Pode fazer restrição de dados (não colocando a coluna desejada na SELECT)
- Em Views simples, podemos executar comandos DML (insert, update, delete);
- Em Views complexas, na maioria dos casos, não é possível executar comandos DML (possuem funções, grupos);
- Recomendado fazer toda a SELECT primeiramente para depois ser encapsulada na VIEW;
- Sintaxe: CREATE [OR REPLACE] VIEW nome_da_view AS SELECT …
- Quando utilizado o REPLACE, caso já tenha uma VIEW com o mesmo nome, a mesma irá ser sobrescrita;
- Com o parâmetro FORCE, pode-se criar a VIEW sem ter necessariamente as tabelas físicas no banco de dados. (na execução, irá dar erro);
- Com o parâmetro NOFORCE (padrão), pode-se criar a VIEW com tabelas existentes no banco de dados;
- Com o parâmetro WITH CHECK OPTION, apenas as linhas retornadas na VIEW podem ser alteradas;
- Com o parâmetro WITH READ ONLY, somente pode ser executada a VIEW sem a possibilidade de executar comandos DML;
EXEMPLOS:
CREATE OR REPLACE VIEW VW_SALARIO_PROG AS SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY * 13 AS SAL_CLT_ANUAL FROM EMPLOYEES WHERE JOB_ID = 'IT_PROG';
–MOSTRANDO A ESTRUTURA
DESC VW_SALARIO_PROG;
–EXECUTANDO A VIEW
SELECT * FROM VW_SALARIO_PROG;
EXEMPLO 2
CREATE OR REPLACE VIEW VW_SALARIO_PROG (CODIGO,NOME,SOBRENOME,SALARIO_ANUAL) AS SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY * 13 AS SAL_CLT_ANUAL FROM EMPLOYEES WHERE JOB_ID = 'IT_PROG';
VIEWS COMPLEXAS
- As VIEWS complexas geralmente possuem relacionamento com duas ou mais tabelas (Compostas de queries complexas);
- Na maioria das vezes, não é possível executar instruções DML;
- Quando a select possui funções de grupo, GROUP BY, DISTINCT, ROWNUM, Expressões (colunas virtuais), colunas NOT NULL não retornadas na VIEW, NÃO podemos executar comandos DML;
- O parâmetro WITH CHECK OPTION é possível executar instruções INSERT e UPDATE, somente com as linhas que a view pode retornar;
- Também serve para evitar que os dados seja deletados da tabela, sendo somente deletados pela VIEW que possui o WITH CHECK OPTION;
- O parâmetro WITH READ ONLY cria uma VIEW que não será possível executar nenhuma instrução DML, sendo somente executada para leitura de dados;
- DROP VIEW faz a eliminação da view do banco de dados;
EXEMPLO:
CREATE VIEW SALARIOS_FUNC(FUNCAO, MIN_SALARIO, MAX_SALARIO, MED_SALARIO) AS SELECT B.JOB_TITLE, MIN(A.SALARY), MAX(A.SALARY), AVG(A.SALARY) FROM EMPLOYEES A NATURAL JOIN JOBS B GROUP BY B.JOB_TITLE; SELECT * FROM SALARIOS_FUNC; SELECT * FROM DEPARTMENTS; CREATE OR REPLACE VIEW VW_DEPTO_60 AS SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 60 WITH CHECK OPTION CONSTRAINT CK_DEPTO_60; SELECT * FROM VW_DEPTO_60; --IRA ATUALIZAR UPDATE VW_DEPTO_60 SET SALARY = SALARY + 1 WHERE DEPARTMENT_ID = 60; SELECT * FROM VW_DEPTO_60; --NAO IRÁ ATUALIZAR UPDATE VW_DEPTO_60 SET DEPARTMENT_ID = 20 WHERE EMPLOYEE_ID = 103; CREATE OR REPLACE VIEW VW_DEPTO_20 (ID, NOME, CARGO) AS SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID FROM EMPLOYEES WHERE DEPARTMENT_ID = 20 WITH READ ONLY; SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 20; DELETE FROM VW_DEPTO_20 WHERE ID = 202;
ÍNDICES
- Índice serve para que dados sejam organizados para melhorar o processo de procura em tabelas;
- É um dos objetos do banco de dados, que pode ser criado pelo usuário ou pelo Oracle;
- Para campos do tipo Primary Key ou Unique, as chaves são criadas automaticamente pelo Oracle;
- Os índices ocupam mais espaço no banco de dados, sendo marcadores que permitem que os dados sejam acessados rapidamente;
- Pode-se criar ou eliminar índices em uma tabela a qualquer momento;
- Quando uma tabela é excluída, todos os índices dessa tabela também é excluída;
- Os índices exclusivos são criados exclusivamente pelo Oracle, quando definimos as colunas PRIMARY KEY ou UNIQUE. Podem ser criados manualmente, mas é recomendado que o Oracle faça esse gerenciamento;
- Índices NÃO EXCLUSIVOS são criados pelos usuários, a qual tem a finalidade de aumentar a performance das consultas;
- Os índices exclusivos são criados exclusivamente pelo Oracle, quando definimos as colunas PRIMARY KEY ou UNIQUE. Podem ser criados manualmente, mas é recomendado que o Oracle faça esse gerenciamento;
- Índices NÃO EXCLUSIVOS são criados pelos usuários, a qual tem a finalidade de aumentar a performance das consultas;
- Um índice pode ter mais de uma coluna;
- A ordem das colunas influenciam na performance do retorno das linhas;
- A utilização correta dos índices influencia também na performance de retorno das linhas (duas ou mais colunas);
SINTAXE:
CREATE INDEX NOME_INDICE ON NOME_TABELA(COLUNA1, ...); DROP INDEX NOME_INDICE;
- Aconselhável criar um índice quando:
- Quando NÃO existirem muitos valores nulos na coluna consultada;
- Quando as colunas forem muito utilizadas na cláusula WHERE;
- Quando o retorno de registros representa em torno de 2% a 4% (para tabelas com grande quantidade de registros);
- Colunas que possuem faixas de valores, como datas (que são utilizadas no WHERE);
- Aconselhável a NÃO criar um índice quando:
- Quando a tabela sofre comandos DML frequentemente (por exemplo, quando registros são inseridos, todos os índices da tabela também precisarão ser atualizados);
- Tabelas com poucos registros;
- Quando o retorno de registros na maioria das consultas equivalerem a menos de 4% dos registros;
- Sempre que criar índices, é necessário avaliar se haverá mais consultas ou mais inserções/atualizações na tabela;
Exemplo:
CREATE INDEX IDX_PRIMEIRONOME ON EMPLOYEES(FIRST_NAME); SELECT * FROM EMPLOYEES WHERE FIRST_NAME = 'Valli'; DROP INDEX IDX_PRIMEIRONOME; CREATE INDEX IDX_PRIMEIROSEGUNDONOME ON EMPLOYEES (FIRST_NAME,LAST_NAME); SELECT * FROM EMPLOYEES WHERE FIRST_NAME = 'Bruce'; SELECT * FROM EMPLOYEES WHERE FIRST_NAME = 'Bruce' AND LAST_NAME = 'Ernst'; DROP INDEX IDX_PRIMEIROSEGUNDONOME;
SEQUENCES
- Sequence é um dos objetos utilizados para armazenamento de números sequenciais em uma determinada ordem definida na sua criação;
- Geralmente, é utilizado na criação de PRIMARY KEYS, em número sequenciais;
- A utilização desse recurso garante que as chaves primárias não se repetirão;
- É um objeto independente de qualquer tabela;
- Geralmente, é associado a uma tabela para seguir uma sequencia, porém nada impede de ser utilizado em mais de uma tabela;
- A sintaxe de criação da SEQUENCE é:
CREATE SEQUENCE NOME_SEQUENCE INCREMENT BY QUANTIDADE [MAXVALUE VALOR_MAXIMO] [MINVALUE VALOR_MINIMO] [CYCLE] [CACHE QUANTIDADE];
- O incremento padrão é de 1 em 1;
- O parâmetro CYCLE é utilizado quando a numeração chega ao valor máximo (maxvalue) e o número é reiniciado novamente;
- O padrão na criação da SEQUENCE é NOCYCLE;
- O parâmetro CACHE já define uma determinada quantidade de números reservados, para que seja mais rápido a utilização das sequencias (mais performance). O padrão é quantidade de 20;
- Quando utilizamos SEQUENCES, podemos utilizar as pseudocolunas NEXVAL, que retorna o próximo número disponível da sequencia; Mesmo que tenha várias sessões diferentes utilizando a mesma sequence (ainda em transação), o valor nunca se repete;
- Quando utilizamos SEQUENCES, podemos utilizar as pseudocolunas CURRVAL, que retorna o valor atual da sequence daquela seção; Só irá funcionar depois de ter utilizado o NEXTVAL na mesma sessão;
- O Oracle não sabe qual é o valor corrente da sessão antes de ter executado o NEXTVAL;
- Para se utilizar o NEXTVAL e CURRVAL:
- Pode ser utilizada em uma SELECT, a menos que ela seja uma subquery;
- Pode ser utilizada no comando VALUES do INSERT;
- Pode ser utilizada no comando SET do UPDATE;
- No SELECT de uma subquery para realizar um INSERT;
- NÃO se pode utilizar o NEXTVAL e CURRVAL:
- Em um SELECT em uma VIEW;
- Em um SELECT, com DISTINCT, ORDER BY, GROUP BY ou HAVING;
- Em uma coluna definida com o valor DEFAULT;
- Em uma subquery em um DELETE, UPDATE ou em um SELECT;
- Quando utilizamos o parâmetro CACHE QUANTIDADE, o Oracle coloca em cache essa quantidade para que se tenha mais performance na utilização da sequence. O padrão é com a quantidade de 20;
- É indicado quando se tem tabelas que sofram grande quantidade de inserções de dados;
- Depois que a sequence é gerada, o numero não é reaproveitado.
- As sequences são geradas independentes que a transação termine com COMMIT ou ROLLBACK;
- Falhas no sistema do Oracle também podem perder os valores que estão no cache;
- Quando utilizamos uma sequence para várias tabelas, os números não irão ser sequencias para as tabelas utilizadas;
- Podemos utilizar o comando ALTER e DROP para se fazer alteração e exclusão da sequence respectivamente;
- Quando alteramos uma sequence, apenas os próximos números são alterados;
- Não é possível alterar o START WITH de uma sequence, porém pode-se dar drop e create novamente;
- Apenas quem tem permissão de DROP ANY SEQUENCE e o dono podem alterar as sequences;
EXEMPLOS:
CREATE TABLE TESTE_SEQUENCIA ( CODIGO NUMBER(10) NOT NULL PRIMARY KEY, NOME VARCHAR(30) ); DESC TESTE_SEQUENCIA; SELECT * FROM TESTE_SEQUENCIA; CREATE SEQUENCE SQ_TESTE_01 INCREMENT BY 10 START WITH 100 MAXVALUE 300 NOCACHE NOCYCLE;
–UTILIZANDO O NEXVAL
INSERT INTO TESTE_SEQUENCIA (CODIGO, NOME) VALUES (SQ_TESTE_01.NEXTVAL, 'SAITO'); --VERIFICANDO O VALOR INSERIDO NA TABELA SELECT SQ_TESTE_01.CURRVAL FROM DUAL; SELECT * FROM TESTE_SEQUENCIA; INSERT INTO TESTE_SEQUENCIA (CODIGO, NOME) VALUES (SQ_TESTE_01.NEXTVAL, 'ANCHIETA'); --VERIFICANDO O VALOR DA SEQUENCE SELECT SQ_TESTE_01.CURRVAL FROM DUAL; --ELIMITANDO UMA SEQUENCE DROP SEQUENCE SQ_TESTE_01;
SINONIMOS
- SINONIMO é um dos objetos utilizados para se fazer referencia a outro objeto. Geralmente, grande nomes de tabelas podem ser referenciadas com um nome menor ou referencia a objetos de outros usuários podem ser feitos acessos mais facilmente;
- Sinônimo público pode ser acesso por qualquer usuário e o sinônimo privado pode ser acesso somente pelo seu criador;
- Os sinônimos não podem acessar objetos que estão dentro de uma PACKAGE;
- Para sinônimos privados, o nome deve ser único, diferente de outros objetos deste mesmo usuário;
SINTAXE:
CREATE SYNONYM NOME_SINONIMO FOR NOME_OBJETO DROP SYNONYM NOME_SINONIMO
EXEMPLOS:
--com erro, pois o usuário hr nao terá privilégio CREATE PUBLIC SYNONYM TESTE_SIN FOR TESTE_TABELA ; --excluindo o sinonimo publico DROP PUBLIC SYNONYM TESTE_SIN; --criacao do sinonimo privado CREATE SYNONYM TESTE_SIN FOR TESTE_TABELA; SELECT * FROM TESTE_SIN; --excluindo do sinonimo DROP SYNONYM TESTE_SIN;
Exercícios
- Crie uma View chamada VW_EMPREGADOS_DEPTOS, que mostre os dados da tabela employees, tendo os campos código, nome e sobrenome concatenado (espaço entre eles), e email, onde os JOB_IDS sejam IT_PROG e PU_CLERK;
- Prove se é possível fazer inserção de dados através da view criada no exercício 1; Explique o porque de conseguir ou não conseguir;
- Crie uma view chamada VW_EMPREGADOSPORTEMPO, CONTENDO o código do empregado, nome, data de admissão que estejam admitidos entre os damos de 2003 e 2005. Crie com o parâmetro WITH CHECK OPTION;
- Insira 3 linhas de dados pela view criada no exercício 3, e explique se você conseguiu inserir ou não esses dados;
- Crie uma view chamada EMPREGADOS_DEP, que utilize uma tabela com a seguinte estrutura: DEPENDENTES(EMPLOYEE_ID NUMBER(6), COD_DEP NUMBER(2), NOME_DEP VARCHAR(30), DATA_NASC DATE), onde EMPLOYEE_ID E COD_DEP são chaves primárias. A visão deve conter o código de dependente, o nome e a data de nascimento do dependente. Atenção: A tabela ainda não existe. NÃO CRIE A TABELA. Utilize o parâmetro correto para se fazer a view;
- Crie uma sequence chamada SQ_NUMERO_DEP, iniciando pelo numero 10, incremento de 5 em 5, tamanho mínimo 10, tamanho máximo de 500, com 30 números em cache;
- Crie a tabela dependentes do exercício 5, e faca a inserção de 5 dependentes, utilizando a sequence do exercício 6;
- Crie um índice chamado IDX_DEP_NOME na tabela dependentes, no campo NOME_DEP;