Зачем нужны функции возвращаюшие коллекции в sql контексте?

#sql #oracle #plsql

Недавно узнал что можно в запросе sql вернуть коллекцию, например вот так:

select 
  cast (multiset
    (select поле from таблица)
  as тип коллекции) 
from dual


Зачем это нужно в sql, если работать с коллекциями можно в pl/sql коде?
    

Ответы

Ответ 1

В вашем примере cast функция с ключевым словом multiset конвертирует набор реляционных
данных в sql коллекцию. Основное назначение манипуляция данных в таблицах с колонками
объявлеными как коллекции.

Другое применение - маппинг классической схемы с реляционной моделью данных с объектно-реляционной
посредством объектных представлений.

Небольшой пример:

create or replace type myItemType as object (id number, name varchar(32));
/
--Type MYITEMTYPE compiled

create type myItemArrayType as table of myItemType;
/
--Type MYITEMARRAYTYPE compiled

create table myItems (items myItemArrayType) nested table items store as items_table;
/
--Table MYITEMS created.

-- обычное занесение одного значения 
insert into myItems values (myItemArrayType(myItemType(0, 'item 0')));
--1 row inserted.

-- заполнене коллекции из реляционной таблицы (вместо dual здесь одна или несколько
таблиц) 
insert into myItems select cast(multiset(
    select level, 'item ' || level from dual connect by level <= 5  
) as myItemArrayType) from dual
;
--1 row inserted.

select  items.* from myItems, table(items) items;

ID  NAME
--  ------ 
0   item 0
1   item 1
2   item 2
3   item 3
4   item 4
5   item 5


Вывод - item 0 из первой строчки, item 1 - item 5 из второй.

Предыдущий ответ
Вопрос был изменён и в первоночальной версии он был о применении методов для работы
с коллекциями в sql выражениях.

Mетоды для работы с коллекциями, не могут быть исползованы в SQL выражениях. 
В документации Oracle: 
A collection method invocation can appear anywhere that an invocation of a PL/SQL
subprogram of its type (function or procedure) can appear, except in a SQL statement.

PS Может вы имеете в виду что-то другое под определением "методы для работы с коллекциями"?
Привидите пример.