Oracle集合的方法

在使用Oracle的集合方法時，需要注意以下幾點：

1. 不能在SQL語句中調用集合的方法

2. EXTEND和TRIM不能用於關聯數組

3. EXISTS, COUNT, LIMIT, FIRST, LAST, PRIOR和NEXT是函數；EXTEND, TRIM和DELETE是過程

4. EXISTS, PRIOR, NEXT, TRIM, EXTEND和DELETE一個集合下標作為參數，通常是整數，但也可以是關聯數組使用的字符串

5. 只有EXISTS可以應用於NULL集合，其他方法應用於NULL集合，PL/SQL會產生COLLECTION_IS_NULL錯誤

 

EXISTS方法，检查元素是否存在

DECLARE TYPE numlist IS TABLE OF INTEGER; -- 定义一个内嵌表的类型numlist n numlist := numlist(1,3,5,7); -- 定义一个类型为numlist的变量n，并初始化为(1,3,5,7) BEGIN n.DELETE(2); -- 删除第2个元素 IF n.EXISTS(1) THEN -- 判断是否存在第1个元素 DBMS_OUTPUT.PUT_LINE('OK, element #1 exists.'); END IF; IF n.EXISTS(2) = FALSE THEN -- 判断是否存在第2个元素 DBMS_OUTPUT.PUT_LINE('OK, element #2 has been deleted.'); END IF; IF n.EXISTS(99) = FALSE THEN -- 判断是否存在第99个元素 DBMS_OUTPUT.PUT_LINE('OK, element #99 does not exist at all.'); END IF; END; -- 结果 OK, element #1 exists. OK, element #2 has been deleted. OK, element #99 does not exist at all.

 

COUNT方法，获取集合的元素个数

DECLARE TYPE numlist IS TABLE OF NUMBER; -- 定义一个以NUMBER为元素的内嵌表类型numlist n numlist := numlist(2,4,6,8); -- 定义一个类型为numlist的变量，并初始化为(2,4,6,8) BEGIN DBMS_OUTPUT.PUT_LINE('There are ' || n.COUNT || ' elements in N.'); -- 输出当前的元素个数 n.EXTEND(3); -- 追加3个元素 DBMS_OUTPUT.PUT_LINE('Now there are ' || n.COUNT || ' elements in N.'); -- 输出当前的元素个数 n := numlist(86, 87, 88, 89); -- 重新赋值一个新的numlist DBMS_OUTPUT.PUT_LINE('Now there are ' || n.COUNT || ' elements in N.'); -- 输出当前的元素个数 n.TRIM(2); -- 删除最后2个元素 DBMS_OUTPUT.PUT_LINE('Now there are ' || n.COUNT || ' elements in N.'); -- 输出当前的元素个数 n.DELETE(1); -- 删除第一个元素 DBMS_OUTPUT.PUT_LINE('Now there are ' || n.COUNT || ' elements in N.'); -- 输出当前的元素个数 END; -- 结果 There are 4 elements in N. Now there are 7 elements in N. Now there are 4 elements in N. Now there are 2 elements in N. Now there are 1 elements in N.

 

LIMIT方法，获取集合的最大元素个数

× 对于内嵌表和关联数组，因为没有声明大小，所以LIMIT返回NULL值

DECLARE TYPE dnames_var IS VARRAY(7) OF VARCHAR2(30); -- 定义一个可变数组的类型 TYPE numlist IS TABLE OF NUMBER; -- 定义一个内嵌表的类型 TYPE cities_aa IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER; -- 定义一个关联数组的类型 dept_names dnames_var := dnames_var('Shipping', 'Sales', 'Finance', 'Payroll'); -- 定义并初始化一个可变数组实例的变量 n numlist := numlist(1,3); -- 定义并初始化一个内嵌表实例的变量 chi_cities cities_aa; -- 定义一个关联数组实例的变量 BEGIN -- 输出当前可变数组的元素个数 DBMS_OUTPUT.PUT_LINE('dept_names has ' || dept_names.COUNT || ' elements now'); -- 输出当前可变数组的最大元素个数 DBMS_OUTPUT.PUT_LINE('dept_names''s type can hold a maximum of ' || dept_names.LIMIT || ' elements'); -- 输出当前可变数组还可以用EXTEND方法追加元素的次数 DBMS_OUTPUT.PUT_LINE('The maximum number you can use with dept_names.EXTEND() is ' || (dept_names.LIMIT - dept_names.COUNT)); BEGIN dept_names.EXTEND(1); -- 输出当前可变数组还可以用EXTEND方法追加元素的次数 DBMS_OUTPUT.PUT_LINE('DEBUG 1: The maximum number you can use with dept_names.EXTEND() is ' || (dept_names.LIMIT - dept_names.COUNT)); dept_names.EXTEND(1); -- 输出当前可变数组还可以用EXTEND方法追加元素的次数 DBMS_OUTPUT.PUT_LINE('DEBUG 2: The maximum number you can use with dept_names.EXTEND() is ' || (dept_names.LIMIT - dept_names.COUNT)); dept_names.EXTEND(1); -- 输出当前可变数组还可以用EXTEND方法追加元素的次数 DBMS_OUTPUT.PUT_LINE('DEBUG 3: The maximum number you can use with dept_names.EXTEND() is ' || (dept_names.LIMIT - dept_names.COUNT)); dept_names.EXTEND(1); -- 产生ORA-06532错误 -- 输出当前可变数组还可以用EXTEND方法追加元素的次数 DBMS_OUTPUT.PUT_LINE('DEBUG 4: The maximum number you can use with dept_names.EXTEND() is ' || (dept_names.LIMIT - dept_names.COUNT)); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLCODE || ': ' || SQLERRM); END; -- 内嵌表的LIMIT返回NULL IF n.LIMIT IS NULL THEN DBMS_OUTPUT.PUT_LINE('Nested table''s LIMIT is NULL'); END IF; -- 关联数组的LIMIT返回NULL chi_cities(756) := 'zhuhai'; IF chi_cities.LIMIT IS NULL THEN DBMS_OUTPUT.PUT_LINE('Associative table''s LIMIT is NULL'); END IF; END; -- 结果 dept_names has 4 elements now dept_names's type can hold a maximum of 7 elements The maximum number you can use with dept_names.EXTEND() is 3 DEBUG 1: The maximum number you can use with dept_names.EXTEND() is 2 DEBUG 2: The maximum number you can use with dept_names.EXTEND() is 1 DEBUG 3: The maximum number you can use with dept_names.EXTEND() is 0 -6532: ORA-06532: Subscript outside of limit Nested table's LIMIT is NULL Associative table's LIMIT is NULL

 

 FIRST和LAST方法，获取集合的首下标和尾下标

DECLARE TYPE numlist IS TABLE OF NUMBER; -- 定义一个内嵌表的类型 n numlist := numlist(1,3,5,7,9); -- 定义并初始化一个内嵌表实例的变量 counter INTEGER; -- 计数器 BEGIN -- 输出首下标和尾下标 DBMS_OUTPUT.PUT_LINE('At beginning'); DBMS_OUTPUT.PUT_LINE('N''s first subscript is ' || n.FIRST); DBMS_OUTPUT.PUT_LINE('N''s last subscript is ' || n.LAST); -- 循环输出元素 FOR i IN n.FIRST..n.LAST LOOP DBMS_OUTPUT.PUT_LINE('Element #' || i || ' = ' || n(i)); END LOOP; -- 删除第1个元素，然后再输出首下标和尾下标 n.DELETE(1); DBMS_OUTPUT.PUT_LINE('After delete first element'); DBMS_OUTPUT.PUT_LINE('N''s first subscript is ' || n.FIRST); DBMS_OUTPUT.PUT_LINE('N''s last subscript is ' || n.LAST); -- 删除第3个元素 n.DELETE(3); DBMS_OUTPUT.PUT_LINE('Deleted third element'); -- 普通循环 DBMS_OUTPUT.PUT_LINE('Common FOR loop'); FOR i IN n.FIRST..n.LAST LOOP BEGIN DBMS_OUTPUT.PUT_LINE('Element #' || i || ' = ' || n(i)); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error at #' || i || '[' || SQLERRM || ']'); END; END LOOP; -- 特别循环 DBMS_OUTPUT.PUT_LINE('Special WHILE loop'); IF n IS NOT NULL THEN counter := n.FIRST; WHILE counter IS NOT NULL LOOP DBMS_OUTPUT.PUT_LINE('Element #' || counter || ' = ' || n(counter)); counter := n.NEXT(counter); -- 获取下一元素的下标 END LOOP; ELSE DBMS_OUTPUT.PUT_LINE('N is null, nothing to do.'); END IF; -- 重新给第3个元素赋值 n(3) := 99; -- 普通循环 DBMS_OUTPUT.PUT_LINE('Common FOR loop'); FOR i IN n.FIRST..n.LAST LOOP BEGIN DBMS_OUTPUT.PUT_LINE('Element #' || i || ' = ' || n(i)); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error at #' || i || '[' || SQLERRM || ']'); END; END LOOP; END; -- 结果 At beginning N's first subscript is 1 N's last subscript is 5 Element #1 = 1 Element #2 = 3 Element #3 = 5 Element #4 = 7 Element #5 = 9 After delete first element N's first subscript is 2 N's last subscript is 5 Deleted third element Common FOR loop Element #2 = 3 Error at #3[ORA-01403: no data found] Element #4 = 7 Element #5 = 9 Special WHILE loop Element #2 = 3 Element #4 = 7 Element #5 = 9 Common FOR loop Element #2 = 3 Element #3 = 99 Element #4 = 7 Element #5 = 9

 

PRIOR和NEXT方法，获取前一个元素或后一个元素的下标

DECLARE TYPE numlist IS TABLE OF NUMBER; -- 定义一个内嵌表类型 n numlist := numlist(1996,1997,1998,1999); -- 定义并初始化一个内嵌表实例的变量 counter INTEGER; -- 计数器 BEGIN -- 输出第2个元素前后的下标 DBMS_OUTPUT.PUT_LINE('The element after #2 is #' || n.NEXT(2)); DBMS_OUTPUT.PUT_LINE('The element before #2 is #' || n.PRIOR(2)); -- 删除第3个元素 n.DELETE(3); -- 输出删除第3个元素后，第2个元素之后的下标 DBMS_OUTPUT.PUT_LINE('Now, the element after #2 is #' || n.NEXT(2)); -- 输出首下标之前或尾下标之后的下标为NULL值 IF n.PRIOR(n.FIRST) IS NULL THEN DBMS_OUTPUT.PUT_LINE('Can''t get PRIOR of the first element or NEXT of the last element'); END IF; -- 从前向后输出元素，NEXT会跳过删除掉的值 counter := n.FIRST; WHILE counter IS NOT NULL LOOP DBMS_OUTPUT.PUT_LINE('Counting up: Element #' || counter || ' = ' || n(counter)); counter := n.NEXT(counter); END LOOP; -- 从后往前输出元素，PRIOR会跳过删除掉的值 counter := n.LAST; WHILE counter IS NOT NULL LOOP DBMS_OUTPUT.PUT_LINE('Counting down: Element #' || counter || ' = ' || n(counter)); counter := n.PRIOR(counter); END LOOP; END; -- 结果 The element after #2 is #3 The element before #2 is #1 Now, the element after #2 is #4 Can't get PRIOR of the first element or NEXT of the last element Counting up: Element #1 = 1996 Counting up: Element #2 = 1997 Counting up: Element #4 = 1999 Counting down: Element #4 = 1999 Counting down: Element #2 = 1997 Counting down: Element #1 = 1996

 

EXTEND方法，增加集合的元素

EXTEND有3中形式

EXTEND: 追加一个NULL元素

EXTEND(n): 追加n个NULL元素

EXTEND(n, i): 追加n个第i个元素

DECLARE TYPE numlist IS TABLE OF INTEGER; -- 定义内嵌表类型 n numlist := numlist(2,4,6,8); -- 内嵌表实例 x numlist := numlist(1,3); -- 内嵌表实例 -- 打印内嵌表的过程 PROCEDURE print_numlist(the_list numlist) IS output VARCHAR2(128); BEGIN FOR i IN the_list.FIRST..the_list.LAST LOOP output := output || NVL(TO_CHAR(the_list(i)), 'NULL') || ' '; END LOOP; DBMS_OUTPUT.PUT_LINE(output); END; BEGIN DBMS_OUTPUT.PUT_LINE('At first, N has ' || n.COUNT || ' elements'); n.EXTEND(5); DBMS_OUTPUT.PUT_LINE('Now N has ' || n.COUNT || ' elements'); print_numlist(n); DBMS_OUTPUT.PUT_LINE('At first, X has ' || x.COUNT || ' elements'); x.EXTEND(4,2); DBMS_OUTPUT.PUT_LINE('Now X has ' || x.COUNT || ' elements'); print_numlist(x); END; -- 结果 At first, N has 4 elements Now N has 9 elements 2 4 6 8 NULL NULL NULL NULL NULL At first, X has 2 elements Now X has 6 elements 1 3 3 3 3 3

 

 TRIM方法，减少集合的元素个数

两种形式：

TRIM：在集合尾部删除一个元素

TRIM(n)：在集合尾部删除n个元素

DECLARE TYPE numlist IS TABLE OF NUMBER; n numlist := numlist(1,2,3,5,7,11); PROCEDURE print_numlist(lst numlist) IS output VARCHAR2(128); BEGIN IF n.COUNT = 0 THEN DBMS_OUTPUT.PUT_LINE('No elements in collection.'); ELSE FOR i IN lst.FIRST..lst.LAST LOOP output := output || NVL(TO_CHAR(lst(i)), 'NULL') || ' '; END LOOP; DBMS_OUTPUT.PUT_LINE(output); END IF; END; BEGIN print_numlist(n); n.TRIM(2); -- 删除最后2个元素 print_numlist(n); n.TRIM; -- 删除最后1个元素 print_numlist(n); n.TRIM(n.COUNT); -- 删除所有剩余的元素 print_numlist(n); -- 如果指定太多元素要删除，TRIM会产生异常SUBSCRIPT_BEYONG_COUNT BEGIN n := numlist(1,2,3); n.TRIM(100); -- 没有100个元素 EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); END; -- 当用DELETE删除元素时，占位符仍然保留下来，使用TRIM时，这些占位符也会计算 n := numlist(1,2,3,4); n.DELETE(3); -- 删除了第3个元素'3' -- 这时，集合还包含(1,2,4)，TRIM最后2个元素会把4和占位符删除，而不'2'和'4' n.TRIM(2); print_numlist(n); BEGIN n := numlist(1,2,3); n.DELETE(2); -- 删除了第2个元素，但保留了占位符 n(2) := 99; -- 可以重新赋值 n.TRIM; -- 删除了最后一个元素，且不保留占位符 n(3) := 100; -- 不能重新赋值，会引起ORA-06533错误 EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); END; END; -- 结果 1 2 3 5 7 11 1 2 3 5 1 2 3 No elements in collection. ORA-06533: Subscript beyond count 1 2 ORA-06533: Subscript beyond count

 

DELETE方法，删除集合的元素

不带参数的DELETE会删除所有元素，并设置COUNT为0

DELETE(n)会删除内嵌表或以数值为主键的关联数组的第n个元素

DELETE(m,n)会删除内嵌表或关联数组的所有范围在m..n之间的元素，如果m>n，那么DELETE(m,n)不起作用

DECLARE TYPE numlist IS TABLE OF NUMBER; n numlist := numlist(10,20,30,40,50,60,70,80,90,100); TYPE nicklist IS TABLE OF VARCHAR2(64) INDEX BY VARCHAR2(32); nicknames nicklist; PROCEDURE print_list(lst numlist, head VARCHAR2) IS output VARCHAR2(2000) := head || CHR(10); BEGIN IF lst IS NULL THEN output := output || 'The list is null.'; ELSE IF lst.FIRST IS NOT NULL AND lst.LAST IS NOT NULL THEN FOR i IN lst.FIRST .. lst.LAST LOOP BEGIN output := output || NVL(TO_CHAR(lst(i)), 'NULL') || ' '; EXCEPTION WHEN NO_DATA_FOUND THEN output := output || 'NULL '; END; END LOOP; ELSE output := output || 'The list is empty.'; END IF; END IF; DBMS_OUTPUT.PUT_LINE(output || CHR(10)); END; PROCEDURE print_nicknames(nns nicklist, head VARCHAR2) IS output VARCHAR2(2000) := head || CHR(10); keyname VARCHAR2(32); BEGIN keyname := nns.FIRST; WHILE keyname IS NOT NULL LOOP output := output || '[' || keyname || ',' || nns(keyname) || '] '; keyname := nns.NEXT(keyname); END LOOP; DBMS_OUTPUT.PUT_LINE(output || CHR(10)); END; BEGIN print_list(n, 'At the begining'); n.DELETE(2); print_list(n, 'After deleted #2'); n.DELETE(3,6); print_list(n, 'After deleted #3 to #6'); n.DELETE(7,7); print_list(n, 'After deleted #7'); n.DELETE(8,3); print_list(n, 'After deleted #8 to #3'); n.DELETE; print_list(n, 'After deleted all elements'); nicknames('Rob') := 'Robert'; nicknames('Bob') := 'Robert'; nicknames('Buffy') := 'Esmerelda'; nicknames('Chip') := 'Charles'; nicknames('Dan') := 'Daniel'; nicknames('Fluffy') := 'Ernestina'; print_nicknames(nicknames, 'At beginning'); nicknames.DELETE('Chip'); print_nicknames(nicknames, 'After deleted ''Chip'''); nicknames.DELETE('Buffy','Fluffy'); print_nicknames(nicknames, 'After deleted from ''Buffy'' to ''Fluffy'''); END; -- 结果 At the begining 10 20 30 40 50 60 70 80 90 100 After deleted #2 10 NULL 30 40 50 60 70 80 90 100 After deleted #3 to #6 10 NULL NULL NULL NULL NULL 70 80 90 100 After deleted #7 10 NULL NULL NULL NULL NULL NULL 80 90 100 After deleted #8 to #3 10 NULL NULL NULL NULL NULL NULL 80 90 100 After deleted all elements The list is empty. At beginning [Bob,Robert] [Buffy,Esmerelda] [Chip,Charles] [Dan,Daniel] [Fluffy,Ernestina] [Rob,Robert] After deleted 'Chip' [Bob,Robert] [Buffy,Esmerelda] [Dan,Daniel] [Fluffy,Ernestina] [Rob,Robert] After deleted from 'Buffy' to 'Fluffy' [Bob,Robert] [Rob,Robert]

 

 

附与集合相关的异常

DECLARE TYPE wordlist IS TABLE OF VARCHAR2(5); words wordlist; err_msg VARCHAR2(100); PROCEDURE display_error IS BEGIN err_msg := SUBSTR(SQLERRM, 1, 100); DBMS_OUTPUT.PUT_LINE('Error message = ' || err_msg); END; BEGIN BEGIN words(1) := 10; -- 产生COLLECTION_IS_NULL错误 -- 还没调用构造器 -- 这个异常只会对可变数组(varray)和内嵌表(nested table)有效 -- 但对关联数组无效，因为它不需要构造器 EXCEPTION WHEN OTHERS THEN display_error; END; words := wordlist('1st', '2nd', '3rd'); words(3) := words(1) || '+2'; BEGIN words(3) := 'longer than 5 characters'; -- 产生VALUE_ERROR错误 -- 指定的值太长 EXCEPTION WHEN OTHERS THEN display_error; END; BEGIN words('B') := 'dunno'; -- 产生VALUE_ERROR错误 -- 内嵌表的下标必须是整数 -- 另外，NULL也不能作为下标使用 EXCEPTION WHEN OTHERS THEN display_error; END; BEGIN words(0) := 'zero'; -- 产生SUBSCRIPT_OUTSIDE_LIMIT错误 -- 下标0超出了允许的下标范围 EXCEPTION WHEN OTHERS THEN display_error; END; BEGIN words(4) := 'maybe'; -- 产生SUBSCRIPT_BEYOND_COUNT -- 下标4超出了内嵌表允许的元素个数 -- 要增加新元素，必须先调用EXTEND方法 EXCEPTION WHEN OTHERS THEN display_error; END; BEGIN words.DELETE(1); IF words(1) = 'First' THEN NULL; END IF; -- 产生NO_DATA_FOUND错误 -- 下标为1的元素已经被删除 EXCEPTION WHEN OTHERS THEN display_error; END; END; -- 结果 Error message = ORA-06531: Reference to uninitialized collection Error message = ORA-06502: PL/SQL: numeric or value error: character string buffer too small Error message = ORA-06502: PL/SQL: numeric or value error: character to number conversion error Error message = ORA-06532: Subscript outside of limit Error message = ORA-06533: Subscript beyond count Error message = ORA-01403: no data found