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

Benefit of the dynamic sql pl sql, Benefit of the dynamic SQL: This pa... Benefit of the dynamic SQL: This part shows you how to take full benefit of the dynamic SQL and how to keep away from some of the common pitfalls. Passing the Names of Sc

Named notation, Named Notation The second procedure call uses the name... Named Notation The second procedure call uses the named notation. An arrow (=>) serve as the relationship operator that associates the formal parameter to the left of the arro

Authorisations - privileges, Authorisations - Privileges As relational... Authorisations - Privileges As relational theory is silent on the issue of authorisation, it offers nothing with which SQL's vast edifice in support of what it calls privilege

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

Keyword and parameter description - forall statement, Keyword &Parameter De... Keyword &Parameter Description: index_name: This is an undeclared identifier which can be referenced only within the FORALL statement and only as the collection subscript

Write an anonymous block that contains a pl/sql function, a. Write an anon... a. Write an anonymous block that contains a PL/SQL function. Given an order number orderNo, the function will calculate the total number of the parts in the order. Then the anonym

Operators on tables and rows, Operators on Tables and Rows Row Extrac... Operators on Tables and Rows Row Extraction TUPLE FROM r, SQL has row subqueries. These are just like scalar subqueries except that they may specify more than one column.

Transactions in sql, Transactions in SQL BEGIN TRANSACTION, COMMIT, an... Transactions in SQL BEGIN TRANSACTION, COMMIT, and ROLLBACK, SQL has the same syntax except for START in place of BEGIN. However, START TRANSACTION is used only for outermost

Data type conversion, Datatype Conversion At times it is necessary to c... Datatype Conversion At times it is necessary to convert a value from one datatype to another. For e.g. if you want to inspect a rowid, you should convert it to a character stri

Write sql queries, Write SQL queries to solve the following specifications.... Write SQL queries to solve the following specifications. Include the query AND THE OUTPUT.  A screen dump of the output is acceptable. Show as many rows as you can. A screen dump i