PL/SQL Tables

PL/SQL tables are the temporary memory objects that give you access to the data like array. PL/SQL tables can have only one column and one primary key. The data type of the column can be any scalar type and data type of the primary key column is BINARY_INTEGER. The size of the PL/SQL tables is unconstrained, which means the number of rows in a PL/SQL table can increase dynamically.

Declaring PL/SQL Tables
You can declare a PL/SQL table in the declarative part of the PL/SQL block, subprogram, or package. PL/SQL tables are declared in two steps: first, you need to define the TABLE type and then declare the PL/SQL table of that TABLE type.

The following syntax is used to declare a table type:

TYPE type_name IS TABLE OF
{Column_definition}
INDEX BY BINARY_INTEGER;

Here type_name is the name of the TABLE type and Column_definition contains the declaration of the field name, field type, and constraints.


After defining the TABLE type, you can declare the PL/SQL table. The syntax for declaring a PL/SQL table is as follows:

Table_name Table_typename;

In the above syntax, Table_name is the name of the PL/SQL table and Table_typename is the name of the TABLE type which you have defined. The following example shows how to declare a PL/SQL table:

DECLARE
TYPE Tbl_Emp IS TABLE OF Emp.Ename%TYPE
INDEX BY BINARY_INTEGER;

In the above example, Tbl_Emp is a table data type that stores the name of the employees. After defining the table type Tbl_Emp, you can declare PL/SQL tables of that type in the following way:

Tbl_Emp Emp_Name;


Referring PL/SQL Tables
In PL/SQL, the elements of PL/SQL tables can be referred by specifying an index number. The syntax to refer a PL/SQL table is as follows:

plsql_tablename(index)

In the above syntax, plsql_tablename is the name of the PL/SQL table and the index is an expression that returns the BINARY_INTEGER data type.

The following example will illustrate the use of PL/SQL tables.


  DECLARE
  TYPE Emp_name IS TABLE OF Emp.Ename%TYPE
  INDEX BY BINARY_INTEGER;
  TYPE Emp_Sal IS TABLE OF Emp.Sal%TYPE
  INDEX BY BINARY_INTEGER;
  E_name Emp_name;
  E_Sal Emp_Sal;

  Ctrl_index BINARY_INTEGER := 0;
  BEGIN
    FOR EnameRec IN (SELECT * FROM Emp) LOOP
        Ctrl_index := Ctrl_index + 1;
        E_name(Ctrl_index) := EnameRec.Ename;
        E_Sal(Ctrl_index) := EnameRec.Sal;
    END LOOP;
    FOR i IN 1..Ctrl_index LOOP
        DBMS_OUTPUT.PUT_LINE(‘Name: ’ || E_name(i));
        DBMS_OUTPUT.PUT_LINE(‘Salary: ’ || E_Sal(i));
        DBMS_OUTPUT.PUT_LINE(‘-----------------------------------------’);
    END LOOP;
  END;
/