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.
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 (
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;
Once you have created your table type, you can reference that table type to declare the actual table. The general format for a table declaration is:
where <table_name> is the name of the table and <table_type> is the name of a previously declared table type. In the following example I create a general table type for primary keys in PACKAGE and then use that table type to create two tables of primary keys:
PACKAGE company_pkg IS /* Create a generic table type for primary keys */ TYPE primary_keys_tabtype IS TABLE OF NUMBER NOT NULL INDEX BY BINARY_INTEGER; /* Declare two tables based on this table type */ company_keys_tab primary_keys_tabtype; emp_keys_tab primary_keys_tabtype; END company_pkg;
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.