Início / dba / Perguntas / 14631
Accepted
René Nyffenegger
Asked: 2012-03-09 04:12:26 +0800 CST

É (e se como) é possível no Oracle "inserir em <table> valores <rowtype-variable>" se <table> tiver colunas virtuais

  • 772

aqui está uma mesa

create table tq84_virtual_test_without (
  col_1 number,
  col_2 number,
  col_3 number,
  col_4 number,
  col_5 number
);

com a regra de que col_5o valor é a soma das outras quatro colunas.

Assim, a tabela é preenchida de acordo:

insert into tq84_virtual_test_without values( 1, 2, 3, 4, 10);
insert into tq84_virtual_test_without values( 3, 8, 7, 5, 23);

commit;

Agora, digamos que seja necessário copiar uma linha e alterar apenas o valor de uma coluna. É claro que isso pode ser feito de maneira bastante elegante (imho, isto é) com uma variável de tipo de linha , assim

declare
  r tq84_virtual_test_without%rowtype;
begin

  select * into r from tq84_virtual_test_without where col_2 = 8;

  r.col_4 := r.col_4 - 2;
  r.col_5 := r.col_5 - 2;

  insert into tq84_virtual_test_without values r;

end;
/

Isso é elegante porque não sobrecarrega o código-fonte com insert into ... (col_1, col_2...) values (.., ..)instruções e gostaria de manter esse recurso, se possível.

Por outro lado, col_5é um candidato perfeito para uma coluna virtual . Então, aqui está quase a mesma coisa, mas col_5sendo uma coluna virtual:

create table tq84_virtual_test_with (
  col_1 number,
  col_2 number,
  col_3 number,
  col_4 number,
  col_5 as (col_1 + col_2 + col_3 + col_4) virtual
);

insert into tq84_virtual_test_with values( 1, 2, 3, 4, default);
insert into tq84_virtual_test_with values( 3, 8, 7, 5, default);

commit;

Agora, e isso é lamentável, a seguinte construção não funciona mais:

declare
  r tq84_virtual_test_with%rowtype;
begin

  select * into r from tq84_virtual_test_with where col_2 = 8;

  r.col_4 := r.col_4 - 2;

--     
--  ORA-54013: INSERT operation disallowed on virtual columns
--
  insert into tq84_virtual_test_with values r;

end;
/

Então, ainda é possível (e em caso afirmativo, como) usar essa variável de tipo de linha junto com colunas virtuais ?

oracle oracle-11g-r2

4 respostas

  1. Best Answer

    Use uma exibição que exclua as colunas virtuais para fazer a manipulação. Acabei de testar isso e funciona:

    create view v_tq84_virtual_test_with as ( select col_1, col_2, col_3, col_4 from tq84_virtual_test_with );

declare
  r v_tq84_virtual_test_with%rowtype;
begin

  select * into r from v_tq84_virtual_test_with where col_2 = 8;

  r.col_4 := r.col_4 - 2;

  insert into v_tq84_virtual_test_with values r;

end;
/

    Tanto quanto eu posso dizer, esta é a única maneira de contornar sua necessidade de usar %rowtype.

    • 11

  2. Eu realmente gosto da resposta de Phil (+1). Aqui está uma alternativa que você provavelmente já considerou. Eu o incluo aqui apenas para completar. Ele atende ao seu requisito para um tipo de linha, mas certamente não é tão elegante.

    insert into ... (col_1, col_2...) VALUES (vRowType.Col_1, vRowType.Col_2...);

    O outro lado dessa moeda é que você pode definir um cursor apenas com as linhas necessárias, mas isso leva a ainda mais código que seria bom evitar.

    DECLARE
   CURSOR c1 IS
      SELECT col_1, col_2 ... FROM t1 WHERE ...;
   r1 c2%RowType;
BEGIN
  OPEN C1;
  FETCH C1 INTO L_REC;
  INSERT INTO t1 (col_1, col_2...) VALUES r1;
  CLOSE C1;   
END;
/
    • 3

  3. Embora esta seja uma pergunta antiga, vou postar esta resposta para o futuro:

    Se você estiver usando 12c, pode adicionar uma coluna como INVISÍVEL, o que evitará esse problema. Usando isso, você pode continuar usando %rowtype, mas a coluna também não aparecerá nas select *instruções.

    Isso pode ser conveniente para colunas adicionadas apenas para desempenho.

    Artigo da Oracle sobre colunas invisíveis

    • 1

  4. Eu recomendaria usar uma tabela para armazenar os dados da tabela e uma exibição para armazenar os resultados calculados desses dados. Misturar os dois em um objeto híbrido apenas confunde a situação para futuros esforços de desenvolvimento.

    Um desenvolvedor daqui a cinco anos pode presumir que precisa atualizar todas as colunas da tabela para obter totais precisos. Se, em vez disso, ele estivesse consultando a partir de uma exibição, ele teria que examinar a definição da exibição para atualizar os dados, e a maneira correta de atualizar os dados se tornaria rapidamente aparente.

    Colunas virtuais são legais, mas essas coisas devem ser usadas com moderação.

    • 0

relate perguntas