PL/SQL - SPLessons

PL/SQL Array

Chapter 17

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

PL/SQL Array

PL/SQL Array

shape Description

PLSQL Array is used to hold homogeneous data types. The data structure given by the PL/SQL programming dialect is called as varray, which can store consecutive group of components of comparable types. A varray is used to store the group of data, however, an array is used to store a group of comparative data types.

Contiguous memory location exists for all varrays. The end element corresponds by the highest address and the beginning element corresponds to the lowest address. All the components in the varray has an index accompanied with it, and can be dynamically manipulated.

Creating varray type

shape Description

By using the CREATE TYPE statement, one can create a varray type. To create varray type, the most extreme size must be determined in the varray.

shape Syntax

The basic syntax for creating a varray is:

Create or replace type varray_type_name IS VARRAY(n) of <elements_types>;
Varray_type_name => Is a accurate attribute name.
N => Is the series of components in the varray.
Element_type => Is the data sort of an array.

shape Examples

The below example describes the varray program.

SQL> declare
  2  type namesarray is varray(5) of varchar2(10);
  3  type grades is varray(5) of integer;
  4  names namesarray;
  5  marks grades;
  6  total integer;
  7  begin
  8  names:=namesarray('shah','jack','maddie','mick','mad');
  9  marks:=grades(97,78,85,72,84);
 10  total:=names.count;
 11  dbms_output.put_line('Total'||total||'Students');
 12  FOR i in 1 .. total LOOP
 13  dbms_output.put_line('Student:'|| names(i)||'Marks:'||marks(i));
 14  END LOOP;
 15  END;
 16  /
Total 5 students
Student: Shah Marks: 97
Student: Jack Marks: 78
Student: Maddie Marks:85
Student: Mick Marks: 72
Student: Mad Marks:  84
PL/SQL procedure successfully completed.

The above example describes the varray program, in which it will print the student name and total marks.


shape Key Points

  • PLSQL Array – Is a collection of homogeneous data types.
  • Creating varray – By using create type statement, one can create a varray type.