sql - 如何使用简单的更新语句更新表中的 varray 类型？

Question

我有一个表，其中有一列定义为已定义类型的 varray。生产表比下面的例子复杂得多。

我能够在 varray 的类型中选择单个列。但是我想用一个简单的更新语句来更新表(而不是通过 pl/sql 例程)。

如果这不可能(并且我必须通过 pl/sql 例程)，那么编写此代码的聪明而简单的方法是什么？

update (select l.id, t.* from my_object_table l, table(l.object_list) t) 
set value2 = 'obj 4 upd' 
where value1 = 10 

ORA-01733: virtual column not allowed here

这里是类型等的完整示例

create or replace type my_object 
as object(
      value1                    number,
      value2                    varchar2(10),
      value3                    number);

create or replace type my_object_varray as varray(100000000) of my_object;

create table my_object_table (id number not null, object_list my_object_varray);

insert into my_object_table 
   values (1, my_object_varray (
                    my_object(1,'object 1',10), 
                    my_object(2,'object 2',20),
                    my_object(3,'object 3',30) 
                 )
          );

insert into my_object_table 
   values (2, my_object_varray (
                    my_object(10,'object 4',10), 
                    my_object(20,'object 5',20),
                    my_object(30,'object 6',30) 
                 )
          );

select l.id, t.* from my_object_table l, table(l.object_list) t;

Type created.
Type created.
Table created.
1 row created.
1 row created.

        ID     VALUE1 VALUE2         VALUE3
---------- ---------- ---------- ----------
         1          1 object 1           10
         1          2 object 2           20
         1          3 object 3           30
         2         10 object 4           10
         2         20 object 5           20
         2         30 object 6           30

6 rows selected.

Answer 1

我认为您无法从普通 SQL 更新 varray 中的单个对象的值，因为无法引用 varray 索引。 (Alessandro Rossi 发布的链接似乎支持这一点，但不一定是出于这个原因)。当然，我很想被证明是错误的。

我知道您不喜欢 PL/SQL 方法，但如果您确实需要，那么您可以这样做来更新该值:

declare
  l_object_list my_object_varray;
  cursor c is
    select l.id, l.object_list, t.*
    from my_object_table l,
    table(l.object_list) t
    where t.value1 = 10
    for update of l.object_list;
begin
  for r in c loop
    l_object_list := r.object_list;
    for i in 1..l_object_list.count loop
      if l_object_list(i).value1 = 10 then
        l_object_list(i).value2 := 'obj 4 upd';
      end if;
    end loop;

    update my_object_table
    set object_list = l_object_list
    where current of c;
  end loop;
end;
/

anonymous block completed

select l.id, t.* from my_object_table l, table(l.object_list) t;

        ID     VALUE1 VALUE2         VALUE3
---------- ---------- ---------- ----------
         1          1 object 1           10 
         1          2 object 2           20 
         1          3 object 3           30 
         2         10 obj 4 upd          10 
         2         20 object 5           20 
         2         30 object 6           30 

SQL Fiddle .

如果您还要更新其他内容，那么您可能更喜欢返回对象列表并更新了相关值的函数:

create or replace function get_updated_varray(p_object_list my_object_varray,
  p_value1 number, p_new_value2 varchar2)
return my_object_varray as
  l_object_list my_object_varray;
begin
  l_object_list := p_object_list;
  for i in 1..l_object_list.count loop
    if l_object_list(i).value1 = p_value1 then
      l_object_list(i).value2 := p_new_value2;
    end if;
  end loop;

  return l_object_list;
end;
/

然后调用它作为更新的一部分；但您仍然不能直接更新您的内联 View :

update (
  select l.id, l.object_list
  from my_object_table l, table(l.object_list) t
  where t.value1 = 10
)
set object_list = get_updated_varray(object_list, 10, 'obj 4 upd');

SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table

您需要根据相关 ID 进行更新:

update my_object_table
set object_list = get_updated_varray(object_list, 10, 'obj 4 upd')
where id in (
  select l.id
  from my_object_table l, table(l.object_list) t
  where t.value1 = 10
);

1 rows updated.

select l.id, t.* from my_object_table l, table(l.object_list) t;

        ID     VALUE1 VALUE2         VALUE3
---------- ---------- ---------- ----------
         1          1 object 1           10 
         1          2 object 2           20 
         1          3 object 3           30 
         2         10 obj 4 upd          10 
         2         20 object 5           20 
         2         30 object 6           30 

SQL Fiddle .

如果您想进一步隐藏复杂性，您可以创建一个带有调用函数的 instead-of 触发器的 View :

create view my_object_view as
  select l.id, t.* from my_object_table l, table(l.object_list) t
/

create or replace trigger my_object_view_trigger
instead of update on my_object_view
begin
  update my_object_table
  set object_list = get_updated_varray(object_list, :old.value1, :new.value2)
  where id = :old.id;
end;
/

那么更新几乎就是您想要的，至少表面上是这样:

update my_object_view
set value2 = 'obj 4 upd'
where value1 = 10;

1 rows updated.

select * from my_object_view;

        ID     VALUE1 VALUE2         VALUE3
---------- ---------- ---------- ----------
         1          1 object 1           10 
         1          2 object 2           20 
         1          3 object 3           30 
         2         10 obj 4 upd          10 
         2         20 object 5           20 
         2         30 object 6           30 

SQL Fiddle .