The functions FIRST and LAST may be used to set the upper and lower limit of a for-loop to access members of the array one at a time in PL/SQL.
SQL>
SQL> CREATE OR REPLACE TYPE mem_type IS VARRAY(10) of VARCHAR2(15)
2 /
Type created.
SQL>
SQL> CREATE TABLE club (Name VARCHAR2(10),
2 Address VARCHAR2(20),
3 City VARCHAR2(20),
4 Phone VARCHAR2(8),
5 Members mem_type)
6 /
Table created.
SQL>
SQL> INSERT INTO club VALUES ('AL','111 First St.','Mobile',
2 '222-2222', mem_type('Brenda','Richard'));
1 row created.
SQL>
SQL> INSERT INTO club VALUES ('FL','222 Second St.','Orlando',
2 '333-3333', mem_type('Gen','John','Steph','JJ'));
1 row created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE vartest1 IS
2 CURSOR fcur IS SELECT name, members FROM club;
3 BEGIN
4 FOR j IN fcur LOOP
5 dbms_output.put_line('For the '||j.name||' club ...');
6 IF j.members.exists(1) THEN
7 FOR k IN j.members.first..j.members.last LOOP
8 dbms_output.put_line('** '||j.members(k));
9 END LOOP;
10 ELSE
11 dbms_output.put_line('** There are no members on file');
12 END IF;
13 END LOOP; /* end for j in fcur loop */
14 END vartest1;
15 /
Procedure created.
SQL> exec vartest1;
For the AL club ...
** Brenda
** Richard
For the FL club ...
** Gen
** John
** Steph
** JJ
PL/SQL procedure successfully completed.
SQL>
SQL> drop table club;
Table dropped.
SQL> drop type mem_type;
Type dropped.
SQL>