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

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

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

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

4. Insira 3 linhas de dados pela view criada no exercício 3, e explique se você conseguiu inserir ou não esses dados;

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

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

7. Crie a tabela dependentes do exercício 5, e faca a inserção de 5 dependentes, utilizando a sequence do exercício 6;

8. Crie um índice chamado IDX_DEP_NOME na tabela dependentes, no campo NOME_DEP;