Nested tables

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
    -- Assign values to the table
    FOR int IN 1 .. v_count LOOP
    SELECT * INTO tab_department(int) FROM departments WHERE department_id=int*10;
    END LOOP;

    -- Print the total number of rows by using COUNT
    DBMS_OUTPUT.PUT_LINE(tab_department.COUNT||' ROW(S): ');
    -- Print all rows by using FIRST and 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;

    -- Use EXISTS to check for a row.
    IF tab_department.EXISTS(5) THEN
       DBMS_OUTPUT.PUT_LINE('ROW 5 EXISTS');
    ELSE
       DBMS_OUTPUT.PUT_LINE('ROW 5 NOT EXISTS');
    END IF;
    -- Use DELETE to delete a range of rows.
       DBMS_OUTPUT.PUT_LINE('Delete row 2-3');
         tab_department.DELETE(2,3);

    -- Use NEXT to loop through the table from the beginning to the end.
        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;

    -- Use PRIOR to loop through the table from the end to the beginning. Note that COUNT is now 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