PLSQL==>集合

1. 联合数组
   

创建联合数组的语法如下所示（方括号中的保留字和短句是可选的）

TYPE type_name is table of element_type(not null) index by element_type;

type_name TYPE_NAME;

注意，需要两个步骤声明联合数组。首先,使用TYPE语句声明表结构，其中type_name是在第2步骤中所使用的类型的名称，用于声明一个实际的表。element_type是plsql数据类型，如number，varchar2或者date，也可能存其他限制。

DECLARE

    CURSOR name_cur IS

        SELECT last_name FROM student WHERE rownum <= 10;

    TYPE last_name_type IS TABLE OF student.last_name%TYPE INDEX BY BINARY_INTEGER;

    last_name_tab last_name_type;

    v_counter     INTEGER := 0;

BEGIN

    FOR name_rec IN name_cur

    LOOP

        v_counter := v_counter + 1;

        last_name_tab(v_counter) := name_rec.last_name;

        dbms_output.put_line(‘last_name(‘ || v_counter || ‘):‘ ||

                             last_name_tab(v_counter));

    END LOOP;

END;

本例中，使用student表的名字信息填充联合数组last_name_tab的内容。注意，v_counter被用作下标，以引用单独的表元素。

要注意，引用不存在的数据行会抛出NO_DATA_FOUND异常，这一点很重要。

DECLARE

    CURSOR name_cur IS

        SELECT last_name FROM student WHERE rownum <= 10;

    TYPE last_name_type IS TABLE OF student.last_name%TYPE INDEX BY BINARY_INTEGER;

    last_name_tab last_name_type;

    v_counter     INTEGER := 0;

BEGIN

    FOR name_rec IN name_cur

    LOOP

        v_counter := v_counter + 1;

        last_name_tab(v_counter) := name_rec.last_name;

        dbms_output.put_line(‘last_name(‘ || v_counter || ‘):‘ ||

                             last_name_tab(v_counter));

    END LOOP;

    dbms_output.put_line(‘last_name(11):‘ || last_name_tab(11));

 16  END;

 17  /

last_name(1):Crocitto

last_name(2):Landry

last_name(3):Enison

last_name(4):Moskowitz

last_name(5):Olvsade

last_name(6):Mierzwa

last_name(7):Sethi

last_name(8):Walter

last_name(9):Martin

last_name(10):Noviello

DECLARE

*

ERROR at line 1:

ORA-01403: no data found

ORA-06512: at line 15

2.嵌套表

创建嵌套表的语法如下所示（方括号的保留字合短句是可选的）：

TYPE type_name is table of element_type[not null]

type_name TYPE_NAME;

注意，这个声明非常类型于联合数组的声明，只是没有index by binary_integer语句。像在联合数组中一样，限制也应用了于嵌套表的element_type。

要注意，必须首先初始化嵌套表，然后才能引用其中的元素。

DECLARE

    CURSOR name_cur IS

        SELECT last_name FROM student WHERE rownum <= 10;

    TYPE last_name_type IS TABLE OF student.last_name%TYPE;

    last_name_tab last_name_type;

    v_counter     INTEGER := 0;

BEGIN

    FOR name_rec IN name_cur

    LOOP

        v_counter := v_counter + 1;

        last_name_tab(v_counter) := name_rec.last_name;

        dbms_output.put_line(‘last_name(‘ || v_counter || ‘):‘ ||

                             last_name_tab(v_counter));

    END LOOP;

 15  END;

 16  /

DECLARE

*

ERROR at line 1:

ORA-06531: Reference to uninitialized collection

ORA-06512: at line 11

本范例中会导致一个错误，因为当声明嵌套表时，嵌套表被自动设置为null。也就是说，该嵌套表不存在任何元素，因为嵌套表本身是NULL。为引用嵌套表中单个元素，必须使用名为构造器的系统定义函数进行初始化。该构造器拥有与嵌套表类型相同的名称。

注意，在大多数情况下，无法预知特定嵌套表的值。所以，如下语句会产生一个空的，但不是null的嵌套表：

last_name_tab:=last_name_type();

注意，没有给该构造器传人任何参数。

DECLARE

    CURSOR name_cur IS

        SELECT last_name FROM student WHERE rownum <= 10;

    TYPE last_name_type IS TABLE OF student.last_name%TYPE;

    last_name_tab last_name_type := last_name_type();

    v_counter     INTEGER := 0;

BEGIN

    FOR name_rec IN name_cur

    LOOP

        v_counter := v_counter + 1;

        last_name_tab.extend;

        last_name_tab(v_counter) := name_rec.last_name;

        dbms_output.put_line(‘last_name(‘ || v_counter || ‘):‘ ||

                             last_name_tab(v_counter));

    END LOOP;

END;

last_name(1):Crocitto

last_name(2):Landry

last_name(3):Enison

last_name(4):Moskowitz

last_name(5):Olvsade

last_name(6):Mierzwa

last_name(7):Sethi

last_name(8):Walter

last_name(9):Martin

last_name(10):Noviello

PL/SQL procedure successfully completed.

在这个版本中，嵌套表在声明时被初始化。这意味着该嵌套表是空的，但是非null的。游标循环的语句包含一个集合方法：extend。这个方法允许你增加集合的大小。注意，extend方法不能与联合数组一起使用。

那么NULL集合和空集合之间差别？如果某集合尚未被初始化，引用它的单个元素会导致如下错误：

DECLARE

    TYPE integer_type IS TABLE OF INTEGER;

    integer_tab integer_type;

    v_counter   INTEGER := 1;

BEGIN

    dbms_output.put_line(integer_tab(v_counter));

  END;  

DECLARE

*

ERROR at line 1:

ORA-06531: Reference to uninitialized collection

ORA-06512: at line 6

如果集合被初始化，但是集合中没有数据，引用单个元素会导致另一错误：

DECLARE

    TYPE integer_type IS TABLE OF INTEGER;

    integer_tab integer_type := integer_type();

    v_counter   INTEGER := 1;

BEGIN

    dbms_output.put_line(integer_tab(v_counter));

  7  END;

  8  /

DECLARE

*

ERROR at line 1:

ORA-06533: Subscript beyond count

ORA-06512: at line 6

集合的方法：

在前面的例子中，已经用到集合的方法extend。集合方法是一个内置函数，可以像下面那样使用点标记来调用：

collection_name.method_name

下面的列表会解析集合的方法，可以使用集合方法来操作或获取有关特定的集合的信息：

如果某特定元素存在于集合中，则exists会返回true。该方法可以被用于避免subscript_outside_limit异常。

count返回集合中元素的数量。

extend会扩展集合的规模

delete会删除集合中所有元素，指定范围的元素，或特定元素。注意，PLSQL会保持所删除元素的占位符。

first和last会返回集合中第一个和最后一个元素的下标。注意，如果嵌套表的第一个元素被删除，则fisrt方法会返回大于1的值。如果从嵌套表删除一个中间元素，则last方法的返回值会大于count方法的返回值。

prior和next会返回指定集合下标的前序和后续下标。

trim会从集合的末尾删除一个，或者指定数量的元素。注意，PLSQL不会保存被删除元素的占位符。

DECLARE

    TYPE index_by_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

    index_by_table index_by_type;

    TYPE nested_type IS TABLE OF NUMBER;

    nested_table nested_type := nested_type(1, 2, 3, 4, 5, 6, 7, 8, 9, 10);

BEGIN

    --populate index by table

    FOR i IN 1 .. 10

    LOOP

        index_by_table(i) := i;

    END LOOP;

    IF index_by_table.exists(3)

    THEN

        dbms_output.put_line(‘index_by_table(3)=‘ || index_by_table(3));

    END IF;

    --delete 10th element from a collection

    nested_table.delete(10);

    --delete elements 1 through 3 from a collection

    nested_table.delete(1, 3);

    index_by_table.delete(10);

    dbms_output.put_line(‘nested_table.count=‘ || nested_table.count);

    dbms_output.put_line(‘index_by_table.count=‘ || index_by_table.count);

    dbms_output.put_line(‘nested_table.first=‘ || nested_table.first);

    dbms_output.put_line(‘nested_table.last=‘ || nested_table.last);

    dbms_output.put_line(‘index_by_table.first=‘ || index_by_table.first);

    dbms_output.put_line(‘index_by_table.last=‘ || index_by_table.last);

    dbms_output.put_line(‘nested_table.prior=‘ || nested_table.prior(2));

    dbms_output.put_line(‘nested_table.next=‘ || nested_table.next(2));

    dbms_output.put_line(‘index_by_table.prior=‘ ||

                         index_by_table.prior(2));

    dbms_output.put_line(‘index_by_table.next=‘ || index_by_table.next(2));

    --trim last two elements

    nested_table.trim(2);

    --trim last element;

    nested_table.trim;

    dbms_output.put_line(‘nested_table.last=‘ || nested_table.last);

END;

index_by_table(3)=3

nested_table.count=6

index_by_table.count=9

nested_table.first=4

nested_table.last=9

index_by_table.first=1

index_by_table.last=9

nested_table.prior=

nested_table.next=4

index_by_table.prior=1

index_by_table.next=3

nested_table.last=7

由于exists方法返回true，则第一行输出如下所示：

index_by_table(3)=3

因此，如果if语句的计算结果也是true

IF index_by_table.exists(3)

    THEN

        dbms_output.put_line(‘index_by_table(3)=‘ || index_by_table(3));

    END IF;

第二行和第三行输出显示从联合数组和嵌套表删除某些元素之后，方法count的执行结果为：

nested_table.count=6

index_by_table.count=9

接下来，第四行到第七行是first和last方法的输出为：

nested_table.first=4

nested_table.last=9

index_by_table.first=1

index_by_table.last=9

注意，由于早期已经删除前三个元素，所以应用于嵌套表的first方法会返回4.接下来，第八行到第十行是prior和next方法的输出。

nested_table.prior=

nested_table.next=4

index_by_table.prior=1

index_by_table.next=3