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 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: