Collections in oracle pl sql with examples

With this article, we will know how to create and use PL/SQL collection and record variables. All these composite variables have elements that we can treat as individual variables. We can pass composite variables to subprograms as a parameter.
To create a collection or record variable, first we need to define a collection or record type, and then declare a variable of that type.

The most important PLSQL collections are of 3 types as below:
     1. Associative array / Index by Table
     2. Nested Table
     3. Varray

1.  Associative Array / Index by Table :

1. Syntax:
CREATE TYPE typ_nm_obj IS TABLE OF varchar2(size)/binary_integer
INDEX BY varchar2(size)/binary_integer;
typ_nm_tbl typ_nm_obj;

2. No need to initialize.
3. No need of EXTEND keyword.
4. We can use both %TYPE and %ROWTYPE, we can parse records with a multiple column using Associative Array.
5. The number of element is unbounded or too high.
6. Size of the collection cannot increase dynamically.
7. Initially, the Associative array is dense(consecutive) later it can be sparse after deletion of intermediate elements.
8. An associative array cannot be stored in a table.
9. we can use it only in PLSQL block.
10. It cannot be an object type.

Syntax:

TYPE typee_name IS TABLE OF binary_integer/varchar2 
INDEX BY varchar2/binary_integer;

Example:

SQL> DECLARE
 CURSOR cur IS SELECT ename FROM emp;
 TYPE emp_rec_type IS TABLE OF emp.ename%TYPE
 INDEX BY BINARY_INTEGER;
 name_list emp_rec_type;
 cnt NUMBER :=0;
BEGIN
 FOR I IN cur
 LOOP
  cnt := cnt+1;
  name_list(cnt) := I.ename;
  DBMS_OUTPUT.PUT_LINE(name_list(cnt));
 END LOOP;
END;
/
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

PL/SQL procedure successfully completed.


2. Nested Table:

1. Syntax:
CREATE TYPE typ_nm_obj IS TABLE OF varchar2(size);
typ_nm_tbl typ_nm_obj := typ_nm_obj();
2. Need to initialize, otherwise oracle will give error like: ORA-06531 Reference to uninitialized collection.
3. Need to use EXTEND keyword.
4. We can use %TYPE and %ROWTYPE, and can parse records with a multiple column using Nested Table.
5. The number of element is unbounded or too high.
6. Size of the collection can increase dynamically.
7. Initially, the Nested Table is dense(consecutive) later it can be sparse after deletion of intermediate elements. 8. Nested Table can be stored in a table.
9. we can use it both in SQL and PLSQL block.
10. It can be an object type.

Syntax:

TYPE type_name IS TABLE OF element_type [NOT NULL];
table_name type_name;

Example:

%TYPE with a single column.
SQL> DECLARE
 CURSOR cur IS SELECT ename FROM emp;
 TYPE emp_rec_type IS TABLE OF emp.ename%TYPE;
 name_list emp_rec_type := emp_rec_type();
 cnt NUMBER :=0;
BEGIN
 FOR I IN cur
 LOOP
  cnt := cnt+1;
  name_list.EXTEND;
  name_list(cnt) := I.ename;
  DBMS_OUTPUT.PUT_LINE(name_list(cnt));
 END LOOP;
END;
/
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

PL/SQL procedure successfully completed.

%ROWTYPE with Complete row.
SQL> DECLARE
 CURSOR cur IS SELECT * FROM emp;
 TYPE emp_rec_type IS TABLE OF emp%ROWTYPE;
 emp_list emp_rec_type := emp_rec_type();
 cnt NUMBER :=0;
BEGIN
 FOR I IN cur
 LOOP
  cnt := cnt+1;
  emp_list.EXTEND;
  emp_list(cnt).empno := I.empno;
  emp_list(cnt).ename := I.ename;
  emp_list(cnt).job := I.job;
  emp_list(cnt).mgr := I.mgr;
  emp_list(cnt).hiredate := I.hiredate;
  emp_list(cnt).sal := I.sal;
  emp_list(cnt).comm := I.comm;
  emp_list(cnt).deptno := I.deptno;
  DBMS_OUTPUT.PUT_LINE(emp_list(cnt).empno||’, ‘||emp_list(cnt).ename||’, ‘||emp_list(cnt).job||’, ‘||emp_list(cnt).mgr||’, ‘||emp_list(cnt).hiredate||’, ‘||emp_list(cnt).sal||’, ‘||emp_list(cnt).comm||’, ‘||emp_list(cnt).deptno);
 END LOOP;
END;
/
7369, SMITH, CLERK, 7902, 17-DEC-80, 800, , 20
7499, ALLEN, SALESMAN, 7698, 20-FEB-81, 1600, 300, 30
7521, WARD, SALESMAN, 7698, 22-FEB-81, 1250, 500, 30
7566, JONES, MANAGER, 7839, 02-APR-81, 2975, , 20
7654, MARTIN, SALESMAN, 7698, 28-SEP-81, 1250, 1400, 30
7698, BLAKE, MANAGER, 7839, 01-MAY-81, 2850, , 30
7782, CLARK, MANAGER, 7839, 09-JUN-81, 2450, , 10
7788, SCOTT, ANALYST, 7566, 09-DEC-82, 3000, , 20
7839, KING, PRESIDENT, , 17-NOV-81, 5000, , 10
7844, TURNER, SALESMAN, 7698, 08-SEP-81, 1500, 0, 30
7876, ADAMS, CLERK, 7788, 12-JAN-83, 1100, , 20
7900, JAMES, CLERK, 7698, 03-DEC-81, 950, , 30
7902, FORD, ANALYST, 7566, 03-DEC-81, 3000, , 20
7934, MILLER, CLERK, 7782, 23-JAN-82, 1300, , 10

PL/SQL procedure successfully completed.

TABLE OF .. RECORD TYPE.
SQL> DECLARE
TYPE emp_sub_obj IS RECORD(
empno NUMBER(4),
ename VARCHAR2(10),
job VARCHAR2(9),
sal NUMBER(7, 2),
deptno NUMBER(2));
TYPE emp_rec_type IS TABLE OF emp_sub_obj;

CURSOR cur IS SELECT * FROM emp;

emp_list emp_rec_type := emp_rec_type();
cnt NUMBER :=0;
BEGIN
 FOR I IN cur
 LOOP
  cnt := cnt+1;
  emp_list.EXTEND;
  emp_list(cnt).empno := I.empno;
  emp_list(cnt).ename := I.ename;
  emp_list(cnt).job := I.job;
  emp_list(cnt).sal := I.sal;
  emp_list(cnt).deptno := I.deptno;

  DBMS_OUTPUT.PUT_LINE(emp_list(cnt).empno||’, ‘||emp_list(cnt).ename||’, ‘||emp_list(cnt).job||’, ‘||emp_list(cnt).sal||’, ‘||emp_list(cnt).deptno);

 END LOOP;
END;
/
7369, SMITH, CLERK, 800, 20
7499, ALLEN, SALESMAN, 1600, 30
7521, WARD, SALESMAN, 1250, 30
7566, JONES, MANAGER, 2975, 20
7654, MARTIN, SALESMAN, 1250, 30
7698, BLAKE, MANAGER, 2850, 30
7782, CLARK, MANAGER, 2450, 10
7788, SCOTT, ANALYST, 3000, 20
7839, KING, PRESIDENT, 5000, 10
7844, TURNER, SALESMAN, 1500, 30
7876, ADAMS, CLERK, 1100, 20
7900, JAMES, CLERK, 950, 30
7902, FORD, ANALYST, 3000, 20
7934, MILLER, CLERK, 1300, 10

PL/SQL procedure successfully completed.

TABLE OF .. RECORD TYPE with bulk collect and Nested Table.
SQL> DECLARE
TYPE emp_sub_obj IS RECORD(
empno NUMBER(4),
ename VARCHAR2(10),
job VARCHAR2(9),
sal NUMBER(7, 2),
deptno NUMBER(2));
TYPE emp_rec_type IS TABLE OF emp_sub_obj;

CURSOR cur IS SELECT * FROM emp;

emp_list emp_rec_type := emp_rec_type();
BEGIN
 select empno, ename, job, sal, deptno bulk collect into emp_list from emp;
 FOR cnt IN emp_list.first .. emp_list.last
 LOOP
  DBMS_OUTPUT.PUT_LINE(emp_list(cnt).empno||’, ‘||emp_list(cnt).ename||’, ‘||emp_list(cnt).job||’, ‘||emp_list(cnt).sal||’, ‘||emp_list(cnt).deptno);
 END LOOP;
end;
/
7369, SMITH, CLERK, 800, 20
7499, ALLEN, SALESMAN, 1600, 30
7521, WARD, SALESMAN, 1250, 30
7566, JONES, MANAGER, 2975, 20
7654, MARTIN, SALESMAN, 1250, 30
7698, BLAKE, MANAGER, 2850, 30
7782, CLARK, MANAGER, 2450, 10
7788, SCOTT, ANALYST, 3000, 20
7839, KING, PRESIDENT, 5000, 10
7844, TURNER, SALESMAN, 1500, 30
7876, ADAMS, CLERK, 1100, 20
7900, JAMES, CLERK, 950, 30
7902, FORD, ANALYST, 3000, 20
7934, MILLER, CLERK, 1300, 10

PL/SQL procedure successfully completed.


Nested Tbale with object datattype:
SQL> create or replace type typ2 as object(c21 int, c22 varchar2(10));
     /

Type created.


SQL> create or replace type typ3 as object(c31 int, c32 int, c33 varchar2(10));

     /

Type created.


SQL> create table tab1(c1 int, c2 typ2, c3 typ3);


Table created.


SQL> 

BEGIN
  insert into tab1 values(1, typ2(21, ‘r1_c2_22’),typ3(31, NULL, ‘r1_c3_33’));
  insert into tab1 values(2, typ2(NULL, ‘r2_c2_22’),typ3(31, 32, ‘r2_c3_33’));
  insert into tab1 values(3, typ2(21, ‘r3_c2_22’),typ3(NULL, 32, ‘r3_c3_33’));
END;
  6  /

PL/SQL procedure successfully completed.


SQL> select * from tab1;


C1 C2(C21, C22) C3(C31, C32, C33)

———- ———————- ————————–
1 TYP2(21, ‘r1_c2_22’) TYP3(31, NULL, ‘r1_c3_33’)
2 TYP2(NULL, ‘r2_c2_22’) TYP3(31, 32, ‘r2_c3_33’)
3 TYP2(21, ‘r3_c2_22’) TYP3(NULL, 32, ‘r3_c3_33’)

3. VArray:

1. Syntax:
CREATE TYPE typ_nm_obj IS VARRAY OF varchar2(size);
typ_nm_tbl typ_nm_obj := typ_nm_obj();
2. Need to initialize, orherwise oracle will give error like: ORA-06531 Reference to uninitialized collection.
3. Need to use EXTEND keyword.
4. We can use %TYPE and %ROWTYPE, and can parse records with multiple columns using Varray.
5. The number of element are bounded up to the provided limit.
6. Size of the collection can increase dynamically.
7. Varray is always dense(consecutive) because deletion of intermediate elements is not allowed.
8. Varray can be stored in a table.
9. we can use it both in SQL and PLSQL block.
10. It can be an object type.

Syntax:
CREATE OR REPLACE TYPE vary_typ (
  code          CHAR(8) ,
  code_desc     VARCHAR2(32),
  code_indx CHAR(1)
);

CREATE OR REPLACE TYPE vary_typ_v AS VARRAY(6) OF vary_typ;

Example:


SQL> DECLARE
 CURSOR cur IS SELECT ename FROM emp;
 TYPE emp_rec_type IS VARRAY(20) OF emp.ename%TYPE;
 name_list emp_rec_type := emp_rec_type();
 cnt NUMBER :=0;
BEGIN
 FOR I IN cur
 LOOP
  cnt := cnt+1;
  name_list.EXTEND;
  name_list(cnt) := I.ename;
  DBMS_OUTPUT.PUT_LINE(name_list(cnt));
 END LOOP;
END;
/
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

PL/SQL procedure successfully completed.


TABLE OF .. RECORD TYPE with bulk collect and Varray.
SQL> DECLARE
 TYPE emp_sub_obj IS RECORD(
  empno NUMBER(4),
  ename VARCHAR2(10),
  job VARCHAR2(9),
  sal NUMBER(7, 2),
  deptno NUMBER(2));
 TYPE emp_rec_type IS VARRAY(20) OF emp_sub_obj;
 emp_list emp_rec_type := emp_rec_type();
 CURSOR cur IS SELECT * FROM emp;
BEGIN
 select empno, ename, job, sal, deptno bulk collect into emp_list from emp;
 FOR cnt IN emp_list.first..emp_list.last
 LOOP
  DBMS_OUTPUT.PUT_LINE(emp_list(cnt).empno||’, ‘||emp_list(cnt).ename||’, ‘||emp_list(cnt).job||’, ‘||emp_list(cnt).sal||’, ‘||emp_list(cnt).deptno);
 END LOOP;
END;
/
7369, SMITH, CLERK, 800, 20
7499, ALLEN, SALESMAN, 1600, 30
7521, WARD, SALESMAN, 1250, 30
7566, JONES, MANAGER, 2975, 20
7654, MARTIN, SALESMAN, 1250, 30
7698, BLAKE, MANAGER, 2850, 30
7782, CLARK, MANAGER, 2450, 10
7788, SCOTT, ANALYST, 3000, 20
7839, KING, PRESIDENT, 5000, 10
7844, TURNER, SALESMAN, 1500, 30
7876, ADAMS, CLERK, 1100, 20
7900, JAMES, CLERK, 950, 30
7902, FORD, ANALYST, 3000, 20
7934, MILLER, CLERK, 1300, 10

PL/SQL procedure successfully completed.



Choosing between Nested Table and Varray:
  • If the number of elements is known in advance, we should choose Varray or else if it is unknown we should go for Nested table.
  • If index values are consecutive we should choose Varray or else we should go for Nested table.
  • If elements are usually accessed sequentially then we should choose Varray or else if element access is random then we should go for Nested Table. 

Leave a Comment

Your email address will not be published. Required fields are marked *

Twitter
YouTube
Pinterest
LinkedIn