《Oracle PL/SQL实例精讲》学习笔记16——集合（第一部分）

本章内容：

1. PL/SQL表（使用关联数组(Associative Arrays，之前称为索引表（index-by tables）)、嵌套表 (nested tables)、集合方法）

2. 变长数组

3. 多级集合

 

基本概念：

1. 关联数组和嵌套表：

There are two types of PL/SQL tables: associative arrays (formerly known as index-by tables) and nested tables. They have the same structure, and their rows are accessed in the same way—that is, via subscript notation. The main difference between these two types is that nested tables can be stored in a database column, whereas associative arrays cannot.

 2. 集合方法

EXISTS: This function returns TRUE if a specified element exists in a collection  and can be used to avoid raising SUBSCRIPT_OUTSIDE_LIMIT exceptions. 

COUNT: This function returns the total number of elements in a collection. 

EXTEND: This procedure increases the size of a collection. 

DELETE: This procedure deletes either all elements, just the elements in the  specified range, or a particular element from a collection. PL/SQL keeps  placeholders of the deleted elements. 

FIRST and LAST: These functions return subscripts of the first and last elements of  a collection. If the first element of a nested table is deleted, the FIRST method  returns a value greater than 1. If elements are deleted from the middle of a nested  table, the LAST method returns a value greater than the COUNT method. 

PRIOR and NEXT: These functions return subscripts that precede and succeed a  specified collection subscript. 

TRIM: This procedure removes either one or a specified number of elements from  the end of a collection. PL/SQL does not keep placeholders for the trimmed  elements.

代码：

1. 关联数组的使用

SQL> -- For Example ch15_1a.sql
SQL> DECLARE
  2     CURSOR name_cur IS
  3        SELECT last_name
  4          FROM student
  5         WHERE rownum < 10;
  6
  7     TYPE last_name_type IS TABLE OF student.last_name%TYPE
  8        INDEX BY PLS_INTEGER;
  9     last_name_tab last_name_type;
 10
 11     v_index PLS_INTEGER := 0;
 12  BEGIN
 13     FOR name_rec IN name_cur
 14     LOOP
 15        v_index := v_index + 1;
 16        last_name_tab(v_index) := name_rec.last_name;
 17        DBMS_OUTPUT.PUT_LINE ('last_name('||v_index||'): '||last_name_tab(v_index));
 18     END LOOP;
 19  END;
 20  /
last_name(1): Kocka
last_name(2): Jung
last_name(3): Mulroy
last_name(4): Brendler
last_name(5): Carcia
last_name(6): Tripp
last_name(7): Frost
last_name(8): Snow
last_name(9): Scrittorale

 

2. 嵌套表

以下代码将报错：ORA-06531: Reference to uninitialized collection

（Cause:

An element or member function of a nested table or varray was referenced (where an initialized collection is needed) without the collection having been initialized.

Action:

Initialize the collection with an appropriate constructor or whole-object assignment.）

SQL> -- For Example ch15_1b.sql
SQL> DECLARE
  2     CURSOR name_cur IS
  3        SELECT last_name
  4          FROM student
  5         WHERE rownum < 10;
  6
  7     TYPE last_name_type IS TABLE OF student.last_name%TYPE;
  8     last_name_tab last_name_type;
  9
 10     v_index PLS_INTEGER := 0;
 11  BEGIN
 12     FOR name_rec IN name_cur
 13     LOOP
 14        v_index := v_index + 1;
 15        last_name_tab(v_index) := name_rec.last_name;
 16        DBMS_OUTPUT.PUT_LINE ('last_name('|| v_index ||'): '||last_name_tab(v_index));
 17     END LOOP;
 18  END;
 19  /
DECLARE
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 15

原因分析：引用嵌套表之前，需对其进行初始化。

修改后的代码如下：

SQL> -- For Example ch15_1c.sql
SQL> DECLARE
  2     CURSOR name_cur IS
  3        SELECT last_name
  4          FROM student
  5         WHERE rownum < 10;
  6
  7     TYPE last_name_type IS TABLE OF student.last_name%TYPE;
  8     last_name_tab last_name_type := last_name_type();
  9
 10     v_index PLS_INTEGER := 0;
 11  BEGIN
 12     FOR name_rec IN name_cur
 13     LOOP
 14        v_index := v_index + 1;
 15        last_name_tab.EXTEND;
 16        last_name_tab(v_index) := name_rec.last_name;
 17
 18        DBMS_OUTPUT.PUT_LINE ('last_name('||v_index||'): '||last_name_tab(v_index));
 19     END LOOP;
 20  END;
 21  /
last_name(1): Kocka
last_name(2): Jung
last_name(3): Mulroy
last_name(4): Brendler
last_name(5): Carcia
last_name(6): Tripp
last_name(7): Frost
last_name(8): Snow
last_name(9): Scrittorale

 3. 集合方法

SQL> -- For Example ch15_2a.sql
SQL> DECLARE
  2     TYPE index_by_type IS TABLE OF NUMBER
  3        INDEX BY PLS_INTEGER;
  4     index_by_table index_by_type;
  5
  6     TYPE nested_type IS TABLE OF NUMBER;
  7     nested_table nested_type := nested_type(1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
  8
  9  BEGIN
 10     -- Populate associative array
 11     FOR i IN 1..10
 12     LOOP
 13        index_by_table(i) := i;
 14     END LOOP;
 15
 16     -- Check if the associative array has third element
 17     IF index_by_table.EXISTS(3)
 18     THEN
 19        DBMS_OUTPUT.PUT_LINE ('index_by_table(3) = '||index_by_table(3));
 20     END IF;
 21
 22     -- delete 10th element from associative array
 23     index_by_table.DELETE(10);
 24     -- delete 10th element from nested table
 25     nested_table.DELETE(10);
 26     -- delete elements 1 through 3 from nested table
 27     nested_table.DELETE(1,3);
 28
 29     -- Get element counts for associative array and nested table
 30     DBMS_OUTPUT.PUT_LINE ('index_by_table.COUNT = '||index_by_table.COUNT);
 31     DBMS_OUTPUT.PUT_LINE ('nested_table.COUNT   = '||nested_table.COUNT);
 32
 33     -- Get first and last indexes of the associative array
 34     -- and nested table
 35     DBMS_OUTPUT.PUT_LINE ('index_by_table.FIRST = '||index_by_table.FIRST);
 36     DBMS_OUTPUT.PUT_LINE ('index_by_table.LAST  = '||index_by_table.LAST);
 37     DBMS_OUTPUT.PUT_LINE ('nested_table.FIRST   = '||nested_table.FIRST);
 38     DBMS_OUTPUT.PUT_LINE ('nested_table.LAST    = '||nested_table.LAST);
 39
 40
 41     -- Get idexes that precede and succeed 2nd indexes of the associative array
 42     -- and nested table
 43     DBMS_OUTPUT.PUT_LINE ('index_by_table.PRIOR(2) = '||index_by_table.PRIOR(2));
 44     DBMS_OUTPUT.PUT_LINE ('index_by_table.NEXT(2)  = '||index_by_table.NEXT(2));
 45     DBMS_OUTPUT.PUT_LINE ('nested_table.PRIOR(2)   = '||nested_table.PRIOR(2));
 46     DBMS_OUTPUT.PUT_LINE ('nested_table.NEXT(2)    = '|| nested_table.NEXT(2));
 47
 48     -- Delete last two elements of the nested table
 49     nested_table.TRIM(2);
 50     -- Delete last element of the nested table
 51     nested_table.TRIM;
 52
 53     -- Get last index of the nested table
 54     DBMS_OUTPUT.PUT_LINE('nested_table.LAST = '||nested_table.LAST);
 55  END;
 56  /
index_by_table(3) = 3
index_by_table.COUNT = 9
nested_table.COUNT   = 6
index_by_table.FIRST = 1
index_by_table.LAST  = 9
nested_table.FIRST   = 4
nested_table.LAST    = 9
index_by_table.PRIOR(2) = 1
index_by_table.NEXT(2)  = 3
nested_table.PRIOR(2)   =
nested_table.NEXT(2)    = 4
nested_table.LAST = 7