Initializing and referencing collections, PL-SQL Programming

Assignment Help:

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;


Related Discussions:- Initializing and referencing collections

Write an anonymous block that contains a pl/sql procedure, Write an anonymo...

Write an anonymous block that contains a PL/SQL procedure. The procedure takes two input parameter: oldZip and newZip, and it updates the zipcodes table by replacing all oldZip wit

Manipulating collections-nested table examples, Manipulating Collections ...

Manipulating Collections Within PL/SQL, the collections add procedural power and flexibility. The biggest benefit is that your program can compute subscripts to process the spec

Cursors - syntax, Cursors   To execute the multi-row query, the Oracle...

Cursors   To execute the multi-row query, the Oracle opens an unnamed work region which stores the processing information. The cursor names the work region, access the informa

Using operator value in pl sql, Using Operator VALUE: As you may expec...

Using Operator VALUE: As you may expect, the operator VALUE returns the value of an object. The VALUE takes its argument a correlation variable.  For illustration, to return a

Using for update, Using FOR UPDATE If you declare a cursor which will ...

Using FOR UPDATE If you declare a cursor which will be referenced in the CURRENT OF clause of an UPDATE or DELETE statement, you should use the FOR UPDATE clause to obtain an

Union without corresponding - sql, UNION without CORRESPONDING - SQL T...

UNION without CORRESPONDING - SQL The use of UNION without CORRESPONDING. Example is merely by omitting CORRESPONDING, but only because the operands have identical SELECT clau

Understanding nested tables, Understanding Nested Tables Within the data...

Understanding Nested Tables Within the database, the nested tables can be considered as one-column database tables. The Oracle stores the rows of a nested table in no specific o

Object type in pl/sql, Object Type: The object type is a user-define...

Object Type: The object type is a user-defined composite datatype which encapsulates a data structure along with the functions and procedures required to manipulate the data

Positional and named notation, Positional and Named Notation You can wr...

Positional and Named Notation You can write the actual parameters when calling a subprogram, using either positional or named notation. That is, you can point to the relationsh

Primary key - sql, Primary Key - SQL A PRIMARY KEY specification carri...

Primary Key - SQL A PRIMARY KEY specification carries an implicit NOT NULL constraint on each column of the specified key. When more than one key constraint is required, the k

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd