Как манипулировать VARRAYS в sql (оракуле)?

Предположим, я использую табличного человека, и у людей может быть несколько фамилий, поэтому этот атрибут должен быть, например, массивом из 3 элементов (речь не о том, где хранить фамилии), вот простой sql для создания типа фамилия , таблица person и добавление строки примера в sql-разработчик oracle (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');

Это тот случай, когда я хочу получить людей, у которых первая фамилия «билель», а вторая фамилия — «дани».

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(create update delete). и оператор select с varray в операторе where.

Спасибо за ваш ответ.


sql oracle11g varray object-relational-model
person BHA Bilel    schedule 04.12.2018    source источник

Ответы (2)


arrow_upward
0
arrow_downward

Из документации:

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')
person Alex Poole    schedule 04.12.2018
comment
Можете ли вы показать мне, как установить условие where в этом случае? Я хочу выбрать last_name от человека, где last_name=lastn('bilel'); этот sql не работал! - person BHA Bilel; 25.12.2018

arrow_upward
0
arrow_downward

Для оператора select я нашел решение, которое выглядит следующим образом:

select * from person p where id in (select id from table(p.last_name) where 
column_value='bilel' intersect select id from table(p.last_name) where 
column_value='dani');

or

select * from agent ag where id in (select id from table(ag.prenom) 
t1,table(ag.prenom) t2,table(ag.prenom) t3 where t1.column_value='bilel' and 
t2.column_value='dani' and t3.column_value='third');
person BHA Bilel    schedule 25.12.2018