PL-SQL Arrays

PL-SQL Arrays – Learn how to declare  and initialize the values to array with some example programs.

PL-SQL Arrays

An array in PL-SQL is Varray. A Varray is a sequential collection of elements that can store of same type. Varray stores ordered collection of data with the contiguous memory locations.

• For arrays we can mention the size to store number of elements in it.
• The lowest address corresponds to the first element and the highest address to the last element. starting index of array is 1.
• For arrays we can increase the size depends on the requirement.

For Example varray name -PrintArray is a varray that have  some elements stored as shown below.

Printarray

Index	1	2	3	4	5
Value	‘JOHN FORD’	‘LUCY ROY’	‘DANE BRECK’	‘STEPHEN KING’	‘STOT COT’

Now we can observe that the size of the array is  5 and index starts from 1.

How to Create array in PL-SQL?

Syntax

CREATE OR REPLACE TYPE varray_type_name IS VARRAY(n) of <element_type>

varray_type_name is a variable name
n is the number of elements in the varray
element_type is the data type of the elements of the array.

Example

CREATE Or REPLACE TYPE PrintArray AS VARRAY(5) OF VARCHAR2(10);

how to resize the max size of array?

CREATE Or REPLACE TYPE PrintArray AS VARRAY(10) OF VARCHAR2(10);

using the above query we can increase the size of array from 5 to 10.

how to drop the varray ?

DROP TYPE varray_type_name ;

How to declare a Varray in PL/SQL block.

syntax:

TYPE varray_type_name IS VARRAY(n) of <element_type>

Example

TYPE PrintArray AS VARRAY(5) OF VARCHAR2(10);

Example program to print the Names in the array.

DECLARE
TYPE PRINTARRAY IS VARRAY(5) OF VARCHAR2(20);
PRINTNAMES PRINTARRAY;
BEGIN
  PRINTNAMES :=PRINTARRAY('JOHN FORD','LUCY ROY','DANE BRECK','STEPHEN KING','STOT COT');
  FOR I      IN 1..PRINTNAMES.COUNT
  LOOP
    DBMS_OUTPUT.PUT_LINE(PRINTNAMES(I) );
  END LOOP;
END;

Output

JOHN FORD
LUCY ROY
DANE BRECK
STEPHEN KING
STOT COT

Note
The first index is 1 not 0.

how to initialize the values for an array?

PRINTNAMES(i) := 'value';

Example program to initialize the array

DECLARE
TYPE PRINTARRAY IS VARRAY(5) OF VARCHAR2(20);
PRINTNAMES PRINTARRAY:=PRINTARRAY();
BEGIN
  -- PRINTNAMES :=PRINTARRAY('JOHN FORD','LUCY ROY','DANE BRECK','STEPHEN KING','STOT COT');
  --intialize the array size using extend key word
  PRINTNAMES.extend(5);
  --initialize the values to index
  PRINTNAMES(1) := 'JOHN FORD';
  PRINTNAMES(2) := 'LUCY ROY';
  PRINTNAMES(3) := 'DANE BRECK';
  PRINTNAMES(4) := 'STEPHEN KING';
  PRINTNAMES(5) := 'STOT COT';
  FOR I         IN 1..PRINTNAMES.COUNT
  LOOP
    DBMS_OUTPUT.PUT_LINE(PRINTNAMES(I) );
  END LOOP;
END;

Output

JOHN FORD
LUCY ROY
DANE BRECK
STEPHEN KING
STOT COT

Example program to use %Type with VARRAY

DECLARE
  CURSOR e_employees
  IS
    SELECT name FROM employee_tbl1;
TYPE e_list
IS
  varray (10) of employee_tbl1.Name%type ;
  name_list e_list :=e_list();
  counter INTEGER :=0;
BEGIN
  FOR n IN e_employees
  LOOP
    counter            := counter +1;
    name_list.extend;
    name_list(counter)  := n.name; 
    dbms_output.put_line('employee('||counter||'):'||name_list(counter));
  END LOOP;
END;

Output

employee(1):Tom
employee(2):Pam
employee(3):John
employee(4):Sam
employee(5):Todd
employee(6):Ben
employee(7):Sara
employee(8):Valarie
employee(9):James
employee(10):Russell

NOTES

1. The starting index for varrays is always 1.
2. Can initialize the varray elements using the constructor method of the varray type, which has the same name as the varray.
3. Varrays are one-dimensional arrays.
4. A varray is automatically NULL when it is declared and must be initialized before its elements can be referenced
5. These are dense and Not sparse, which means there is no way to delete individual elements of a Varray.