http://www.psoug.org/reference/varray.html CREATE OR REPLACE TYPE Project AS OBJECT ( project_no NUMBER(2), title VARCHAR2(35), cost NUMBER(7,2)); /desc projectSELECT type_name, typecode, instantiable FROM user_types; Define VARRAY of object type CREATE OR REPLACE TYPE ProjectList AS VARRAY(50) OF Project; /desc projectlistSELECT type_name, typecode, instantiable FROM user_types; Create table based on VARRAY Create relational table department, which has a column of type ProjectListCREATE TABLE department ( dept_id NUMBER(2), name VARCHAR2(15), budget NUMBER(11,2), projects ProjectList);desc departmentset describe depth all linenum on indent ondesc department Inserting into the VARRAY containing table Each item in column projects is a varray that will store the projects scheduled for a given department.Now, you are ready to populate relational table department. In the following example, notice how varray constructor ProjectList() provides values for column projects:INSERT INTO department VALUES(30, 'Accounting', 1205700, ProjectList (Project(1, 'Design New Expense Report', 3250), Project(2, 'Outsource Payroll', 12350), Project(3, 'Evaluate Merger Proposal', 2750), Project(4, 'Audit Accounts Payable', 1425)));INSERT INTO department VALUES(50, 'Maintenance', 925300, ProjectList (Project(1, 'Repair Leak in Roof', 2850), Project(2, 'Install New Door Locks', 1700), Project(3, 'Wash Front Windows', 975), Project(4, 'Repair Faulty Wiring', 1350), Project(5, 'Winterize Cooling System', 1125)));INSERT INTO department VALUES(60, 'Security', 750400, ProjectList (Project(1, 'Issue New Employee Badges', 13500), Project(2, 'Find Missing IC Chips', 2750), Project(3, 'Upgrade Alarm System', 3350), Project(4, 'Inspect Emergency Exits', 1900)));SELECT * FROM department; Delete Record with VARRAY In the final example, you delete the Accounting Department and its project list from table department:DELETE FROM department WHERE dept_id = 30;SELECT * FROM department;
CREATE OR REPLACE TYPE Project AS OBJECT (
project_no NUMBER(2),
title VARCHAR2(35),
cost NUMBER(7,2));
/desc projectSELECT type_name, typecode, instantiable
FROM user_types;
Define VARRAY of object type CREATE OR REPLACE TYPE ProjectList AS VARRAY(50) OF Project;
/desc projectlistSELECT type_name, typecode, instantiable
FROM user_types;
Create table based on VARRAY Create relational table department, which has a column of type ProjectListCREATE TABLE department (
dept_id NUMBER(2),
name VARCHAR2(15),
budget NUMBER(11,2),
projects ProjectList);desc departmentset describe depth all linenum on indent ondesc department
Inserting into the VARRAY containing table Each item in column projects is a varray that will store
the projects scheduled for a given department.Now, you are ready to populate relational table department.
In the following example, notice how varray constructor
ProjectList() provides values for column projects:INSERT INTO department
VALUES(30, 'Accounting', 1205700,
ProjectList (Project(1, 'Design New Expense Report', 3250),
Project(2, 'Outsource Payroll', 12350),
Project(3, 'Evaluate Merger Proposal', 2750),
Project(4, 'Audit Accounts Payable', 1425)));INSERT INTO department
VALUES(50, 'Maintenance', 925300,
ProjectList (Project(1, 'Repair Leak in Roof', 2850),
Project(2, 'Install New Door Locks', 1700),
Project(3, 'Wash Front Windows', 975),
Project(4, 'Repair Faulty Wiring', 1350),
Project(5, 'Winterize Cooling System', 1125)));INSERT INTO department
VALUES(60, 'Security', 750400,
ProjectList (Project(1, 'Issue New Employee Badges', 13500),
Project(2, 'Find Missing IC Chips', 2750),
Project(3, 'Upgrade Alarm System', 3350),
Project(4, 'Inspect Emergency Exits', 1900)));SELECT * FROM department;
Delete Record with VARRAY In the final example, you delete the Accounting Department
and its project list from table department:DELETE FROM department WHERE dept_id = 30;SELECT * FROM department;