Contents:
Characteristics of PL/SQL TablesPL/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 31 -1 to 2 31 -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.
Related Articles
copyright programming versatality 2012
