Oracle定义联合数组及使用技巧

陈晓龙

联合数组以前被称为PL/SQL表。在表中不能使用联合数组，只能将它们用作程序设计的结构体。只能在PL/SQL中访问联合数组：

联合数组以前被称为PL/SQL表。在表中不能使用联合数组，只能将它们用作程序设计的结构体。只能在PL/SQL中访问联合数组。

注意到联合数组带来的一些关键问题是非常重要的。这些问题使我们介绍它们的用法时，需要采取一些特别的方法。这些问题包括：

联合数组不需要初始化，也没有构造函数语法。在对它们进行赋值以前，也不需要专门为其分配存储空间，也就不需要使用集合API的EXTEND方法。

在ORACLE 10G中，以及在ORACLE 10G以前的版本中，都可以使用数字索引联合数组。另外，在ORACLE 10G中，还可以使用具有唯一性的变长字符串作为联合数组的索引。

可以使用任意的整数作为联合数组的索引，这就说明联合数组的索引可以是任意正数、负数或0。
可以显式地将等价的%ROWTYPE、记录类型和对象类型的返回值，转换成联合数组的结构体。

联合数组是使用FORALL语句或BULK COLLECT子句的关键，而后者则允许数据库到程序设计单元的批转换。
在使用了全球化设置，例如NLS_COMP或NLS_SORT初始化参数的数据库中，将字符串用作联合数组索引的时候，需要我们进行特殊的处理。

1、定义联合数组和用作PL/SQL的程序结构体
在PL/SQL语言中定义联合数组的语法有两种，一种是：
CREATE OR REPLACE TYPE type_name
AS TABLE OF element_type [NOT NULL]
INDEX BY [PLS_INTEGER | BINARY_INTEGER | VARCHAR2(size) ];
可以将正数、负数或者0值用作联合数组的索引。ORACLE 10G中的PLS_INTEGER何BINARY_INTEGER类型都是不受限制的数据类型，这两个数据类型都映射到C/C++、C#和JAVA的调用规范中。
变长字符串的最大长度为4000个字符。
另一种定义联合数组的语法是：
CREATE OR REPLACE TYPE type_name
AS TABLE OF element_type [NOT NULL]
INDEX BY key_type;
其中的key_type允许我们使用VARCHAR2、STRING或LONG类型。使用VARCHAR2和STRING时，都需要定义大小。使用LONG类型时，则不需要定义大小，因为它是通过定义VARCHAR（32760）进行定义的。
联合数组不需要进行初始化，也没有构造函数语法。这是与其他两种集合类型（VARRAYS和嵌套表）有着本质区别的地方。
如果你像下面这样构造一个联合数组，那么会引发PLS-00222异常。
代码如下:

-- Define an associative array of strings.
TYPE card_table IS TABLE OF VARCHAR2(5 CHAR)
INDEX BY BINARY_INTEGER;
-- and attempt to construct an associative array.
cards CARD_TABLE := card_table('A','B','C');
BEGIN
NULL;
END;


在前面的介绍中，我们知道对象的构造函数是完全可以作为一个函数使用的。其他集合类型，例如VARRAYS和嵌套表，都是显式定义构造函数的对象类型。而联合数组只是一个结构体，不是一个对象类型。因此，它不能显式地创建构造函数，也无法调用构造函数。
2、联合数组的初始化
前面已经说过，我们可以将数字或者具有唯一性的变长字符串作为索引，构造联合数组。数字索引比如为整数，可以为正整数、负整数和0值。唯一性的变长字符串可以是VARCHAR2、STRING或LONG数据类型。
1）以数字作为联合数组索引
下面的例子给出了一个向以数字为索引的联合数组中的元素赋值的过程，该示例示范了将VARRAY的内容转移到联合数组的过程。
代码如下:

-- Define a varray of twelve strings.
TYPE months_varray IS VARRAY(12) OF STRING(9 CHAR);
-- Define an associative array of strings.
TYPE calendar_table IS TABLE OF VARCHAR2(9 CHAR)
INDEX BY BINARY_INTEGER;
-- and construct a varray.
month MONTHS_VARRAY :=
months_varray('January','February','March'
,'April','May','June'
,'July','August','September'
,'October','November','December');
-- an associative array variable.
calendar CALENDAR_TABLE;
BEGIN
-- Check if calendar has no elements.
IF calendar.COUNT = 0 THEN
-- Print a title
DBMS_OUTPUT.PUT_LINE('Assignment loop:');
DBMS_OUTPUT.PUT_LINE('----------------');
-- Loop through all the varray elements.
FOR i IN month.FIRST..month.LAST LOOP
-- Initialize a null associative array element.
calendar(i) := '';
-- Print an indexed element from the associative array.
DBMS_OUTPUT.PUT_LINE(
'Index ['||i||'] is ['||calendar(i)||']');
-- Assign the numeric index valued varray element
-- to an equal index valued associative array element.
calendar(i) := month(i);
END LOOP;
-- Print a title
DBMS_OUTPUT.PUT(CHR(10));
DBMS_OUTPUT.PUT_LINE('Post-assignment loop:');
DBMS_OUTPUT.PUT_LINE('---------------------');
-- Loop through all the associative array elements.
FOR i IN calendar.FIRST..calendar.LAST LOOP
-- Print an indexed element from the associative array.
DBMS_OUTPUT.PUT_LINE(
'Index ['||i||'] is ['||calendar(i)||']');
END LOOP;
END IF;
END;
/


在第一个FOR-LOOP循环中，用等于VARRAY类型的month索引的一个索引值，为联合数组类型的calendar变量赋上一个空值。这是为联合数组分配空间的唯一方法。
2）以唯一字符串作为联合数组索引
如下例所示：
代码如下:

-- Define a varray of twelve variable length strings.
TYPE months_varray IS VARRAY(12) OF STRING(9 CHAR);
-- Define an associative array of variable length strings.
TYPE calendar_table IS TABLE OF VARCHAR2(9 CHAR)
INDEX BY VARCHAR2(9 CHAR);
-- and construct a varray.
month MONTHS_VARRAY :=
months_varray('January','February','March'
,'April','May','June'
,'July','August','September'
,'October','November','December');
-- an associative array variable.
calendar CALENDAR_TABLE;
BEGIN
-- Check if calendar has no elements.
IF calendar.COUNT = 0 THEN
-- Print a title
DBMS_OUTPUT.PUT_LINE('Assignment loop:');
DBMS_OUTPUT.PUT_LINE('----------------');
-- Loop through all the varray elements.
FOR i IN month.FIRST..month.LAST LOOP
-- Assign the numeric index valued varray element
-- to an equal index valued associative array element.
calendar(month(i)) := ''; --i;
-- Print an indexed element from the associative array.
DBMS_OUTPUT.PUT_LINE(
'Index ['||month(i)||'] is ['||i||']');
END LOOP;
-- Print a title
DBMS_OUTPUT.PUT(CHR(10));
DBMS_OUTPUT.PUT_LINE('Post-assignment loop:');
DBMS_OUTPUT.PUT_LINE('---------------------');
-- Loop through all the associative array elements.
FOR i IN calendar.FIRST..calendar.LAST LOOP
-- Print an indexed element from the associative array.
DBMS_OUTPUT.PUT_LINE(
'Index ['||i||'] is ['||calendar(i)||']');
END LOOP;
END IF;
END;


运行上面这段代码会出现错误。ORA-06502：PL/SQL：numeric or value error：character to number convertion error。在第一个FOR-LOOP中的初始化是没有任何问题的。可是在第二个FOR-LOOP循环中，程序试图向计数器变量传递一个非数字的值。在上面的程序中，这个计数器变量是i。计数器变量的数据类型被定义为PLS_INTEGER类型。所以，就不能将整个变长字符串的索引值赋给一个整型变量—因为变长字符串不是整数。这样，自然就引发了类型转换错误ORA-06502。该示例之所以会引发错误，是因为在初始化联合数组成员的时候，其中的计数器变量被转换为VARCHAR2类型，而在读联合数组的时候，又将该计数器类型转为INTEGER类型。
这其实给我们提出了一个新问题。非数字索引值需要我们明确的知道索引的开始值以及索引的递增方法。集合API的FIRST何NEXT方法提供了这种工具。
如下例所示：


代码如下:-- Define variables to traverse an associative array that
-- uses variable length strings for index values.
current VARCHAR2(9 CHAR);
element INTEGER;
-- Define a varray of twelve variable length strings.
TYPE months_varray IS VARRAY(12) OF STRING(9 CHAR);
-- Define an associative array of variable length strings.
TYPE calendar_table IS TABLE OF VARCHAR2(9 CHAR)
INDEX BY VARCHAR2(9 CHAR);
-- and construct a varray.
month MONTHS_VARRAY :=
months_varray('January','February','March'
,'April','May','June'
,'July','August','September'
,'October','November','December');
-- an associative array variable.
calendar CALENDAR_TABLE;
BEGIN
-- Check if calendar has no elements.
IF calendar.COUNT = 0 THEN
-- Print a title
DBMS_OUTPUT.PUT_LINE('Assignment loop:');
DBMS_OUTPUT.PUT_LINE('----------------');
-- Loop through all the varray elements.
FOR i IN month.FIRST..month.LAST LOOP
-- Assign the numeric index valued varray element
-- to an equal index valued associative array element.
calendar(month(i)) := TO_CHAR(i);
-- Print an indexed element from the associative array.
DBMS_OUTPUT.PUT_LINE(
'Index ['||month(i)||'] is ['||i||']');
END LOOP;
-- Print a title
DBMS_OUTPUT.PUT(CHR(10));
DBMS_OUTPUT.PUT_LINE('Post-assignment loop:');
DBMS_OUTPUT.PUT_LINE('---------------------');
-- Loop through all the associative array elements.
FOR i IN 1..calendar.COUNT LOOP
-- Check if the first element in the loop.
IF i = 1 THEN
-- Assign the first character index to a variable.
current := calendar.FIRST;
-- Use the derived index to find the next index.
element := calendar(current);
ELSE
-- Check if next index value exists.
IF calendar.NEXT(current) IS NOT NULL THEN
-- Assign the character index to a variable.
current := calendar.NEXT(current);
-- Use the derived index to find the next index.
element := calendar(current);
ELSE
-- Exit loop since last index value is read.
EXIT;
END IF;
END IF;
-- Print an indexed element from the associative array.
DBMS_OUTPUT.PUT_LINE(
'Index ['||current||'] is ['||element||']');
END LOOP;
END IF;
END;


3、与BULK COLLECT和FORALL结合使用联合数组
使用BULK COLLECT和FORALL胃我们打开了消除行级处理之门。使用BULK COLLECT可以获取存储在联合数组或嵌套表中的记录集。使用FORALL可以成批的发送DML语句。FORALL可以插入、更新和删除数据。这些方法减少了PL/SQL引擎和SQL引擎之间来回切换上下文环境的次数。如果没有这些方法，就会有太多的解析或取值过程。
你应该还记得行级处理实际上使用的是%ROWTYPE和%TYPE。前者可以直接映射到记录类型上。BULK COLLECT可以将%ROWTYPE或%TYPE类型的值的一个集合作为联合数组或嵌套表的一个集合进行赋值。FORALL提供了一种可以将联合数组或嵌套表中的内容转移到数据库对象的方法。
联合数组和嵌套表集合类型可以与BULK COLLECT和FORALL结合使用。使用嵌套表时，需要将嵌套表构造为空元素的集合。BULK COLLECT会显式地分配嵌套表的存储空间。不需要对联合数组进行构造，只要一个批赋值就可以了。同样，联合数组和嵌套表都可以作为SQL命令FORALL的源结构。
如下示例所示：


代码如下:-- Create a table for the example.
CREATE TABLE bulk_numbers
(number_id NUMBER NOT NULL
,CONSTRAINT number_id_pk PRIMARY KEY (number_id));
-- Define an associative array of integers.
TYPE number_table IS TABLE OF bulk_numbers.number_id%TYPE
INDEX BY BINARY_INTEGER;
-- Define a variable of the associative array type.
number_list NUMBER_TABLE;
BEGIN
-- Loop from 1 to a million and increment associative array.
FOR i IN 1..10000 LOOP
-- Assign number value.
number_list(i) := i;
END LOOP;
-- Loop through all to do a bulk insert.
FORALL i IN 1..number_list.COUNT
INSERT
INTO bulk_numbers
VALUES (number_list(i));
-- Commit records.
COMMIT;
END;
-- Use a BULK COLLECT to retrieve a table into an
-- associative array.
-- Define an associative array of integers.
TYPE number_table IS TABLE OF bulk_numbers.number_id%TYPE
INDEX BY BINARY_INTEGER;
-- Define a variable of the associative array type.
number_list NUMBER_TABLE;
BEGIN
-- Check if calendar has no elements.
SELECT number_id
BULK COLLECT
INTO number_list
from bulk_numbers;
-- Print a title
DBMS_OUTPUT.PUT_LINE('Bulk Collected:');
DBMS_OUTPUT.PUT_LINE('---------------');
-- Loop through to print elements.
--只打印前两条和最后两条记录
FOR i IN number_list.FIRST..number_list.LAST LOOP
-- Print only the first and last two.
IF i <= 2 OR i >= 9999 THEN
-- Print an indexed element from the associative array.
DBMS_OUTPUT.PUT_LINE('Number ['||number_list(i)||']');
END IF;
END LOOP;
END;


在BULK COLLECT子句中使用了ORDER BY，保证得出的结果是按照数字升序排列的。如果不对元素进行排序，就会发现它们是按照随机的顺序获取的，而不是按它们的数字顺序进行获取的。

张兴康

陈晓龙 发表于 2016-9-30 15:48

你发的这个帖子，打开的时候谷歌都在问我是否需要翻译

陈晓龙

张兴康 发表于 2016-9-30 13:49

张兴康