PL/SQL Tables

Contents:

Characteristics of PL/SQL Tables
PL/SQL Tables and DML Statements
PL/SQL Tables and Other Collections
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.

   

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: 

PROCEDURE set_name (name_in IN VARCHAR2, row_in in INTEGER)

IS

TYPE string_tabletype IS

TABLE OF VARCHAR2 (30) INDEXED BY BINARY_INTEGER;

company_name_table string_tabletype;

BEGIN

company_name_table (row_in):= name_in;

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. 

 

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 Nested Tables and VARRAYs.

  

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. 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.) 

 

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

 

Characteristics of PL SQL Tables 

 

A definition worth repeating: A PL/SQL table is a one-dimensional, unbounded, sparse collection of homogenous elements, indexed by integers.

Let's examine each of these characteristics in detail:

One-dimensional 

A PL/SQL table can have only one column. It is, in this way, similar to a one-dimensional array. You cannot define a PL/SQL table so that it can be referenced as follows:

my_table (10, 44)

This is a two-dimensional structure and not currently supported.

Unbounded or Unconstrained 

There is no predefined limit to the number of rows in a PL/SQL table. The PL/SQL table grows dynamically as you add more rows to the table. The PL/SQL table is, in this way, very different from an array.

Related to this definition, no rows for PL/SQL tables are allocated for this structure when it is defined.

Sparse

In contrast, an array is a dense data structure. When you declare an array, all cells in the array are allocated in memory and are ready to use. In a PL/SQL table, a row exists in the table only when a value is assigned to that row. Rows do not have to be defined sequentially. Instead you can assign a value to any row in the table. So row 15 could have a value of ‘Fox’ and row 15446 a value of `Red', with no other rows defined in between. 

Homogeneous elements 

Because a PL/SQL table can have only a single column, all rows in a PL/SQL table contain values of the same datatype. It is, therefore, homogeneous.

With PL/SQL Release 2.3, you can have PL/SQL tables of records. The resulting table is still, however, homogeneous. Each row simply contains the same set of columns. 

Indexed by integers 

PL/SQL tables currently support a single indexing mode: by BINARY_INTEGER. This number acts as the "primary key" of the PL/SQL table. The range of a BINARY_INTEGER is from -2 ³¹ -1 to 2 ³¹ -1.

 Notice that the index of a row in the table can even be negative. In the PL/SQL table, a negative row number is no different from a positive one; it's just another integer. However, there are ways that you, the programmer, can take advantage of differences between positive and negative row numbers in your application. For example, the ps_global package, included on the companion disk, uses rows with positive numbers to store "globals-by-number" and uses negative row values to store "globals-by-name".

Because the row number does not have to be used sequentially and has such enormous range, you can use this integer index in interesting ways. For example, the row number for a PL/SQL table could be the primary key of your company table, so that: 

company_name (14055)

contains the name of the company whose company_id = 14055.