PL/SQL定义部分之二复合数据类型

本文详细介绍PL/SQL定义部分之二复合数据类型

　　复合类型　 复合类型可以存储多个值，包括记录和集合。集合是一维的，但是可以在集合中放入其它集合，变成多维集合。集合又分为索引表、嵌套表和变长数组三种。要使用集合，我们首先要创建集合类型，然后声明该类型的变量。我们可以在任何PL/SQL块、子程序或包的声明部分使用TABLE和VARRAY关键字来声明集合类型。集合的作用域和同其他类型变量一样，在一个块或子程序中，当程序进入块或子程序时有效，退出时失效。在包中，集合在我们第一次引用包的时候有效，直至会话终止时才失效。

　　一、记录　　　

　　 记录可以包括多个成员，从而可以保存多个值。记录中的成员的数据类型可以不同。记录在使用时必须先声明记录类型，然后定义记录变量，最后才能在PL/SQL中使用。

　　示例1：

DECLARE
　TYPE MyRecord IS RECORD(
　name VARCHAR2(10),
　age　 SMALLINT
　);
　mr MyRecord;
　BEGIN
　mr.name:='zhangsan';
　mr.age:=20;
　DBMS_OUTPUT.PUT_LINE('姓名:'||mr.name||'　　　 年龄:'||mr.age);
　END;

　　示例2：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;

关键字： 分层查询 函数 触发器语法 Oracle物化视图 跟踪sql语句

本文详细介绍PL/SQL定义部分之二复合数据类型

　　示例3：

DECLARE
TYPE MyRecord IS RECORD(
　 no　　　 NUMBER(2),
　 name　 VARCHAR2(14),
　 city　 scott.dept.loc%TYPE
);
deptRecord MyRecord;
BEGIN
--SELECT 语句后列的数目少于记录中的变量数目
SELECT dname,loc INTO deptRecord.name,deptRecord.city FROM DEPT WHERE DEPTNO=30;
deptRecord.no:=30;
DBMS_OUTPUT.PUT_LINE(deptRecord.no||':'||deptRecord.name||':'||deptRecord.city);
END;

　　示例4：

　　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;

关键字： 分层查询 函数 触发器语法 Oracle物化视图 跟踪sql语句

本文详细介绍PL/SQL定义部分之二复合数据类型

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

关键字： 分层查询 函数 触发器语法 Oracle物化视图 跟踪sql语句

本文详细介绍PL/SQL定义部分之二复合数据类型

　　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开始编号，最大至2³¹，下标可以是一个整数，也可以是整数表达式。

关键字： 分层查询 函数 触发器语法 Oracle物化视图 跟踪sql语句

本文详细介绍PL/SQL定义部分之二复合数据类型

　　示例：

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;

　　四、嵌套表应用示例

　　示例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、查询嵌套表列的数据：

关键字： 分层查询 函数 触发器语法 Oracle物化视图 跟踪sql语句

本文详细介绍PL/SQL定义部分之二复合数据类型

　　查询全部的列信息

SELECT * FROM PERSON;

　　在isql*plus中结果如下：

　　通过嵌套表变量显示信息

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

本文转载自 http://doc.chinaunix.net/oracle/200903/208315.shtml