Banco de Dados II – Aula 10

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;
  1. 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;
  1. 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;
  1. Insira 3 linhas de dados pela view criada no exercício 3, e explique se você conseguiu inserir ou não esses dados;
  1. 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;
  1. 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;
  1. Crie a tabela dependentes do exercício 5, e faca a inserção de 5 dependentes, utilizando a sequence do exercício 6;
  1. Crie um índice chamado IDX_DEP_NOME na tabela dependentes, no campo NOME_DEP;

Video

Anúncios

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s

learningdatabase.com.br

Tecnologias em Banco de Dados Relacionais, Modelagem de dados dimencionais, tecnologias SQL Servere e Oracle

Aprendendo Programação

Algorítmos, C, C++,Pascal, Python, R

WikiDBA

by Virendra Yaduvanshi - Microsoft SQL Server Database Architect | Consultant | Blogger | Specialist | DBA | Speaker

Blog - Fabiano Neves Amorim

SELECT * FROM [Coisas Da Minha Cabeça] WHERE dbo.fn_TempoParaPost() < dbo.fn_TempoLivre()

Tércio Costa, Oracle Developer, OCE SQL, ACE Associate

Guia de estudos para certificação ORACLE SQL(1Z0-047, 1Z0-051, 1Z0-061 e 1Z0-071) e PL/SQL(1Z0-144, 1Z0-146 e 1Z0-148)

Strate SQL

Data Adventures with an Architect

%d blogueiros gostam disto: