Using PL/SQL to Create Functions to Access Elements : Varray « Collections « Oracle PL/SQL Tutorial

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

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> CREATE OR REPLACE FUNCTION vs (vlist club.members%type, sub integer)
  2  RETURN VARCHAR2
  3  IS
  4  BEGIN
  5    IF sub <= vlist.last THEN
  6      RETURN vlist(sub);
  7    END IF;
  8      RETURN NULL;
  9  END vs;
 10  /

Function created.

SQL>
SQL> SELECT vs(members,2)
  2  FROM club
  3  /

VS(MEMBERS,2)
-----------------
Richard
John

SQL> SELECT DECODE(vs(members,3),null,'No members',vs(members,3))
  2  FROM club
  3  WHERE name IN ('FL', 'MD')
  4  /

DECODE(VS(MEMBERS,3),NULL,'NOMEMBERS',VS(MEMBERS,3))
----------------------------------------------------
Steph

SQL>
SQL>
SQL> drop table club;

Table dropped.

SQL> drop type mem_type;

Type dropped.

SQL>