Oracle nested table

========================================================== ============================= Nested table test ========================== ========================================================== = Author: tsmld (http://tsmld.itpub.net)
Posted at: 2006.12.05
Category: Database
Source: http://tsmld.itpub.net/post/19744/234628
----------------------------------------------------------------- Nested table

-- Create an object type

Create or replace type emp_type as object (
Name varchar2 (10), salary number (6, 2 ),
Hiredate date );
/

-- Create a nested table Type
Create or replace type emp_array is table of emp_type;
/

-- Create a table

Create Table Department (
Deptno number (2), dname varchar2 (10 ),
Employee emp_array
) Nested table employee store as employee;
/
-- Insert data
Insert into Department values (10, 'sales', emp_array (
Emp_type ('Scott ', 1000, sysdate ),
Emp_type ('Smith ', 1200, sysdate ),
Emp_type ('bucke', 1500, sysdate ))
);
-- Query
Select D. dname, E. Name, E. Salary
From department d, table (D. Employee) E

Reference: <Oracle 9i Pro * C/C ++>