SQL

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_103a.htm#2081741

Table Collections：

     You can perform DML operations on nested tables only if they are defined as columns of a table。

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());

INSERT INTO TABLE(SELECT h.people FROM hr_info h
   WHERE h.department_id = 280)
   VALUES ('Smith', 280, 1750);

Collection Unnesting：

    Suppose that table which we select is not a nested table column , but is instead a separate table. You can extract the same rows 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;

     Instead, we can created a function people_func that extracts from various sources  also.

Using the LEVEL Pseudocolumn:

SELECT LPAD(' ',2*(LEVEL-1)) || last_name org_chart,
        employee_id, manager_id, job_id
    FROM employees
    START WITH job_id = 'AD_VP'
    CONNECT BY PRIOR employee_id = manager_id;

Using Distributed Queries:

This shows a query that joins the table on the local database with the other table on the remote database.

Using Correlated Subqueries:

....................