LAST and COUNT give the same result for VARRAYs. : Varray « Collections « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Collections
3. Varray

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>
SQL> CREATE OR REPLACE TYPE mem_type IS VARRAY(10) of VARCHAR2(15)
  2  /

Type created.

SQL>
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>
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>
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>
SQL> drop table club;

Table dropped.

SQL> drop type mem_type;

Type dropped.

SQL>
SQL>
SQL>