日期:2014-05-16 浏览次数:20408 次
一、抽象数据类型
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
免责声明: 本文仅代表作者个人观点,与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
|