Oracle PL/SQL Tutorial/Collections/COLLECT

Материал из SQL эксперт
Перейти к: навигация, поиск

COLLECT Operator

You use the COLLECT operator to get a list of values as a nested table.

You can cast the returned nested table to a nested table type using the CAST operator.

The following query illustrates the use of COLLECT:



   <source lang="sql">

SQL> SQL> CREATE Or Replace TYPE nestedTableType IS TABLE OF VARCHAR2(10)

 2  /

Type created. SQL> SQL> CREATE TABLE employee (

 2    id         INTEGER PRIMARY KEY,
 3    first_name VARCHAR2(10),
 4    last_name  VARCHAR2(10),
 5    addresses  nestedTableType
 6  )
 7  NESTED TABLE
 8    addresses
 9  STORE AS
10    nested_addresses2 TABLESPACE users;

Table created. SQL> SELECT tablespace_name

 2  FROM user_tablespaces
 3  /

TABLESPACE_NAME


SYSTEM UNDO SYSAUX TEMP USERS SQL> SQL> SELECT COLLECT(first_name)

 2  FROM employee;

COLLECT(FIRST_NAME)


SYSTP3ppbcSo4QhS0YU4yNeNpiA==() SQL> SQL> SQL> drop table employee; Table dropped. SQL> drop type nestedTableType; Type dropped. SQL></source>