SQL>
SQL> create table department
2 ( dept_id number(2),
3 dept_name varchar2(14),
4 no_of_emps varchar2(13)
5 )
6 /
Table created.
SQL>
SQL> INSERT INTO department VALUES (10, 'ACCOUNTING', 'NEW YORK');
1 row created.
SQL> INSERT INTO department VALUES (20, 'RESEARCH', 'DALLAS');
1 row created.
SQL> INSERT INTO department VALUES (30, 'SALES', 'CHICAGO');
1 row created.
SQL> INSERT INTO department VALUES (40, 'OPERATIONS', 'BOSTON');
1 row created.
SQL>
SQL>
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 CURSOR all_depts IS SELECT * FROM department ORDER BY dept_name;
3
4 TYPE dept_array IS VARRAY(100) OF department%ROWTYPE;
5
6 depts dept_array;
7 inx1 PLS_INTEGER;
8 inx2 PLS_INTEGER;
9 BEGIN
10 inx1 := 0;
11
12 depts := dept_array ();
13
14 FOR dept IN all_depts LOOP
15 inx1 := inx1 + 1;
16 depts.extend();
17 depts(inx1).dept_id := dept.dept_id;
18 depts(inx1).dept_name := dept.dept_name;
19 depts(inx1).no_of_emps := dept.no_of_emps;
20 END LOOP;
21
22 FOR inx2 IN 1..depts.count LOOP
23 DBMS_OUTPUT.PUT_LINE (
24 depts(inx2).dept_id ||
25 ' ' || depts(inx2).dept_name);
26 END LOOP;
27 END;
28 /
10 ACCOUNTING
40 OPERATIONS
20 RESEARCH
30 SALES
PL/SQL procedure successfully completed.
SQL>
SQL> drop table department;
Table dropped.
SQL>
SQL> --