Oracle PL/SQL - Printing Elements of Sparse Nested Table

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Data Type
  4. Nested Tables

Introduction

For an associative array indexed by string, the prior and next indexes are determined by key values, which are in sorted order.

The following code uses FIRST, NEXT, and a WHILE LOOP statement to print the elements of an associative array.

It prints the elements of a sparse nested table from first to last, using FIRST and NEXT, and from last to first, using LAST and PRIOR.

Demo

SQL>
SQL> DECLARE-- from ww  w .  j  a  v a 2 s . co m
  2    TYPE NumList IS TABLE OF NUMBER;
  3    n NumList := NumList(1, 2, NULL, NULL, 5, NULL, 7, 8, 9, NULL);
  4    idx INTEGER;
  5
  6  BEGIN
  7    DBMS_OUTPUT.PUT_LINE('First to last:');
  8    idx := n.FIRST;
  9    WHILE idx IS NOT NULL LOOP
 10      DBMS_OUTPUT.PUT('n(' || idx || ') = ');
 11      print(n(idx));
 12      idx := n.NEXT(idx);
 13    END LOOP;
 14
 15    DBMS_OUTPUT.PUT_LINE('Last to first:');
 16    idx := n.LAST;
 17    WHILE idx IS NOT NULL LOOP
 18      DBMS_OUTPUT.PUT('n(' || idx || ') = ');
 19      print(n(idx));
 20      idx := n.PRIOR(idx);
 21    END LOOP;
 22  END;
 23  /
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

PL/SQL procedure successfully completed.

SQL>

Related Topic