PL/SQL集合类型详细讲解

PL/SQL集合类型是类似于高级语言数组的一种复合数据类型，集合类型包括索引表（PL/SQL表）、嵌套表（Nested Table）和变长数组（VARRAY）三种类型。 <一> 、索引表 索引表也称为PL/SQL表，它是Oracle早期版本用于处理PL/SQL数组的数据类型。索引表的元素个数没有限制，并且下标可以为负值。注意，索引表只能作为PL/SQL复合数据类型使用，而不能作为表列的数据类型使用。 语法： Sql代码 TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY key_type; identifier type_name; TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY key_type; identifier type_name; 说明：从Oracle9i开始，索引表下标不仅允许使用数据类型BINARY_INTEGER、PLS_INTEGER,而且允许使用数据类型VARCHAR2。 示例一： Sql代码 declare type xm_table_type is table of drv_admin.drv_temp.xm%type index by binary_integer; xm_talbe xm_table_type; begin select xm into xm_talbe(-1) from drv_admin.drv_temp where lsh='&lsh'; dbms_output.put_line('姓名:'||xm_talbe(-1)); end; declare type xm_table_type is table of drv_admin.drv_temp.xm%type index by binary_integer; xm_talbe xm_table_type; begin select xm into xm_talbe(-1) from drv_admin.drv_temp where lsh='&lsh'; dbms_output.put_line('姓名:'||xm_talbe(-1)); end; 示例二： Sql代码 declare type area_table_type is table of number index by varchar2(10); area_table area_table_type; begin area_table('北京'):=1; area_table('上海'):=2; area_table('天津'):=3; dbms_output.put_line('第一个元素：'||area_table.first);--第一个元素下标 dbms_output.put_line('最后一个元素：'||area_table.last);--最后一个元素下标 end; declare type area_table_type is table of number index by varchar2(10); area_table area_table_type; begin area_table('北京'):=1; area_table('上海'):=2; area_table('天津'):=3; dbms_output.put_line('第一个元素：'||area_table.first);--第一个元素下标 dbms_output.put_line('最后一个元素：'||area_table.last);--最后一个元素下标 end; <二> 、嵌套表 嵌套表的元素下标从1开始，并且元素个数没有限制。嵌套表数组元素值可以是稀疏的。 注意：索引表类型不能作为表列的数据类型使用，但嵌套表类型可以作为表列的数据类型。 语法： Sql代码 type type_name is table of element_type; identifier type_name; type type_name is table of element_type; identifier type_name; 1.在PL/SQL块中使用嵌套表 Sql代码 declare type xm_table_type is table of drv_admin.drv_temp.xm%type; xm_table xm_table_type; begin xm_table:=xm_table_type(' ',' ',' ');--这里必须使用构造方法初始化嵌套表变量。 select xm into xm_table(2) from drv_admin.drv_temp where lsh='&lsh'; dbms_output.put_line('姓名：'||xm_table(2)); end; declare type xm_table_type is table of drv_admin.drv_temp.xm%type; xm_table xm_table_type; begin xm_table:=xm_table_type(' ',' ',' ');--这里必须使用构造方法初始化嵌套表变量。 select xm into xm_table(2) from drv_admin.drv_temp where lsh='&lsh'; dbms_output.put_line('姓名：'||xm_table(2)); end; 2.在表列中使用嵌套表 在表列中使用嵌套表类型，必须首先使用CREATE TYPE命令建立嵌套表类型。另外注意，必须要为嵌套表列指定专门的存储表。 示例如下： Sql代码 create type phone_type is table of varchar2(20); / create table employee( id number(4),name varchar2(10),sal number(6,2),phone phone_type )nested table phone store as phone_table; create type phone_type is table of varchar2(20); / create table employee( id number(4),name varchar2(10),sal number(6,2),phone phone_type )nested table phone store as phone_table; (1)在嵌套表中插入数据 当定义嵌套表类型时，Oracle自动为该类型生成相应的构造方法。当为嵌套表列插入数据时，需要使用嵌套表的构造方法。 Sql代码 BEGIN INSERT INTO employee VALUES(1,'TOM',800, phone_type('028-90909800','13913001300') ); END; / BEGIN INSERT INTO employee VALUES(1,'TOM',800, phone_type('028-90909800','13913001300') ); END; / (2)在嵌套表中检索数据 需要定义嵌套表类型的变量接收其数据。 Sql代码 DECLARE phone_table phone_type; BEGIN SELECT phone INTO phone_table FROM employee WHERE id=1; FOR i IN 1..phone_table.COUNT LOOP dbms_output.put_line('电话号码：'||phone_table(i)); END LOOP; END; DECLARE phone_table phone_type; BEGIN SELECT phone INTO phone_table FROM employee WHERE id=1; FOR i IN 1..phone_table.COUNT LOOP dbms_output.put_line('电话号码：'||phone_table(i)); END LOOP; END; (3)在嵌套表中更新数据 首先需要定义嵌套表变量，并使用构造方法初始化该变量，然后才可在执行部分使用UPDATE语句更新其数据。 Sql代码 DECLARE phone_table phone_type:=phone_type('028-10001000','139800900100'); BEGIN UPDATE employee SET phone=phone_table WHERE id=1; END; / DECLARE phone_table phone_type:=phone_type('028-10001000','139800900100'); BEGIN UPDATE employee SET phone=phone_table WHERE id=1; END; / <三> 、变长数组（VARRAY） 可以作为表列的数据类型使用。其元素下标以1开始，并且元素的最大个数是有限制的。 语法： Sql代码 TYPE type_name IS VARRAY(size_limit) OF element_type [NOT NULL]; Identifier type_name; TYPE type_name IS VARRAY(size_limit) OF element_type [NOT NULL]; Identifier type_name; 注意，当使用VARRAY元素时，必须要使用其构造方法初始化VARRAY元素。 1.在PL/SQL块中使用VARRAY Sql代码 DECLARE TYPE xm_table_type IS VARRAY(20) OF drv_admin.drv_temp.xm%TYPE; xm_table xm_table_type:=xm_table_type(' '); BEGIN SELECT xm INTO xm_table(1) FROM drv_admin.drv_temp WHERE lsh='&lsh'; dbms_output.put_line('姓名：'||xm_table(1)); END; / DECLARE TYPE xm_table_type IS VARRAY(20) OF drv_admin.drv_temp.xm%TYPE; xm_table xm_table_type:=xm_table_type(' '); BEGIN SELECT xm INTO xm_table(1) FROM drv_admin.drv_temp WHERE lsh='&lsh'; dbms_output.put_line('姓名：'||xm_table(1)); END; / 2.在表列中使用VARRAY Sql代码 CREATE TYPE phone_type IS VARRAY(20) OF VARCHAR2(20); / CREATE TABLE employee( id NUMBER(4), name VARCHAR2(10), sal NUMBER(6,2), phone phone_type ); CREATE TYPE phone_type IS VARRAY(20) OF VARCHAR2(20); / CREATE TABLE employee( id NUMBER(4), name VARCHAR2(10), sal NUMBER(6,2), phone phone_type ); 注意，嵌套表列的元素个数没有限制，而VARRAY列的元素个数是有限制的。 <四> 、PL/SQL记录表 为了在PL/SQL块中处理多行多列数据，开发人员可以使用PL/SQL记录表。 Sql代码 DECLARE TYPE tmp_table_type IS TABLE OF drv_admin.drv_temp%ROWTYPE INDEX BY BINARY_INTEGER; tmp_table tmp_table_type; BEGIN SELECT * INTO tmp_table(1) FROM drv_admin.drv_temp WHERE lsh='&lsh'; dbms_output.put_line('姓名：'||tmp_table(1).xm); dbms_output.put_line('备注：'||tmp_table(1).bz); END; / DECLARE TYPE tmp_table_type IS TABLE OF drv_admin.drv_temp%ROWTYPE INDEX BY BINARY_INTEGER; tmp_table tmp_table_type; BEGIN SELECT * INTO tmp_table(1) FROM drv_admin.drv_temp WHERE lsh='&lsh'; dbms_output.put_line('姓名：'||tmp_table(1).xm); dbms_output.put_line('备注：'||tmp_table(1).bz); END; / <五> 、集合方法 语法： Sql代码 collection_name.method_name[(parameters)] collection_name.method_name[(parameters)] 注意，集合方法只能在PL/SQL语句中使用，而不能在SQL语句中调用。另外集全方法EXTEND和TRIM只适用于嵌套表和VARRAY，而不适用于索引表。 1.EXISTS:确定集合元素是否存在。 Sql代码 IF xm_table.EXISTS(1) THEN xm_table(1):='Tom'; ELSE dbms_output.put_line('必须初始化集合元素'); END IF; IF xm_table.EXISTS(1) THEN xm_table(1):='Tom'; ELSE dbms_output.put_line('必须初始化集合元素'); END IF; 2.COUNT:返回当前集合变量中的无素总个数。 3.LIMIT:返回集合元素的最大个数。VARRAY返回所允许的最大元素个数，其它返回NULL。 4.FIRST和LAST:返回集合变量第一个和最后一个元素的下标。 5.PRIOR和NEXT:返回当前元素的前一个和后一下元素下标。 6.EXTEND 用于扩展集合变量的尺寸，并为它们增加元素。注意，该方法只适用于嵌套表和VARRAY。EXTEND用于为集合变量添加一个null元 素，EXTEND(n)用于为集合变量添加n个null元素，EXTEND(N,I)用于为集合变量添加n个元素(元素值与第i个元素相同)。 7.TRIM 用于从集合尾部删除元素，它有TRIM和TRIM(n)两种调用格式。TRIM用于从集合尾部删除一个元素；TRIM(n)则用于从集合尾部删除n个元素。注意，该方法只适用于嵌套格和VARRAY。 8.DELETE 用于删除元素，但该方法只适用于嵌套格和索引表，而不适用于VARRAY。DELETE用于删除集合变量的所有元素；DELETE(n)用于删除集合变量的第n个元素；而DELETE(m,n)则用于删除集合变量中从m到n之间的所有元素。