Defining and Declaring Collections

To create the collections, you must define a collection type, and then declare the collections of that type. You can define the VARRAY types and TABLE in the declarative part of any of the PL/SQL block, package, or subprogram. For the nested tables, you can use the syntax as shown below:


TYPE type_name IS TABLE OF element_type [NOT NULL];

and for varrays, use the syntax shown below:

TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit)
OF element_type [NOT NULL];


Where type_name is a type specifier used later to declare the collections, size_limit is a positive integer literal, and an element_type is any PL/SQL datatype except

BINARY_INTEGER, PLS_INTEGER
BOOLEAN
BLOB, CLOB (restriction applies only to varrays)
LONG, LONG RAW
NATURAL, NATURALN
NCHAR, NCLOB, NVARCHAR2
object types with BLOB or CLOB attributes (restriction applies only to varrays)
object types with TABLE or VARRAY attributes
POSITIVE, POSITIVEN
REF CURSOR
SIGNTYPE
STRING
TABLE
VARRAY


If element_type is a record type, then every field in the record must be a scalar type or an object type.


For the index-by tables, use the following syntax:

TYPE type_name IS TABLE OF element_type [NOT NULL]
INDEX BY BINARY_INTEGER;


Dissimilar nested tables and varrays, the index-by tables can have the following element types: BOOLEAN, LONG, BINARY_INTEGER, LONG RAW, NATURAL, NATURALN, PLS_ INTEGER, POSITIVE, POSITIVEN, STRING, and SIGNTYPE. That is because the nested tables and varrays are intended primarily for the database columns. As such, they cannot use the PL/SQL-specific types. If it is declared locally, they could theoretically use those types, and the restriction is preserved for consistency.

The Index-by tables are initially sparse. This enables you, for instance, to store the reference data in a temporary index-by table using a numeric primary key like the index. In the illustration below, you declare an index-by table of records. Every element of the table stores a row from the emp database table.


DECLARE
TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE
INDEX BY BINARY_INTEGER;
emp_tab EmpTabTyp;
BEGIN
/* Retrieve employee record. */
SELECT * INTO emp_tab(7468) FROM emp WHERE empno = 7788;


When defining a VARRAY type, you should specify its maximum size. In the following illustration, you define a type that stores up to 366 dates:

DECLARE
TYPE Calendar IS VARRAY(366) OF DATE;


To identify the element type, you can use %TYPE that provides the datatype of a variable or database column. You can also use %ROWTYPE that provides the rowtype of a cursor or database table. The Two illustrations are as follows:



DECLARE
TYPE EmpList IS TABLE OF emp.ename%TYPE; -- based on column
CURSOR c1 IS SELECT * FROM dept;
TYPE DeptFile IS VARRAY(20) OF c1%ROWTYPE; -- based on cursor


In the next illustration, you use a RECORD type to specify the element type:

DECLARE
TYPE AnEntry IS RECORD (
term VARCHAR2(20),
meaning VARCHAR2(200));
TYPE Glossary IS VARRAY(250) OF AnEntry;


In the final illustration, you impose a NOT NULL constraint on the element type:

DECLARE
TYPE EmpList IS TABLE OF emp.empno%TYPE NOT NULL;

An initialization clause is not needed (or allowed).




Declaring Collections

Once you define a collection type, you can declare the collections of that type, as the SQL Plus script below shows:

CREATE TYPE CourseList AS TABLE OF VARCHAR2(10) -- define type
/
CREATE TYPE Student AS OBJECT ( -- create object
id_num INTEGER(4),
name VARCHAR2(25),
address VARCHAR2(35),
status CHAR(2),
courses CourseList) -- declare nested table as attribute
/


The identifier courses represent the whole nested table. Each and every element of courses will store the code name of a college course like ’Math 1020’.
The script below creates a database column which stores varrays. Each and every element of the varrays will store a Project object.


CREATE TYPE Project AS OBJECT( --create object
project_no NUMBER(2),
title VARCHAR2(35),
cost NUMBER(7,2))
/
CREATE TYPE ProjectList AS VARRAY(50) OF Project -- define VARRAY
type
/
CREATE TABLE department ( -- create database table
dept_id NUMBER(2),
name VARCHAR2(15),
budget NUMBER(11,2),
projects ProjectList) -- declare varray as column
/


The illustration below shows that you can use %TYPE to provide the datatype of a earlier declared collection:

DECLARE
TYPE Platoon IS VARRAY(20) OF Soldier;
p1 Platoon;
p2 p1%TYPE;


You can declare collections as the proper parameters of the functions and procedures.
In that way, you can pass collections to the stored subprograms and from one subprogram to another. In the example below, you declare a nested table as the proper parameter of a packaged procedure:


CREATE PACKAGE personnel AS
TYPE Staff IS TABLE OF Employee;
...
PROCEDURE award_bonuses (members IN Staff);
END personnel;



You can also specify a collection type in the RETURN clause of a function specification, as the illustration below shows:


DECLARE
TYPE SalesForce IS VARRAY(25) OF Salesperson;
FUNCTION top_performers (n INTEGER) RETURN SalesForce IS...


The Collections follow the usual scoping and instantiation rules. In a block or subprogram, the collections are instantiated whenever you enter the block or subprogram and cease to exist when you exit. In a package, the collections are instantiated when you first reference the package and cease to exist whenever you end the database session.