Mega Code Archive
Creating and Using VARRAYs
<![CDATA[
SQL>
SQL> create type employee_type as object (
2 employee_id number,
3 first_name varchar2(30),
4 last_name varchar2(30)
5 );
6 /
Type created.
SQL>
SQL> create type employee_list_type as varray(50) of employee_type;
2 /
Type created.
SQL> create table departments (
2 department_id number,
3 department_name varchar2(30),
4 manager employee_type,
5 employees employee_list_type );
Table created.
SQL>
SQL> insert into departments ( department_id,
2 department_name,
3 manager,
4 employees )
5 values ( 10,
6 'Accounting',
7 employee_type( 1, 'Danielle', 'Steeger' ),
8 employee_list_type(
9 employee_type( 2, 'Madison', 'Sis' ),
10 employee_type( 3, 'Robert', 'Cabove' ),
11 employee_type( 4, 'Michelle', 'Sechrist' ))
12 );
1 row created.
SQL>
SQL>
SQL> insert into departments ( department_id,
2 department_name,
3 manager,
4 employees )
5 values ( 20,
6 'Research',
7 employee_type( 11, 'Ricky', 'Lil' ),
8 employee_list_type(
9 employee_type( 12, 'Ricky', 'Ricardo' ),
10 employee_type( 13, 'Lucy', 'Ricardo' ),
11 employee_type( 14, 'Fred', 'Mertz' ),
12 employee_type( 15, 'Ethel', 'Mertz' ))
13 );
1 row created.
SQL>
SQL> column department_name format a13
SQL> column employees format a63 word_wrapped
SQL> select department_name, employees
2 from departments;
DEPARTMENT_NA EMPLOYEES(EMPLOYEE_ID, FIRST_NAME, LAST_NAME)
------------- ---------------------------------------------------------------
Accounting EMPLOYEE_LIST_TYPE(EMPLOYEE_TYPE(2, 'Madison', 'Sis'),
EMPLOYEE_TYPE(3, 'Robert', 'Cabove'), EMPLOYEE_TYPE(4,
'Michelle', 'Sechrist'))
Research EMPLOYEE_LIST_TYPE(EMPLOYEE_TYPE(12, 'Ricky', 'Ricardo'),
EMPLOYEE_TYPE(13, 'Lucy', 'Ricardo'), EMPLOYEE_TYPE(14, 'Fred',
'Mertz'), EMPLOYEE_TYPE(15, 'Ethel', 'Mertz'))
SQL>
SQL>
SQL> drop table departments;
Table dropped.
SQL>
SQL> drop type employee_list_type;
Type dropped.
SQL>
SQL> drop type employee_type;
Type dropped.
SQL>
SQL>
]]>