Oracle PL/SQL Tutorial - PL/SQL Associative Arrays

PL/SQL supports three kinds of arrays, or PL/SQL collections.

Associative arrays is originally called PL/SQL tables.

Associative arrays allow us to create a single-dimension array.

Associative arrays can be based on almost any data type.

Example

The following PL/SQL procedure demonstrates how to declare an associative array or PL/SQL table.

Declaring an associative array consists of two steps.

Declare the new TYPE.
Declare a new variable of that TYPE.


declare-- from  ww  w  .ja va2  s .com

    TYPE name_table IS TABLE OF EMP.name%TYPE INDEX BY BINARY_INTEGER;
    t_name name_table;
    n_name binary_integer;

begin
  t_name(1)  := 'First';
  t_name(10) := 'Last';
  
  SYS.DBMS_OUTPUT.put_line(t_name(1));
  SYS.DBMS_OUTPUT.put_line(t_name(10));
  SYS.DBMS_OUTPUT.put_line('There are '||t_name.count()||' elements.');
  n_name := t_name.first();
  SYS.DBMS_OUTPUT.put_line('The first element is '||n_name||'.');
  n_name := t_name.next(n_name);
  SYS.DBMS_OUTPUT.put_line('The next element is '||n_name||'.');
  n_name := t_name.last();
  SYS.DBMS_OUTPUT.put_line('The last element is '||n_name||'.');
  n_name := t_name.prior(n_name);
  SYS.DBMS_OUTPUT.put_line('The prior element is '||n_name||'.');
  if t_name.exists(1) then
    SYS.DBMS_OUTPUT.put_line('Element 1 exists.');
  end if;
  SYS.DBMS_OUTPUT.put_line('deleting element 10');
  t_name.delete(10);
  SYS.DBMS_OUTPUT.put_line('There are '||t_name.count()||' elements.');
  SYS.DBMS_OUTPUT.put_line('deleting all elements');
  t_name.delete();
  SYS.DBMS_OUTPUT.put_line('There are '||t_name.count()||' elements.');
end;
/

Note

The syntax to declare the associative array is as follows:


TYPE <plsql_table_type_name> IS TABLE OF <data_type> 
INDEX BY BINARY_INTEGER;

<plsql_table_type_name> is the name you are giving to the new PL/SQL table TYPE,

<data_type> is the data type to use for the elements in the table or associative array.

Declare an associative array based on the new type with the following syntax:


<variable_name> <plsql_table_type_name>;

<variable_name> is an identifier for the PL/SQL table, <plsql_table_type_name> is the name of the TYPE.

Note 2

An associative array can be sparsely populated.

We don't need to add items consecutively to the array.

We can add them to any index value between -2,147,483,647 and 2,147,483,647.

The following table lists the PL/SQL table(Associative Array) built-in functions and procedures.

With PL/SQL tables, it's also possible to use a varchar2 data type as the index value.

So anywhere you see a reference to binary_integer, you can replace it with varchar2.

Method	Description
count()	Returns the number of elements
delete(ain_index in binary_integer)	Deletes the specified element
delete()	Deletes all elements
exists(ain_index in binary_integer)	Returns TRUE if the element exists; otherwise, FALSE
first()	Returns the index of the first element
last()	Returns the index of the last element
prior(ain_index in binary_integer)	Returns the index of the first element before the specified element
next(ain_index in binary_integer)	Returns the index of the first element after the specified element

Example 2

The following code is an example of a PL/SQL table (associative array) based on a row-level anchor.


declare-- from  ww  w  .ja va  2s  .c  o  m
    TYPE name_table IS TABLE OF EMP%ROWTYPE INDEX BY BINARY_INTEGER;
    t_name name_table;
    n_name binary_integer;

begin
  t_name(1).name  := 'CSS';
  t_name(10).name := 'HTML';
  SYS.DBMS_OUTPUT.put_line(t_name(1).name);
  SYS.DBMS_OUTPUT.put_line(t_name(10).name);
  SYS.DBMS_OUTPUT.put_line('There are '||t_name.count()||' elements.');
  n_name := t_name.first();
  SYS.DBMS_OUTPUT.put_line('The first element is '||n_name||'.');
  n_name := t_name.next(n_name);
  SYS.DBMS_OUTPUT.put_line('The next element is '||n_name||'.');
  n_name := t_name.last();
  SYS.DBMS_OUTPUT.put_line('The last element is '||n_name||'.');
  n_name := t_name.prior(n_name);
  SYS.DBMS_OUTPUT.put_line('The prior element is '||n_name||'.');
  if t_name.exists(1) then
    SYS.DBMS_OUTPUT.put_line('Element 1 exists.');
  end if;
  SYS.DBMS_OUTPUT.put_line('deleting element 10');
  t_name.delete(10);
  SYS.DBMS_OUTPUT.put_line('There are '||t_name.count()||' elements.');
  SYS.DBMS_OUTPUT.put_line('deleting all elements');
  t_name.delete();
  SYS.DBMS_OUTPUT.put_line('There are '||t_name.count()||' elements.');
end;
/

Note 3

The code above uses the keyword %ROWTYPE to anchor to a composite record type based on the columns in the WORKERS table.

The code also use the composite record's field name name with a dot operator (.) to the PL/SQL table's name and index, in order to store the name value in the associative array.

Record Types

We can use data type for declare a PL/SQL record.


declare--   w w w . j  a v  a  2 s .c om
    TYPE name_record is record (
    first_name                            EMP.first_name%TYPE,
    middle_name                           EMP.middle_name%TYPE,
    last_name                             EMP.last_name%TYPE );

    TYPE name_table is table of name_record index by binary_integer;
    t_name                                name_table;

begin
     t_name(1).first_name  := 'Jack';
     t_name(1).last_name   := 'Smith';
     t_name(2).first_name  := 'Jason';
     t_name(2).last_name   := 'O'Brain';

     SYS.DBMS_OUTPUT.put_line(t_name(1).last_name||', '||t_name(1).first_name);
     SYS.DBMS_OUTPUT.put_line(t_name(2).last_name||', '||t_name(2).first_name);
end;
/

Note 4

The syntax to declare a PL/SQL record is as follows:


TYPE <plsql_record_type_name> IS RECORD ( 
<field_name_1> <data_type_1>, 
<field_name_2> <data_type_2>,... 
<field_name_N> <data_type_N>);

where <plsql_record_type_name> is the name for the new PL/SQL record type, <field_name> is the name of a field in the record, <data_type> is the data type for the corresponding field.

The code above uses the dot operator (.) followed by the name of the field in the record to address the composite data type values in the PL/SQL table.

The syntax for creating a composite data type in a PL/SQL block a row instead of a record.

Then we could refer to a field as column.


TYPE <plsql_row_type_name> IS ROW ( 
<column_name_1> <data_type_1>, 
<column_name_2> <data_type_2>,... 
<column_name_N> <data_type_N>);

Multidimensional Arrays

The following code demonstrates how to use a PL/SQL table inside a PL/SQL record in order to work around the one-dimension limit.


declare-- from w ww .  j a  va 2  s  . co  m
    TYPE name_table is table of EMP.name%TYPE index by binary_integer;
    TYPE name_record is record (dim2 name_table );
    TYPE dim1 is table of name_record index by binary_integer;
    t_dim1 dim1;

begin
  t_dim1(1).dim2(1) := 'CSS HTML';
  t_dim1(1).dim2(2) := 'SQL';

  t_dim1(2).dim2(1) := 'Java';
  t_dim1(2).dim2(2) := 'Javascript';

  SYS.DBMS_OUTPUT.put_line(t_dim1(1).dim2(1));
  SYS.DBMS_OUTPUT.put_line(t_dim1(1).dim2(2));
  SYS.DBMS_OUTPUT.put_line(t_dim1(2).dim2(1));
  SYS.DBMS_OUTPUT.put_line(t_dim1(2).dim2(2));
end;
/