PL/SQL高级应用 - 集合

PL/SQL高级应用 - 集合

读者可能会问，为什么要使用集合？毕竟，使用带有外键的两个表已经允许创建数据之间的关联。答案是：相对于使用两个表，存储在集合中的数据可以被数据库更快地访问。通常情况下，如果数据只被一个表使用，那么使用集合。PL/SQL提供了3种不同的集合类型：index-by表（PL/SQL 2.0及更高版本）、嵌套表（PL/SQL 8.0及更高版本）和可变数组（PL/SQL 8.0及更高版本）。如下所示的程序例子介绍了上述3种集合类型的使用方法。


index-by表

index-by表类似于C语言中的数组，声明index-by表的方法如下：

TYPE tabletype IS TABLE OF type INDEX BY BINARU_INTEGER;

其中，tabletype是指所定义的新类型的类型名；type是要定义的index-by表的类型。

下例是一个定义index-by表的代码实例：

TYPE Country_Name IS TABLE OF COUNTRIES.COUNTRIES_NAME%TYPE

         INDEX BY BINARY_INTEGER;  --声明类型

v_Name Country_Name;              --声明变量

声明了类型和变量后，就可以用以下语句使用index-by表中的单个元素：

v_Name(index)

其中，index是指表中的第几个元素。



index的数据类型是BINARY_INTEGER。

1．元素赋值

我们可以使用以下语句给表中的元素赋值：

BEGIN

v_Name(1):= ‘China’;

v_Name(2):= ‘USA’;

END;



index-by表中的元素不是按特定顺序排列的，这是与C语言数组不同的，在C语言中数组在内存中是顺序存储的，因此元素的下标也是有序的。

也就是说下面的元素赋值也是合法的：

BEGIN

v_Name(1):= ‘China’;

v_Name(-2):= ‘USA’;

v_Name(5):= ‘Italy’;

END;



index-by表的元素的个数只是受到BINARY_INTEGER类型的限制，即index的范围为–2 147 483 647～2 147 483 647。因此只要在此范围内给元素赋值都是合法的。

2．调用不存在的元素

需要注意的是，对所有的index-by表的元素的调用之前都必须首先给该元素赋值，要不然系统就会提示出错。

DECLARE

TYPE StudyTab IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;

v_StudyTab StudyTab;

BEGIN

FOR v_Count IN 1..5 LOOP

  v_StudyTab(v_Count):=v_Count*100;

END LOOP;

FOR v_Count IN 1..5 LOOP

  DBMS_OUTPUT.PUT_LINE(v_StudyTab(v_Count));

END LOOP;

END;

在上面的代码中首先给各个元素赋值，然后将其输出，结果如下：

100

200

300

400

500

PL/SQL 过程已成功完成。

当将第二个FOR循环中的循环范围设置为1..6时，由于v_StudyTab(6)元素没有赋值，因此系统会出现如下的错误信息：

100

200

300

400

500

DECLARE

*

ERROR 位于第 1 行:

ORA-01403: 未找到数据

ORA-06512: 在line 10
嵌套表

嵌套表的声明和index-by表的声明十分类似，具体声明如下：

TYPE table_name is TABLE OF table_type [NOT NULL];

嵌套表与index-by表的唯一不同就是没有了INDEX BY BINARU_INTEGER子句，因此区别这两种类型的唯一方法就是看是否含有INDEX BY BINARU_INTEGER子句。

1．嵌套表初始化

嵌套表的初始化与index-by表的初始化是完全不同的。在声明了类型以后，再声明一个index-by表变量类型，如果没有给该表赋值，那么该表就是一个空的index-by表，但是在以后的语句中可以继续向index-by表中添加元素；而声明了嵌套表变量类型是指如果嵌套表中没有任何元素，那么它会自动初始化为NULL，并且是只读的，如果还想向这个嵌套表中添加元素，系统就会提示出错。下面的代码很好地说明了嵌套表的初始化：

DECLARE

TYPE StudyTab IS TABLE OF VARCHAR2(20);

v_StudyTab StudyTab:=StudyTab('Tom','Jack','Rose');

BEGIN

FOR v_Count IN 1..3 LOOP

  DBMS_OUTPUT.PUT_LINE(v_StudyTab(v_Count));

END LOOP;

END;

以上是嵌套表正确初始化的过程，系统会输出如下信息：

Tom

Jack

Rose

PL/SQL 过程已成功完成。

嵌套表初始化时没有元素，而后如果向其中添加元素，系统就会提示出错：

DECLARE

TYPE StudyTab IS TABLE OF VARCHAR2(20);

v_StudyTab StudyTab;

BEGIN

v_StudyTab(4):='Ronaldo';

END;

DECLARE

*

ERROR 位于第 1 行:

ORA-06531: 引用未初始化的收集

ORA-06512: 在line 5

如果嵌套表初始化了，但是还向其中添加元素，则出错信息如下：

DECLARE

*

ERROR 位于第 1 行:

ORA-06533: 下标超出数量

ORA-06512: 在line 5

2．元素序列

嵌套表与index-by表十分相似，只是嵌套表在结构上是有序的，而index-by表是无序的，也就是说，如果给一个嵌套表赋值，那么嵌套表元素的index将会从1开始依次递增，请看如下的代码：

D

ECLARE

TYPE NumTab IS TABLE OF NUMBER(4);

v_Num NumTab:=NumTab(1,3,4,5,7,9,11);

BEGIN

FOR v_Count IN 1..7 LOOP

  DBMS_OUTPUT.PUT_LINE( 'v_Num(' || v_Count || ')=' || v_Num(v_Count));

END LOOP;

END;

系统执行结果如下：

v_Num(1)=1

v_Num(2)=3

v_Num(3)=4

v_Num(4)=5

v_Num(5)=7

v_Num(6)=9

v_Num(7)=11

在此可以清楚地看到嵌套表是有序的。
可变数组

可变数组的声明如下：

TYPE type_name IS {VARRAY|VARYING ARRAY}(maximum_size)

         OF element_type[NOT NULL];

         --type_name是可变数组的类型名。

         --maximum_size是指可变数组元素个数的最大值。

         --element_type是指数组元素的类型。

可变数组的可变是指当定义了数组的最大上限后，数组元素的个数可以在这个最大上限内变化，但是不得超过最大上限，当数组元素的个数超过了最大上限后，系统就会提示出错。可变数组的存储和C语言的数组是相同的，各个元素在内存中是连续存储的。

下面是一个合法的可变数组的声明：

DECLARE

         --定义一个包含星期的可变数组

TYPE Dates IS VARRAY(7) OF VARCHAR2(10);

         --定义一个包含月份的可变数组

TYPE Months IS VARRAY(12) OF VARCHAR2(10);

与嵌套表一样，可变数组也需要初始化，初始化时需要注意的是赋值的数量必须保证不大于可变数组的最大上限。下例是可变数组初始化的实例：

DECLARE

         TYPE Dates IS VARRAY(7) OF VARCHAR2(10);

v_Dates Dates:=Dates('Monday','Tuesday','Wesdnesday');

BEGIN

DBMS_OUTPUT.PUT_LINE(v_Dates(1));

DBMS_OUTPUT.PUT_LINE(v_Dates(2));

DBMS_OUTPUT.PUT_LINE(v_Dates(3));

END;

与嵌套数组一样，当元素个数超出了最大上限后，系统会提示出错，这里不再重复介绍，读者可以将上例改造一下，如将最后一个输出语句的值改为v_Dates(4)。
集合的属性和方法

index-by表、嵌套表和可变数组都是对象类型，因此它们本身就有属性或者方法，集合的属性和方法的调用与其他对象类型的调用相同：

Object.Attribute或者Object.Method

下面将来介绍集合类型中几种常用的属性和方法。

（1）COUNT：COUNT是一个整数，它用来返回集合中的数组元素个数，下例说明了该属性的用法。

DECLARE

  --定义3种集合类型

TYPE Name IS TABLE OF VARCHAR(20) INDEX BY BINARY_INTEGER;

  TYPE Pwd IS TABLE OF VARCHAR(20);

  TYPE Dates IS VARRAY(7) OF VARCHAR2(10);

  --声明集合并初始化后两种集合类型

v_Name Name;

  v_Pwd Pwd:=Pwd('10000','12345','22','yes','no');

  v_Dates Dates:=Dates('Monday','Sunday');

BEGIN

  --初始化Index-By表

v_Name(1):='Tom';

  v_Name(-1):='Jack';

  v_Name(4):='Rose';

  --输出3种集合类型的元素个数

DBMS_OUTPUT.PUT_LINE('The Index-By Count is : '||v_Name.Count);

  DBMS_OUTPUT.PUT_LINE('The Nested Count is : '||v_Pwd.Count);

  DBMS_OUTPUT.PUT_LINE('The Varray Count is : '||v_Dates.Count);

END;

系统运行结果如下：

The Index-By Count is : 3

The Nested Count is : 5

The Varray Count is : 2

PL/SQL 过程已成功完成。

COUNT属性在PL/SQL编程中是十分有用的属性，对于那些集合元素的个数未知，而又想对其进行操作的模块是十分方便的。

（2）DELETE：DELETE是用来删除集合中的一个或者多个元素。需要注意的是由于DELETE方法执行的删除操作是大小固定的，所以对于可变数组来说是没有DELETE方法的（如果对可变数组执行DELETE方法，将会出错）。DELETE有以下3种方式：

l          DELETE：没带参数的DELETE方法即将整个集合删除。

l          DELETE(x)：即将位于集合表中第x个位置的元素删除。

l          DELETE(x,y)：即将集合表中从第x个元素到第y个元素之间的所有元素删除。



执行DELETE方法后，集合的COUNT值将会立刻变化；而且当要删除的元素不存在时，DELETE不会报错，而是跳过该元素，继续执行下一步操作。

下面是一个DELETE方法和COUNT属性联合使用的例子：

DECLARE

  TYPE Pwd IS TABLE OF VARCHAR(20);

  v_Pwd Pwd:=Pwd('10000','12345','22','yes','no','OK','All','Hello','Right','Left','FootBall');

BEGIN

--输出表的原始数据元素的个数

DBMS_OUTPUT.PUT_LINE('The original table count is : ');

DBMS_OUTPUT.PUT_LINE(v_Pwd.count);

--删除一个元素后再输出表的元素的个数

v_Pwd.DELETE(4);

DBMS_OUTPUT.PUT_LINE('After delete a element, table count is : ');

DBMS_OUTPUT.PUT_LINE(v_Pwd.count);

--删除一些元素后输出表的元素的个数

v_Pwd.DELETE(6,8);

DBMS_OUTPUT.PUT_LINE('After delete some element, table count is : ');

DBMS_OUTPUT.PUT_LINE(v_Pwd.count);

END;

（3）EXISTS：EXISTS用来判断集合中的元素是否存在。具体用法如下。

EXISTS(x)，即判断位于位置x处的元素是否存在，如果存在则返回TRUE，如果x大于集合的最大范围，则返回FALSE。



使用EXISTS判断时，只要在指定位置处有元素存在即可，即使该处的元素为 NULL，EXISTS也会返回TRUE。

（4）EXTEND：EXTEND用来将元素添加到集合的末端，具体形式有以下几种。

l          EXTEND：没带参数的EXTEND是将一个NULL元素添加到集合的末端。

l          EXTEND(x)：该形式是将x个NULL元素添加到集合的末端。

l          EXTEND(x,y)：该形式是将x个位于y的元素添加到集合的末端。

请看如下的代码：

DECLARE

  TYPE Pwd IS TABLE OF VARCHAR(20);

  v_Pwd Pwd:=Pwd('10000','12345','22','yes','no','OK','All','Hello','Right','Left','FootBall');

  v_Count INTEGER;

BEGIN

--输出初始集合的最后一个元素

v_Count:=v_Pwd.LAST;

DBMS_OUTPUT.PUT_LINE(v_Pwd(v_Count));

--向集合添加了两个'12345'（位于第2的元素）后，输出集合的最后一个元素

v_Pwd.EXTEND(2,'2');

v_Count:=v_Pwd.LAST;

DBMS_OUTPUT.PUT_LINE(v_Pwd(v_Count));

--在集合末端添加两个NULL值后，将最后一个位置赋值，并输出

v_Pwd.EXTEND(2);

v_Count:=v_Pwd.LAST;

v_Pwd(15):='Basketball';

DBMS_OUTPUT.PUT_LINE(v_Pwd(v_Count));

END;

系统运行结果如下：

FootBall

12345

Basketball

PL/SQL 过程已成功完成。



由于Index-by表元素的随意性，因此EXTEND方法只对嵌套表和可变数组有效。

（5）FIRST和LAST：FIRST用来返回集合第一个元素的位置，而LAST则是返回集合最后一个元素的位置。

（6）LIMIT：LIMIT用来返回集合中的最大元素个数。由于嵌套表没有上限，所以当嵌套表使用 LIMIT时，总是返回NULL值。如下例：

DECLARE

  TYPE Pwd IS TABLE OF VARCHAR(20);

  v_Pwd Pwd:=Pwd('10000','12345','22','yes','no','OK','All','Hello','Right','Left','FootBall');

  TYPE Name IS VARRAY(20) OF VARCHAR(20);

  v_Name Name:=Name('10000','12345','22','yes','no','OK','All','Hello','Right','Left','FootBall');

BEGIN

--分别输出嵌套表和可变数组的LIMIT值

DBMS_OUTPUT.PUT_LINE('The NestedTable Limit is : '||v_Pwd.LIMIT);

DBMS_OUTPUT.PUT_LINE('The VarrayTable Limit is : '||v_Name.LIMIT);

END;

系统运行结果如下：

The NestedTable Limit is :

The VarrayTable Limit is : 20

PL/SQL 过程已成功完成。

（7）NEXT和PRIOR：NEXT和PRIOR在使用时后面都会接一个参数，形式如下：

NEXT(x)、PRIOR(x)

其中，NEXT(x)返回的是位置为x处的元素后面的那个元素，而PRIOR(x) 则刚好相反，它返回的是x处的元素前面的那个元素。

通常NEXT和PRIOR一起使用，FIRST和LAST一起使用，用来进行循环处理：

DECLARE

  TYPE Pwd IS TABLE OF VARCHAR(20);

  v_Pwd Pwd:=Pwd('10000','12345','22','yes','no','OK','All','Hello','Right','Left','FootBall');

  v_Count INTEGER;

BEGIN

v_Count:=v_Pwd.FIRST;

WHILE v_Count<=v_Pwd.LAST LOOP

  DBMS_OUTPUT.PUT_LINE(v_Pwd(v_Count));

  v_Count:=v_Pwd.NEXT(v_Count);

END LOOP;

END;

系统执行结果如下：

10000

12345

22

yes

no

OK

All

Hello

Right

Left

FootBall

PL/SQL 过程已成功完成。

上例中是顺序打印集合中的元素，其实通过灵活运用这4个函数我们还可以反向打印集合中的各个元素，具体代码如下：

DECLARE

  TYPE Pwd IS TABLE OF VARCHAR(20);

  v_Pwd Pwd:=Pwd('10000','12345','22','yes','no','OK','All','Hello','Right','Left','FootBall');

  v_Count INTEGER;

BEGIN

v_Count:=v_Pwd.LAST;

WHILE v_Count>=v_Pwd.FIRST LOOP

  DBMS_OUTPUT.PUT_LINE(v_Pwd(v_Count));

  v_Count:=v_Pwd.PRIOR(v_Count);

END LOOP;

END;

（8）TRIM：TRIM方法用来删除集合末端的元素。

其具体形式如下。

l          TRIM：没有参数的TRIM是从集合中末端删除一个元素。

l          TRIM(x)：该形式是从集合末端删除x个元素，其中x要小于集合的COUNT数，否则系统会提示出错。



与EXTEND一样的是，由于Index-by表元素的随意性，因此TRIM方法只对嵌套表和可变数组有效。