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

Declaring exceptions - user-defined exceptions, Declaring Exceptions T...

Declaring Exceptions The Exceptions can be declared only in the declarative part of the PL/SQL subprogram, block, or package. By introducing its name, you can declare an excep

Package specification, The Package Specification The package specificat...

The Package Specification The package specifications contain the public declarations. The scopes of these declarations are local to your database representation and global to t

Parameter and keyword description - object types, Parameter and Keyword Des...

Parameter and Keyword Description: type_name: This identifies a user-defined type specifier that is used in the subsequent declarations of the objects. AUTHID Clause:

Union all - sql, UNION ALL - SQL Further varieties of UNION arise when...

UNION ALL - SQL Further varieties of UNION arise when we replace the key word DISTINCT by ALL in any of the foregoing examples, as in Example. ALL specifies that if row r appe

Relational algebra - sql, Relational Algebra - SQL It describes some ...

Relational Algebra - SQL It describes some operators, that together constitute an algebra that is not only relationally complete but also irreducibly so (very nearly- apart f

Projection and existential quantification - sql, Projection and Existential...

Projection and Existential Quantification - SQL Intuitively it might seem that projection in SQL is simply a matter of specifying the required columns in the SELECT clause, a

Ssrs report writing, SSRS Report Writing Project Description: This re...

SSRS Report Writing Project Description: This report is part of a larger project to make a SQL Server Reporting Service (SSRS) based reporting solution. There can be more rep

What is a collection, What Is a Collection The collection is an ordered...

What Is a Collection The collection is an ordered group of elements, all of similar type (for e.g. the grades for a class of students). Each element has a unique subscript whic

Check constraints in sql, CHECK Constraints in SQL A CHECK constraint ...

CHECK Constraints in SQL A CHECK constraint is a table constraint defined using the key word CHECK, as already illustrated in several examples in this chapter. In particular,

Parameter and keyword description - fetch statement, Parameter and Keyword ...

Parameter and Keyword Description: cursor_name: This identifies an explicit cursor formerly declared within the present scope. cursor_variable_name: These identif

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