Initializing and Referencing Collections
Until you initialize a collection, a nested table or varray is automatically null (i.e. the collection itself is null, not its elements). To initialize a nested table or varray, you must use a constructor that is a system-defined function with the same name as the collection type. This function "constructs" collections from the elements which passed to it.
In the following illustration, you pass 6 elements to the constructor CourseList(), that returns a nested table containing those elements:
DECLARE
my_courses CourseList;
BEGIN
my_courses := CourseList(’Econ 2010’, , ’Mgmt 3100’, ’Acct 3401’
’PoSc 3141’, ’Mktg 3312’, ’Engl 2005’);
...
END;
In the next illustration, you pass 3 objects to the constructor ProjectList(), that returns a varray containing those objects:
DECLARE
accounting_projects ProjectList;
BEGIN
accounting_projects :=
ProjectList(Project(1, 'Design New Expense Report', 3250),
Project(2, 'Outsource Payroll', 12350),
Project(3, 'Audit Accounts Payable', 1425));
...
END;
You need not initialize the entire varray. For illustration, if a varray has a maximum size of 50, you can pass less than 50 elements to its constructor.
Except you impose the NOT NULL constraint or specify a record type for the elements, you can pass null elements to the constructor. An illustration is as follow:
BEGIN
my_courses := CourseList(’Math 3010’, NULL, ’Stat 3202’, ...);
The next illustration shows that you can initialize a collection in its declaration that is a good programming practice:
DECLARE
my_courses CourseList :=
CourseList(’Art 1111’, ’Hist 3100’, ’Engl 2005’, ...);
If you call a constructor without the arguments, you get an empty but non-null collection, as the example below shows:
DECLARE
TYPE Clientele IS VARRAY(100) OF Customer;
vips Clientele := Clientele(); -- initialize empty varray
BEGIN
IF vips IS NOT NULL THEN -- condition yields TRUE
...
END IF;
END;
Except for index-by tables, the PL/SQL never calls a constructor completely, so you should call it clearly. The Constructor calls are allowed wherever the function calls are allowed, which includes the SELECT, VALUES, and SET clauses.
In the illustration below, you insert a Student object into the object table sophomores. The table constructor CourseList() gives a value for the attribute courses.
BEGIN
INSERT INTO sophomores
VALUES (Student(5035, ’Janet Alvarez’, ’122 Broad St’, ’FT’,
CourseList(’Econ 2010’, ’Acct 3401’, ’Mgmt 3100’, ...)));
...
In the final illustration, you insert a row into the database table department. The varray constructor ProjectList() gives a value for the column projects.
BEGIN
INSERT INTO department
VALUES(60, 'Security', 750400,
ProjectList(Project(1, 'Issue New Employee Badges', 9500),
Project(2, 'Find Missing IC Chips', 2750),
Project(3, 'Inspect Emergency Exits', 1900)));
...
Referencing Collection Elements
Each reference to an element involves a collection name and a subscript enclosed in the parentheses. The subscript determines that element is processed. To reference an element, you should specify its subscript using the syntax as shown:
collection_name(subscript)
Where the subscript is an expression that yields the integer. For index-by tables, the legal subscript range from -2147483647.. 2147483647. For nested tables, the legal range is from 1 .. 2147483647. And, for varrays, the legal range is from 1 .. size_limit.
You can reference a collection in all the expression contexts. In the example below, you reference an element in the nested table names:
DECLARE
TYPE Roster IS TABLE OF VARCHAR2(15);
names Roster := Roster(’J Hamil’, ’D Caruso’, ’R Singh’, ...);
i BINARY_INTEGER;
BEGIN
...
IF names(i) = ’J Hamil’ THEN
...
END IF;
END;
The later illustration shows that you can reference the elements of the collection in the subprogram calls:
DECLARE
TYPE Roster IS TABLE OF VARCHAR2(15);
names Roster := Roster(’J Hamil’, ’D Piro’, ’R Singh’, ...);
i BINARY_INTEGER;
BEGIN
...
verify_name(names(i)); -- call procedure
END;
When calling a function which returns a collection, use the syntax below to reference the elements in the collection:
function_name(parameter_list)(subscript)
For illustration, the call references below are the third element in the varray returned by the function new_hires:
DECLARE
TYPE Staff IS VARRAY(20) OF Employee;
staffer Employee;
FUNCTION new_hires (hiredate DATE) RETURN Staff IS ...
BEGIN
staffer := new_hires(’16-OCT-96’)(3); -- call function
...
END;