PL 嵌套表, 或者称为嵌套表，是可以在 PL 程序中可以引用、模仿数组的非永久表。用户可以定义一个表类型，然后声明这种类型的变量。之后，用户就可以将记录添加到用户的 PL 表中，并且采用与引用数组元素大体相同的方法引用记录。

嵌套表包括两个基本成分:

• 数据处理类型为 BINARY_INTEGER 主键

• 标量或记录数据类型的列

语法如下：

TYPE type_name IS TABLE OF
    {column_type | variable%TYPE | table.column%TYPE } [NOT NULL] | table%ROWTYPE
    [ INDEX BY BINARY_INTEGER];

PL 嵌套表表类型支持的取值方法如下：

注意

PL 表的记录被删除后，其他记录的行号不会因此移动。如下例所示：

obclient> DECLARE
    ->     TYPE T_dept_table IS TABLE OF
    ->         departments%ROWTYPE INDEX BY BINARY_INTEGER;
    ->     TAB_department T_dept_table;
    ->     v_count number(2) :=6;
    -> BEGIN
    ->     -- 为表赋值
    ->     FOR int IN 1 .. v_count LOOP
    ->         SELECT * INTO TAB_department(int) FROM departments WHERE department_id=int*10;
    ->     END LOOP;
    ->
    ->     -- 利用COUNT打印总行数
    ->     DBMS_OUTPUT.PUT_LINE(TAB_department.COUNT||' ROW(S): ');
    ->     -- 利用FIRST和LAST打印所有行
    ->     FOR int IN TAB_department.FIRST .. TAB_department.LAST LOOP
    ->         DBMS_OUTPUT.PUT_LINE('Department number: '||TAB_department(int).department_id);
    ->         DBMS_OUTPUT.PUT_LINE('Department name: '|| TAB_department(int).department_name);
    ->     END LOOP;
    ->
    ->     -- 利用EXISTS检查记录
    ->     IF TAB_department.EXISTS(5) THEN
    ->         DBMS_OUTPUT.PUT_LINE('ROW 5 EXISTS');
    ->     ELSE
    ->         DBMS_OUTPUT.PUT_LINE('ROW 5 NOT EXISTS');
    ->     END IF;
    ->
    ->     -- 用DELETE删除一个范围
    ->     DBMS_OUTPUT.PUT_LINE('Delete row 2-3');
    ->     TAB_department.DELETE(2,3);
    ->
    ->     -- 用NEXT从前向后打印记录
    ->     DBMS_OUTPUT.PUT_LINE('Looping from first');
    ->     v_count := 1;
    ->     WHILE v_count IS NOT NULL
    ->     LOOP
    ->         DBMS_OUTPUT.PUT_LINE(TAB_department(v_count).department_id);
    ->         v_count := TAB_department.next(v_count);
    ->     END LOOP;
    ->
    ->     -- 用PRIOR从后向前打印，注意此时COUNT为4
    ->     DBMS_OUTPUT.PUT_LINE('Looping from last');
    ->     v_count := TAB_department.COUNT;
    ->     WHILE v_count IS NOT NULL
    ->     LOOP
    ->         DBMS_OUTPUT.PUT_LINE(TAB_department(v_count).department_id);
    ->         v_count := TAB_department.prior(v_count);
    ->     END LOOP;
    -> END;
    -> /
Query OK, 0 rows affected (0.18 sec)
6 ROW(S):
Department number: 10
Department name: Administration
Department number: 20
Department name: Marketing
Department number: 30
Department name: Purchasing
Department number: 40
Department name: Human Resources
Department number: 50
Department name: Shipping
Department number: 60
Department name: IT
ROW 5 EXISTS
Delete row 2-3
Looping from first
10
40
50
60
Looping from last
40
10