PL/SQL User's Guide and Reference

PL/SQL Tables

Description

PL/SQL tables are objects of type TABLE, which are modelled as (but not the same as) database tables. PL/SQL tables use a primary key to give you array-like access to rows. Like an array, a PL/SQL table is an ordered collection of elements of the same type. Each element has a unique index number that determines its position in the ordered collection.

However, PL/SQL tables differ from arrays in two important ways. First, arrays have fixed lower and upper bounds, but PL/SQL tables are unbounded. So, the size of a PL/SQL table can increase dynamically. Second, arrays require consecutive index numbers, but PL/SQL tables do not. So, a PL/SQL table can be indexed by any series of integers. For more information, see "PL/SQL Tables" .

To create PL/SQL tables, you must take two steps. First, you define a TABLE type, then declare PL/SQL tables of that type.

Syntax

table_type_definition ::=

TYPE table_type_name IS TABLE OF 
   {  cursor_name%ROWTYPE
    | record_type_name
    | record_name%TYPE
    | scalar_type_name
    | table_name%ROWTYPE
    | table_name.column_name%TYPE
    | variable_name%TYPE} [NOT NULL] INDEX BY BINARY_INTEGER;

plsql_table_declaration ::=

plsql_table_name table_type_name;

Keyword and Parameter Description

table_type_name

This identifies a user-defined type specifier, which is used in subsequent declarations of PL/SQL tables.

cursor_name

This identifies an explicit cursor previously declared within the current scope.

record_type_name

This identifies a RECORD type previously defined within the current scope. For more information, see "User-Defined Records"

record_name

This identifies a user-defined record previously declared within the current scope.

scalar_type_name

This identifies a predefined scalar datatype such as BOOLEAN, NUMBER, or VARCHAR2, which must be specified without constraints. For more information, see "Datatypes"

table_name

This identifies a database table (or view) that must be accessible when the declaration is elaborated.

table_name.column_name

This identifies a database table and column that must be accessible when the declaration is elaborated.

variable_name

This identifies a PL/SQL variable previously declared within the current scope.

%ROWTYPE

This attribute provides a record type that represents a row in a database table or a row fetched from a previously declared cursor. Fields in the record and corresponding columns in the row have the same names and datatypes.

%TYPE

This attribute provides the datatype of a previously declared record, database column, or variable.

INDEX BY BINARY INTEGER

The index of a PL/SQL table must have datatype BINARY_INTEGER, which can represent signed integers. The magnitude range of a BINARY_INTEGER value is -2147483647 .. 2147483647.

plsql_table_name

This identifies an entire PL/SQL table.

Usage Notes

You can define TABLE types in the declarative part of any block, subprogram, or package. To specify the element type, you can use %TYPE or %ROWTYPE.

A PL/SQL table is unbounded; its index can include any BINARY_ INTEGER value. So, you cannot initialize a PL/SQL table in its declaration. For example, the following declaration is illegal:

sal_tab SalTabTyp := (1500, 2750, 2000, 950, 1800);  -- illegal

The INDEX BY clause must specify datatype BINARY_INTEGER, which has a magnitude range of -2147483647 .. 2147483647. If the element type is a record type, every field in the record must have a scalar datatype such as CHAR, DATE, or NUMBER.

You can declare PL/SQL tables as the formal parameters of functions and procedures. That way, you can pass PL/SQL tables to stored subprograms and from one subprogram to another.

PL/SQL tables follow the usual scoping and instantiation rules. In a package, PL/SQL tables are instantiated when you first reference the package and cease to exist when you end the database session. In a block or subprogram, local PL/SQL tables are instantiated when you enter the block or subprogram and cease to exist when you exit.

Every PL/SQL table has the attributes EXISTS, COUNT, FIRST, LAST, PRIOR, NEXT, and DELETE. They make PL/SQL tables easier to use and your applications easier to maintain. For more information, see "Using PL/SQL Table Attributes" .

The first reference to an element in a PL/SQL table must be an assignment. Until an element is assigned a value, it does not exist. If you reference a nonexistent element, PL/SQL raises the predefined exception NO_DATA_FOUND.

To reference elements in a PL/SQL table, you specify an index number using the following syntax:

plsql_table_name(index)

When calling a function that returns a PL/SQL table, you use the following syntax to reference elements in the table:

function_name(parameters)(index)

If the function result is a PL/SQL table of records, you use the following syntax to reference fields in a record:

function_name(parameters)(index).field_name

Currently, you cannot use the syntax above to call a parameterless function because PL/SQL does not allow empty parameter lists. That is, the following syntax is illegal:

function_name()(index)  -- illegal; empty parameter list

Instead, declare a local PL/SQL table to which you can assign the function result, then reference the PL/SQL table directly.

You can retrieve Oracle data into a PL/SQL table in three ways: the SELECT INTO statement lets you select a single row of data; the FETCH statement or a cursor FOR loop lets you fetch multiple rows.

Using the SELECT INTO statement, you can select a column entry into a scalar element. Or, you can select an entire row into a record element. Using the FETCH statement or a cursor FOR loop, you can fetch an entire column of Oracle data into a PL/SQL table of scalars. Or, you can fetch an entire table of Oracle data into a PL/SQL table of records.

You cannot reference record variables in the VALUES clause. So, you cannot insert entire records from a PL/SQL table of records into rows in a database table.

With the Oracle Call Interface (OCI) or the Oracle Precompilers, you can bind host arrays to PL/SQL tables declared as the formal parameters of a subprogram. That allows you to pass host arrays to stored functions and procedures.

You can use a BINARY_INTEGER variable or compatible host variable to index the host arrays. Given the array subscript range m .. n, the corresponding PL/SQL table index range is always 1 .. n - m + 1. For example, if the array subscript range is 5 .. 10, the corresponding PL/SQL table index range is 1 .. (10 - 5 + 1) or 1 .. 6.

Examples

In the following example, you define a TABLE type named SalTabTyp:

DECLARE
   TYPE SalTabTyp IS TABLE OF emp.sal%TYPE
      INDEX BY BINARY_INTEGER;

Once you define type SalTabTyp, you can declare PL/SQL tables of that type, as follows:

sal_tab SalTabTyp;

The identifier sal_tab represents an entire PL/SQL table.

In the next example, you assign the sum of variables salary and increase to the tenth row in PL/SQL table sal_tab:

sal_tab(10) := salary * increase;

In the following example, you select a row from the database table dept into a record stored by the first element of the PL/SQL table dept_tab:

DECLARE
   TYPE DeptTabTyp IS TABLE OF dept%ROWTYPE
      INDEX BY BINARY_INTEGER;
   dept_tab DeptTabTyp;
BEGIN
   /* Select entire row into record stored by first element. */
   SELECT * INTO dept_tab(1) FROM dept WHERE deptno = 10;
   IF dept_tab(1).dname = 'ACCOUNTING' THEN
      ...
   END IF;
   ...
END;

In the final example, you fetch rows from a cursor into the PL/SQL table of records emp_tab:

DECLARE
   TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE
      INDEX BY BINARY_INTEGER;
   emp_tab EmpTabTyp;
   i BINARY_INTEGER := 0;
   CURSOR c1 IS SELECT * FROM emp;
BEGIN
   OPEN c1;
   LOOP
      i := i + 1;
      /* Fetch entire row into record stored by ith element. */
      FETCH c1 INTO emp_tab(i);
      EXIT WHEN c1%NOTFOUND;
      -- process data record
   END LOOP;
   CLOSE c1;
END;