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

本章内容：

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

2. 变长数组（大小可变的数组）

3. 多级集合 （多维数组）

基本概念：

                                                                   关联数组、嵌套表及变长数组

代码：

1. 变长数组

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 VARRAY(10) OF student.last_name%TYPE;
  8     last_name_varray 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_varray.EXTEND;
 16        last_name_varray(v_index) := name_rec.last_name;
 17
 18        DBMS_OUTPUT.PUT_LINE ('last_name('||v_index||'): '||last_name_varray(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

PL/SQL procedure successfully completed.

SQL> -- For Example ch15_3a.sql
SQL> DECLARE
  2     TYPE varray_type IS VARRAY(10) OF NUMBER;
  3     varray varray_type := varray_type(1, 2, 3, 4, 5, 6);
  4
  5  BEGIN
  6     DBMS_OUTPUT.PUT_LINE ('varray.COUNT = '||varray.COUNT);
  7     DBMS_OUTPUT.PUT_LINE ('varray.LIMIT = '||varray.LIMIT);
  8
  9     DBMS_OUTPUT.PUT_LINE ('varray.FIRST = '||varray.FIRST);
 10     DBMS_OUTPUT.PUT_LINE ('varray.LAST  = '||varray.LAST);
 11
 12     -- Append two copies of the 4th element to the varray
 13     varray.EXTEND(2, 4);
 14     DBMS_OUTPUT.PUT_LINE ('varray.LAST  = '||varray.LAST);
 15     DBMS_OUTPUT.PUT_LINE ('varray('||varray.LAST||')   = '||varray(varray.LAST));
 16
 17     -- Trim last two elements
 18     varray.TRIM(2);
 19     DBMS_OUTPUT.PUT_LINE('varray.LAST =  '||varray.LAST);
 20  END;
 21  /
varray.COUNT = 6
varray.LIMIT = 10
varray.FIRST = 1
varray.LAST  = 6
varray.LAST  = 8
varray(8)   = 4
varray.LAST =  6

2. 多级集合

SQL> DECLARE
  2     TYPE varray_type1 IS VARRAY(4) OF INTEGER;
  3     TYPE varray_type2 IS VARRAY(3) OF varray_type1;
  4
  5     varray1 varray_type1 := varray_type1(2, 4, 6, 8);
  6     varray2 varray_type2 := varray_type2(varray1);
  7  BEGIN
  8     DBMS_OUTPUT.PUT_LINE ('Varray of integers');
  9     FOR i IN 1..4
 10     LOOP
 11        DBMS_OUTPUT.PUT_LINE ('varray1('||i||'): '||varray1(i));
 12     END LOOP;
 13
 14     varray2.EXTEND;
 15     varray2(2) := varray_type1(1, 3, 5, 7);
 16
 17     DBMS_OUTPUT.PUT_LINE (chr(10)||'Varray of varrays of integers');
 18     FOR i IN 1..2
 19     LOOP
 20        FOR j IN 1..4
 21        LOOP
 22           DBMS_OUTPUT.PUT_LINE ('varray2('||i||')('||j||'): '||varray2(i)(j));
 23        END LOOP;
 24     END LOOP;
 25  END;
 26  /
Varray of integers
varray1(1): 2
varray1(2): 4
varray1(3): 6
varray1(4): 8

Varray of varrays of integers
varray2(1)(1): 2
varray2(1)(2): 4
varray2(1)(3): 6
varray2(1)(4): 8
varray2(2)(1): 1
varray2(2)(2): 3
varray2(2)(3): 5
varray2(2)(4): 7