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

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

Object types - syntax, Object Types An object type is a user-defined co...

Object Types An object type is a user-defined complex datatype which encapsulates the data structure along with the functions and procedures required to manipulate the data. Th

Why use cursor variables, Why Use Cursor Variables ? Primarily, you use...

Why Use Cursor Variables ? Primarily, you use the cursor variables to pass the query result sets between the PL/SQL stored subprograms and different clients. Neither PL/SQL nor

Query, ALTER TABLE bb_basketitem ADD CONSTRAINT bitems_qty_ck CHECK (quan...

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

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

Fetch statement - syntax, FETCH Statement The FETCH statement retrieve ...

FETCH Statement The FETCH statement retrieve rows of data one at a time from the result set of the multi-row query. The data is stored in fields or variables which correspond t

Example of delete - sql, Example of DELETE - SQL As with UPDATE, a FOR...

Example of DELETE - SQL As with UPDATE, a FOR PORTION OF clause can be specified if the target table has a defined period name, as illustrated in Example. Example: Deleting

Data types in sql - decimal, Data Types in SQL - Decimal DECIMAL, NUME...

Data Types in SQL - Decimal DECIMAL, NUMERIC, REAL, FLOAT and various other terms for various sets of rational numbers. When these key words are specified for the declared typ

Sqls counterpart of the key words, SQLs counterpart of the key words: ...

SQLs counterpart of the key words: The text from the opening parenthesis to the end of the fourth line specifies the declared type of the table, meaning that every table ever

Iterative control:exit statements, EXIT The EXIT statement forces a loop...

EXIT The EXIT statement forces a loop to done unconditionally. Whenever an EXIT statement is encountered, the loop is done immediately and controls the passes to the next statem

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