Assigning and comparing collections, PL-SQL Programming

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.

Posted Date: 10/3/2012 6:39:48 AM | Location : United States







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

Write discussion on Assigning and comparing collections
Your posts are moderated
Related Questions
Number Types The Number types permit you to store the numeric data (real numbers, integers, and floating-point numbers), show quantities, and do computations. BINARY_INTEG

ALTER TABLE bb_basketitem ADD CONSTRAINT bitems_qty_ck CHECK (quantity BEGIN INSERT INTO bb_basketitem VALUES (88,8,10.8,21,16,2,3); END; Brewbean’s wants to add a check

Explicit Cursor Attributes The cursor variable or each cursor has four attributes: %FOUND, %ISOPEN, %ROWCOUNT, and %NOTFOUND. When appended to the cursor or cursor variable, th

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,

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

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

What Is a Record  ? A record is a group of related data items that stored in the fields, each with its own name and datatype. Assume that you have different data about an em

Implicit Cursor is declared and used by the oracle environment internally. while the explicit cursor is declared and used by the external user. more over implicitly cursors are no

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

Declaring Records Whenever you define a RECORD type, you may declare records of that type, as the illustration shows: DECLARE TYPE StockItem IS RECORD ( item_no INTEG