Traversing Sparse Associative Arrays

A sparse associative array (indexed by string) might have gaps between elements. You can traverse it with a statement, as in the following example.

To run the code in the following example, which prints the elements of the job_titles array:

At the end of the declarative part of the example in Declaring Associative Arrays, insert this variable declaration:
```
i jobs_.job_id%TYPE;
```
In the executable part of the example in Populating Associative Arrays , after the code that populates the job_titles array, insert the code from Example: Traversing a Sparse Associative Array.

The following example includes two collection method invocations, job_titles.FIRST and job_titles.NEXT(i). job_titles.FIRST returns the first element of job_titles, and job_titles.NEXT(i) returns the subscript that succedes i. For more information about FIRST, see Oracle Database PL/SQL Language Reference. For more information about NEXT, see Oracle Database PL/SQL Language Reference.

Traversing a Sparse Associative Array

/* Declare this variable in declarative part:

   i jobs_.job_id%TYPE;

   Add this code to the executable part,
   after code that populates job_titles:
*/

i := job_titles.FIRST;

WHILE i IS NOT NULL LOOP
  DBMS_OUTPUT.PUT_LINE(RPAD(i, 12) || job_titles(i));
  i := job_titles.NEXT(i);
END LOOP;

Result:

AC_ACCOUNT  Public Accountant
AC_MGR      Accounting Manager
AD_ASST     Administration Assistant
AD_PRES     President
AD_VP       Administration Vice President
FI_ACCOUNT  Accountant
FI_MGR      Finance Manager
HR_REP      Human Resources Representative
IT_PROG     Programmer
MK_MAN      Marketing Manager
MK_REP      Marketing Representative
PR_REP      Public Relations Representative
PU_CLERK    Purchasing Clerk
PU_MAN      Purchasing Manager
SA_MAN      Sales Manager
SA_REP      Sales Representative
SH_CLERK    Shipping Clerk
ST_CLERK    Stock Clerk
ST_MAN      Stock Manager

Traversing Sparse Associative Arrays

Related Topics