[Chapter 10] 10.4 Declaring a PL/SQL TableChapter 10PL/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 TYPEThe 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 datatypeAny PL/SQL-supported scalar datatype, such as VARCHAR2, POSITIVE, DATE, or BOOLEAN. Anchored datatypeA 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. 10.4.2 Declaring the PL/SQL TableOnce 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: <table_name> <table_type>;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;10.3 PL/SQL Tables and DML Statements10.5 Referencing and Modifying PL/SQL Table Rows Copyright (c) 2000 O'Reilly & Associates. All rights reserved.