Usage of PL/SQL Collections

Shailender Thallam
9 years ago
/*********************************************************
*PURPOSE: Sample Usage of PL/SQL Collections             *
*AUTHOR: Shailender Thallam                              *
**********************************************************/
SET serveroutput ON;
DECLARE
  --------------------
  --Record type object
  --------------------
type emp_type
IS
  record
  (
    empno  NUMBER(4),
    ename  VARCHAR2(10),
    job    VARCHAR2(9),
    deptno NUMBER(2));
  -------------------
  --Table type object
  -------------------
type emp_table
IS
  TABLE OF emp_type;
  ---------------------
  --Table type variable
  ---------------------
  emp_var emp_table := emp_table();
  --------
  --Cursor
  --------
  CURSOR emp_cur
  IS
    SELECT * FROM emp;
  --
  --
BEGIN
  ----------------------------------------
  --Assigning value to table type variable
  ----------------------------------------
  FOR i IN emp_cur
  LOOP
    BEGIN
      --
      emp_var.extend(); --To add one cell of memory to table type variable since it is empty
      emp_var(emp_var.last).ename := i.ename;
      emp_var(emp_var.last).job   := i.job;
      --
    EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('Error while assigning: '||SQLERRM);
    END;
  END LOOP;
  --
  --
  dbms_output.put_line('row count: '||emp_var.count);
  ------------------------------------------------
  --Printing values present in table type variable
  ------------------------------------------------
  FOR i IN emp_var.first .. emp_var.last
  LOOP
    BEGIN
      --
      dbms_output.put_line(RPAD('Ename('||i||')',10,'')||emp_var(i).ename);
      dbms_output.put_line(RPAD('Job('||i||')',10,'')||emp_var(i).job);
      dbms_output.put_line(RPAD('Empno('||i||')',10,'')||emp_var(i).empno); --will print blank since no value is assigned
      --
    EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('Error while printing: '||SQLERRM);
    END;
  END LOOP;
  --
END;
/
For Further Reading
