You can use the PRIOR and NEXT methods to go forward or backward in a collection, with deleted elements ignored even if the DELETE method retains placeholders for the deleted elements.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
The PRIOR and NEXT methods are applicable to traversing sparse collections.
After you specify an index:
PRIORreturns the indexes of elements (if any) that precede the specified index in the collection. Otherwise,PRIORreturnsNULL. For any collectionc,c.PRIOR(c.FIRST)returnsNULL.NEXTreturns the indexes of elements (if any) that succeed the specified index in the collection. Otherwise,NEXTreturnsNULL. For any collectionc,c.NEXT(c.LAST)returnsNULL.
You do not have to specify an existent index. However, if collection c is a variable-size array (or varray) and the indexes exceed c.LIMIT:
c.PRIOR(index)returnsc.LAST.c.NEXT(index)returnsNULL.
In the following example, the collection is 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
In the following example, the collection is 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 example uses FIRST and NEXT, and LAST and PRIOR to output all elements in a sparse nested table.
