Data de postagem: 09/11/2009 12:19:04
Uma feature interessante do Oracle são colunas virtuais. Antigamente o problema era resolvido com triggers, porém as colunas (consideradas virtuais) poderiam ser preenchidas atendendo os quesitos de preenchimento da função CHECK.
Observem um exemplo prático da utilização de colunas virtuais da versão 11.
Criando a tabela
Criando a tabela t1 com três colunas: c1, c2 e c3, todas do tipo number
SQL> create table t1(c1 number, c2 number, c3 number generated always as (c1+c2) virtual); Tabela criada.Uma lista do conteúdo (como não houve inserção de dados, não haverá nada)
SQL> select * from t1;nao ha linhas selecionadas
Testes com dados reais
Inserindo dados
SQL> insert into t1 (c1, c2) values (1,1); 1 linha criada.SQL> insert into t1 (c1, c2) values (2, 2); 1 linha criada.SQL> insert into t1 (c1, c2) values (3, 3); 1 linha criada.SQL> insert into t1 (c1, c2) values (3, 2); 1 linha criada.SQL> commit; Commit concluido.Agora que há dados, uma consulta para listar o conteúdo
SQL> select * from t1; C1 C2 C3 ---------- ---------- ---------- 1 1 2 2 2 4 3 3 6 3 2 5 4 linhas selecionadas.Observa-se que a coluna C3 contém os valores referente a soma das colunas C1 e C2.
Testes com dados incompletos
Um teste preenchendo somente uma das colunas
SQL> insert into t1 (c1) values (5); 1 linha criada. SQL> insert into t1 (c2) values (5); 1 linha criada. SQL> commit; Commit concluido.Consultando os novos valores
SQL> select * from t1; C1 C2 C3 ---------- ---------- ---------- 1 1 2 2 2 4 3 3 6 3 2 5 5 5 6 linhas selecionadas.Nesta etapa, observamos que a coluna C3 não foi preenchida.
Vamos inserir os valores que estão faltando e efetuar a consulta para observar o resultado
SQL> update t1 set c2 = 10 where c1 = 5; 1 linha atualizada. SQL> update t1 set c1 = 5 where c2 = 5; 1 linha atualizada. SQL> commit; Commit concluido. SQL> select * from t1; C1 C2 C3 ---------- ---------- ---------- 1 1 2 2 2 4 3 3 6 3 2 5 5 10 15 5 5 10 6 linhas selecionadas.Observa-se, neste caso, que a coluna C3 só aparecerá quando os quesitos para preenchimento forem satisfeitos.
Testes para inserir dados na coluna virtual
Inserindo dados incorretos
SQL> insert into t1 (c1, c2, c3) values (1,1,1); insert into t1 (c1, c2, c3) values (1,1,1) * ERRO na linha 1: ORA-54013: A operacao INSERT nao e permitida em colunas virtuaisInserindo dados corretos (soma das colunas C1 e C2)SQL> insert into t1 (c1, c2, c3) values (1,1,2); insert into t1 (c1, c2, c3) values (1,1,2) * ERRO na linha 1: ORA-54013: A operacao INSERT nao e permitida em colunas virtuaisObserva-se que o erro ORA-54013 é exibido quando há tentativa de inserção em uma coluna virtual.
O Regis Araújo efetuou uma pergunta pertinente a respeito do assunto, sobre a necessidade de adicionar uma coluna virtual a uma tabela existente.
Efetuei um pequeno teste.
Conteúdo atual da tabela, com a coluna virtual C3 = (C1+C2).
SQL> select * from t1; C1 C2 C3 ---------- ---------- ---------- 1 1 2 2 2 4 3 3 6 3 2 5 5 10 15 5 5 10 6 linhas selecionadas.Eliminando a coluna virtual da tabela existente.
SQL> alter table t1 drop column c3; Tabela alterada.Observando como ficou a tabela.
SQL> select * from t1; C1 C2 ---------- ---------- 1 1 2 2 3 3 3 2 5 10 5 5 6 linhas selecionadas.Adicionando uma coluna virtual C3, agora com a multiplicação das colunas existentes.
SQL> alter table t1 add c3 number generated always as (c1*c2) virtual; Tabela alterada.Observando o resultado.
SQL> select * from t1; C1 C2 C3 ---------- ---------- ---------- 1 1 1 2 2 4 3 3 9 3 2 6 5 10 50 5 5 25 6 linhas selecionadas.-- O resultado mostra a possibilidade de adicionarmos colunas virtuais a tabelas existentes.
Agradeço ao Regis pela dica.