Assigning and comparing collections, PL-SQL Programming

Assignment Help:

Assigning and Comparing Collections

One collection can be assigned to other by an SELECT, INSERT, UPDATE, or FETCH statement, an assignment statement, or by a subprogram call. As the illustration shown below, the collections should have the same datatype. Having the similar element type is not enough.

DECLARE
TYPE Clientele IS VARRAY(100) OF Customer;
TYPE Vips IS VARRAY(100) OF Customer;
group1 Clientele := Clientele(...);
group2 Clientele := Clientele(...);
group3 Vips := Vips(...);
BEGIN
group2 := group1;
group3 := group2; -- illegal;


Various datatypes becomes automatically null (and should be reinitialized). Now consider the illustration as shown below:

 
DECLARE
TYPE Clientele IS TABLE OF Customer;
group1 Clientele := Clientele(...); -- initialized
group2 Clientele; -- atomically null
BEGIN
IF group1 IS NULL THEN ... -- condition yields FALSE
group1 := group2;
IF group1 IS NULL THEN ... -- condition yields TRUE
...
END;


Similarly, if you assign the non-value NULL to a collection, the collection becomes automatically null.


Assigning Collection Elements

You can assign the value of an expression to the specific element in a collection by using the syntax

collection_name(subscript) := expression;



Where the expression yields a value of the type specified for elements in the collection type definition. If the subscript is null or not convertible to an integer, the PL/SQL raises the predefined exception VALUE_ERROR. If the collection is automatically null, then the PL/SQL raises COLLECTION_IS_NULL. Some of the examples are shown below:




DECLARE
TYPE NumList IS TABLE OF INTEGER;
nums NumList := NumList(10,20,30);
ints NumList;
...
BEGIN
...
nums(1) := TRUNC(high/low);
nums(3) := nums(1);
nums(2) := ASCII(’B’);
/* Assume execution continues despite the raised exception. */
nums(’A’) := 40; -- raises VALUE_ERROR
ints(1) := 15; -- raises COLLECTION_IS_NULL
END;


Comparing Whole Collections

The Nested tables and varrays can be automatically null; therefore they can be tested for the nullity, as the example below shows:

DECLARE
TYPE Staff IS TABLE OF Employee;
members Staff;
BEGIN
...
IF members IS NULL THEN ... -- condition yields TRUE;
END;


Though, the collections cannot be compared for equality or inequality. For illustration, the IF condition below is illegal as shown:

DECLARE
TYPE Clientele IS TABLE OF Customer;
group1 Clientele := Clientele(...);
group2 Clientele := Clientele(...);
BEGIN
...
IF group1 = group2 THEN -- causes compilation error
...
END IF;
END;
This restriction also applies to implicit the comparisons. For illustration, the collections cannot appear in an ORDER BY, GROUP BY, or DISTINCT list.


Related Discussions:- Assigning and comparing collections

Relational operators and logical operators, Relational Operators and Logica...

Relational Operators and Logical Operators It prepares the ground for subsequent sections in which each specific relational operator is paired with its logical counterpart, su

Read-only operator (+) - sql, Read-Only Operator (+) - SQL The term r...

Read-Only Operator (+) - SQL The term read-only operator to the mathematical term function. Here I just need to add that the SQL standard reserves the term function for read-

Keyword & parameter description - exception_init pragma, Keyword & Paramete...

Keyword & Parameter Description: PRAGMA: These keywords signify that the statement is a pragma (i.e. compiler directive). The Pragmas are processed at the compile time, n

Build a purchases report that matches the general ledger, Great Plains (Mic...

Great Plains (Microsoft Dynamics) Purchases Report Project Description: I want to build a purchases report that matches the General Ledger. presently, when I join the PM20

Manipulating individual elements - pl/sql, Manipulating Individual Elements...

Manipulating Individual Elements Faraway you have manipulated an entire collection. Within the SQL, to manipulate the individual elements of the collection, and then use the ope

Using aliases-declarations in sql, Using Aliases The Select-list items f...

Using Aliases The Select-list items fetched from a cursor related with the %ROWTYPE should have simple names or, if they are expressions, should have aliases. In the example bel

Avoiding collection exceptions, Avoiding Collection Exceptions   In ma...

Avoiding Collection Exceptions   In many cases, if you reference a nonexistent collection element, then PL/SQL raises a predefined exception. Consider the illustration shown b

Extension and and in sql, Extension and AND in SQL The theory book giv...

Extension and AND in SQL The theory book gives the following simple example of relational extension in Tutorial D: EXTEND IS_CALLED ADD ( FirstLetter ( Name ) AS Initial )

Pl sql code to declare cursors with parameter, Write a pl/sql block that de...

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

Library system, Hi,am developing a library system and relating all the tabl...

Hi,am developing a library system and relating all the table is somehow complex,could you kindly assist me

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