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

Using aggregation on nested tables - sql, Using Aggregation on Nested Table...

Using Aggregation on Nested Tables Example is the most direct translation of its counterpart in the theory book that can be obtained in SQL but it is so over-elaborate that no

Disjunction - sql, Disjunction (OR, ∨) Again we have nine rows instead...

Disjunction (OR, ∨) Again we have nine rows instead of just four and again, when unknown is not involved, the rows are as for 2VL. Also, when anything is paired with true, t

Parameter aliasing, Parameter Aliasing   To optimize the subprogram ca...

Parameter Aliasing   To optimize the subprogram call, the PL/SQL compiler can decide between the two techniques of the parameter passing. With the by-value techniques, the v

Methods in pl/sql, Methods: In normal, a method is a subprogram declar...

Methods: In normal, a method is a subprogram declared in an object type specification using the keyword MEMBER or STATIC. The method cannot have similar name as the object typ

Use external routines - improve performance of application, Use External Ro...

Use External Routines The PL/SQL is particular for the SQL transaction processing. Therefore, several tasks are more quickly completed in a lower-level language like C that is

Explicitly specifying the join condition - sql, Explicitly specifying the j...

Explicitly specifying the join condition - SQL SELECT * FROM IS_CALLED JOIN IS_ENROLLED_ON ON ( IS_CALLED.StudentId = IS_ENROLLED_ON.StudentId ) Now, the key word JO

Null statement-sequential control, NULL Statement The NULL statement c...

NULL Statement The NULL statement clearly specifies in action; it does nothing other than to pass control to the next statement. It can, though, improve the readability. In a

EXCEPTION handling, set serveroutput on declare a number(5); b numb...

set serveroutput on declare a number(5); b number(5); c number(5); begin a:=&a; b:=&b; c:=a/b; dbms_output.put_line(c); exception when zero_divide then d

Need azure crm web application with authentication, Need Azure CRM Web Appl...

Need Azure CRM Web Application with two-factor authentication We presently have a CRM-like database stored on MS Azure that we presently access over an MS Access application. It

Declaring and initializing objects in pl/sql, Declaring and Initializing Ob...

Declaring and Initializing Objects: An object type is once defined and installed in the schema; you can use it to declare the objects in any PL/SQL, subprogram, block or packa

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