Oracle Magazine - July/August 2012

l_names (307) := 'Dali'; show_contents (l_names);

END;

/

I can also scan the contents of a collection in reverse, starting with LAS T and using the PRIOR method, as shown in Listing 4.

DELETING COLLEC TION ELEMENTS

PL/SQL offers a DELETE method, which you can use to remove all, one, or some elements from a collection. Here are some examples: 1. Remove all elements from a collection;

use the DELETE method without any arguments. This form of DELE TE works with all three kinds of collections.

l_names.DELETE;

2. Remove the first element in a collection; to remove one element, pass the index value to DELETE. This form of DELETE can be used only with an associative array or a nested table.

l_names.DELETE ( l_names.FIRST);

3. Remove all the elements between the specified low and high index values. This form of DELETE can be used only with an associative array or a nested table.

l_names.DELETE (100, 200);

If you specify an undefined index value, Oracle Database will not raise an error.

You can also use the TRIM method with varrays and nested tables to remove elements from the end of the collection. You can trim one or many elements:

l_names.TRIM; l_names.TRIM ( 3);

GET COMFY WI TH COLLECTIONS
It is impossible to take full advantage of
PL/SQL, including some of its powerful
features, if you do not use collections. This
article has provided a solid foundation
for working with collections, but there are
still several advanced features to explore,
including string-indexed and nested collec-

Code Listing 2: Display all strings in a collection

CREATE OR REPLACE PROCEDURE show_contents ( names_in IN DBMS_UTILITY.maxname_array)

IS
BEGIN
FOR indx IN names_in.FIRST .. names_in.LAST
LOOP
DBMS_OUTPUT.put_line (names_in (indx));
END LOOP;

END;

/

Code Listing 3: Use WHILE to iterate through a collection

CREATE OR REPLACE PROCEDURE show_contents ( names_in IN DBMS_UTILITY.maxname_array)

IS l_index PLS_INTEGER := names_in.FIRST;

BEGIN WHILE (l_index IS NOT NULL)

LOOP DBMS_OUTPUT.put_line (names_in (l_index)); l_index := names_in.NEXT (l_index);

END LOOP; END;

/

Code Listing 4: Scan a collection in reverse

CREATE OR REPLACE PROCEDURE show_contents ( names_in IN DBMS_UTILITY.maxname_array)

IS l_index PLS_INTEGER := names_in.LAST;

BEGIN WHILE (l_index IS NOT NULL)

LOOP DBMS_OUTPUT.put_line (names_in (l_index)); l_index := names_in.PRIOR (l_index);

END LOOP; END;

/

tions, which will be covered in a future article.

The next article in this PL/SQL 101 series will explore how to use collections with PL/SQL’s most important performance-related PL/SQL features: FORALL and

BULK COLLEC T. 

Steven Feuerstein
(steven.feuerstein@
quest.com) is Quest
Software’s PL/SQL
evangelist. He has
published 10 books on
Oracle PL/SQL (O’Reilly Media) and is an Oracle
ACE Director. More information is available at
stevenfeuerstein.com.