本章内容:
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