Loop through a table collection : Table of rowtype « PL SQL « Oracle PL / SQL

1. Oracle PL / SQL
2. PL SQL
3. Table of rowtype

Loop through a table collection

 
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
 31      depts.trim(5);
 32
 33      depts.delete(1);
 34
 35      DBMS_OUTPUT.PUT_LINE(depts.count);
 36
 37      FOR inx1 IN 1..depts_max+5 LOOP
 38          IF depts.exists(inx1) THEN
 39              DBMS_OUTPUT.PUT_LINE (
 40                  depts(inx1).dept_id ||
 41                  ' ' || depts(inx1).dept_name);
 42          END IF;
 43      END LOOP;
 44
 45  END;
 46  /
10 ACCOUNTING
40 OPERATIONS
20 RESEARCH
30 SALES
10 ACCOUNTING
10 ACCOUNTING
10 ACCOUNTING
10 ACCOUNTING
10 ACCOUNTING
3
40 OPERATIONS
20 RESEARCH
30 SALES

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> drop table department;

Table dropped.

SQL> --

 

Related examples in the same category