主页 / dba / 问题 / 14631
Accepted
René Nyffenegger
Asked: 2012-03-09 04:12:26 +0800 CST

如果 <table> 具有虚拟列，Oracle 是否可以（以及如何）“插入 <table> 值 <rowtype-variable>”

  • 772

这是一张桌子

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

规则是col_5's value 是其他四列的总和。

所以表格被相应地填充：

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;

现在，比如说，需要复制一行并只更改一列的值。这当然可以使用rowtype-variable非常优雅地完成（恕我直言） ，就像这样

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;
/

这很优雅，因为它不会用insert into ... (col_1, col_2...) values (.., ..)语句使源代码混乱，如果可能的话，我想保留这个特性。

另一方面，col_5是虚拟列的完美候选者。所以，这几乎是一样的，但它col_5是一个虚拟列：

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;

现在，不幸的是，以下构造不再起作用：

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;
/

那么，这是否仍然有可能（如果是，如何）将此行类型变量与虚拟列一起使用？

oracle oracle-11g-r2

4 个回答

  1. Best Answer

    使用排除虚拟列的视图来执行操作。我刚刚测试过它并且它有效：

    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;
/

    据我所知，这是解决您使用 %rowtype 要求的唯一方法。

    • 11

  2. 我真的很喜欢菲尔的回答（+1）。这是您可能已经考虑过的替代方案。我在这里包含它只是为了完整性。它满足您对行类型的要求，但肯定没有那么优雅。

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

    这枚硬币的另一面是您可以定义一个仅包含您需要的行的游标，但这会导致更多的代码可以避免。

    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. 虽然这是一个老问题，但我会在未来发布这个答案：

    如果您使用的是 12c，则可以添加一列作为 INVISIBLE，这将避免此问题。使用它，您可以继续使用%rowtype，但该列也不会出现在select *语句中。

    这对于仅出于性能而添加的列很方便。

    关于隐形列的 Oracle 文章

    • 1

  4. 我建议使用表来存储表数据和视图来存储从该数据计算的结果。将两者混合在一个混合对象中只会混淆未来开发工作的情况。

    五年后的开发人员可能会假设他需要更新表的所有列才能获得准确的总数。相反，如果他从视图中查询，他将不得不查看视图定义以更新数据，并且更新数据的正确方法将很快变得显而易见。

    虚拟列很酷，但应该谨慎使用这些东西。

    • 0

相关问题