oracle arry,Oracle VARRAY

定义

a varray is a variable length array object.

查看用户定义的类型：

select v.type_name, v.typecode, v.typeid, v.instantiable from user_types v

创建type

CREATE OR REPLACE TYPE Project AS OBJECT (

project_no NUMBER(2),

title VARCHAR2(35),

cost NUMBER(7,2));

创建varray

create or replace type projectlist as varray(50) of project

create table based on varray

CREATE TABLE department (

dept_id NUMBER(2),

name VARCHAR2(15),

budget NUMBER(11,2),

projects ProjectList);

向包含varray字段的表插入数据

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

删除包含varry字段的表记录

delete department t where t.dept_id=30