Oracle 11g Release 1 (11.1) PL/SQL Collection 方法

http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/collections.htm#i27396

本文内容

• EXISTS 方法
• COUNT 方法
• LIMIT 方法
• FIRST 和 LAST 方法
• PRIOR 和 NEXT 方法
• EXTEND 方法
• TRIM 方法
• DELETE 方法

collection 方法是一个内置的 PL/SQL 子程序，可以返回 collection 信息，或是在 collection 上执行操作，很方便。

你可以通过点记号来调用 collection 方法。语法如下图所示：

图1 Collection Method 调用

不能在 SQL 语句调用 collection 方法。

当 collection 为空时，你只能使用 EXISTS 方法，使用其他方法都会抛出 COLLECTION_IS_NULL 异常。

EXISTS 方法

若 collection 中第 n 个元素存在，则 EXISTS(n) 返回 TRUE；否则，返回 FALSE。EXISTS 方法结合 DELETE 方法，会把 collection 变成稀疏 nested tables（sparse nested tables）。通过 EXISTS 方法，避免引用一个不存在的元素，从而产生异常。当传递一个超出范围的标值时，EXISTS 方法返回 FALSE，而不是产生 SUBSCRIPT_OUTSIDE_LIMIT 异常。

示例1：演示检查元素是否存在

DECLARE
   TYPE NumList IS TABLE OF INTEGER;
   n NumList := NumList(1,3,5,7);
BEGIN
   n.DELETE(2); -- Delete the second element
   IF n.EXISTS(1) THEN
      DBMS_OUTPUT.PUT_LINE('OK, element #1 exists.');
   END IF;
   IF n.EXISTS(2) = FALSE THEN
      DBMS_OUTPUT.PUT_LINE('OK, element #2 was deleted.');
   END IF;
   IF n.EXISTS(99) = FALSE THEN
      DBMS_OUTPUT.PUT_LINE('OK, element #99 does not exist at all.');
   END IF;
END;
/

COUNT 方法

COUNT 返回 collection 中元素的当前数量。当你不知道 collection 中有多少元素时，很有用。例如，当你把获取的表的一列，放到一个 nested table 时，元素的数量取决于结果集的大小。

对于 varray，COUNT 总是等于 LAST。通过 EXTEND 和 TRIM 方法，你可以增加或减少 varray 的大小，因此，COUNT 值是变化的，取决于 LIMIT 方法的值。

对于 nested tables，COUNT 方法通常等于 LAST 方法。然而，若你从 nested table 删除元素，则 COUNT 小于 LAST。当你整理元素时，COUNT 会忽略已删除的元素。使用不带参数的 DELETE 方法会设置 COUNT 为 0。

备注：FIRST 方法和 LAST 方法返回最大和最小的索引数。后面说明。

示例2：演示 collection 中元素的个数

DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   n NumList := NumList(2,4,6,8);
     -- Collection starts with 4 elements.
BEGIN
   DBMS_OUTPUT.PUT_LINE
     ('There are ' || n.COUNT || ' elements in N.');
   n.EXTEND(3); -- Add 3 new elements at the end.
   DBMS_OUTPUT.PUT_LINE
     ('Now there are ' || n.COUNT || ' elements in N.');
   n := NumList(86,99); -- Assign a completely new value with 2 elements.
   DBMS_OUTPUT.PUT_LINE
     ('Now there are ' || n.COUNT || ' elements in N.');
   n.TRIM(2); -- Remove the last 2 elements, leaving none.
   DBMS_OUTPUT.PUT_LINE
     ('Now there are ' || n.COUNT || ' elements in N.');
END;
/

LIMIT 方法

LIMIT 返回 collection 可以容纳元素的最大数量。若 collection 没有最大大小，则 LIMIT 回返 NULL。

示例3：演示检查 collection 的最大容量

DECLARE
   TYPE dnames_var IS VARRAY(7) OF VARCHAR2(30);
   dept_names dnames_var :=
     dnames_var('Shipping','Sales','Finance','Payroll');
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');
   DBMS_OUTPUT.PUT_LINE
    ('The maximum number you can use with '
     || 'dept_names.EXTEND() is '
     || (dept_names.LIMIT - dept_names.COUNT));
END;
/

FIRST 和 LAST 方法

FIRST 方法和 LAST 方法返回的值，取决于 collection 的索引类型，是 integer，还是 string。

对于使用 integer 索引的 collection，FIRST 方法和 LAST 方法返回第一个和最后一个（最小和最大）的索引数。

对于使用 string 索引的 associative array，FIRST 方法和 LAST 方法返回最前和最后的键值。若 NLS_COMP 初始化参数设置为 ANSI，顺序是基于指定的 NLS_SORT 初始化参数。

若 collection 是空的，则 FIRST 和 LAST 返回 NULL。若 collection 只包含一个元素，则 FIRST 和 LAST 返回相同的值。

示例4：演示使用 FIRST 和 LAST 方法

DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   n NumList := NumList(1,3,5,7);
   counter INTEGER;
BEGIN
   DBMS_OUTPUT.PUT_LINE('N''s first subscript is ' || n.FIRST);
   DBMS_OUTPUT.PUT_LINE('N''s last subscript is ' || n.LAST);
-- When the subscripts are consecutive starting at 1,
-- it's simple to loop through them.
   FOR i IN n.FIRST .. n.LAST
   LOOP
      DBMS_OUTPUT.PUT_LINE('Element #' || i || ' = ' || n(i));
   END LOOP;
   n.DELETE(2); -- Delete second element.
-- When the subscripts have gaps
-- or the collection might be uninitialized,
-- the loop logic is more extensive.
-- Start at the first element
-- and look for the next element until there are no more.
   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;
END;
/

PRIOR 和 NEXT 方法

PRIOR(n) 方法和 NEXT(n) 方法的返回值取决于 collection 的索引类型，是 integer，还是 string。

PRIOR(n) 返回 collection 中索引为 n 的前一个的索引数。NEXT(n) 返回索引为 n 的后一个的索引数。若 n 没有前一个，则 PRIOR(n) 返回 NULL。若 n 没有后一个，则 NEXT(n) 返回 NULL。

对于使用 VARCHAR2 键的 associative arrays，这两个方法返回相应的键值。若没有指定 NLS_COMP 为 ANSI，则顺序取决于字符串中字符的二进制值。否则，顺序取决于 NLS_SORT 参数。

这两个方法比通过一个固定的标值来循环更可靠，因为元素在循环期间可能被从 collection 插入或删除。特别是 associative arrays，标值可能不是连续的，可能是 (1,2,4,8,16) 或 ('A','E','I','O','U')。

示例5：演示使用 PRIOR 和 NEXT 访问 Collection 元素

DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   n NumList := NumList(1966,1971,1984,1989,1999);
BEGIN
   DBMS_OUTPUT.PUT_LINE('The element after #2 is #' || n.NEXT(2));
   DBMS_OUTPUT.PUT_LINE('The element before #2 is #' || n.PRIOR(2));
   n.DELETE(3);
     -- Delete an element to show how NEXT can handle gaps.
   DBMS_OUTPUT.PUT_LINE
     ('Now the element after #2 is #' || n.NEXT(2));
   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.');
   END IF;
END;
/

示例6：演示使用 NEXT 访问 Nested

DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   n NumList := NumList(1,3,5,7);
   counter INTEGER;
BEGIN
   n.DELETE(2); -- Delete second element.
-- When the subscripts have gaps,
-- loop logic is more extensive.
-- Start at first element and look for next element
-- until there are no more.
   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;
-- Run the same loop in reverse order.
   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;
/

EXTEND 方法

EXTEND 方法用于增加 nested table 或 varray 的大小（容量）。

该方法有三个形式：

• EXTEND 追加一个 null 元素到 collection。
• EXTEND(n) 追加 n 个 null 元素到 collection。
• EXTEND(n,i) 追加第 i 个元素的 n 个副本到 collection。

不能在带索引的表使用 EXTEND。不能对一个为初始化的 collection 使用 EXTEND。若在 TABLE 或 VARRAY 类型上规定约束 NOT NULL，则不能使用 EXTEND  的前两个形式。

EXTEND 方法在一个 collection 的内部大小上操作，包含任何被已删除的元素。使用 DELETE(n) 后，饮用已删除的元素，但是不能引用无参的 DELETE，因此所有元素已完全删除。若 EXTEND 遇到已删除的元素，它会仍然包含他们的空间。PL/SQL 会维持已删除元素的空间，因此，你可以通过赋新值来重新创建。

示例7：演示增加 collection 的大小

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); -- Add 5 elements at the end.
   DBMS_OUTPUT.PUT_LINE
     ('Now N has ' || n.COUNT || ' elements.');
-- Elements 5, 6, 7, 8, and 9 are all NULL.
   print_numlist(n);
   DBMS_OUTPUT.PUT_LINE
     ('At first, X has ' || x.COUNT || ' elements.');
   x.EXTEND(4,2); -- Add 4 elements at the end.
   DBMS_OUTPUT.PUT_LINE
     ('Now X has ' || x.COUNT || ' elements.');
-- Elements 3, 4, 5, and 6 are copies of element #2.
   print_numlist(x);
END;
/

备注：

若初始化一个有 5 个元素的 nested table，之后，删除元素 2 和 5，内部大小为 5，COUNT 方法返回 3，LAST 方法返回 4。所有已删除的元素，不管其位置如何，都同等对待。 

TRIM 方法

该方法有如下两个形式：

• TRIM 从 collection 末尾移除一个元素。
• TRIM(n) 从 collection 末尾移除 n 个元素。

若想移除所有元素，使用无参的 DELETE 方法。

不能在 associative array 上使用 TRIM 方法。

示例8：演示使用 TRIM 减少 collection 的大小

DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   n NumList := NumList(1,2,3,5,7,11);
   PROCEDURE print_numlist(the_list NumList) IS
      output VARCHAR2(128);
   BEGIN
      IF n.COUNT = 0 THEN
         DBMS_OUTPUT.PUT_LINE('No elements in collection.');
      ELSE
         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 IF;
   END;
BEGIN
   print_numlist(n);
   n.TRIM(2); -- Remove last 2 elements.
   print_numlist(n);
   n.TRIM; -- Remove last element.
   print_numlist(n);
   n.TRIM(n.COUNT); -- Remove all remaining elements.
   print_numlist(n);
-- If too many elements are specified,
-- TRIM raises the exception SUBSCRIPT_BEYOND_COUNT.
   BEGIN
      n := NumList(1,2,3);
      n.TRIM(100);
      EXCEPTION
        WHEN SUBSCRIPT_BEYOND_COUNT THEN
          DBMS_OUTPUT.PUT_LINE
            ('There weren''t 100 elements to be trimmed.');
   END;
-- When elements are removed by DELETE,
-- placeholders are left behind.
--  TRIM counts these placeholders
--  as it removes elements from the end.
   n := NumList(1,2,3,4);
   n.DELETE(3);  -- delete element 3
-- At this point, n contains elements (1,2,4).
-- TRIMming the last 2 elements
-- removes the 4 and the placeholder, not 4 and 2.
   n.TRIM(2);
   print_numlist(n);
END;
/

备注：

若 n 太大，则会产生 SUBSCRIPT_BEYOND_COUNT 异常。 

示例9：演示对已删除的元素使用

TRIM 方法在一个 collection 的内部大小上操作，包含任何被已删除的元素。已删除的元素是指调用 DELETE(n) 后，不能是删除了的所有的元素、无参数的 DELETE。

DECLARE
   TYPE CourseList IS TABLE OF VARCHAR2(10);
   courses CourseList;
BEGIN
   courses := CourseList('Biol 4412', 'Psyc 3112', 'Anth 3001');
   courses.DELETE(courses.LAST);  -- delete element 3
   /* At this point, COUNT equals 2, the number of valid
      elements remaining. So, you might expect the next
      statement to empty the nested table by trimming
      elements 1 and 2. Instead, it trims valid element 2
      and deleted element 3 because TRIM includes deleted
      elements in its tally. */
   courses.TRIM(courses.COUNT);
   DBMS_OUTPUT.PUT_LINE(courses(1));  -- prints 'Biol 4412'
END;
/

备注：

一般，不要依赖 TRIM 和 DELETE 之间的互操作。最好把 nested tables 当作固定大小的数组，只用 DELETE，或是把它们当作栈，只使用 TRIM 和 EXTEND。

因为，PL/SQL 不会维持已经 trim 的元素的空间，你不能对一个已经 trim 的元素重新赋值。

 

DELETE 方法

该方法有如下三个形式：

• DELETE 不带参数的 DELETE 删除所有元素，并设置 COUNT 为 0。
• DELETE(n) 对于数字键的 associative array 或 nested table，删除第 n 个元素。若 associative array 是字符串键，则对应键的元素被删除。若 n 为 null，则 DELETE(n) 什么都不做。
• DELETE(m,n) 从 associative array 或 nested table 删除范围 m..n 的所有元素。若 m 大于 n，或 m 和 n 其中一个为 null，则 DELETE(n) 什么都不做。

示例10：演示删除 collection 元素

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;
BEGIN
   n.DELETE(2);    -- deletes element 2
   n.DELETE(3,6);  -- deletes elements 3 through 6
   n.DELETE(7,7);  -- deletes element 7
   n.DELETE(6,3);  -- does nothing since 6 > 3
   n.DELETE;      -- deletes all elements
   nicknames('Bob') := 'Robert';
   nicknames('Buffy') := 'Esmerelda';
   nicknames('Chip') := 'Charles';
   nicknames('Dan') := 'Daniel';
   nicknames('Fluffy') := 'Ernestina';
   nicknames('Rob') := 'Robert';
-- following deletes element denoted by this key
   nicknames.DELETE('Chip');
-- following deletes elements with keys in this alphabetic range
   nicknames.DELETE('Buffy','Fluffy');
END;
/

varrays 总是有连续的标值，因此，你不能删除单个元素，除非通过 TRIM 方法删除末尾的元素。你可以使用无参的 DELETE 删除所有元素。

若要删除的元素不存在，则 DELETE(n) 简单掠过；不会产生异常。PL/SQL 为已删除的元素保持其空间，因此，你可以为该空间重新赋值。使用 DELETE(n) 后，饮用已删除的元素，但是不能引用无参的 DELETE，因此所有元素已完全删除。

DELETE 可以让你维护稀疏的 nested tables。你可以在数据库存储稀疏的 nested tables，就像任何 nested tables 一样。

分配给 collection 的内存大小随着 collection 的大小而增加。若你删除整个 collection ，或单独删除所有元素，则 collection 存储元素的内存才会释放。