Oracle PL/SQL - PL SQL Data Type VARRAY Type

Introduction

VARRAYs stand for Variable-size arrays.

In the Oracle environment, array subscripts start from 1, and not from 0 as in C and Java.

VARRAYs are of fixed length.

You specify the length of the array when you define it.

Arrays of elements of the same type use sequential numbers as a subscript.

VARRAYS can be used both in PL/SQL and SQL:

declare
     type VarrayType is varray(size) of ElementType;
     ...

create or replace type VarrayType
     is varray(size) of ElementType;

The size of a VARRAY must be a positive integer and cannot be null.

There are some restrictions on the data types you can use as the base element type:

Demo

SQL>
SQL> declare--   w  ww .  j a  v  a 2 s  .  c  om
  2        type month_va is varray(13) of VARCHAR2(20);
  3        v_month_va month_va;
  4        v_count_nr number;
  5  begin
  6        v_month_va:=month_va ('January','February',
  7                                  'March','April','May',
  8                                  'June','July','August',
  9                                  'September','October','November',
 10                                  'December');
 11        DBMS_OUTPUT.put_line('Length:'||v_month_va.count);
 12        v_month_va.extend;
 13        v_month_va(v_month_va.last):='Null';
 14        DBMS_OUTPUT.put_line('Length:'||v_month_va.count);
 15
 16        for i in v_month_va.first ..v_month_va.last
 17        loop
 18             select count(*)
 19                into v_count_nr
 20                from emp
 21             where nvl(replace(to_char(hiredate,'Month'),' '),'Null')=v_month_va(i);
 22
 23             DBMS_OUTPUT.put_line(v_month_va(i) ||': '||v_count_nr);
 24        end loop;
 25  end;
 26  /
Length:12
Length:13
January: 0
February: 0
March: 0
April: 0
May: 1
June: 0
July: 0
August: 0
September: 0
October: 0
November: 1
December: 0
Null: 0

PL/SQL procedure successfully completed.
SQL>

Oracle PL/SQL - PL SQL Data Type VARRAY Type

Introduction

Demo

Related Topic