http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/collections.htm#CIHIEBJC
本文内容
- 定义 Collection 类型
- 声明 Collection 变量
- 初始化和引用 Collection
- 引用 Collection 元素
- 给 Collection 赋值
- 多维 Collection
- 比较 Collection
- Collection 方法
- Collection 异常
了解 Oracle 集合意义在于：当我们采用编程语言开发应用程序时，对其内置的集合类型，如数组、链表等，使用得很多，也很方便，但是当业务逻辑变得复杂时，如需要同时向多个表插入数据，和一个表插入多条数据，也许还需要事物控制，此时使用匿名子程序似乎很合适。那么，在匿名子程序中使用集合类型就不可避免。
另外，若有一个用编程语言写的函数，其涉及集合操作，出于某种必要的原因，想改写成 Oracle 函数，那么，对了解 Oracle 集合操作很有必要。
定义 Collection 类型
先定义 collection 类型，之后再声明该类型的变量。
你可以在模式级别、包或 PL/SQL 块内定义一个 collection 类型。
在模式级别创建的 collection 类型是 standalone stored type。用 CREATE TYPE 语句创建。它存储在数据库，直到用 DROP TYPE 语句删除该类型。
在包内创建的 collection 类型是 packaged type。它存储在数据库，直到用 DROP PACKAGE 语句删除包。
在 PL/SQL 块创建的 collection 类型只在块内可用，只有块嵌入在 standalone 或 packaged subprogram 中它才存储在数据库。
collection 类型遵循与其他类型、变量一样的作用域和实例化规则。当你输入一个块或子程序时，collection 被实例化，当退出时，销毁。在一个包中，当初从引用包时，collection 被实例化，并在结束数据库会话时，销毁。
你可以在任何 PL/SQL 块、子程序，或包的声明部分，使用 TYPE 来定义 TABLE 和 VARRAY 类型。
对于在 PL/SQL 内声明的 nested table 和 varray，table 或 varray 的元素类型可以是，除了 REF CURSOR 以外的任意 PL/SQL 数据类型。
当定义一个 VARRAY 类型时，必须用一个正整数指定最大大小。如下所示，定义一个能最多存储 366 个日期的 VARRAY：
DECLARE
TYPE Calendar IS VARRAY(366) OF DATE;
associative arrays 可以用任意键值插入元素。键不需要是连续的。键的数据类型可以是 PLS_INTEGER、VARCHAR2，或 VARCHAR2 子类型的一个：VARCHAR、STRING、或 LONG.
必须指定基于 VARCHAR2 键的长度，除了 LONG，因为，它等价于声明一个 VARCHAR2(32760) 类型的键。类型 RAW、LONG RAW、ROWID、CHAR 和 CHARACTER 不允许做为一个 associative array 的键。LONG 和 LONG RAW 仅仅是为了向后兼容。
不允许初始化子句。associative arrays 没有构造标记。当使用基于 VARCHAR2 键来引用 associative arrays 中的一个元素时，你可以使用如 DATE 或 TIMESTAMP 等类型，只要该类型可以用 TO_CHAR 函数转换成 VARCHAR2。
声明 Collection 变量
定义一个 collection 类型后，用该类型声明变量。在声明中使用新类型名，同预定义类型一样，如 NUMBER。
示例 1：演示声明 nested table、varray 和 associative array
DECLARE
TYPE nested_type IS TABLE OF VARCHAR2(30);
TYPE varray_type IS VARRAY(5) OF INTEGER;
TYPE assoc_array_num_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
TYPE assoc_array_str_type IS TABLE OF VARCHAR2(32) INDEX BY PLS_INTEGER;
TYPE assoc_array_str_type2 IS TABLE OF VARCHAR2(32) INDEX BY VARCHAR2(64);
v1 nested_type;
v2 varray_type;
v3 assoc_array_num_type;
v4 assoc_array_str_type;
v5 assoc_array_str_type2;
BEGIN
-- an arbitrary number of strings can be inserted v1
v1 := nested_type('Shipping', 'Sales', 'Finance', 'Payroll');
v2 := varray_type(1, 2, 3, 4, 5); -- Up to 5 integers
v3(99) := 10; -- Just start assigning to elements
v3(7) := 100; -- Subscripts can be any integer values
v4(42) := 'Smith'; -- Just start assigning to elements
v4(54) := 'Jones'; -- Subscripts can be any integer values
v5('Canada') := 'North America';
-- Just start assigning to elements
v5('Greece') := 'Europe';
-- Subscripts can be string values
END;
/
示例 2：演示用 %TYPE 声明 Collection
使用 %TYPE 指定之前已声明的 collection 类型，改变 collection 定义，会根据元素数量和类型自动更新其他变量。
DECLARE
TYPE few_depts IS VARRAY(10) OF VARCHAR2(30);
TYPE many_depts IS VARRAY(100) OF VARCHAR2(64);
some_depts few_depts;
/* If the type of some_depts changes from few_depts to many_depts,
local_depts and global_depts will use the same type
when this block is recompiled */
local_depts some_depts%TYPE;
global_depts some_depts%TYPE;
BEGIN
NULL;
END;
/
示例 3：演示声明一个 nested Table 作为存储过程的参数
声明 collection 为子程序的形参，把 collection 从一个子程序传递给另一个子程序。
CREATE PACKAGE personnel AS
TYPE staff_list IS TABLE OF employees.employee_id%TYPE;
PROCEDURE award_bonuses (empleos_buenos IN staff_list);
END personnel;
/
CREATE PACKAGE BODY personnel AS
PROCEDURE award_bonuses (empleos_buenos staff_list) IS
BEGIN
FOR i IN empleos_buenos.FIRST..empleos_buenos.LAST
LOOP
UPDATE employees SET salary = salary + 100
WHERE employees.employee_id = empleos_buenos(i);
END LOOP;
END;
END;
/
若从包外部调用 personnel.award_bonuses，可以声明一个 personnel.staff_list 类型的变量，并把它作为参数传递。
DECLARE
good_employees personnel.staff_list;
BEGIN
good_employees := personnel.staff_list(100, 103, 107);
personnel.award_bonuses (good_employees);
END;
/
也可以在 RETURN 子句指定 collection 类型。
示例 4：演示用 %TYPE 和 %ROWTYPE 指定 collection 元素类型
若指定元素类型，则可用 %TYPE，提供一个变量或数据库列的数据类型（data type ）。也可用 %ROWTYPE，提供游标或数据库表的行类型（row type）。
DECLARE
-- Nested table type that can hold an arbitrary number
-- of employee IDs.
-- The element type is based on a column from the EMPLOYEES table.
-- You need not know whether the ID is a number or a string.
TYPE EmpList IS TABLE OF employees.employee_id%TYPE;
-- Declare a cursor to select a subset of columns.
CURSOR c1 IS SELECT employee_id FROM employees;
-- Declare an Array type that can hold information
-- about 10 employees.
-- The element type is a record that contains all the same
-- fields as the EMPLOYEES table.
TYPE Senior_Salespeople IS VARRAY(10) OF employees%ROWTYPE;
-- Declare a cursor to select a subset of columns.
CURSOR c2 IS SELECT first_name, last_name FROM employees;
-- Array type that can hold a list of names. The element type
-- is a record that contains the same fields as the cursor
-- (that is, first_name and last_name).
TYPE NameList IS VARRAY(20) OF c2%ROWTYPE;
BEGIN
NULL;
END;
/
示例 5：演示使用 RECORD 类型指定元素类型：Records 的 varray
DECLARE
TYPE name_rec IS RECORD(
first_name VARCHAR2(20),
last_name VARCHAR2(25));
TYPE names IS VARRAY(250) OF name_rec;
BEGIN
NULL;
END;
/
示例 6：演示在 Collection 元素上加 NOT NULL 约束
DECLARE
TYPE EmpList IS TABLE OF employees.employee_id%TYPE NOT NULL;
v_employees EmpList := EmpList(100, 150, 160, 200);
BEGIN
v_employees(3) := NULL; -- assigning NULL raises an exception
END;
/
初始化和引用 Collection
初始化时，一个 nested table 或 varray 会自动为 null。collection 本身为 null，它没有元素。若初始化一个 nested table 或 varray，可以使用系统预定义的构造函数，该构造函数与 collection 类型名同名，通过传递给它的元素构造 collection。
必须显示为 nested table 和 varray 变量调用构造函数。而 associative array 则不需要这样。
示例 7：演示构造 nested table
DECLARE
TYPE dnames_tab IS TABLE OF VARCHAR2(30);
dept_names dnames_tab;
BEGIN
dept_names := dnames_tab('Shipping','Sales','Finance','Payroll');
END;
/
nested table 没有声明大小，可以在构造函数放很多元素。
示例 8：演示构造 varray
DECLARE
-- In the varray, put an upper limit on the number of elements
TYPE dnames_var IS VARRAY(20) OF VARCHAR2(30);
dept_names dnames_var;
BEGIN
-- Because dnames is declared as VARRAY(20),
-- you can put up to 10 elements in the constructor
dept_names := dnames_var('Shipping','Sales','Finance','Payroll');
END;
/
示例 9：演示 Collection 构造函数包含 Null 元素
DECLARE
TYPE dnames_tab IS TABLE OF VARCHAR2(30);
dept_names dnames_tab;
TYPE dnamesNoNulls_type IS TABLE OF VARCHAR2(30) NOT NULL;
BEGIN
dept_names := dnames_tab('Shipping', NULL,'Finance', NULL);
-- If dept_names were of type dnamesNoNulls_type,
-- you could not include null values in the constructor
END;
/
示例 10：演示 Collection 声明和构造相结合
DECLARE
TYPE dnames_tab IS TABLE OF VARCHAR2(30);
dept_names dnames_tab :=
dnames_tab('Shipping','Sales','Finance','Payroll');
BEGIN
NULL;
END;
/
示例 11：演示无参数的 varray 构造函数，会得到一个空的，而不是 null 的 collection
DECLARE
TYPE dnames_var IS VARRAY(20) OF VARCHAR2(30);
dept_names dnames_var;
BEGIN
IF dept_names IS NULL THEN
DBMS_OUTPUT.PUT_LINE
('Before initialization, the varray is null.');
-- While the varray is null, you cannot check its COUNT attribute.
-- DBMS_OUTPUT.PUT_LINE
-- ('It has ' || dept_names.COUNT || ' elements.');
ELSE
DBMS_OUTPUT.PUT_LINE
('Before initialization, the varray is not null.');
END IF;
dept_names := dnames_var(); -- initialize empty varray
IF dept_names IS NULL THEN
DBMS_OUTPUT.PUT_LINE
('After initialization, the varray is null.');
ELSE
DBMS_OUTPUT.PUT_LINE
('After initialization, the varray is not null.');
DBMS_OUTPUT.PUT_LINE
('It has ' || dept_names.COUNT || ' elements.');
END IF;
END;
/
引用 Collection 元素
引用元素包括一个 collection 名字和用大括号括起来的标值（subscript）。通过下面语法：
collection_name (subscript)
这里的“标值”，通常，是一个返回整数值的表达式，或用字符串声明的 associative arrays 键，它是一个 VARCHAR2。
标值的范围如下：
- 对于 nested tables，1..2147483647 （上限是 PLS_INTEGER）。
- 对于 varrays，1.. size_limit，你在声明中指定的大小，size_limit 不能超过 2147483647。
- 对于带数字型键的 associative arrays，-2147483648..2147483647。
- 对于带字符串键的 associative arrays，键的长度和可能值的数量依赖于类型声明中 VARCHAR2 的限制，和数据库字符集。
示例 12：演示引用 nested table 元素
DECLARE
TYPE Roster IS TABLE OF VARCHAR2(15);
names Roster :=
Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh');
PROCEDURE verify_name(the_name VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(the_name);
END;
BEGIN
FOR i IN names.FIRST .. names.LAST
LOOP
IF names(i) = 'J Hamil' THEN
DBMS_OUTPUT.PUT_LINE(names(i));
-- reference to nested table element
END IF;
END LOOP;
verify_name(names(3));
-- procedure call with reference to element
END;
/
示例 13：演示引用 associative array 元素
DECLARE
TYPE sum_multiples IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
n PLS_INTEGER := 5; -- number of multiples to sum for display
sn PLS_INTEGER := 10; -- number of multiples to sum
m PLS_INTEGER := 3; -- multiple
FUNCTION get_sum_multiples
(multiple IN PLS_INTEGER, num IN PLS_INTEGER)
RETURN sum_multiples IS
s sum_multiples;
BEGIN
FOR i IN 1..num LOOP
s(i) := multiple * ((i * (i + 1)) / 2);
-- sum of multiples
END LOOP;
RETURN s;
END get_sum_multiples;
BEGIN
-- invoke function to retrieve
-- element identified by subscript (key)
DBMS_OUTPUT.PUT_LINE
('Sum of the first ' || TO_CHAR(n) || ' multiples of ' ||
TO_CHAR(m) || ' is ' || TO_CHAR(get_sum_multiples (m, sn)(n)));
END;
/
给 Collection 赋值
一个 collection 可以通过 INSERT、UPDATE、FETCH 或 SELECT 语句赋值。通过下面语法，把表达式值赋给 collection 中的一个指定元素：
collection_name (subscript) := expression;
其中，表达式值与类型兼容。
可以使用 SET、MULTISET UNION、MULTISET INTERSECT 和 MULTISET EXCEPT 操作符把 nested tables 转换成赋值语句的一部分。
下面情况，赋一个值给 collection 元素会产生异常：
- 若标值为 NULL 或不能转换成正确的数据类型，PL/SQL 会产生异常 VALUE_ERROR。通常，标值必须是整数。associative arrays 也可以有 VARCHAR2 标值。
- 若标值引用一个为初始化的元素，则 PL/SQL 会产生异常 SUBSCRIPT_BEYOND_COUNT。
- 若 collection （自动）为 null，则 PL/SQL 会产生异常 COLLECTION_IS_NULL。
示例 14：演示 Collection 类型兼容赋值
DECLARE
TYPE last_name_typ IS VARRAY(3) OF VARCHAR2(64);
TYPE surname_typ IS VARRAY(3) OF VARCHAR2(64);
-- These first two variables have the same data type.
group1 last_name_typ := last_name_typ('Jones','Wong','Marceau');
group2 last_name_typ := last_name_typ('Klein','Patsos','Singh');
-- This third variable has a similar declaration,
-- but is not the same type.
group3 surname_typ := surname_typ('Trevisi','Macleod','Marquez');
BEGIN
-- Allowed because they have the same data type
group1 := group2;
-- Not allowed because they have different data types
-- group3 := group2; -- raises an exception
END;
/
示例 15：演示 nested table 赋值为 Null。
若把一个自动为 null 的 nested table 或 varray 赋值给另一个 nested table 或 varray，则另一个 collection 必须重新初始化。同样，把一个 collection 赋值为 NULL，它自动为 null。
DECLARE
TYPE dnames_tab IS TABLE OF VARCHAR2(30);
-- This nested table has some values
dept_names dnames_tab :=
dnames_tab('Shipping','Sales','Finance','Payroll');
-- This nested table is not initialized ("atomically null").
empty_set dnames_tab;
BEGIN
-- At first, the initialized variable is not null.
if dept_names IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('OK, at first dept_names is not null.');
END IF;
-- Then assign a null nested table to it.
dept_names := empty_set;
-- Now it is null.
if dept_names IS NULL THEN
DBMS_OUTPUT.PUT_LINE('OK, now dept_names has become null.');
END IF;
-- Use another constructor to give it some values.
dept_names := dnames_tab('Shipping','Sales','Finance','Payroll');
END;
/
示例 16：演示用 Set 操作符给 nested tables 赋值
可以对 nested tables 应用一些 ANSI 标准的操作符。
DECLARE
TYPE nested_typ IS TABLE OF NUMBER;
nt1 nested_typ := nested_typ(1,2,3);
nt2 nested_typ := nested_typ(3,2,1);
nt3 nested_typ := nested_typ(2,3,1,3);
nt4 nested_typ := nested_typ(1,2,4);
answer nested_typ;
-- The results might be in a different order than you expect.
-- Do not rely on the order of elements in nested tables.
PROCEDURE print_nested_table(the_nt nested_typ) IS
output VARCHAR2(128);
BEGIN
IF the_nt IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Results: <NULL>');
RETURN;
END IF;
IF the_nt.COUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE('Results: empty set');
RETURN;
END IF;
FOR i IN the_nt.FIRST .. the_nt.LAST
LOOP
output := output || the_nt(i) || ' ';
END LOOP;
DBMS_OUTPUT.PUT_LINE('Results: ' || output);
END;
BEGIN
answer := nt1 MULTISET UNION nt4; -- (1,2,3,1,2,4)
print_nested_table(answer);
answer := nt1 MULTISET UNION nt3; -- (1,2,3,2,3,1,3)
print_nested_table(answer);
answer := nt1 MULTISET UNION DISTINCT nt3; -- (1,2,3)
print_nested_table(answer);
answer := nt2 MULTISET INTERSECT nt3; -- (3,2,1)
print_nested_table(answer);
answer := nt2 MULTISET INTERSECT DISTINCT nt3; -- (3,2,1)
print_nested_table(answer);
answer := SET(nt3); -- (2,3,1)
print_nested_table(answer);
answer := nt3 MULTISET EXCEPT nt2; -- (3)
print_nested_table(answer);
answer := nt3 MULTISET EXCEPT DISTINCT nt2; -- ()
print_nested_table(answer);
END;
/
示例 17：演示用复杂数据类型给 varray 赋值
DECLARE
TYPE emp_name_rec is RECORD (
firstname employees.first_name%TYPE,
lastname employees.last_name%TYPE,
hiredate employees.hire_date%TYPE
);
-- Array type that can hold information 10 employees
TYPE EmpList_arr IS VARRAY(10) OF emp_name_rec;
SeniorSalespeople EmpList_arr;
-- Declare a cursor to select a subset of columns.
CURSOR c1 IS SELECT first_name, last_name, hire_date
FROM employees;
Type NameSet IS TABLE OF c1%ROWTYPE;
SeniorTen NameSet;
EndCounter NUMBER := 10;
BEGIN
SeniorSalespeople := EmpList_arr();
SELECT first_name, last_name, hire_date
BULK COLLECT INTO SeniorTen
FROM employees
WHERE job_id = 'SA_REP'
ORDER BY hire_date;
IF SeniorTen.LAST > 0 THEN
IF SeniorTen.LAST < 10 THEN EndCounter := SeniorTen.LAST;
END IF;
FOR i in 1..EndCounter LOOP
SeniorSalespeople.EXTEND(1);
SeniorSalespeople(i) := SeniorTen(i);
DBMS_OUTPUT.PUT_LINE(SeniorSalespeople(i).lastname || ', '
|| SeniorSalespeople(i).firstname || ', ' ||
SeniorSalespeople(i).hiredate);
END LOOP;
END IF;
END;
/
示例 18：演示用复杂数据类型给 table 赋值
DECLARE
TYPE emp_name_rec is RECORD (
firstname employees.first_name%TYPE,
lastname employees.last_name%TYPE,
hiredate employees.hire_date%TYPE
);
-- Table type that can hold information about employees
TYPE EmpList_tab IS TABLE OF emp_name_rec;
SeniorSalespeople EmpList_tab;
-- Declare a cursor to select a subset of columns.
CURSOR c1 IS SELECT first_name, last_name, hire_date
FROM employees;
EndCounter NUMBER := 10;
TYPE EmpCurTyp IS REF CURSOR;
emp_cv EmpCurTyp;
BEGIN
OPEN emp_cv FOR SELECT first_name, last_name, hire_date
FROM employees
WHERE job_id = 'SA_REP' ORDER BY hire_date;
FETCH emp_cv BULK COLLECT INTO SeniorSalespeople;
CLOSE emp_cv;
-- for this example, display a maximum of ten employees
IF SeniorSalespeople.LAST > 0 THEN
IF SeniorSalespeople.LAST < 10 THEN
EndCounter := SeniorSalespeople.LAST;
END IF;
FOR i in 1..EndCounter LOOP
DBMS_OUTPUT.PUT_LINE
(SeniorSalespeople(i).lastname || ', '
|| SeniorSalespeople(i).firstname || ', ' || SeniorSalespeople(i).hiredate);
END LOOP;
END IF;
END;
/
多维 Collection
http://www.cnblogs.com/liuning8023/archive/2012/05/13/2498482.html
比较 Collections
可以检查一个 collection 是否为 null。不能进行大于、小于等比较。这个约束也适用于隐式比较。例如，collection 不能出现在 DISTINCT、GROUP BY 或 ORDER BY 里。
若想进行比较操作，必须自定义比较函数。
示例 19：演示检查一个 collection 是否为 NULL
nested table 和 varray 可以自动为 null，所以可以用 NULL 来测试。
DECLARE
TYPE emp_name_rec is RECORD (
firstname employees.first_name%TYPE,
lastname employees.last_name%TYPE,
hiredate employees.hire_date%TYPE
);
TYPE staff IS TABLE OF emp_name_rec;
members staff;
BEGIN
-- Condition yields TRUE because you have not used a constructor.
IF members IS NULL THEN
DBMS_OUTPUT.PUT_LINE('NULL');
ELSE
DBMS_OUTPUT.PUT_LINE('Not NULL');
END IF;
END;
/
示例 20：演示比较两个 Nested Tables
nested tables 可以进行等于或不等于比较。但是它们不是按顺序的，不存在大于或小于比较。
DECLARE
TYPE dnames_tab IS TABLE OF VARCHAR2(30);
dept_names1 dnames_tab :=
dnames_tab('Shipping','Sales','Finance','Payroll');
dept_names2 dnames_tab :=
dnames_tab('Sales','Finance','Shipping','Payroll');
dept_names3 dnames_tab :=
dnames_tab('Sales','Finance','Payroll');
BEGIN
-- You can use = or !=, but not < or >.
-- These 2 are equal even though members are in different order.
IF dept_names1 = dept_names2 THEN
DBMS_OUTPUT.PUT_LINE
('dept_names1 and dept_names2 have the same members.');
END IF;
IF dept_names2 != dept_names3 THEN
DBMS_OUTPUT.PUT_LINE
('dept_names2 and dept_names3 have different members.');
END IF;
END;
/
示例 21：演示用 Set 操作符比较 nested tables
可以使用 ANSI-standard 的 Set 操作符，检查一个 nested table 的某个属性，或比较两个 nested table。
DECLARE
TYPE nested_typ IS TABLE OF NUMBER;
nt1 nested_typ := nested_typ(1,2,3);
nt2 nested_typ := nested_typ(3,2,1);
nt3 nested_typ := nested_typ(2,3,1,3);
nt4 nested_typ := nested_typ(1,2,4);
answer BOOLEAN;
howmany NUMBER;
PROCEDURE testify
(truth BOOLEAN DEFAULT NULL
quantity NUMBER DEFAULT NULL) IS
BEGIN
IF truth IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE
(CASE truth WHEN TRUE THEN 'True' WHEN FALSE THEN 'False' END);
END IF;
IF quantity IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE(quantity);
END IF;
END;
BEGIN
answer := nt1 IN (nt2,nt3,nt4); -- true, nt1 matches nt2
testify(truth => answer);
answer := nt1 SUBMULTISET OF nt3; -- true, all elements match
testify(truth => answer);
answer := nt1 NOT SUBMULTISET OF nt4; -- also true
testify(truth => answer);
howmany := CARDINALITY(nt3); -- number of elements in nt3
testify(quantity => howmany);
howmany := CARDINALITY(SET(nt3)); -- number of distinct elements
testify(quantity => howmany);
answer := 4 MEMBER OF nt1; -- false, no element matches
testify(truth => answer);
answer := nt3 IS A SET; -- false, nt3 has duplicates
testify(truth => answer);
answer := nt3 IS NOT A SET; -- true, nt3 has duplicates
testify(truth => answer);
answer := nt1 IS EMPTY; -- false, nt1 has some members
testify(truth => answer);
END;
/
Collection 方法
http://www.cnblogs.com/liuning8023/archive/2012/05/07/2489261.html
Collection 异常
http://www.cnblogs.com/liuning8023/archive/2012/05/13/2498482.html
