I recently learned that you can return a collection in a sql query, for example like this:

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

Why is it necessary in sql, if you can work with collections in pl / sql code?

  • Probably, all the same, it means SQL commands inside PL / SQL code? The most obvious example is bulk binding. Then, a PL / SQL statement is used in the same command, for example FORALL and a call to the list of selection columns in the collection elements. - hinotf 2:51 pm
  • these functions can be called simply in the sql engine - heff

1 answer 1

In your example, the cast function with the multiset keyword converts a set of relational data into an sql collection. The main purpose is to manipulate data in tables with columns declared as collections.

Another application is the mapping of a classical schema with a relational data model with an object-relational through object representations.

A small example:

 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

Conclusion - item 0 from the first line, item 1 - item 5 from the second.

Previous answer
The question was changed and in the original version it was about applying methods for working with collections in sql expressions.

Methods for working with collections can not be used in SQL expressions.
In the Oracle documentation:
A collection method of PL / SQL can be used, where you can find a SQL statement .

PS Maybe you mean something else under the definition of "methods for working with collections"? See an example.

  • select cast (multiset (select field from table) as collection type) from dual. This code can be called simply from sql. - heff
  • Of course you can, And where is the method for working with collections in this select? - 0xdb
  • perhaps not correctly formulated, the essence of why the collection in sql? - heff
  • Or change the question, or ask a new one. The question “why collection in sql” is very general. Collections are used since version 8. Tag plsql confuses only. - 0xdb
  • An example that I wrote how to use in practice? Why collections in oracle understand why they are needed in a sample sql / - heff