EXISTS and LAST : Varray « Collections « Oracle PL/SQL Tutorial

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

Suppose we add a row with no members to the Club table:

EXISTS returns a Boolean that acknowledges the presence (T) or absence (F) of a member of a VARRAY.

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> INSERT INTO club values ('NY','55 Fifth Ave.','NYC',
  2    '999-9999',null)
  3  SELECT *
  4  FROM club
  5
SQL>
SQL> CREATE OR REPLACE FUNCTION vs (vlist club.members%type, sub integer)
  2  RETURN VARCHAR2
  3  IS
  4  BEGIN
  5    IF vlist.exists(1) THEN
  6     IF sub <= vlist.last THEN
  7       RETURN vlist(sub);
  8     ELSE
  9       RETURN 'Less than '||sub||' members';
 10     END IF;
 11    ELSE
 12     RETURN 'No members';
 13    END IF;
 14  END vs;
 15  /

Function created.

SQL> ---The EXISTS function requires an argument to tell which element of the VARRAY is referred to. In the above function we are saying in the coded if-statement that if there is no first element, then return "No members." If a first member of the array is present, then the array is not null and we can look for whichever member is sought (per the value of sub). If the value of sub is less than the value of the last subscript, then the return of "'Less than '||sub||' members'" is effected.
SQL>
SQL> SELECT c.name, vs(members,3) member_name
  2  FROM club c;

NAME
----------
MEMBER_NAME
------------------------
AL
Less than 3 members

FL
Steph


SQL>
SQL> drop table club;

Table dropped.

SQL> drop type mem_type;

Type dropped.