Oracle PRIOR and NEXT methods.

Preamble

In Oracle PL/SQL, the PRIOR and NEXT methods are functions that allow you to move back and forth in the collection (ignoring the deleted items even if DELETE stores fillers for them). These methods are useful for crossing rare collections.

Considering the index:

• PRIOR returns the index of the previous existing element in the collection, if any. Otherwise, PRIOR returns NULL.
• For any collection c, c.PRIOR (c.FIRST) returns NULL.
• NEXT returns the index of a subsequent existing collection item, if one exists. Otherwise, NEXT returns NULL.
• For any c collection, c.NEXT (c.LAST) returns NULL.
• This index does not have to exist. However, if the collection is Varray, the index cannot exceed LIMIT.

Syntax of PRIOR and NEXT collection methods in Oracle PL/SQL

collection_name.PRIOR;
collection_name.NEXT;

Parameters and arguments of PRIOR and NEXT collection methods

• collection_name – the name of the collection.
• PRIOR – the previous existing element of the collection.
• NEXT – the next existing element of the collection.

See also collection methods: DELETE, TRIM, EXTEND, EXISTS, FIRST and LAST, COUNT, LIMIT.

An example to understand how to use the PRIOR and NEXT collection methods in Oracle PL/SQL

An example that initializes a Nested Table with six elements, removes the fourth element and then shows the PRIOR and NEXT values for elements 1 to 7. Elements 4 and 7 do not exist. Element 2 exists despite its zero value.

DECLARE
TYPE nt_type IS TABLE OF NUMBER;
nt nt_type := nt_type(18, NULL, 36, 45, 54, 63);

BEGIN
nt.DELETE(4);
DBMS_OUTPUT.PUT_LINE('nt(4) was deleted.');

FOR i IN 1...7 LOOP
DBMS_OUTPUT.PUT('nt.PRIOR(' || i || ') = '); print(nt.PRIOR(i));
DBMS_OUTPUT.PUT('nt.NEXT(' || i || ') = '); print(nt.NEXT(i));
END LOOP;
END LOOP;
Result:
nt(4) was deleted.
nt.PRIOR(1) = NULL
nt.NEXT(1) = 2
nt.PRIOR(2) = 1
nt.NEXT(2) = 3
nt.PRIOR(3) = 2
nt.NEXT(3) = 5
nt.PRIOR(4) = 3
nt.NEXT(4) = 5
nt.PRIOR(5) = 3
nt.NEXT(5) = 6
nt.PRIOR(6) = 5
nt.NEXT(6) = NULL
nt.PRIOR(7) = 6
nt.NEXT(7) = NULL

For an Associative Array indexed by a string, the previous and next indexes are determined by the key values, which are in sorted order. In the example below, FIRST, NEXT and WHILE LOOP loop are used to print Associative Array elements.

DECLARE
TYPE NumList IS TABLE OF NUMBER;
n NumList := NumList(1, 2, NULL, NULL, 5, NULL, 7, 8, 9, NULL);
subscript INTEGER;

BEGIN
DBMS_OUTPUT.PUT_LINE('First to last:');
subscript := n.FIRST;
WHILE subscript IS NOT NULL LOOP
DBMS_OUTPUT.PUT('n(' || subscript || ') = ');
print(n(subscript));
subscript := n.NEXT(subscript);
END LOOP;

DBMS_OUTPUT.PUT_LINE('--------------');

DBMS_OUTPUT.PUT_LINE('Last to first:');
subscript := n.LAST;
WHILE subscript IS NOT NULL LOOP
DBMS_OUTPUT.PUT('n(' || subscript || ') = ');
print(n(subscript));
subscript := n.PRIOR(subscript);
END LOOP;
END;

Result:
First to last:
n(1) = 1
n(2) = 2
n(3) = NULL
n(4) = NULL
n(5) = 5
n(6) = NULL
n(7) = 7
n(8) = 8
n(9) = 9
n(10) = NULL
--------------
Last to first:
n(10) = NULL
n(9) = 9
n(8) = 8
n(7) = 7
n(6) = NULL
n(5) = 5
n(4) = NULL
n(3) = NULL
n(2) = 2
n(1) = 1

This example prints the elements of an unlimited Nested Table from first to last using FIRST and NEXT, and from last to first using LAST and PRIOR.

PL/SQL tutorial: PL/SQL Collection Method Prior & Next in Oracle Database

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.