Colunas virtuais podem ser utilizadas como as colunas normais das
tabelas, mas seus valores são derivados de expressões e seus resultados não são armazenados em disco. Abaixo segue a sintaxe para a
definição de uma coluna virtual
nome_da_coluna [tipo_de_dado] [GENERATED ALWAYS] AS (expressão)[VIRTUAL]
Se o tipo de dados for
omitido, é determinado com base no resultado da expressão.
O script abaixo
cria e insere dados em uma tabela de testes, chamada EMP_COL_VIRUTAL,
que possui duas colunas virtuais para mostrar a funcionalidade. A primeira
coluna usa a sintaxe mais abreviada,
enquanto o segundo usa a forma mais detalhada.
CREATE TABLE EMP_COL_VIRTUAL(
id NUMBER,
nome VARCHAR2(10),
sobrenome VARCHAR2(10),
salario NUMBER(9,2),
comissao1 NUMBER(3),
comissao2 NUMBER(3),
salario1 AS (ROUND(salario*(1+comissao1/100),2)),
salario2 NUMBER GENERATED ALWAYS AS (ROUND(salario*(1+comissao2/100),2)) VIRTUAL,
CONSTRAINT PK_EMP PRIMARY KEY (id));
INSERT INTO EMP_COL_VIRTUAL (id, nome, sobrenome, salario, comissao1, comissao2)
VALUES (1, 'JOAO', 'CERTO', 100, 5, 10);
INSERT INTO EMP_COL_VIRTUAL (id, nome, sobrenome, salario, comissao1, comissao2)
VALUES (2, 'JOSE', 'ERRADO', 200, 10, 20);
COMMIT;
A consulta abaixo mostra os valores inseridos na
tabela, em destaque as colunas virtuais (salario1 e salario2)
SQL> SELECT * FROM EMP_COL_VIRTUAL;
ID NOME SOBRENOME SALARIO COMISSAO1 COMISSAO2 SALARIO1 SALARIO2
--- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 JOAO CERTO 100 5 10 105 110
2 JOSE ERRADO 200 10 20 220 240
As expressões usadas para gerar as colunas
virtuais, podem ser visualizadas na coluna DATA_DEFAULT das visões [DBA | ALL | USER] _TAB_COLUMNS.
SQL> COLUMN data_default FORMAT A50
SQL> SELECT column_name, data_default
FROM user_tab_columns
WHERE table_name ='EMP_COL_VIRTUAL';
COLUMN_NAME DATA_DEFAULT
------------ ---------------------------------------
ID
NOME
SOBRENOME
SALARIO
COMISSAO1
COMISSAO2
SALARIO1 ROUND("SALARIO"*(1+"COMISSAO1"/100),2)
SALARIO2 ROUND("SALARIO"*(1+"COMISSAO2"/100),2)
8 rows selected.
Adicionando uma coluna virtual na tabela
Para adicionar colunas virtuais em uma tabela pode-se utilizar o mesmo critério, no exemplo abaixo será somado duas colunas gerando uma nova coluna.
SQL> ALTER TABLE EMP_COL_VIRTUAL ADD (TOTAL_COMISSAO AS (comissao1 + comissao2));
Table altered.
SQL> DESC EMP_COL_VIRTUAL
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
NOME VARCHAR2(10)
SOBRENOME VARCHAR2(10)
SALARIO NUMBER(9,2)
COMISSAO1 NUMBER(3)
COMISSAO2 NUMBER(3)
SALARIO1 NUMBER
SALARIO2 NUMBER
TOTAL_COMISSAO NUMBER
SQL> SELECT * FROM EMP_COL_VIRTUAL;
ID NOME SOBRENOME SALARIO COMISSAO1 COMISSAO2 SALARIO1 SALARIO2 TOTAL_COMISSAO
-- ---------- ---------- --------- ---------- ---------- ---------- ---------- --------------
1 JOAO CERTO 100 5 10 105 110 15
2 JOSE ERRADO 200 10 20 220 240 30
SQL> COLUMN data_default FORMAT A50
SQL> SELECT column_name, data_default
FROM user_tab_columns
WHERE table_name ='EMP_COL_VIRTUAL';
COLUMN_NAME DATA_DEFAULT
------------------ --------------------------------------------------
ID
NOME
SOBRENOME
SALARIO
COMISSAO1
COMISSAO2
SALARIO1 ROUND("SALARIO"*(1+"COMISSAO1"/100),2)
SALARIO2 ROUND("SALARIO"*(1+"COMISSAO2"/100),2)
TOTAL_COMISSAO "COMISSAO1"+"COMISSAO2"
Removendo uma coluna virtual na tabela
Para remover virtuais em uma tabela o processo é igual a remoção de uma coluna física.
SQL> alter table EMP_COL_VIRTUAL drop column TOTAL_COMMISSAO;
Table altered.
SQL> SELECT * FROM EMP_COL_VIRTUAL;
ID NOME SOBRENOME SALARIO COMISSAO1 COMISSAO2 SALARIO1 SALARIO2
-- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 JOAO CERTO 100 5 10 105 110
2 JOSE ERRADO 200 10 20 220 240
Algumas observações e restrições sobre colunas virtuais:
§ Os índices definidos para colunas virtuais são equivalentes aos índices baseados em função.
§ Colunas virtuais podem ser referenciadas na cláusula WHERE para a execução de “updates” e “deletes”, mas eles não podem ser manipulados por comandos DML.
§ As tabelas com colunas virtuais podem ser ter seu conteúdo armazenado em “cache”.
§ Funções em expressões devem ser determinísticas, no momento da criação da tabela, mas, posteriormente, pode ser recompilados sem invalidar a coluna virtual. Nesses casos, devem ser tomadas as seguintes medidas depois que a função for recompilada:
Restrição na coluna virtual deve ser desativada e reativada.
Índices na coluna virtual devem ser reconstruídos.
Visões materializadas, baseadas em colunas virtuais devem ser totalmente atualizadas.
O resultado da tabela que está em “cache” deve ser liberado se as consultas em “cache” acessarem a coluna virtual envolvida.
As estatísticas da tabela devem ser recalculadas.
§ Colunas virtuais não são suportadas para índices organizados (organized-indexes), índices externos, índiceoobjeto, índices clusterizados ou para tabelas temporárias.
§ A expressão utilizada para a definição de uma coluna virtual tem as seguintes restrições:
O resultado de uma expressão deve ser um valor escalar. Ele não poderá retornar um tipo de dado definido pelo usuário ou LOB ou LONG RAW.
Referências: Oracle® Database Concepts 11g Release 2 (11.2)
Oracle® Database SQL Language Reference 11g Release 2 (11.2)
Oracle® Database Administration Guide 11g Release 2 (11.2)
MSc.
Rubens Oliveira
DBA Oracle Consultor
