PRIOR and NEXT can be used to navigate through a sparse collection, ignoring deleted elements (even if a DELETE operation has reserved a placeholder for them).
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition does not support this feature.
PRIOR and NEXT are applicable for traversing sparse collections.
After specifying an index:
PRIORreturns the index of the previously existing element in the collection (if it exists). Otherwise,PRIORreturnsNULL. For any collectionc,c.PRIOR(c.FIRST)returnsNULL.NEXTreturns the index of the next existing element in the collection (if it exists). Otherwise,NEXTreturnsNULL. For any collectionc,c.NEXT(c.LAST)returnsNULL.
The specified index does not necessarily exist. However, if the collection c is a varray and the index exceeds c.LIMIT, then:
c.PRIOR(index)returnsc.LAST.c.NEXT(index)returnsNULL.
Here is an example of a varray:
obclient> DECLARE
TYPE var_ty IS VARRAY(10) OF NUMBER;
v_num var_ty := VAR_TY(11,22,33,44,55);
BEGIN
DBMS_OUTPUT.PUT_LINE(NVL(v_num.prior (10), -1));
DBMS_OUTPUT.PUT_LINE(NVL(v_num.next (10), -1));
END;
/
Query OK, 0 rows affected (0.04 sec)
5
-1
Here is an example of a nested table:
DECLARE
TYPE oblist IS TABLE OF VARCHAR(10);
nt OBLIST := oblist('A', 'B', NULL, NULL, 'E', NULL, 'F', 'G', 'H', NULL);
idxt INTEGER;
BEGIN
DBMS_OUTPUT.PUT_LINE('From First to Last Using Next:');
idxt := nt.FIRST;
WHILE idxt IS NOT NULL LOOP
DBMS_OUTPUT.PUT('nt(' || idxt || ') = ');
DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(nt(idxt)), 'NULL'));
idxt := nt.NEXT(idxt);
END LOOP;
DBMS_OUTPUT.PUT_LINE('--------------');
DBMS_OUTPUT.PUT_LINE('From Last to First Using Prior:');
idxt := nt.LAST;
WHILE idxt IS NOT NULL LOOP
DBMS_OUTPUT.PUT('nt(' || idxt || ') = ');
DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(nt(idxt)), 'NULL'));
idxt := nt.PRIOR(idxt);
END LOOP;
END;
/
Query OK, 0 rows affected
From First to Last Using Next:
nt(1) = A
nt(2) = B
nt(3) = NULL
nt(4) = NULL
nt(5) = E
nt(6) = NULL
nt(7) = F
nt(8) = G
nt(9) = H
nt(10) = NULL
--------------
From Last to First Using Prior:
nt(10) = NULL
nt(9) = H
nt(8) = G
nt(7) = F
nt(6) = NULL
nt(5) = E
nt(4) = NULL
nt(3) = NULL
nt(2) = B
nt(1) = A
The preceding examples use FIRST and NEXT, and LAST and PRIOR to output all elements in a sparse nested table.