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
3 SELECT *
4 FROM department
5 ORDER BY dept_name;
6
7 TYPE dept_table IS TABLE OF department%ROWTYPE;
8
9 depts dept_table;
10 depts_max PLS_INTEGER;
11 inx1 PLS_INTEGER;
12 BEGIN
13 depts_max := 0;
14
15 depts := dept_table ();
16
17 FOR dept IN all_depts LOOP
18 depts_max := depts_max + 1;
19 depts.extend;
20 depts(depts_max).dept_id := dept.dept_id;
21 depts(depts_max).dept_name := dept.dept_name;
22 depts(depts_max).no_of_emps := dept.no_of_emps;
23 END LOOP;
24
25 depts.extend(5,1);
26
27 FOR inx1 IN 1..depts_max+5 LOOP
28 DBMS_OUTPUT.PUT_LINE (depts(inx1).dept_id ||' ' || depts(inx1).dept_name);
29 END LOOP;
30 END;
31 /
10 ACCOUNTING
40 OPERATIONS
20 RESEARCH
30 SALES
10 ACCOUNTING
10 ACCOUNTING
10 ACCOUNTING
10 ACCOUNTING
10 ACCOUNTING
PL/SQL procedure successfully completed.
SQL>
SQL> drop table department;
Table dropped.
SQL>
SQL>
SQL> --