Fun with Collection Methods

View All Scripts Login to Run Script

Script Name Fun with Collection Methods
Description In this script, I demonstrate most of the methods available for PL/SQL collections, including COUNT, FIRST, LAST, NEXT, PRIOR, DELETE.
Area PL/SQL General
Contributor Steven Feuerstein
Created Wednesday January 13, 2016

Statement 1

Rather than declare and populate a collection in each step, I declare a package-level type and variable, and fill it up. Since the variable is declared at the package level, it has session scope and persistence.

Set Up Collection

CREATE OR REPLACE PACKAGE methods_data 
IS 
   TYPE names_t IS TABLE OF VARCHAR2 (100); 
 
   my_family   names_t 
                  := names_t ('Veva', 
                              'Chris', 
                              'Eli', 
                              'Lauren', 
                              'Loey', 
                              'Juna', 
                              'Steven'); 
END;

Package created.

Statement 2
Pass a single value to DELETE and the element at that index value is removed - if it exists.
Delete One Element
```
BEGIN 
   methods_data.my_family.DELETE (3); 
END;
```
```
Statement processed.
```

Statement 3

If you try to "touch" an element at an undefined index value, PL/SQL raises NO_DATA_FOUND. If you want to avoid this problem, check first to see if the index value is defined with the EXISTS method.

Use EXISTS to See if Element is Defined

BEGIN 
   IF methods_data.my_family.EXISTS (2) 
   THEN 
      DBMS_OUTPUT.put_line (methods_data.my_family (2)); 
   END IF; 
 
   BEGIN 
      DBMS_OUTPUT.put_line (methods_data.my_family (3)); 
   EXCEPTION 
      WHEN NO_DATA_FOUND 
      THEN 
         DBMS_OUTPUT.put_line ('That''s why we have EXISTS!'); 
   END; 
 
   IF NOT methods_data.my_family.EXISTS (3) 
   THEN 
      DBMS_OUTPUT.put_line ('No name at index 3!'); 
   END IF; 
END;

Chris
That's why we have EXISTS!
No name at index 3!

Statement 4
Remove from the collection all elements between index values 5 and 7.
Delete a Range of Values
```
BEGIN 
   methods_data.my_family.DELETE (5, 7); 
END;
```
```
Statement processed.
```
Statement 5
COUNT tells you how many elements are in the collection, returns 0 if empty. It does not count undefined index values between FIRST and LAST.
COUNT Shows You the, Ahem, Count
```
BEGIN 
   DBMS_OUTPUT.put_line ( 
      'Count left = ' || methods_data.my_family.COUNT); 
END;
```
```
Count left = 3
```

Statement 6

Pass no arguments to DELETE and the collection is emptied of all elements.

Empty Entire Collection

BEGIN 
   methods_data.my_family.DELETE; 
 
   DBMS_OUTPUT.put_line ( 
      'Count left = ' || methods_data.my_family.COUNT); 
END;

Count left = 0

Statement 7

It was set up by the package initialization. Let's get it back to that former state with a direct assignment, using the constructor function. This writes over whatever was in there before.

Rebuild Collection

BEGIN 
   methods_data.my_family := 
      methods_data.names_t ('Veva', 
               'Chris', 
               'Eli', 
               'Lauren', 
               'Loey', 
               'Juna', 
               'Steven'); 
END;

Statement processed.

Statement 8

FIRST gives you the lowest defined index value, LAST gives you the highest. NEXT takes you to the next defined index value ("skipping" over any undefined values in between). PRIOR takes to the previous defined index value. NEXT from LAST is always NULL. PRIOR from FIRST is always NULL.

Navigation Methods: FIRST, LAST, NEXT, PRIOR

BEGIN 
   DBMS_OUTPUT.put_line ( 
      'First = ' || methods_data.my_family.FIRST); 
 
   DBMS_OUTPUT.put_line ( 
      'Last = ' || methods_data.my_family.LAST); 
 
   DBMS_OUTPUT.put_line ( 
      'Next from 3 = ' || methods_data.my_family.NEXT (3)); 
 
   DBMS_OUTPUT.put_line ( 
         'Next from last = ' 
      || NVL ( 
            TO_CHAR (methods_data.my_family.NEXT ( 
                       methods_data.my_family.LAST)), 
            'Not a thing')); 
 
   methods_data.my_family.delete (2); 
 
   DBMS_OUTPUT.put_line ( 
         'Prior from 3 (with 2 deleted) = ' 
      || methods_data.my_family.PRIOR (3)); 
 
   DBMS_OUTPUT.put_line ( 
         'Prior from first = ' 
      || NVL ( 
            TO_CHAR (methods_data.my_family.PRIOR ( 
                        methods_data.my_family.FIRST)), 
            'Not a thing')); 
END;

First = 1
Last = 7
Next from 3 = 4
Next from last = Not a thing
Prior from 3 (with 2 deleted) = 1
Prior from first = Not a thing

Statement 9
Use EXTEND to add elements to the end of a collection (note: this is only for nested tables and varrays). Pass no arguments, and a single element is added.
EXTEND Adds to Collection
```
BEGIN 
   methods_data.my_family.EXTEND; 
   DBMS_OUTPUT.put_line ( 
      'Last = ' || methods_data.my_family.LAST); 
END;
```
```
Last = 8
```
Statement 10
Pass a single argument to EXTEND and that number of elements are added. If you know you will be adding N elements, do it with a single call, rather than repeated calls to EXTEND in, say, a loop.
Extend Multiple Elements
```
BEGIN 
   methods_data.my_family.EXTEND (5); 
   DBMS_OUTPUT.put_line ( 
      'Last = ' || methods_data.my_family.LAST); 
END;
```
```
Last = 13
```

Statement 11

Rebuild Collection

BEGIN 
   methods_data.my_family := 
      methods_data.names_t ('Veva', 
               'Chris', 
               'Eli', 
               'Lauren', 
               'Loey', 
               'Juna', 
               'Steven'); 
END;

Statement processed.

Statement 12

If you pass a second argument to EXTEND, then all the new elements added are assigned the value found in that specified index value.

Extend with Copy

BEGIN 
   methods_data.my_family.EXTEND (10, 2); 
 
   FOR indx IN 1 .. methods_data.my_family.COUNT 
   LOOP 
      DBMS_OUTPUT.put_line (methods_data.my_family (indx)); 
   END LOOP; 
END;

Veva
Chris
Eli
Lauren
Loey
Juna
Steven
Chris
Chris
Chris
Chris
Chris
Chris
Chris
Chris
Chris
Chris

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database