Oracle PL/SQL - PRIOR and NEXT Collection Methods

Introduction

PRIOR and NEXT move backward and forward in the collection.

These method ignores deleted elements, even if DELETE kept placeholders for them.

You can use these methods for traversing sparse collections.

Given an index, PRIOR returns the index of the preceding existing element of the collection, if one exists. Otherwise, PRIOR returns NULL.

For any collection c, c.PRIOR(c.FIRST) returns NULL.

Given an index, NEXT returns the index of the succeeding existing element of the collection, if one exists. Otherwise, NEXT returns NULL.

For any collection c, c.NEXT(c.LAST) returns NULL.

The given index need not exist. However, if the collection c is a varray, and the index exceeds c.LIMIT, then:

c.PRIOR(index) returns c.LAST.
c.NEXT(index) returns NULL.

For example:

Demo

SQL>
SQL> DECLARE-- from w  ww  . j  a  va2s .  c  o  m
  2    TYPE Arr_Type IS VARRAY(10) OF NUMBER;
  3    v_Numbers Arr_Type := Arr_Type();
  4  BEGIN
  5    v_Numbers.EXTEND(4);
  6
  7    v_Numbers (1) := 10;
  8    v_Numbers (2) := 20;
  9    v_Numbers (3) := 30;
 10    v_Numbers (4) := 40;
 11
 12    DBMS_OUTPUT.PUT_LINE(NVL(v_Numbers.prior (3400), -1));
 13    DBMS_OUTPUT.PUT_LINE(NVL(v_Numbers.next (3400), -1));
 14  END;
 15  /
4
-1

PL/SQL procedure successfully completed.

SQL>

Oracle PL/SQL - PRIOR and NEXT Collection Methods

Introduction

Demo

Related Topics