table_collection_expression The table_collection_expression lets you inform Oracle that the value of collection_expression should be treated as a table for purposes of query and DML operations. The collection_expression can be a subquery, a column, a function, or a collection constructor. Regardless of its form, it must return a collection value (that is, a value whose type is nested table or varray). This process of extracting the elements of a collection is called collection unnesting.The collection_expression can reference columns of tables defined to its left in the FROM clause. This is called left correlation. Left correlation can occur only in table_collection_expression. Other subqueries cannot contains references to columns defined outside the subquery.The optional "(+)" lets you specify that table_collection_expression should return a row with all fields set to NULL if the collection is null or empty. The "(+)" is valid only if collection_expression uses left correlation. The result is similar to that of an outer join.Table Collections: Examples You can perform DML operations on nested tables only if they are defined as columns of a table. Therefore, when the query_table_expr_clause of an INSERT, DELETE, or UPDATE statement is a table_collection_expression, the collection expression must be a subquery that uses the TABLE function to select the table's nested table column. The examples that follow are based on the following scenario:Suppose the database contains a table hr_info with columns department_id, location, and manager_id, and a column of nested table type people which has last_name, department_id, and salary columns for all the employees of each respective manager:CREATE TYPE people_typ AS OBJECT ( last_name VARCHAR2(25), department_id NUMBER(4), salary NUMBER(8,2)); / CREATE TYPE people_tab_typ AS TABLE OF people_typ; / CREATE TABLE hr_info ( department_id NUMBER(4), location_id NUMBER(4), manager_id NUMBER(6), people people_tab_typ) NESTED TABLE people STORE AS people_stor_tab;INSERT INTO hr_info VALUES (280, 1800, 999, people_tab_typ()); The following example inserts into the people nested table column of hr_info table's department numbered 280:INSERT INTO TABLE(SELECT h.people FROM hr_info h WHERE h.department_id = 280) VALUES ('Smith', 280, 1750); The next example updates Department 280's people nested table:UPDATE TABLE(SELECT h.people FROM hr_info h WHERE h.department_id = 280) p SET p.salary = p.salary + 100; The next example deletes from Department 280's people nested table:DELETE TABLE(SELECT h.people FROM hr_info h WHERE h.department_id = 280) p WHERE p.salary > 1700;Collection Unnesting: Examples To select data from a nested table column you again use the TABLE function to treat the nested table as columns of a table. This process is called "collection unnesting.You could get all the rows from hr_info (created in the preceding example) and all the rows from the people nested table column of hr_info using the following statement: SELECT t1.department_id, t2.* FROM hr_info t1, TABLE(t1.people) t2 WHERE t2.department_id = t1.department_id; Now suppose that people is not a nested table column of hr_info, but is instead a separate table with columns last_name, department_id, address, hiredate, and salary. You can extract the same rows as in the preceding example with this statement:SELECT t1.department_id, t2.* FROM hr_info t1, TABLE(CAST(MULTISET( SELECT t3.last_name, t3.department_id, t3.salary FROM people t3 WHERE t3.department_id = t1.department_id) AS people_tab_typ)) t2; Finally, suppose that people is neither a nested table column of table hr_info nor a table itself. Instead, you have created a function people_func that extracts from various sources the name, department, and salary of all employees. You can get the same information as in the preceding examples with the following query:SELECT t1.department_id, t2.* FROM hr_info t1, TABLE(CAST (people_func( ... ) AS people_tab_typ)) t2;
The table_collection_expression lets you inform Oracle that the value of collection_expression should be treated as a table for purposes of query and DML operations. The collection_expression can be a subquery, a column, a function, or a collection constructor. Regardless of its form, it must return a collection value (that is, a value whose type is nested table or varray). This process of extracting the elements of a collection is called collection unnesting.The collection_expression can reference columns of tables defined to its left in the FROM clause. This is called left correlation. Left correlation can occur only in table_collection_expression. Other subqueries cannot contains references to columns defined outside the subquery.The optional "(+)" lets you specify that table_collection_expression should return a row with all fields set to NULL if the collection is null or empty. The "(+)" is valid only if collection_expression uses left correlation. The result is similar to that of an outer join.Table Collections: Examples
You can perform DML operations on nested tables only if they are defined as columns of a table. Therefore, when the query_table_expr_clause of an INSERT, DELETE, or UPDATE statement is a table_collection_expression, the collection expression must be a subquery that uses the TABLE function to select the table's nested table column. The examples that follow are based on the following scenario:Suppose the database contains a table hr_info with columns department_id, location, and manager_id, and a column of nested table type people which has last_name, department_id, and salary columns for all the employees of each respective manager:CREATE TYPE people_typ AS OBJECT (
last_name VARCHAR2(25),
department_id NUMBER(4),
salary NUMBER(8,2));
/
CREATE TYPE people_tab_typ AS TABLE OF people_typ;
/
CREATE TABLE hr_info (
department_id NUMBER(4),
location_id NUMBER(4),
manager_id NUMBER(6),
people people_tab_typ)
NESTED TABLE people STORE AS people_stor_tab;INSERT INTO hr_info VALUES (280, 1800, 999, people_tab_typ());
The following example inserts into the people nested table column of hr_info table's department numbered 280:INSERT INTO TABLE(SELECT h.people FROM hr_info h
WHERE h.department_id = 280)
VALUES ('Smith', 280, 1750);
The next example updates Department 280's people nested table:UPDATE TABLE(SELECT h.people FROM hr_info h
WHERE h.department_id = 280) p
SET p.salary = p.salary + 100;
The next example deletes from Department 280's people nested table:DELETE TABLE(SELECT h.people FROM hr_info h
WHERE h.department_id = 280) p
WHERE p.salary > 1700;Collection Unnesting: Examples
To select data from a nested table column you again use the TABLE function to treat the nested table as columns of a table. This process is called "collection unnesting.You could get all the rows from hr_info (created in the preceding example) and all
the rows from the people nested table column of hr_info using the following
statement: SELECT t1.department_id, t2.* FROM hr_info t1, TABLE(t1.people) t2
WHERE t2.department_id = t1.department_id;
Now suppose that people is not a nested table column of hr_info, but is instead a separate table with columns last_name, department_id, address, hiredate, and salary. You can extract the same rows as in the preceding example with this statement:SELECT t1.department_id, t2.*
FROM hr_info t1, TABLE(CAST(MULTISET(
SELECT t3.last_name, t3.department_id, t3.salary
FROM people t3
WHERE t3.department_id = t1.department_id)
AS people_tab_typ)) t2;
Finally, suppose that people is neither a nested table column of table hr_info nor a table itself. Instead, you have created a function people_func that extracts from various sources the name, department, and salary of all employees. You can get the same information as in the preceding examples with the following query:SELECT t1.department_id, t2.* FROM hr_info t1, TABLE(CAST
(people_func( ... ) AS people_tab_typ)) t2;