Criação de outros objetos de esquema

Além das tabelas, outros objetos de esquema essenciais são visualizações, sequências, índices e sinônimos. Uma visualização é uma tabela lógica ou virtual. Sinônimos são simplesmente nomes alternativos para objetos de banco de dados. Os sinônimos também simplificam a escrita de consultas e fornecem um elemento de segurança do sistema, disfarçando o nome real de um objeto de banco de dados. Sequências são objetos de banco de dados especiais que suportam a geração automática de valores inteiros e são frequentemente usados gere valores de chave primária para tabelas. Índices são criados nas colunas da tabela para facilitar a recuperação rápida de informações das tabelas.

Visualizações

Uma visão de banco de dados é uma tabela lógica ou virtual baseada em uma consulta. As visões são consultadas exatamente como tabelas. Isso significa que da sua perspectiva como desenvolvedor ou do ponto de vista de um usuário do sistema de banco de dados, uma visão se parece com uma tabela. A definição de uma visão como um objeto é armazenado em um dicionário de dados do banco de dados; no entanto, uma visão não armazena dados. Um banco de dados também armazena o plano de execução para a criação de uma visão - isso significa que os dados podem ser recuperados rapidamente através do uso de uma visão, mesmo que os dados reais apresentados por uma consulta SELECT de uma visão não sejam armazenados como parte de uma visão. Em vez disso, os dados são "reunidos" cada vez que uma visão é consultada nas tabelas de banco de dados para as quais uma visão é definida - são chamadas de tabelas base.

A sintaxe geral é fornecida abaixo.

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW [ViewName]
[(Column Alias Name...)] 
AS [Query]
[WITH [CHECK OPTION] [READ ONLY] [CONSTRAINT]];

Da sintaxe,

A opção FORCE permite que uma visão seja criada mesmo se uma tabela base à qual a visão faz referência ainda não exista. Esta opção é usada para criar uma visão antes da criação real das tabelas base e dos dados que a acompanham.

A opção NOFORCE é o oposto de FORCE e permite que um usuário do sistema crie uma visão se ele tiver os privilégios necessários para criar uma visão, e se as tabelas das quais a visão foi criada já existirem. Esta é a opção padrão.

A opção WITH READ ONLY permite a criação de uma visualização que é somente leitura. Você não pode usar os comandos DELETE, INSERT ou UPDATE para modificar dados para uma visualização somente leitura.

A cláusula WITH CHECK OPTION permite a atualização de linhas que podem ser selecionadas por meio da visualização. Também permite especificar restrições de valores. A cláusula CONSTRAINT funciona em conjunto com a cláusula WITH CHECK OPTION para permitir que um administrador de banco de dados atribua um nome exclusivo à CHECK OPTION. Se um administrador de banco de dados omitir a cláusula CONSTRAINT, o Oracle atribuirá automaticamente à restrição um nome gerado pelo sistema que não será muito significativo.

Tipos de vistas

Uma visão simples é criada em cima de apenas uma tabela. É uma consulta SELECT simples sem funções ou cláusula de grupo, mas apenas seleção de colunas da tabela sem qualquer transformação. Se uma DML for executada na visão, ela será refletida imediatamente na tabela base.

Uma visão complexa é criada em várias tabelas usando joins. Ela pode conter funções SQL, agrupar por funções. Mas, como a visão está em vários dados e a seleção de colunas também não é simples, ela não permite a operação DML nela.

Ilustração

Simple View: A visualização simples abaixo seleciona o nome do funcionário, id do departamento e salário para os funcionários com ID do TRABALHO como DEV.

CREATE OR REPLACE VIEW v_emp_dev
AS
SELECT first_name, department_id, salary
FROM employees
WHERE job_id = 'DEV';

Complex view: O exemplo abaixo mostra o nome do departamento, o salário médio sorteado no departamento e a contagem de funcionários que trabalham nele.

CREATE OR REPLACE VIEW EMP_VU
AS
SELECT department_name, AVG (salary) avg_sal, COUNT (first_name) count
FROM employees E, departments D
WHERE E.department_id = D.department_id
GROUP BY department_name;

DESCRIBE [view name]descreve a estrutura da visualização. As colunas são listadas na mesma sequência da definição da vista.

Operações DML em uma visualização

As operações DML podem ser facilmente exercitadas em visualizações simples. Conforme afirmado anteriormente, as operações de inserção, atualização e exclusão acontecem na verdade na tabela base.

Ao executar uma instrução UPDATE, DELETE ou INSERT DML em uma visão, você está, na verdade, manipulando as linhas de dados da tabela ou tabelas base nas quais a visão está definida. Existem restrições ao uso das instruções UPDATE, DELETE e INSERT com visualizações. Primeiro, para usar a instrução UPDATE, DELETE ou INSERT com uma visualização, a visualização deve ser atualizável. Uma visualização é atualizável se a cláusula SELECT não especificar nenhuma função agregada na listagem SELECT. Além disso, a visualização não poderia foram criados por meio do uso de uma cláusula ou cláusulas GROUP BY, DISTINCT ou UNION. É permitido que funções agregadas sejam usadas em uma subconsulta SELECT em uma cláusula FROM. Além disso, a visualização não pode ter nenhuma coluna derivada na lista SELECT. Em seguida, se uma visão for criada como resultado de uma operação JOIN (uma visão de junção), as instruções UPDATE e INSERT só podem modificar ou inserir linhas em uma das tabelas base por vez. Você não pode modificar linhas de duas ou mais tabelas com uma única instrução de linguagem de manipulação de dados (DML). Por fim, uma instrução DELETE só pode ser executada em uma exibição se uma tabela for referenciada em uma cláusula FROM. Isso significa simplesmente que você não pode excluir linhas de uma tabela que não foi especificada.

Cláusula WITH CHECK OPTION

WITH CHECK OPTION é uma cláusula opcional que especifica o nível de verificação a ser feito ao inserir ou atualizar dados por meio de uma visão. Se uma visão for criada usando a cláusula WITH CHECK OPTION, cada linha que é inserida ou atualizada na tabela base por meio da visão deve estar de acordo com a definição de exibição. Observe que a opção não pode ser especificada se a exibição for criada como somente leitura.

Por exemplo, uma visão V_EMP_DEV é criada para funcionários que são desenvolvedores (JOB_ID = DEV).

CREATE OR REPLACE VIEW v_emp_dev
AS
SELECT first_name, department_id, salary, 
FROM employees
WHERE job_id = 'DEV'
WITH CHECK OPTION empvu_dev;

Um usuário tenta atualizar o salário de um funcionário de RH por meio da visualização, mas encontra uma exceção. É porque a visualização foi criada COM OPÇÃO DE VERIFICAÇÃO.

UPDATE v_emp_dev
SET salary = salary+500
WHERE JOB_ID = 'HR';
ORA-01402: view WITH CHECK OPTION where-clause violation

Se fosse uma visão simples, a instrução UPDATE não teria gerado nenhuma exceção.

Abandonando a vista

Um administrador de banco de dados (DBA) ou proprietário de visualização pode eliminar uma visualização com a instrução DROP VIEW. Se uma visualização tiver restrições definidas, você precisará especificar a cláusula CASCADE CONSTRAINTS ao eliminar uma visualização; caso contrário, a instrução DROP VIEW falha no processamento. Se outra visão ou outro objeto de banco de dados, como um sinônimo ou visão materializada (ambos os objetos são discutidos posteriormente neste capítulo) faz referência a uma visão eliminada, o Oracle não descarta esses objetos de banco de dados; em vez disso, o Oracle os marca como inválidos. Você pode descartar esses objetos inválidos ou redefini-los para torná-los válidos novamente.

O comando DROP VIEW abaixo elimina a view EMP_VU do banco de dados.

DROP VIEW EMP_VU;

Sequências

O Oracle oferece a capacidade de gerar sequências de números exclusivos para esse tipo de uso, e eles são chamados de sequências. Geralmente, as sequências são usadas para gerar valores inteiros sequenciais exclusivos que são usados ​​como valores de chave primária em tabelas de banco de dados. Uma sequência de números pode ser gerado em ordem crescente ou decrescente. Observe que um número, uma vez gerado por sequência, não pode ser revertido.

Sintaxe

CREATE SEQUENCE <sequence name>
[INCREMENT BY < number >]
[START WITH < start value number>]
[MAXVALUE < MAXIMUM VLAUE NUMBER>]
[NOMAXVALUE]
[MINVALUE < minimum value number>]
[CYCLE | NOCYCLE]
[CACHE < number of sequence value to cache> | NOCACHE]
[ORDER | NOORDER];

Da sintaxe,

A instrução CREATE SEQUENCE deve especificar um nome de sequência exclusivo. Esta é a única cláusula exigida na declaração. Se você não especificar nenhuma das outras cláusulas, todos os números de sequência gerados seguirão as configurações padrão do Oracle.

A cláusula INCREMENT BY determina como uma sequência aumenta conforme cada número é gerado. O incremento padrão é um; no entanto, se você tiver um bom motivo para uma sequência pular números, poderá especificar um incremento diferente. Um incremento numérico positivo gera números de sequência ascendentes com um intervalo igual ao intervalo selecionado. Um incremento numérico negativo gera números de sequência descendentes.

A cláusula START WITH especifica o valor numérico inicial para a sequência - o número inicial padrão é um. Além disso, você deve especificar um valor inicial se já tiver algumas linhas com dados na coluna que agora armazenarão valores de sequência.

A cláusula MAXVALUE especifica o valor máximo para o qual uma sequência pode ser incrementada. Na ausência de MAXVALUE, o valor máximo permitido que pode ser gerado para uma sequência é bastante grande, 10 elevado à 27ª potência - 1. O padrão é NOMAXVALUE.

A cláusula MINVALUE especifica o valor mínimo de uma sequência para uma sequência decrescente (aquela que gera números em ordem decrescente). O padrão é NOMINVALUE.

A cláusula CYCLE especifica que os valores da sequência podem ser reutilizados se a sequência atingir o MAXVALUE especificado. Se a sequência é executada em ciclos, os números são gerados começando novamente no valor START WITH.

A cláusula CACHE pode melhorar o desempenho do sistema, permitindo que o Oracle gere um lote especificado de números sequenciados a ser armazenado na memória cache.

Se você especificar CACHE sem especificar um número, o tamanho do cache padrão é de 20 números de sequência. Opcionalmente, você pode especificar NOCACHE para evitar o cache de números de sequência.

A cláusula ORDER especifica que os números de sequência são alocados na ordem cronológica exata em que são solicitados.

NEXTVAL e CURRVAL

Os valores de sequência são gerados por meio do uso de duas pseudocolunas chamadas currval e nextval. Uma pseudocoluna se comporta como uma coluna de tabela, mas as pseudocolunas não são realmente armazenadas em uma tabela. Na primeira vez que você selecionar a pseudocoluna nextval, o valor inicial a sequência é retornada. As seleções subsequentes da pseudocoluna nextval fazem com que a sequência aumente conforme especificado na cláusula INCREMENT BY e retorne o valor da sequência recém-gerado. A pseudocoluna currval retorna o valor atual da sequência, que é o valor retornado por a última referência a nextval.

Em uma sessão, NEXTVAL, e não o CURRVAL, deve ser a primeira ação na sequência. Isso porque em uma sessão, quando o NEXTVAL gera o primeiro número da sessão a partir da sequência, o Oracle mantém o valor atual no CURRVAL.

Sintaxe:

Sequence.NEXTVAL
Sequence.CURRVAL

Pontos a serem observados -

• CURRVAL e NEXTVAL só podem ser usados ​​no SQL externo de uma instrução select.

• CURRVAL e NEXTVAL podem ser usados ​​na instrução INSERT para substituir uma chave primária de coluna. Ele pode ser usado como uma cláusula de subconsulta e também na cláusula VALUES.

• CURRVAL e NEXTVAL podem ser usados ​​para atualizar valores nas tabelas.

• CURRVAL e NEXTVAL não podem estar na lista de seleção VIEW, com a palavra-chave DISTINCT, com cláusulas GROUP BY, HAVING ou ORDER BY e expressão DEFAULT em uma instrução CREATE TABLE ou ALTER TABLE.

Modificando a sequência

O proprietário da sequência pode modificar uma sequência para alterar apenas os atributos como cláusulas INCREMENT BY value, MINVALUE, MAXVALUE, CYCLE ou CACHE. Observe que as alterações feitas seriam refletidas nos próximos números.

Sintaxe:

ALTER SEQUENCE [sequence name]
INCREMENT BY n
MAXVALUE n
NOCACHE
NOCYCLE

Abandonando a sequência

O comando DROP SEQUENCE elimina as sequências que precisam ser recriadas ou não são mais necessárias.

DROP SEQUENCE [sequence name]

Índices

Índices são os objetos de banco de dados usados ​​para ajustar o desempenho da consulta SELECT. Existem diferentes tipos de índices, incluindo aqueles usados ​​para impor restrições de chave primária, índices exclusivos, índices não exclusivos e índices concatenados, entre outros. Sem índices, as consultas exigiriam que o Oracle varrasse todas as linhas de uma tabela para retornar as linhas necessárias para a tabela de resultados. Um índice é criado nas colunas da tabela, que então armazena todos os valores da coluna sob o segmento de índice. Diferentemente da sequência, os índices são tabela específicos. Eles são eliminados automaticamente quando a tabela é eliminada.

Os índices podem ser criados automática ou manualmente. Ao especificar uma restrição PRIMARY KEY ou UNIQUE, o Oracle criará automaticamente um índice exclusivo para oferecer suporte à recuperação rápida de dados para a tabela especificada.

Alternativamente, o usuário pode criar índices manualmente para otimizar o desempenho da consulta. Índices criados manualmente podem ser exclusivos ou não exclusivos. Índices não exclusivos podem ser B-Tree, Bitmap ou índice baseado em função. Por padrão, o Oracle cria índices B-Tree em colunas .Aqui está a sintaxe

Sintaxe

CREATE [UNIQUE][BITMAP]INDEX index
ON table (column [, column]...);

Observe que UNIQUE e BITMAP devem ser especificados apenas para índices exclusivos e de bitmap. Por padrão, o Oracle cria índices B-Tree para índices normais.

Um índice composto (também chamado de índice concatenado) é um índice criado em várias colunas de uma tabela. As colunas em um índice composto podem aparecer em qualquer ordem e não precisam ser colunas adjacentes na tabela. Os índices compostos aumentam a velocidade de recuperação de linhas para consultas nas quais a cláusula WHERE faz referência a todas ou à parte inicial das colunas no índice composto. Um índice pode conter no máximo 32 colunas.

Por exemplo, um usuário cria o índice IDX_EMP na coluna HIRE_DATE da tabela EMPLOYEES. O uso do índice reduzirá a E / S do disco percorrendo a varredura do caminho indexado e localizando os dados que são filtrados na coluna HIRE_DATE.

CREATE INDEX IDX_EMP ON employees(hire_date);

Descartando o índice

Os índices não podem ser modificados, mas podem ser alterados para fins de análise, reconstrução ou cálculo de estatísticas. Se a definição do índice tiver que ser modificada, ela deverá ser eliminada e recriada. A sintaxe do comando DROP INDEX é simples.

DROP INDEX index_name;

Sinônimos

Um sinônimo é um pseudônimo, ou seja, uma forma abreviada usada para simplificar a tarefa de referenciar um objeto de banco de dados. O conceito é análogo ao uso de apelidos para amigos e conhecidos. A referência a um objeto pertencente a outro usuário requer o nome do esquema para ser prefixado com ele. Com a ajuda de um sinônimo, você reduz o esforço de referenciar o objeto junto com o nome do esquema. Dessa forma, o sinônimo fornece transparência de localização porque o nome do sinônimo oculta o nome do objeto real e seu proprietário.

Existem duas categorias de sinônimos, público e privado. Um sinônimo público pode ser usado para permitir fácil acesso a um objeto para todos os usuários do sistema. Na verdade, o indivíduo que cria um sinônimo público não possui o sinônimo - em vez disso, ele pertencerá ao grupo de usuários PÚBLICO que existe no Oracle. Os sinônimos privados, por outro lado, pertencem ao usuário do sistema que os cria e residem naquele esquema do usuário.

Sintaxe

CREATE [PUBLIC] SYNONYM [synonym name] 
FOR OBJECT;

Um usuário do sistema pode conceder o privilégio de usar sinônimos privados de sua propriedade para outros usuários do sistema. Para criar sinônimos, você precisa ter o privilégio CREATE SYNONYM. Além disso, você deve ter o privilégio CREATE PUBLIC SYNONYM para criar sinônimos públicos .Se um sinônimo for declarado como público, o nome do sinônimo não pode já estar em uso como um sinônimo público. A tentativa de criar um sinônimo público que já existe fará com que o comando CREATE PUBLIC SYNONYM falhe e o Oracle retornará o ORA-00955: nome já é usado por uma mensagem de erro de objeto existente.

Ilustração

Considere dois usuários U1 e U2.U1 com acesso à tabela EMPLOYEES. Portanto, para permitir o acesso na tabela EMPLOYEES também ao U2, um sinônimo pode ser criado no esquema U2. O acesso deve ser concedido por U1 a U2.

CONN U2/U2
SQL> CREATE SYNONYM EMP_SYN	FOR U1.employees;

CONN U1/U1
SQL> GRANT ALL ON EMP_SYN TO U2;

CONN U2/U2
SQL> SELECT * FROM EMP_SYN;

Abandonando um Sinônimo

Um usuário pode abandonar o sinônimo que possui. Para eliminar um sinônimo público, você deve ter o privilégio DROP PUBLIC SYNONYM.

DROP SYNONYM EMP_SYN;