Oracle 10g PLSQL New Features (Part 3) - Enhancements To PLSQL Collections (ctd)

Oracle 10g PLSQL new features part 1 covered PLSQL regular expressions and compiler enhancements and part 2 started our look at enhancements to PL/SQL collections which continues here.

PLSQL Collection Enhancements

Having looked at comparing nested table collections (part 2), let's

look at using the set operators CARDINALITY, IS [NOT] A SET, IS [NOT] EMPTY to determine the properties of a nested table collection.

Then in Oracle 10g PLSQL new features part 4 we'll look at how to assign to nested tables using the set operators MULTISET UNION [DISTINCT] ,

MULTISET INTERSECT [DISTINCT], MULTISET EXCEPT [DISTINCT] and SET.

Using Set Operators On Oracle 10g PLSQL Collections

Example 1 - CARDINALITY

The cardinality function returns the count of the number of elements in a nested table type collection. The points to note with this function are that an empty set returns a count of 0 as expected but a null set returns a value of NULL.

declare
type BOOK_LIST is table of varchar2(100);
oracle_books book_list :=
book_list('Oracle 10g New Features'
,'Oracle 10g PLSQL New Features'
,'Oracle 10g Application Server');

empty_list book_list := book_list();
other_books book_list;

begin
dbms_output.put_line('cardinality of oracle_books is '|| cardinality(oracle_books));
dbms_output.put_line('no. of books in other_books is '|| cardinality(other_books));
dbms_output.put_line('no. of books in empty_list is '|| cardinality(empty_book_list));
end;

Example 1 produces the following results:

cardinality of oracle_books is 3
number of books in other_books is
number of books in empty_book_list is 0

Example 2 - IS [NOT] EMPTY

This PLSQL operator returns TRUE if a collection is empty or FALSE otherwise and of course the results can be inverted by use of the optional NOT operator.

declare
type book_list is table of varchar2(100);
oracle_books book_list;
oracle_books_2 book_list := book_list();
oracle_books_3 book_list := book_list('PLSQL New Features',
'Oracle 10g New Features','Oracle 10g Application Server');

begin
    if oracle_books is empty then
dbms_output.put_line('oracle_books is empty');
    elsif oracle_books is null then
dbms_output.put_line('oracle_books is null');
    end if;
    if oracle_books_2 is empty then
dbms_output.put_line('oracle_books_2 is empty');
    else dbms_output.put_line('oracle_books_2 NOT empty');
end if;
    if oracle_books_3 is not empty then
dbms_output.put_line ('oracle_books_3 NOT empty'); end if;
end;

Example 2 produces the following results:

oracle_books is null
oracle_books_2 is empty
oracle_books_3 NOT empty

Example 3 - IS [NOT] A SET

This is used to test whether or not a given PLSQL collection has all unqie elements. If any duplicates are found or the collection is null then it is not a set.

declare
type book_list is table of varchar2(100);
null_list book_list;
empty_list book_list := book_list();

oracle_books book_list := book_list('PLSQL New Features',
'Oracle 10g New Features','Oracle 10g Application Server');

plsql_books book_list := book_list('PLSQL New Features',
'PLSQL New Features','PLSQL New Features');

rslt VARCHAR2(4);

begin

if empty_list is not a set then rslt := 'NOT '; end if;
dbms_output.put_line('empty_list is '||rslt||'a set');

if null_list is a set then rslt := null;
else rslt := 'NOT '; end if;
dbms_output.put_line('null_list is '||rslt||'a set');

if plsql_books is not a set then rslt := 'NOT ';
else rslt := null; end if;
dbms_output.put_line('plsql_books is '||rslt||'a set');

if oracle_books is not a set then rslt := 'NOT ';
else rslt := null; end if;
dbms_output.put_line('oracle_books is '||rslt||'a set');

end;

Example 3 produces the following results:

empty_list is a set
null_list is NOT a set
plsql_books is NOT a set
oracle_books is a set

The remaining collection enhancements in Oracle 10g PLSQL are covered in part 4.

Need help with your Oracle systems? Follow the links for Oracle training and consultancy in the UK and Oracle training and consultancy in New Zealand.

Remember to sign up to our newsletter to ensure you have the knowledge you need to do your job better, faster and smarter.

Return to Oracle 10g New Features Overview