Initializing and referencing collections, PL-SQL Programming

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;

Posted Date: 10/3/2012 6:38:58 AM | Location : United States







Related Discussions:- Initializing and referencing collections, Assignment Help, Ask Question on Initializing and referencing collections, Get Answer, Expert's Help, Initializing and referencing collections Discussions

Write discussion on Initializing and referencing collections
Your posts are moderated
Related Questions
ROWID and UROWID Internally, every database table has a ROWID pseudo column that stores binary values known as rowids. Each rowid shows the storage address of a row. A physical

Initializing Objects: Till you initialize an object by calling the constructor for its object type, the object is automatically null. That is, the object itself is null, not me

At times, Brewbean's has changed the id number for existing products. In the past, they have had to add a new product row with the new id to the BB_PRODUCT table, modify all the co

Write a pl/sql block that declares and uses cursors with parameters. In a loop, use a cursor to retrieve the department number and the department name from the departments table

Benefit of the dynamic SQL: This part shows you how to take full benefit of the dynamic SQL and how to keep away from some of the common pitfalls. Passing the Names of Sc

Pass the nulls to a dynamic SQL: Passing Nulls: Assume that you want to pass the nulls to a dynamic SQL statement. For illustration, you may write the EXECUTE IMMEDIATE

Calling Constructors: The Calls to a constructor are allowed wherever the function calls are allowed. Similarly to the functions, a constructor is called as a section of an ex

Challenge 1 You are required to do the project and write a test plan for it. Demo 4 is a check writer program for employees. In it, the user enters all information about the

Multiple Assignment- SQL SQL supports multiple assignment to local variables and also applies multiple assignment semantics in SET clauses of UPDATE statements, but does not

MAX and MIN operator in SQL Example: (SELECT MAX (Mark) FROM EXAM_MARK WHERE StudentId = 'S1') (SELECT MIN (Mark) FROM EXAM_MARK WHERE StudentId = 'S1') Example