如何在 sql (oracle) 中操作 VARRAYS？

BHA胆汁

假设我正在使用一个表 person，并且person 可能有多个姓氏，因此该属性应该是一个包含 3 个元素的 varray（这不是关于存储姓氏的位置），这里是一个用于创建类型姓氏的简单 sql ，表person并在oracle的sql developer（11G XE）中添加一个示例行：

create type lastn as varray(3) of varchar2(10);
CREATE TABLE person
(
    ID NUMBER NOT NULL 
  , last_name lastn
  , CONSTRAINT EXEMPLE_PK PRIMARY KEY 
      (
          ID 
      )
     ENABLE 
);

insert into person values(1,lastn('dani','bilel'));

我知道如何一次更新所有姓氏，但我需要保留现有姓氏并添加其他姓氏，或删除单个姓氏而不影响其他姓氏。简而言之，我希望我的代码像（我不熟悉 PL/SQL）：

insert into table
    (select last_name from example where id=1)
   values lastn('new');

在这种情况下，我想要让姓氏为“bilel”而第二个姓氏为“dani”的人

select * from person where id in (select id from pernom p,table(p.last_name) 
where column_value(1)='bilel' and column_value(2)='dani');

我知道它不能那样工作，但我想知道在这种情况下的CRUD（创建更新删除）语句。并在 where 语句中选择带有 varray 的语句。

感谢您的答复。

亚历克斯·普尔

从文档：

Oracle 不支持对 VARRAY 列进行分段更新。但是，VARRAY 列可以作为原子单元插入或更新。

如那里的示例所示，您可以改为通过 PL/SQL 操作集合；包括向数组添加元素：

declare
  l_last_name lastn;
begin
  select last_name into l_last_name
  from person where id = 1;

  l_last_name.extend();
  l_last_name(l_last_name.count) := 'third';

  update person
  set last_name = l_last_name
  where id = 1;
end;
/

PL/SQL procedure successfully completed.

select last_name from person where id = 1;

LAST_NAME                                         
--------------------------------------------------
LASTN('dani', 'bilel', 'third')

您也可以通过cast(multiset(...) as ...)以下方式执行此操作：

-- rollback; to reverse PL/SQL block actions above

update person p
set last_name = cast(multiset(
    select column_value
    from table (last_name)
    union all
    select 'third' from dual
  ) as lastn)
where id = 1;

1 row updated.

select last_name from person where id = 1;

LAST_NAME                                         
--------------------------------------------------
LASTN('dani', 'bilel', 'third')

这last_name会将现有值分解为多行，并在新值中合并，然后将组合结果转换回您的varray类型。

您可以以类似的方式删除或更新元素：

update person p
set last_name = cast(multiset(
    select column_value
    from table (last_name)
    where column_value != 'bilel'
  ) as lastn)
where id = 1;

1 row updated.

select last_name from person where id = 1;

LAST_NAME                                         
--------------------------------------------------
LASTN('dani', 'third')

update person p
set last_name = cast(multiset(
    select case column_value when 'third' then 'second' else column_value end
    from table (last_name)
  ) as lastn)
where id = 1;

1 row updated.

select last_name from person where id = 1;

LAST_NAME                                         
--------------------------------------------------
LASTN('dani', 'second')

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。