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

Ask an Expert

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

Write your message here..

Related Questions

Sql outer join, SQL outer join SELECT * FROM IS_CALLED NATURAL LEFT... SQL outer join SELECT * FROM IS_CALLED NATURAL LEFT JOIN IS_ENROLLED_ON Note that adding LEFT to an invocation of CROSS JOIN has no effect unless the right-hand operand

Appgen to sap data export, We are seeking a freelance consultant that is fa... We are seeking a freelance consultant that is familiar with Appgen applications. We require exporting all our data into a format appropriate for importing into SAP Business One. Pl

Declaring a cursor, Declaring a Cursor The Forward references are not ... Declaring a Cursor The Forward references are not allowed in the PL/SQL. Therefore, you must declare a cursor before referencing it in other statements. Whenever you declare a

Write sql code that will create the writes table, Question: Consider th... Question: Consider the following relations (primary keys are underlined): AUTHOR (ANo, aname, address, speciality) PUBLISHER (PNo, pname, Location) BOOK (BNo, Title, ISBN,

Merge and truncate in sql, MERGE and TRUNCATE in SQL SQL has two more ... MERGE and TRUNCATE in SQL SQL has two more table update operators, MERGE and TRUNCATE. MERGE, like INSERT, takes a source table s and uses it to update a target table t. Brief

Majority of differences among 9i-10g-11g, Majority of Differences among 9i,... Majority of Differences among 9i, 10G, 11G :- These are some combine feature which has differences among others. Automatic Workload Repository (AWR) Drop database' s

Example of not exists in sql, Example of NOT EXISTS in SQL Example: Us... Example of NOT EXISTS in SQL Example: Use of NOT EXISTS CREATE ASSERTION Must_be_enrolled_to_take_exam_alternative1 CHECK ( NOT EXISTS (SELECT StudentId, CourseId

Updating variables, Updating Variables For assignment, SQL uses the ke... Updating Variables For assignment, SQL uses the key word SET, as in SET X = X + 1 (read as "set X equal to X+1") rather than X: = X + 1 as found in many computer languages.

Projection in sql - correct version, Projection in SQL - correct version ... Projection in SQL - correct version Student StudentId is enrolled on some course. SELECT DISTINCT StudentId FROM IS_ENROLLED_ON In more complicated examples it is someti

Do you know anyone that can do this type of coding or not?, Task 2 [12 mark... Task 2 [12 marks] Write the package body for the following package specification (the detailed description of each function and procedure is provided in the appendix below). Place