PL/SQL集合类型的整理学习

http://log-cd.iteye.com/blog/521177

 

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

Sql代码

1. TYPE type_name IS TABLE OF element_type  
2. [NOT NULL] INDEX BY key_type;  
3. identifier type_name; 

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

Sql代码

1. declare
2.   type xm_table_type is table of drv_admin.drv_temp.xm%type  
3. index by binary_integer;  
4.   xm_talbe xm_table_type;  
5. begin
6. select xm into xm_talbe(-1) from drv_admin.drv_temp  
7. where lsh='&lsh';  
8.   dbms_output.put_line('姓名:'||xm_talbe(-1));  
9. end; 

   示例二：

Sql代码

1. declare
2.   type area_table_type is table of number  
3. index by varchar2(10);  
5.   area_table area_table_type;  
6. begin
7.   area_table('北京'):=1;  
8.   area_table('上海'):=2;  
9.   area_table('天津'):=3;  
10.   dbms_output.put_line('第一个元素：'||area_table.first);--第一个元素下标
11.   dbms_output.put_line('最后一个元素：'||area_table.last);--最后一个元素下标
12. end; 

<二>、嵌套表
   嵌套表的元素下标从1开始，并且元素个数没有限制。嵌套表数组元素值可以是稀疏的。
   注意：索引表类型不能作为表列的数据类型使用，但嵌套表类型可以作为表列的数据类型。
   语法：

Sql代码

1. type type_name is table of element_type;  
2. identifier type_name; 

1.在PL/SQL块中使用嵌套表

Sql代码

1. declare
2.   type xm_table_type is table of drv_admin.drv_temp.xm%type;  
3.   xm_table xm_table_type;  
4. begin
5.   xm_table:=xm_table_type(' ',' ',' ');--这里必须使用构造方法初始化嵌套表变量。
6. select xm into xm_table(2) from drv_admin.drv_temp  
7. where lsh='&lsh';  
8.   dbms_output.put_line('姓名：'||xm_table(2));  
9. end; 

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

Sql代码

1. create type phone_type is table of varchar2(20);  
2. /  
3. create table employee(  
4.   id number(4),name varchar2(10),sal number(6,2),phone phone_type  
5. )nested table phone store as phone_table; 

(1)在嵌套表中插入数据
  当定义嵌套表类型时，Oracle自动为该类型生成相应的构造方法。当为嵌套表列插入数据时，需要使用嵌套表的构造方法。

Sql代码

1. BEGIN
2. INSERT INTO employee VALUES(1,'TOM',800,  
3.     phone_type('028-90909800','13913001300')  
4.   );  
5. END;  
6. / 

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

Sql代码

1. DECLARE
2.   phone_table phone_type;  
3. BEGIN
4. SELECT phone INTO phone_table  
5. FROM employee WHERE id=1;  
6. FOR i IN 1..phone_table.COUNT LOOP  
7.    dbms_output.put_line('电话号码：'||phone_table(i));  
8. END LOOP;  
9. END; 

(3)在嵌套表中更新数据
  首先需要定义嵌套表变量，并使用构造方法初始化该变量，然后才可在执行部分使用UPDATE语句更新其数据。

Sql代码

1. DECLARE
2.   phone_table phone_type:=phone_type('028-10001000','139800900100');  
3. BEGIN
4. UPDATE employee SET phone=phone_table  
5. WHERE id=1;  
6. END;  
7. / 

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

Sql代码

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

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

Sql代码

1. DECLARE
2.   TYPE xm_table_type IS VARRAY(20) OF drv_admin.drv_temp.xm%TYPE;  
3.   xm_table xm_table_type:=xm_table_type(' ');  
4. BEGIN
5. SELECT xm INTO xm_table(1) FROM drv_admin.drv_temp  
6. WHERE lsh='&lsh';  
7.   dbms_output.put_line('姓名：'||xm_table(1));  
8. END;  
9. / 

2.在表列中使用VARRAY

Sql代码

1. CREATE TYPE phone_type IS VARRAY(20) OF VARCHAR2(20);  
2. /  
3. CREATE TABLE employee(  
4.   id NUMBER(4), name VARCHAR2(10), sal NUMBER(6,2), phone phone_type  
5. ); 

   注意，嵌套表列的元素个数没有限制，而VARRAY列的元素个数是有限制的。
<四>、PL/SQL记录表
  为了在PL/SQL块中处理多行多列数据，开发人员可以使用PL/SQL记录表。

Sql代码

1. DECLARE
2.   TYPE tmp_table_type IS TABLE OF drv_admin.drv_temp%ROWTYPE  
3. INDEX BY BINARY_INTEGER;  
5.   tmp_table tmp_table_type;  
6. BEGIN
7. SELECT * INTO tmp_table(1) FROM drv_admin.drv_temp  
8. WHERE lsh='&lsh';  
9.   dbms_output.put_line('姓名：'||tmp_table(1).xm);  
10.   dbms_output.put_line('备注：'||tmp_table(1).bz);  
11. END;  
12. / 

<五>、集合方法
   语法：

Sql代码

1. collection_name.method_name[(parameters)] 

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

Sql代码

1. IF xm_table.EXISTS(1) THEN
2.    xm_table(1):='Tom';  
3. ELSE
4.    dbms_output.put_line('必须初始化集合元素');  
5. 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之间的所有元素。