10.4 Declaring a PLSQL Table

Chapter 10
PL/SQL Tables

 

---

10.4 Declaring a PL/SQL Table

As with a record, a PL/SQL table is declared in two stages:

• Define a particular PL/SQL table structure (made up of strings, dates, etc.) using the table TYPE statement. The result of this statement is a datatype you can use in declaration statements.

• Declare the actual table based on that table type. The declaration of a PL/SQL table is a specific instance of a generic datatype.

10.4.1 Defining the Table TYPE

The TYPE statement for a PL/SQL table has the following format:

TYPE <table_type_name> IS TABLE OF <datatype> [ NOT NULL ]    INDEX BY BINARY_INTEGER;

where <table_type_name> is the name of the table structure you are creating and <datatype> is the datatype of the single column in the table. You can optionally specify that the table be NOT NULL, meaning that every row in the table that has been created must have a value.

You must always specify INDEX BY BINARY_INTEGER at the end of the TYPE...TABLE statement, even though it is the only type of index you can have currently in a PL/SQL table.

PL/SQL uses BINARY_INTEGER indexes because they allow for the fastest retrieval of data. (In this case, the primary key is already in the internal binary format, so it does not have to be converted before it can be used by the runtime environment.)

The rules for the table type name are the same as for any identifier in PL/SQL: the name can be up to 30 characters in length, it must start with a letter, and it can include some special characters such as underscore ( _ ) and dollar sign ($).

The datatype of the table type's column can be any of the following:

Scalar datatype

Any PL/SQL-supported scalar datatype, such as VARCHAR2, POSITIVE, DATE, or BOOLEAN.

Anchored datatype

A datatype inferred from a column, previously defined variable, or cursor expression using the %TYPE attribute.

Here are some examples of table type declarations:

TYPE company_keys_tabtype IS TABLE OF company.company_id%TYPE NOT NULL    INDEX BY BINARY_INTEGER; TYPE reports_requested_tabtype IS TABLE OF VARCHAR2 (100)    INDEX BY BINARY_INTEGER;

NOTE: Prior to PL/SQL Release 2.3, you may not use composite datatypes declaring a PL/SQL table's column. With Release 2.3, you can create PL/SQL tables of records.