10. PL/SQL Tables

Contents:
PL/SQL Tables and Other Collections
Characteristics of PL/SQL Tables
PL/SQL Tables and DML Statements
Declaring a PL/SQL Table
Referencing and Modifying PL/SQL Table Rows
Filling the Rows of a PL/SQL Table
Clearing the PL/SQL Table
PL/SQL Table Enhancements in PL/SQL Release 2.3
Working with PL/SQL Tables

A PL/SQL table is a one-dimensional, unbounded, sparse collection of homogeneous elements, indexed by integers. In technical terms, it is like an array; it is like a SQL table; yet it is not precisely the same as either of those data structures. This chapter explains these characteristics of the PL/SQL table in detail, so that you will understand the differences between PL/SQL tables and traditional arrays and SQL tables, and so that you will know how to use PL/SQL tables in your programs.

Like PL/SQL records, PL/SQL tables are composite data structures. Figure 10.1 shows a PL/SQL table composed of a single column named emp_name, with names saved to rows 100, 225, 226, 300, and 340.

Figure 10.1: The single-column, one-dimensional PL/SQL table

Let's take a look at an example and then explore the characteristics of a table. The following procedure accepts a name and a row and assigns that name to the corresponding row in the PL/SQL table:

1  PROCEDURE set_name (name_in IN VARCHAR2, row_in in INTEGER)
2  IS
3
4     TYPE string_tabletype IS
5        TABLE OF VARCHAR2(30) INDEXED BY BINARY_INTEGER;
6
7     company_name_table string_tabletype;
8
9  BEGIN
10    company_name_table (row_in) := name_in;
11 END;

The TYPE statement in lines 4-5 defines the structure of a PL/SQL table: a collection of strings with a maximum length of 30 characters. The INDEXED BY BINARY_INTEGER clause defines the integer key for the data structure. The table declaration in line 7 declares a specific PL/SQL table based on that table structure. In line 10, traditional array syntax is used to assign a string to a specific row in that PL/SQL table.

10.1 PL/SQL Tables and Other Collections

A PL/SQL table is one type of collection structure and, until Oracle8, it was the only one supported by PL/SQL. With Oracle8, there are now two additional types. This section briefly describes those types and includes information you need to know about using PL/SQL tables and other collections with different versions of PL/SQL. For detailed information about the new collection types, see Chapter 19, Nested Tables and VARRAYs .

10.1.1 PL/SQL Tables

PL/SQL tables are available only in releases of PL/SQL Version 2. PL/SQL tables reside in the private PL/SQL area of the Oracle Server database instance; they are not available as client-side structures at this time. As a result, you cannot declare and manipulate PL/SQL tables in your Oracle Developer/2000 environment.

You can, on the other hand, build stored procedures and packages which work with PL/SQL tables, but hide these structures behind their interface. You can then call this stored code from within Oracle Developer/2000 to take advantage of Version 2 features like PL/SQL tables.

Note that PL/SQL Release 2.3 offers several substantial enhancements to the PL/SQL tables, covered in Section 10.8, "PL/SQL Table Enhancements in PL/SQL Release 2.3" . If you are using PL/SQL Release 2.3 (available with Oracle7 Server Release 7.3), make sure you read through this section and make use of these advanced features. All topics covered earlier in the chapter assume PL/SQL Release 2.2 or earlier and do not take advantage of these new features. (Where applicable, I will point you to the relevant Release 2.3 features.)

10.1.2 Nested Tables and VARRAYs

Like PL/SQL tables, the two collection types introduced under Oracle8 -- nested tables and variable arrays (VARRAYs) -- can be used in PL/SQL programs. But these collection types offer something new: they can be used as the datatypes of fields in conventional tables and attributes of objects.

Both nested tables and VARRAYs can be used in PL/SQL and in the database (for example, as a column). They differ in certain ways, though, and you will find that each has pros and cons in certain situations, which I discuss in Chapter 19 .