Use 12c Enhancements to the DEFAULT Clause, Invisible Columns, Virtual Columns and Identity Columns in Table Creation/Alteration

Neste post iremos ver algumas novidades do 12c com respeito a tabelas, tanto no momento da sua criação como na alteração.

INVISIBLE COLUMNS

O valor DEFAULT é VISIBLE. Se definirmos uma coluna como INVISIBLE ela não será listada quando executarmos um SELECT *, apenas se especificarmos o nome da coluna no SELECT LIST. Também não irá ser listada no DESC|DESCRIBE. Devemos especificar na lista de colunas no INSERT. Também não é suportada em external tables, cluster tables, ou temporary tables.

Para especificar uma coluna como VISIBLE ou INVISBLE, devemos colocar a palavra reservada logo após o datatype conforme imagem abaixo. Isso também serve para VIEWs.

column_definition

DEFAULT

Logo após especificarmos se a coluna poderá ser VISIBLE ou INVISIBLE, temos a cláusula DEFAULT. Já conhecemos muito bem essa cláusula, em que defini um valor DEFAULT que será utilizado caso não seja especificado um valor para esta coluna. Acontece que na versão 12c, temos alguns melhoramentos nessa cláusula.

O primeiro melhoramento é a possibilidade de utilizar sequências como valor default. Podemos tanto usar sequence.curval como sequence.nextval. Alguns detalhes são bons de se observar. A sequence já deve existir. Outra coisa é que se der um DROP na sequence o INSERT que user o DEFAULT irá falhar. Outra observação é utilizar o nome completo da sequence, schema.sequence. Se não fizermos isso, o Oracle sempre utilizará o schema do usuário da sessão, então poderá resultar em falhas, uma alternativa seria o uso de PUBLIC SYNOYM. Também temos que ter privilégio de SELECT na SEQUENCE.

Percebam que na primeira imagem acima, existe algo novo. Uma opção ON NULL. Isso quer dizer que quando inserirmos o valor NULL de forma explícita, o valor DEFAULT será utilizado.

IDENTITY

identity_clause

A cláusula IDENTITY é uma alternativa a cláusula DEFAULT. Faz basicamente a mesma coisa que a cláusula DEFAULT utilizando uma sequence, ou seja, auto incrementa o valor de uma coluna simplificando o trabalho de desenvolvimento e também a migração de outro SGBD para Oracle ser mais simples.

A primeira escolha que devemos fazer é entre ALWAYS e BY DEFAULT. Com o ALWAYS, o Oracle sempre vai utilizar a sequence para gerar o valor para a coluna. Se tentarmos especificar um valor diretamente no INSERT ou UPDATE, um erro será gerado, mas se utilizarmos o BY DEFAULT um erro não será gerado. O ON NULL tem o meso comportamento da cláusula DEFAULT que vimos mais acima.

Já o identity_options tem a seguinte sintaxe:

identity_options

AS opções são exatamente as mesmas de CREATE SEQUENCE. A única diferença é a opção de START WITH LIMIT VALUE, no qual só poderá ser utilizado no ALTER TABLE MODIFY. No qual ao modificar uma coluna com essa opção o Oracle irá encontrar o valor máximo ou mínimo(ASC ou DESC) já presente nessa coluna e o próximo valor da sequência será mais o incremento.

Existe algumas restrições que são:

Apenas uma coluna identity por tabela.

O datatype tem que ser numérico.

As constraints NOT NULL e NOT DEFERRABLE são implicitamente aplicadas. Se especificarmos uma constraint inline que entre em conflito com isto, um erro será retornado.

CREATE TABLE AS SELECT não irá herdar a propriedade identity da coluna.

VIRTUAL COLUMN

Uma coluna virtual não é armazenada em disco, em vez disso o seu valor é obtido através de expressões ou funções que já conhecemos muito bem. Podemos utilizá-las em queryes, DML e DDL, podem ser indexadas e coletar estatísticas.

virtual_column_definition

Conforme imagem acima, devemos especificar o nome da coluna junto com o seu datatype como uma outra coluna qualquer. Logo após isso podemos opcionalmente colocar ela como VISIBLE ou INVISIBLE. A opção GENERATED ALWAYS serve apenas para adicionar uma clareza semântica, informando que os valores não serão armazenados em disco, mas que serão gerados sob demanda.

Logo após temos a column_expression, que nada mais é que a lógica que será usada para gerar o valor. Aqui podemos utilizar outras colunas da mesma tabela, constantes ou funções.

Já a palavra reservada VIRTUAL que é opcional também é apenas para uma clareza semântica. Logo após isso temos evaluation_edition_clause e unusable_editions_clause.

evaluation_edition_clause

evaluation_edition_clause

Serve apenas se estamos utilizando uma função PL/SQL editioned. CURRENT EDITION para a edição corrente no qual o DDL está sendo executado. Podemos especificar uma edição através de EDITION edition ou então NULL EDITION que é o mesmo que omitir a evaluation_edition_clause.

unusable_editions_clause

unusable_editions_clause

Serve para especificar edições que serão inutilizáveis. Temos duas opções, a UNUSABLE BEFORE e a UNUSABLE BEGINNING WITH.

UNUSABLE BEFORE

Será inutilizável em edições anteriores a edição corrente em que o DDL é executado(CURRENT EDITION) ou de uma certa edição (EDITION edition)

UNUSABLE BEGINNING WITH

Inutilizável em edições na qual a DDL é executada (CURRENT EDITION) e posteriores. Ou certa edição e posteriores(EDITION edition). E por último a NULL edition que é a mesma coisa que omitir a UNUSABLE BEGINNING WITH.

Agora, por último, algumas restrições:

Não podemos atualizar o valor de uma coluna virtual diretamente no SET de um UPDATE.

Só podem ser utilizadas em HEAP TABLES e não em tabelas index-organized, external, object, cluster, ou temporary.

Na column_expression não podemos: referenciar outra coluna virtual, referenciar outras colunas que não sejam da mesma tabela e o resultado da expressão deverá ser um valor escalar.