PL/SQL User's Guide and Reference

PL/SQL Table Attributes

Description

Every PL/SQL table has the attributes EXISTS, COUNT, FIRST, LAST, PRIOR, NEXT, and DELETE. They make PL/SQL tables easier to use and your applications easier to maintain.

The attributes EXISTS, PRIOR, NEXT, and DELETE take parameters. Each parameter must be an expression that yields a BINARY_INTEGER value or a value implicitly convertible to that datatype.

DELETE acts like a procedure, which is called as a statement. The other PL/SQL table attributes act like a function, which is called as part of an expression. For more information, see "Using PL/SQL Table Attributes" .

Syntax

plsql_table_attribute ::=

plsql_table_name{  .COUNT
                 | .DELETE[(index[, index])]
                 | .EXISTS(index)
                 | .FIRST
                 | .LAST
                 | .NEXT(index)
                 | .PRIOR(index)}

Keyword and Parameter Description

plsql_table_name

This identifies a PL/SQL table previously declared within the current scope.

COUNT

This is a PL/SQL table attribute, which can be appended to the name of a PL/SQL table. COUNT returns the number of elements that a PL/SQL table contains.

DELETE

This is a PL/SQL table attribute, which can be appended to the name of a PL/SQL table. This attribute has three forms. DELETE removes all elements from a PL/SQL table. DELETE(n) removes the nth element. If n is null, DELETE(n) does nothing. DELETE(m, n) removes all elements in the range m .. n. If m is larger than n or if m or n is null, DELETE(m, n) does nothing.

index

This is a numeric expression that must yield a value of type BINARY_INTEGER or a value implicitly convertible to that datatype. For more information, see "Datatype Conversion"

EXISTS

This is a PL/SQL table attribute, which can be appended to the name of a PL/SQL table. EXISTS(n) returns TRUE if the nth element in a PL/SQL table exists. Otherwise, EXISTS(n) returns FALSE.

FIRST, LAST

These are PL/SQL table attributes, which can be appended to the name of a PL/SQL table. FIRST and LAST return the first and last (smallest and largest) index numbers in a PL/SQL table. If the PL/SQL table is empty, FIRST and LAST return nulls. If the PL/SQL table contains only one element, FIRST and LAST return the same index number.

NEXT, PRIOR

These are PL/SQL table attributes, which can be appended to the name of a PL/SQL table. NEXT(n) returns the index number that succeeds index n in a PL/SQL table. PRIOR(n) returns the index number that precedes index n. If n has no successor, NEXT(n) returns a null. Likewise, if n has no predecessor, PRIOR(n) returns a null.

Usage Notes

Currently, you cannot use PL/SQL table attributes in a SQL statement. If you try, you get a compilation error.

DELETE lets you free the resources held by a PL/SQL table. DELETE(n) and DELETE(m, n) let you prune a PL/SQL table. If an element to be deleted does not exist, DELETE simply skips it; no exception is raised.

The amount of memory allocated to a PL/SQL table can increase or decrease dynamically. As you delete elements, memory is freed page by page. If you delete the entire PL/SQL table, all the memory is freed.

You can use EXISTS to avoid the exception NO_DATA_FOUND, which is raised when you reference a nonexistent element.

You can use PRIOR or NEXT to traverse PL/SQL tables from which some elements have been deleted.

Examples

In the following example, you delete elements 20 through 30 from a PL/SQL table:

ename_tab.DELETE(20, 30);

The next example shows that you can use FIRST and LAST to specify the lower and upper bounds of a loop range provided each element in that range exists:

FOR i IN emp_tab.FIRST .. emp_tab.LAST LOOP
   ...
END LOOP;

In the following example, PL/SQL executes the assignment statement only if the element sal_tab(i) exists:

IF sal_tab.EXISTS(i) THEN
   sal_tab(i) := sal_tab(i) + 500;
ELSE
   RAISE salary_missing;
END IF;

You can use PRIOR or NEXT to traverse PL/SQL tables from which some elements have been deleted, as the following generic example shows:

DECLARE
   ...
   i BINARY_INTEGER;
BEGIN
   ..
   i := any_tab.FIRST;  -- get index of first element
   WHILE i IS NOT NULL LOOP
      ...  -- process any_tab(i)
      i := any_tab.NEXT(i);  -- get index of next element
   END LOOP;
END;