PL/SQL集合类型

    PL/SQL集合类型是类似于高级语言数组的一种复合数据类型，集合类型包括索引表（PL/SQL表）、嵌套表（Nested Table）和变长数组（VARRAY）三种类型。
<一>、索引表
    索引表也称为PL/SQL表，它是Oracle早期版本用于处理PL/SQL数组的数据类型。索引表的元素个数没有限制，并且下标可以为负值。注意，索引表只能作为PL/SQL复合数据类型使用，而不能作为表列的数据类型使用。
    语法：

TYPE type_name IS TABLE OF element_type
[NOT NULL] INDEX BY key_type;
identifier type_name;

    说明：从Oracle9i开始，索引表下标不仅允许使用数据类型BINARY_INTEGER、PLS_INTEGER,而且允许使用数据类型VARCHAR2。
    示例一：

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 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开始，并且元素个数没有限制。嵌套表数组元素值可以是稀疏的。
   注意：索引表类型不能作为表列的数据类型使用，但嵌套表类型可以作为表列的数据类型。
   语法：

type type_name is table of element_type;
identifier type_name;

1.在PL/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;

2.在表列中使用嵌套表
  在表列中使用嵌套表类型，必须首先使用CREATE TYPE命令建立嵌套表类型。另外注意，必须要为嵌套表列指定专门的存储表。
  示例如下：

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自动为该类型生成相应的构造方法。当为嵌套表列插入数据时，需要使用嵌套表的构造方法。

BEGIN
  INSERT INTO employee VALUES(1,'TOM',800,
    phone_type('028-90909800','13913001300')
  );
END;
/

(2)在嵌套表中检索数据
   需要定义嵌套表类型的变量接收其数据。

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语句更新其数据。

DECLARE
  phone_table phone_type:=phone_type('028-10001000','139800900100');
BEGIN
  UPDATE employee SET phone=phone_table
    WHERE id=1;
END;
/

<三>、变长数组（VARRAY）
   可以作为表列的数据类型使用。其元素下标以1开始，并且元素的最大个数是有限制的。
   语法：

TYPE type_name IS VARRAY(size_limit) OF element_type [NOT NULL];
Identifier type_name;

   注意，当使用VARRAY元素时，必须要使用其构造方法初始化VARRAY元素。
1.在PL/SQL块中使用VARRAY

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

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记录表。

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;
/

<五>、集合方法
   语法：

collection_name.method_name[(parameters)]

   注意，集合方法只能在PL/SQL语句中使用，而不能在SQL语句中调用。另外集全方法EXTEND和TRIM只适用于嵌套表和VARRAY，而不适用于索引表。
1.EXISTS:确定集合元素是否存在。

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之间的所有元素。

评论

3 楼 log_cd 2010-08-21  

   PL/SQL表只有两列,其中第一列为索引列为Integer类型索引列，9i后可以支持字符索引。第二列为用户自定义列，声明用户可用列类型。
   通过索引列中的索引值来操作PL/SQL表中对应的用户自定义列。类似于键值对。确保索引值的唯一性。如果索引值重复不会报错，会覆盖前面的值。
    记录(Record)的单行多列和物理存储数据的表。

2 楼 nwpu053871 2010-08-20  

索引表和PL/SQL记录表有区别吗？

1 楼 nwpu053871 2010-08-20  

总结的很好很详细！