oracle中 table collection expression是做什么用的？谁有示例语句？
oracle中   table   collection   expression是做什么用的？谁有示例语句？

from子句中。

------解决方案--------------------
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