一、抽象数据类型
1、创建类型
--地址类型
CREATE OR REPLACE TYPE AddressType AS OBJECT
(
Country varchar2(15),
City varchar2(20),
Street varchar2(30)
);

2、类型嵌套
--创建基于前一个类型的新的抽象数据类型：巨星类型
CREATE OR REPLACE TYPE SuperStarType AS OBJECT
(
StarName varchar2(30),
Address AddressType
);

3、基于抽象类型创建关系表
CREATE TABLE SuperStar
(
StarID varchar(10),
Star SuperStarType
);

4、基于抽象类型创建对象表
CREATE TABLE SuperStarObj of SuperStarType;

5、使用构造方法在表中插入记录
INSERT INTO SuperStar VALUES(''001'',SuperStarType(''Zidane'',AddressType(''France'',''Paris'',''People Street NO.1'')));

6、查询表中记录
（1）SQL> SELECT * FROM SuperStar;

STARID
----------
STAR(STARNAME, ADDRESS(COUNTRY, CITY, STREET))
--------------------------------------------
001
SUPERSTARTYPE(''Zidane'', ADDRESSTYPE(''France'', ''Paris'', ''People Street NO.1''))

（2）
SELECT s.StarID,s.Star.StarName,s.Star.Address.Country,s.Star.Address.City,s.Star.Address.Street FROM SuperStar s

STARID STAR.STARNAME STAR.ADDRESS.CO STAR.ADDRESS.CITY STAR.ADDRESS.STREET
---------- ------------------------------ --------------- -------------------- ---------------------
001 Zidane France Paris People Street NO.1

7、抽象数据类型的继承
（1）创建一个类型
CREATE OR REPLACE TYPE PersonType AS OBJECT
(
PersonName varchar(10),
PersonSex varchar(2),
PersonBirth date
) not final;
(2)派生一个类型
CREATE OR REPLACE TYPE StudentType UNDER PersonType
(
StudentNO int,
StudentScore int
);
(3)查看数据字典
SQL> DESC StudentType
StudentType extends SYS.PERSONTYPE
Name
------------------------------------------
PERSONNAME
PERSONSEX
PERSONBIRTH
STUDENTNO
STUDENTSCORE
(4)创建对象表
CREATE TABLE student OF StudentType;
(5)向对象表中插入数据
INSERT INTO student VALUES(''Rose'',''nv'',to_date(''1983-05-02'',''yyyy-mm-dd''),1001,98);
(6) 查询数据
SQL> SELECT * FROM student;

PERSONNAME PE PERSONBIR STUDENTNO STUDENTSCORE
---------- -- --------- ---------- ------------
Rose nv 02-MAY-83 1001 98

二、可变数组
1、创建带有可变数组的表
（1）创建可变数组的基类型
CREATE OR REPLACE TYPE MingXiType AS OBJECT
(
GoodID varchar2(20),
InCount int,
ProviderID varchar(20)
);
（2）创建嵌套项类型的可变数组
CREATE OR REPLACE TYPE arrMingXiType AS VARRAY(100) OF MingXiType;
（3）创建一个主表
CREATE TABLE InStockOrder
(
OrderID varchar(15) Not Null Primary Key,
InDate date,
OperatorID varchar(15),
MingXi arrMingXiType
);

2、操作可变数组
（1）插入数据
INSERT INTO InStockOrder
VALUES(''200710110001'',TO_DATE(''2007-10-11'',''YYYY-MM-DD''),''007'',
arrMingXiType(MingXiType(''G001'',100,''1001''),
MingXiType(''G002'',888,''1002''))
);
(2)查询数据
SQL> SELECT * FROM InStockOrder;

ORDERID INDATE OPERATORID
--------------- --------- ---------------
MINGXI(GOODID, INCOUNT, PROVIDERID)
----------------------------------
200710110001 11-OCT-07 007
ARRMINGXITYPE(MINGXITYPE(''G001'', 100, ''1001''), MINGXITYPE(''G002'', 888, ''1002'')
(3)使用Table()函数
SQL> SELECT * FROM Table(SELECT t.MingXi FROM InStockOrder t
WHERE t.OrderID=''200710110001'');

GOODID INCOUNT PROVIDERID
-------------------- ---------- --------------------
G001 100 1001
G002 888 1002
（4）修改数据
UPDATE InStockOrder
SET MingXi=arrMingXiType(MingXiType(''G001'',200,''1001''),
MingXiType(''G002'',8888,''1002''))
WHERE OrderID=''200710110001''
注意：不能更新VARRAY中的单个元素，必须更新整个VARRAY

三、嵌套表
1、创建嵌套表
（1）创建嵌套表的基类型
CREATE OR REPLACE TYPE MingXiType AS OBJECT
(
GoodID varchar2(20),
InCount int,
ProviderID varchar(20)
) not final;
(2)创建嵌套表类型
CREATE OR REPLACE TYPE nestMingXiType AS TABLE OF MingXiType;
（3）创建主表，其中一列是嵌套表类型
CREATE TABLE InStockTable
(
OrderID varchar(15) Not Null Primary Key,
InDate date,
OperatorID varchar(15),
MingXi nestMingXiT