Oracle PL/SQL - Varrays (Variable-Size Arrays)

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Data Type
  4. Varrays (Variable-Size Arrays)

Introduction

A varray (variable-size array) is an array whose number of elements can vary from zero to the declared maximum size.

To access an element of a varray variable, use the syntax variable_name(index).

The lower bound of index is 1; the upper bound is the current number of elements.

The upper bound changes as you add or delete elements.

The upper bound cannot exceed the maximum size.

An uninitialized varray variable is a null collection.

The following code defines a local VARRAY type.

Demo

SQL>
SQL>-- w  ww .j a  v  a 2 s .c  o m
SQL> DECLARE
  2    TYPE Foursome IS VARRAY(4) OF VARCHAR2(15);  -- VARRAY type
  3
  4   
  5
  6    team Foursome := Foursome('A', 'B', 'C', 'D');
  7
  8    PROCEDURE print_team (heading VARCHAR2) IS
  9    BEGIN
 10      DBMS_OUTPUT.PUT_LINE(heading);
 11
 12      FOR i IN 1..4 LOOP
 13        DBMS_OUTPUT.PUT_LINE(i || '.' || team(i));
 14      END LOOP;
 15    END;
 16
 17  BEGIN
 18    print_team('2001 Team:');
 19
 20    team(3) := 'X';  -- Change values of two elements
 21    team(4) := 'Y';
 22    print_team('2005 Team:');
 23
 24    
 25
 26    team := Foursome('AA', 'BB', 'CC', 'DD');
 27    print_team('2009 Team:');
 28  END;
 29  /
2001 Team:
1.A
2.B
3.C
4.D
2005 Team:
1.A
2.B
3.X
4.Y
2009 Team:
1.AA
2.BB
3.CC
4.DD

PL/SQL procedure successfully completed.

SQL>

Related Topics