PL/SQL -复合数据类型

复合类型　复合类型可以存储多个值，包括记录和集合。集合是一维的，但是可以在集合中放入其它集合，变成多维集合。集合又分为索引表、嵌套表和变长数组三种。要使用集合，我们首先要创建集合类型，然后声明该类型的变量。我们可以在任何PL/SQL块、子程序或包的声明部分使用TABLE和VARRAY关键字来声明集合类型。集合的作用域和同其他类型变量一样，在一个块或子程序中，当程序进入块或子程序时有效，退出时失效。在包中，集合在我们第一次引用包的时候有效，直至会话终止时才失效。
一、记录　　　
记录可以包括多个成员，从而可以保存多个值。记录中的成员的数据类型可以不同。记录在使用时必须先声明记录类型，然后定义记录变量，最后才能在PL/SQL中使用。
实例1：
DECLARE
TYPE MyRecord IS RECORD(
　 no　　　 NUMBER(2),
　 name　 VARCHAR2(14),
　 city　 scott.dept.loc%TYPE
);
deptRecord MyRecord ;
BEGIN
--SELECT 语句后列的数目与记录中的变量数目相同
SELECT deptno,dname,loc into deptRecord FROM DEPT WHERE DEPTNO=30;
DBMS_OUTPUT.PUT_LINE(deptRecord.no||':'||deptRecord.name||':'||deptRecord.city);
END;
示例2：
DECLARE
--特殊定义记录方式，记录中变量的类型与dept表行的类型相同
deptRecord dept%ROWTYPE;
BEGIN
SELECT * INTO deptRecord FROM DEPT WHERE DEPTNO=30;
DBMS_OUTPUT.PUT_LINE(deptRecord.deptNo||':'||deptRecord.dname||':'||deptRecord.loc);
END;
二、索引表(关联数组)
索引表就是键值集合，键是唯一的，用于查找对应的值。键可以是整数或字符串。第一次使用键来指派一个对应的值就是添加元素，而后续这样的操作就是更新元素。
DECLARE
TYPE MyTableTypeName IS TABLE OF ElementType [NOT NULL]
INDEX BY Key_Type;
myName MyTableTypeName;
其中：MyTableTypeName是表类型的名称，ElementType是元素的数据类型，NOT NULL表示不允许引用NULL值，Key_Type是下标的数据类型，BINARY_INTEGER PLS_INTEGER或VARCHAR2(Oracle9i及以后版本可用)。myName是表类型变量名。
示例1
DECLARE
TYPE MyTabelType IS TABLE OF dept.dname%TYPE
NOT NULL INDEX BY BINARY_INTEGER;
my MyTabelType;
BEGIN
SELECT DNAME INTO my(-3) FROM DEPT WHERE　 deptno=20;
SELECT DNAME INTO my(-1) FROM DEPT WHERE　 deptno=40;
DBMS_OUTPUT.PUT_LINE('my(-3):'||my(-3));
DBMS_OUTPUT.PUT_LINE('my(-1):'||my(-1));
END;
示例2：
DECLARE
TYPE MyTableType IS TABLE OF NUMBER
NOT NULL INDEX BY VARCHAR2(20);
my MyTableType;
BEGIN
　 my('China'):=1;
　 my('Japan'):=2;
　 my('USA'):=3;
　 DBMS_OUTPUT.PUT_LINE(my.first);
　 DBMS_OUTPUT.PUT_LINE(my.last);
　 DBMS_OUTPUT.PUT_LINE(my('China'));
　DBMS_OUTPUT.PUT_LINE(my(my.last));
END;
三、嵌套表
　　 要使用嵌套表，首先要创建嵌套表类型，其次声明嵌套表类型变量，然后初始化嵌套表变量，最后引用嵌套表变量元素值。
1、定义嵌套表类型　　
TYPE MyNestedTableType IS TABLE OF ElementType [NOT NULL];
MyNestedTableType是嵌套表类型的名称。ElementType是嵌套表元素的类型，它是可以除了REF CURSOR类型之外的任何PL/SQL类型，但是对于全局嵌套表(CREATE TYPE创建)来说，以下类型也是不允许的：BINARY_INTEGER　 PLS_INTEGER　 BOOLEAN　 LONG　 LONG RAW　 NATURAL 　 NATURALN POSITIVE　 POSITIVEN　 REF CURSOR　 SIGNTYPE　 STRING 。NOT NULL表示元素值不能为NULL。
示例：
DECLARE
--定义记录
TYPE DeptRecord IS RECORD(
　 no　　　 NUMBER(2),
　 name　 VARCHAR2(14),
　 city　 scott.dept.loc%TYPE
);
--定义游标
CURSOR　 DeptCursor IS SELECT * FROM DEPT;
--定义嵌套表元素类型时使用%TYPE
TYPE DNameList IS TABLE OF dept.dname%TYPE;
--定义嵌套表元素类型时使用表的%ROWTYPE
TYPE DeptList1　 IS TABLE OF DEPT%ROWTYPE;
--定义嵌套表元素类型时使用游标的%ROWTYPE
TYPE DeptList2　 IS TABLE OF DeptCursor%ROWTYPE;
--定义嵌套表元素类型时使用记录类型
TYPE DeptList3　 IS TABLE OF　DeptRecord ;
--在SQL语句中定义嵌套表类型
CREATE TYPE PhoneList IS TABLE OF VARCHAR2(10);
2、声明嵌套表类型变量
示例：
DECLARE
TYPE DNameList IS TABLE OF dept.dname%TYPE;
nameList1 DNameList;　
nameList2 nameList1%TYPE;　　 --使用%TYPE声明嵌套表类型变量
--在存储过程参数中定义嵌套表类型变量
CREATE OR REPLACE PROCEDURE proc_name(nameList　 IN DNameList)
注意：不能将嵌套表类型和嵌套表类型变量同名，大小写不同也不可，因PL/SQL不区分大小写
3、初始化嵌套表类型变量
嵌套表必须先通过构造方法初始化(索引表不需要通过构造方法进行初始化)，否则嵌套表变量为NULL,在初始化中元素个数没有限制。
示例：
DECLARE
TYPE DNameList IS TABLE OF dept.dname%TYPE;
my DNameList ;
BEGIN
my:=DNameList('CORESUN','CORESUN','CORESUN');　
注意：my变量在初始化时传入了3个参数，那么嵌套表长度就是3，以后使用时长度不够，必须通过extend函数进行扩展长度。如果在构造方法中没有传入任何参数，则嵌套表长度为0。也可以在声明声明嵌套表类型变量的同时进行初始化，如：
my DNameList:=DNameList('CORESUN','CORESUN','CORESUN');
如果定义元素类型时没有指定NOT NULL，在构造方法中也可以传入NULL,如：
my:=DNameList('CORESUN',NULL,'CORESUN');
4、引用嵌套表类型变量
通过嵌套表变量和圆括号里的索引值来引用嵌套表中的元素，嵌套表的下标是从1开始编号，最大至231 ，下标可以是一个整数，也可以是整数表达式。
四、嵌套表应用示例
示例1：创建记录类型的嵌套表
DECLARE
TYPE DeptRecord IS RECORD(
　 no　　　 NUMBER(2),
　 name　 VARCHAR2(14),
　 city　 scott.dept.loc%TYPE
);
TYPE DeptRecordList IS TABLE OF DeptRecord;
示例2：嵌套表用在另一个表的内部
1、创建全局嵌套表类型
--当嵌套表作为表列的类型时，要先创建，这样的嵌套表我们称为全局嵌套表
CREATE OR REPLACE TYPE PHONE_TYPE IS TABLE OF VARCHAR2(20);
/
CREATE TABLE person
(
　 id NUMBER,
　 name VARCHAR2(10),
　 sal NUMBER(6,2),
　 phone PHONE_TYPE
) NESTED TABLE phone STORE AS PHONE_TABLE;
2、向person表插入数据：
INSERT INTO person VALUES(1,'SCOTT',800,PHONE_TYPE('13256789876','031198765432'));
INSERT INTO PERSON VALUES(20,'CORESUN',2000,PHONE_TYPE('13999999999','13098765345','01064890987'));
3、查询嵌套表列的数据：
示例：
DECLARE
TYPE DNameList IS TABLE OF dept.dname%TYPE;
my DNameList ;
BEGIN
my:=DNameList(NULL,'CORESUN','CORESUN');　
SELECT DNAME INTO my(2) FROM dept where deptno=20;
my(1):='CORESUN';
--my(4):='CORESUN';
DBMS_OUTPUT.PUT_LINE(my(1));
DBMS_OUTPUT.PUT_LINE(my(2));
END;
　　查询全部的列信息
SELECT * FROM PERSON;
　　在isql*plus中结果如下：
[img]D:\11.jpeg[/img]
　　通过嵌套表变量显示信息
DECLARE
pTable PHONE_TYPE;
BEGIN
SELECT phone into pTable FROM person WHERE name='SCOTT';
FOR　　 i　　 IN 1..pTable.COUNT LOOP
　 DBMS_OUTPUT.PUT_LINE('号码'||i||':'||pTable(i));
END LOOP;
　　结果如下：
　　号码1:13256789876
　　号码2:031198765432
　　4、更新嵌套表数据：
DECLARE
pTable PHONE_TYPE:=PHONE_TYPE('034223455432','13888888888');
BEGIN
UPDATE person SET phone=pTable WHERE name='SCOTT';
END;
　　四、可变数组(VARRAY数组)
VARRAY数组是一种集合数据类型，也可以作为表的列来使用，该集合的下标从1开始，并且元素个数是有限制的。语法如下：
DECLARE
TYPE MyArrayTypeName IS VARRAY(size) OF ELEMENT_TYPE [NOT NULL];
my MyArrayTypeName
　　其中MyArrayTypeName 是数组类型的名字，size表示数组最多存储多少个元素，ELEMENT_TYPE是元素的类型，NOT NULL不允许使用NULL值，my是数组变量的名称。
　　示例1：
DECLARE
TYPE MyVArrayType IS VARRAY(10) OF dept.dname%TYPE NOT NULL;
　　--构造方法中最多可以有10(size)个元素
　　--既是没有定义NOT NULL,也不会自动为4-10元素赋NULL值
nameArray MyVArrayType:=MyVArrayType('1','2','3');
BEGIN
SELECT dname into nameArray(1) from dept where deptno=20;
SELECT dname into nameArray(2) from dept where deptno=30;
DBMS_OUTPUT.PUT_LINE(nameArray(1));
DBMS_OUTPUT.PUT_LINE(nameArray(2));
DBMS_OUTPUT.PUT_LINE(nameArray(3));
END;
　　结果如下： RESEARCH
SALES
3