嵌套表

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

obclient> CREATE TABLE departments(department_id number(4),department_name varchar2(15));
Query OK, 0 rows affected

obclient> INSERT INTO departments VALUES(10,'Administration');
Query OK, 1 row affected
obclient> INSERT INTO departments VALUES(20,'Marketing');
Query OK, 1 row affected
obclient> INSERT INTO departments VALUES(30,'Purchasing');
Query OK, 1 row affected
obclient> INSERT INTO departments VALUES(40,'Human Resources');
Query OK, 1 row affected
obclient> INSERT INTO departments VALUES(50,'Shipping');
Query OK, 1 row affected
obclient> INSERT INTO departments VALUES(60,'IT');
Query OK, 1 row affected

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

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