Composite Datatypes   «Prev 

PL/SQL Table Methods

The EXISTS(n) method returns TRUE if the nth element within the PL/SQL table exists.

The COUNT method returns the number of elements that the PL/SQL table currently contains.

The FIRST LAST method returns the first and last (smallest and largest) index numbers within the PL/SQL table. This method returns NULL if the PL/SQL table is empty.

FIRST and LAST Methods

Use the FIRST and LAST methods with nested tables, associative arrays, and VARRAYs to return, respectively, the lowest and highest index values defined in the collection. For string-indexed associative arrays, these methods return strings; “lowest” and “highest” are determined by the ordering of the character set in use in that session. For all other collection types, these methods return integers. The specifications for these functions follow.

FUNCTION FIRST RETURN PLS_INTEGER | VARCHAR2;
FUNCTION LAST RETURN PLS_INTEGER | VARCHAR2;

For example, the following code scans from the start to the end of my collection:
FOR indx IN holidays.FIRST .. holidays.LAST
LOOP
send_everyone_home (indx);
END LOOP;

This kind of loop will only work if the collection is densely populated. In the next example, I use COUNT to concisely specify that I want to append a row to the end of an associative array. I use a cursor FOR loop to transfer data from the database to an associative array of records. When the first record is fetched, the companies collection is empty, so the COUNT operator will return 0.
FOR company_rec IN company_cur
LOOP
companies ((companies.COUNT) + 1).company_id
company_rec.company_id;
END LOOP;

Boundary considerations: FIRST and LAST return NULL when they are applied to initialized collections that have no elements. For VARRAYs, which have at least one element, FIRST is always 1, and LAST is always equal to COUNT.
Exceptions possible: If FIRST and LAST are applied to an uninitialized nested table or a VARRAY, they raise the COLLECTION_ IS_NULL predefined exception.


Example of the FIRST LAST method continued.

The PRIOR(n) method returns the index number that precedes index n within the PL/SQL table.

The NEXT method returns the index number that succeeds index n within a PL/SQL table

DECLARE
  TYPE PetNameTabTyp IS TABLE OF CHAR(10) INDEX
    BY BINARY_INTEGER;
  PName_tab PetNameTabTyp;
BEGIN
  PName_tab(1) := 'DOG';
  PName_tab(3) := 'CAT';
  PName_tab(5) := 'FISH';
  -- delete element 3
  PName_tab.DELETE(3);
  -- PL/SQL keeps a placeholder for element 3.
  -- So, the next statement appends element 4.
  PName_tab.EXTEND;
  PName_tab(4) := 'RABBIT';
END;

The EXTEND (n,i) method increases the size of the PL/SQL table.

The TRIM method removes one element from the end of the PL/SQL table.

The DELETE method removes all the elements from the PL/SQL table.