PL_SQL TABLE

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.

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

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.

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, so you have an awful lot of rows with which to work.[1]

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

10.3 PL/SQL Tables and DML Statements

Tables are PL/SQL constructs. PL/SQL is a linguistic extension of the Oracle SQL language, but it is distinct from SQL. When PL/SQL executes a SQL DML statement (SELECT, INSERT, UPDATE, or DELETE), it actually passes control to the RDBMS or SQL kernel, so the PL/SQL statement containing the DML must conform to the SQL standards. Generally PL/SQL is designed to hide this distinction, but when you work with PL/SQL tables -- just as when you work with records (the other PL/SQL composite data structure) -- you will have to be aware of the differences between SQL and PL/SQL.

The PL/SQL table structure is clearly not a part of the SQL language and standards, in that it employs array-like syntax to access rows in the PL/SQL table. Keep the following restrictions in mind when you work with PL/SQL tables:

·         There is no concept of transaction integrity with PL/SQL tables. You cannot commit information to a PL/SQL table or roll back changes from the table.

·         You cannot SELECT from PL/SQL tables. There is no way to perform set-at-a-time processing to retrieve data from a PL/SQL table. This is a programmatic construct in a programmatic language. Instead you can use PL/SQL loops to move through the contents of a PL/SQL table, one row at a time.

·         You cannot issue DML statements (INSERTs, UPDATEs, and DELETEs) against PL/SQL tables (though PL/SQL Release 2.3 does offer a DELETE operator).

Of course, you can always use the individual rows of a table anywhere in a SQL DML statement where you can use an expression of a compatible type.

In this stage of PL/SQL's evolution, PL/SQL tables remain relatively simple in structure and restricted in usage. You can expect to see rapid progress in the sophistication, performance, and flexibility of this data structure in the coming years, as is evidenced by the enhancements offered in PL/SQL Release 2.3.

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 TYPE

The TYPE statement for a PL/SQL table has the following format:

TYPE  IS TABLE OF  [ NOT NULL ]

   INDEX BY BINARY_INTEGER;

where is the name of the table structure you are creating and 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 datatype

Any PL/SQL-supported scalar datatype, such as VARCHAR2, POSITIVE, DATE, or BOOLEAN.

Anchored datatype

A 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 Table

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 is the name of the table and 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.6 Filling the Rows of a PL/SQL Table

You can assign values to rows of a table in several ways:

·         Direct assignment

·         Iterative assignment

·         Aggregate assignment

These methods are described in the following sections.

10.6.1 Direct Assignment

As shown in previous examples, you can simply assign a value to a row with the assignment operator:

countdown_test_list (43) := 'Internal pressure';

company_names_table (last_name_row) := 'Johnstone Clingers';

Direct assignment makes sense when you need to make a change to a specific row. But what do you use when you want to fill a whole set of rows, for example, unloading a whole cursor-full of information from a database table? Here, iterative assignment may be more appropriate.

10.6.2 Iterative Assignment

In order to fill up multiple rows of a table, I recommend taking advantage of a PL/SQL loop. Within the loop you will still perform direct assignments to set the values of each row, but the primary key value will be set by the loop rather than hardcoded into the assignment itself.

In the following example, I use a WHILE loop to fill and then display a PL/SQL date table with the next set of business days, as specified by the ndays_in parameter:

/* Filename on companion disk: bizdays.sp */

CREATE OR REPLACE PROCEDURE show_bizdays

   (start_date_in IN DATE := SYSDATE, ndays_in IN INTEGER := 30)

IS

   TYPE date_tabtype IS TABLE OF DATE INDEX BY BINARY_INTEGER;

   bizdays date_tabtype;

   /* The row in the table containing the nth_day */

   nth_day  BINARY_INTEGER := 1;

   v_date DATE := start_date_in;

BEGIN

   /* Loop through the calendar until enough biz days are found */

   WHILE nth_day <= ndays_in

   LOOP

      /* If the day is not on the weekend, add to the table. */

      IF TO_CHAR (v_date, 'DY') NOT IN ('SAT', 'SUN')

      THEN

         bizdays (nth_day) := v_date;

         DBMS_OUTPUT.PUT_LINE (v_date);

         nth_day := nth_day + 1;

      END IF;

      v_date := v_date + 1;

   END LOOP;

END show_bizdays;

/

As you can see from this example, using the WHILE loop produces a neat, sequential load of the PL/SQL table.

10.6.3 Aggregate Assignment

Just as you can assign one entire record to another record of the same type and structure, you can perform aggregate assignments with tables as well. In order to transfer the values of one table to another, the datatype of the two tables must be compatible. Beyond that you simply use the assignment operator (:=) to transfer the values of one table to the other. The following example contains an example of an aggregate table assignment:

DECLARE

   TYPE name_table IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;

   old_names name_table;

   new_names name_table;

BEGIN

   /* Assign values to old_names table */

   old_names(1) := 'Smith';

   old_names(2) := 'Harrison';

   /* Assign values to new_names table */

   new_names(111) := 'Hanrahan';

   new_names(342) := 'Blimey';

   /* Transfer values from new to old */

   old_names := new_names;

   /* This assignment will raise NO_DATA_FOUND */

   DBMS_OUTPUT.PUT_LINE (old_names (1));

END;

A table-level assignment completely replaces the previously defined rows in the table. In the preceding example, rows 1 and 2 in old_names are defined before the last, aggregate assignment.

After the assignment, only rows 111 and 342 in the old_names table have values.

10.7 Clearing the PL/SQL Table

What happens when you are done with a PL/SQL table and want to remove it from memory? If a PL/SQL table is like a table, we should be able to DELETE the rows of that table or DROP it entirely, right? It's a nice idea, but you can't perform a SQL DELETE statement on a PL/SQL table because it is not stored in the database. You also cannot DROP a PL/SQL table.

You can set a single row to NULL with the following kind of assignment:

company_names_table (num_rows) := NULL;

But this assignment doesn't actually remove the row or make it undefined; it just sets the value of the row to NULL.

The only way to actually empty a PL/SQL table of all rows is to perform an aggregate assignment with a table that is empty -- a table, that is, with no rows defined.

With this approach, for every PL/SQL table you want to be able to empty, you declare a parallel, empty table of the same table type. When you are finished working with your table, simply assign the empty table to the actual table. This will unassign all the rows you have used. The following example demonstrates this technique:

DECLARE

   TYPE company_names_tabtype IS TABLE OF company.name%TYPE

      INDEX BY BINARY_INTEGER;

   company_names_tab company_names_tabtype;

   /* Here is the empty table declaration */

   empty_company_names_tab company_names_tabtype;

BEGIN

   ... set values in company names table ...

   /* The closest you can come to "dropping" a PL/SQL table */

   company_names_tab := empty_company_names_tab;

END;

10.8 PL/SQL Table Enhancements in PL/SQL Release 2.3

PL/SQL Release 2.3 offers significantly enhanced functionality for PL/SQL tables. These new features include:

·         Records supported as elements of PL/SQL tables. Prior to Release 2.3, the element or single column of the PL/SQL table could only be a scalar datatype, such as VARCHAR2 or BOOLEAN or DATE. Release 2.3 allows you to define table types whose element datatype is defined with the %ROWTYPE declaration attribute or is a named RECORD type.

·         New operations on PL/SQL tables. PL/SQL Release 2.3 offers a set of new built-in functions and procedures which return information about, or modify the contents of, a PL/SQL table. These operations are shown in Table 10.1.

These new features allow you to use PL/SQL tables for a wider range of applications and also manipulate the data in tables in a more natural and efficient manner. You can now create local PL/SQL data structures which mimic precisely the structure of a table stored in the database. You do not have to create separate tables and manage them in parallel to emulate the multiple-column SQL table structure.

You can use the built-ins to obtain PL/SQL table information that previously was unavailable. For example, you can use the COUNT function to determine the number of elements defined in a table. You no longer have to keep track of that number yourself.

Table 10.1: PL/SQL Release 2.3 Built-in Functions and Procedures for Tables
Operator	Description
COUNT	Returns the number of elements currently contained in the PL/SQL table.
DELETE	Deletes one or more elements from the PL/SQL table.
EXISTS	Returns FALSE if a reference to an element at the specified index would raise the NO_DATA_FOUND exception.
FIRST	Returns the smallest index of the PL/SQL table for which an element is defined.
LAST	Returns the greatest index of the PL/SQL table for which an element is defined.
NEXT	Returns the smallest index of the PL/SQL table containing an element which is greater than the specified index.
PRIOR	Returns the greatest index of the PL/SQL table containing an element which is less than the specified index.

These functions and procedures are described in detail later in this chapter.

10.8.1 PL/SQL Tables of Records

To declare a PL/SQL table, you must first declare a PL/SQL table TYPE. The syntax for the TYPE statement is as follows:

TYPE  IS TABLE OF 

   INDEX BY BINARY_INTEGER;

With PL/SQL Release 2.3, the may be a record type. This record type can be defined using the %ROWTYPE declaration attribute. You can also specify a previously defined record structure defined with the TYPE statement for records.

When you do create a PL/SQL table based on a record structure, that record may only be composed of scalar fields. A nested record type (in which a field in the record is yet another record type) may not be used to define a table type.

The following examples illustrate the different ways to declare table types based on records:

·         Declare a PL/SQL table type with same structure as the employee table:

·         TYPE local_emp_table IS TABLE OF employee%ROWTYPE

   INDEX BY BINARY_INTEGER;

·         Declare a PL/SQL table type to correspond to the data returned by a cursor:

·         CURSOR emp_cur IS SELECT * FROM employee;

·          

·         TYPE cursor_emp_table IS TABLE OF emp_cur%ROWTYPE

   INDEX BY BINARY_INTEGER;

·         Declare a PL/SQL table type based on a programmer-defined record:

·         TYPE emp_rectype IS

·            RECORD (employee_id INTEGER, emp_name VARCHAR2(60));

·          

·         TYPE emp_table IS TABLE OF emp_rectype

   INDEX BY BINARY_INTEGER;

Notice that when you use a programmer-defined record type you do not append the %ROWTYPE attribute to the record type. That is only done when you are using a table-based or cursor-based record.

10.8.1.1 Referencing fields of record elements in PL/SQL tables

References to fields of elements of PL/SQL tables which are records have the following syntax:

().

where is the name of the table, is an expression (constant, variable, or computed expression) which evaluates to a number and is the name of the field in the record used to define the PL/SQL table.

If, for example, you have created a PL/SQL table named emp_tab based on a record structure with a field named emp_name, then the following assignment sets the employee name in the 375th row of the PL/SQL table to SALIMBA:

emp_tab(375).emp_name := 'SALIMBA';

The index to the table does not have to be a constant. In the following example, the index is calculated:

IF names_table (old_name_row + 1).last_name = 'SMITH'

THEN

You can define functions which return PL/SQL tables. As a result, you can also reference a field in a PL/SQL table's record with the following syntax:

()().

An example will make it easier to understand this complicated syntax. Suppose that I have defined a function as follows:

FUNCTION best_company (year_in IN INTEGER)

   RETURN company_tabtype;

where company_tabtype is a PL/SQL table type with a record structure for its element. Then the following call to PUT_LINE displays the name of the company found in the tenth row of the returned table:

DBMS_OUTPUT.PUT_LINE

   (best_company(1995)(10).company_name || ' was tenth best!');

To make sense of this expression, break it up into its components:

best_company(1995)

Returns a table, each row of which contains information about a company.

best_company(1995)(10)

Returns the tenth row of that table.

best_company(1995)(10).company_name

Returns the name of the company found in the tenth row of the table.

You can improve the readability of such a statement by separating it as follows:

current_company_rec := best_company(1995)(10);

DBMS_OUTPUT.PUT_LINE

   (current_company.company_name || ' was tenth best!')

where current_company_rec is a record defined with the same type as the RETURN clause of the best_company function. Now you have two statements where only one is really needed, but the code can be more easily understood and therefore maintained.

10.8.1.2 Assigning records in PL/SQL tables

You can assign a whole record fetched from the database directly into the row of a PL/SQL table as shown below (where both the cursor and the PL/SQL table use the same company%ROWTYPE row type declaration):

FOR company_rec IN company_cur

LOOP

   /* Get last row used and add one. */

   next_row := NVL (company_table.LAST, 0) + 1;

   company_table(next_row) := company_rec;

END LOOP;

10.8.2 PL/SQL Table Built-ins

Each of the PL/SQL table built-in procedures and functions provides different information about the referenced PL/SQL table, except for DELETE, which removes rows from the PL/SQL table. The syntax for using the table built-ins for PL/SQL tables is different from the syntax I described in Part 3, Built-In Functions of this book. It employs a "member method" syntax, common in object-oriented languages such as C++.

To give you a feeling for member-method syntax, consider the LAST function. It returns the greatest index value in use in the PL/SQL table. Using standard function syntax, you might expect to call LAST as follows:

IF LAST (company_table) > 10 THEN ... /* Invalid syntax */

In other words, you would pass the PL/SQL table as an argument. In contrast, by using the member-method syntax, the LAST function is a method which "belongs to" the object, in this case the PL/SQL table. So the correct syntax for using LAST is:

IF company_table.LAST > 10 THEN ... /* Correct syntax */

The general syntax for calling these PL/SQL table built-ins is either of the following:

·         An operation which takes no arguments:

.

·         An operation which takes a row index for an argument:

.( [, ])

The following statement, for example, returns TRUE if the 15th row of the company_tab PL/SQL table is defined:

company_tab.EXISTS(15)

By using the member-method syntax, Oracle is able to distinguish the PL/SQL table functions such as EXISTS and DELETE from the SQL operations of the same name (which never appear with dot-qualified notation).

The following sections describe each of the table built-ins.

10.8.2.1 The COUNT function

The COUNT function returns the number of elements currently defined in the PL/SQL table. The specification for the function is:

FUNCTION COUNT RETURN INTEGER;

You call COUNT as follows:

total_rows := emp_table.COUNT;

Notice that if the emp_table structure were defined inside a package, then double dot notation would be needed to get the count:

total_rows := employee_pkg.emp_table.COUNT;

Prior to PL/SQL Release 2.3, the only way to determine this count was to manually keep track of the number of elements defined in the table.

10.8.2.2 The DELETE procedure

The DELETE procedure deletes elements from the specified PL/SQL table. The specifications for the procedure are overloaded, as shown in the following table.

Procedure Header	Description of Use
PROCEDURE DELETE;	Just as with SQL, this simplest form of the DELETE build-in (which takes no arguments at all) has the most sweeping impact: delete all rows from the PL/SQL table.
PROCEDURE DELETE (index_in IN INTEGER);	Delete the row specified by that index.
PROCEDURE DELETE (start_index_in IN INTEGER, end_index_in IN INTEGER);	Deletes all the rows defined between the start and end indexes. If end_index_in is less than start_index_in, then no rows are deleted.

If any of the arguments to DELETE is NULL, then the operation does not remove any rows at all.

You call DELETE as shown in the following examples:

·         Delete all the rows from the names table:

names_tab.DELETE;

·         Delete the 77th row from the globals table:

ps_globals.DELETE (77);

·         Delete all rows in the temperature readings table between the 0th row and the -15,000th row, inclusive:

temp_reading_tab.DELETE (-15000, 0);

Prior to PL/SQL Release 2.3, the only way to delete rows from a PL/SQL table was to assign an empty table to the existing PL/SQL table. The DELETE procedure gives you much finer control over the memory required by your PL/SQL tables.

10.8.2.3 The EXISTS function

The EXISTS function returns TRUE if an element is defined at the specified index in a PL/SQL table. Otherwise, it returns FALSE. The specification for the function is:

FUNCTION EXISTS (index_in IN INTEGER) RETURN BOOLEAN;

You call EXISTS as follows:

IF seuss_characters_table.EXISTS(1) THEN ...

Prior to PL/SQL Release 2.3, you could emulate the EXISTS function with your own function looking something like this:

/* Filename on companion disk: rowexist.sf */

FUNCTION row_exists

   (table_in IN , row_in IN INTEGER) RETURN BOOLEAN

IS

   stg VARCHAR2(20);

BEGIN

   stg := table_in (row_in);

   RETURN TRUE;

EXCEPTION

   WHEN NO_DATA_FOUND

   THEN

      RETURN FALSE;

END;

Unfortunately, you would need a different version of the function for each PL/SQL table TYPE, which makes this a very undesirable approach. The EXISTS function is a big improvement.

10.8.2.4 The FIRST function

The FIRST function returns the lowest value index for which an element is defined in the specified PL/SQL table. The specification for the function is:

FUNCTION FIRST RETURN INTEGER;

You call FIRST as follows:

first_entry_row := employee_table.FIRST;

If the PL/SQL table does not contain any elements at all, FIRST returns NULL.

10.8.2.5 The LAST function

The LAST function returns the highest value index for which an element is defined in the specified PL/SQL table. The specification for the function is:

FUNCTION LAST RETURN INTEGER;

You call LAST as follows:

last_entry_row := employee_table.LAST;

If the PL/SQL table does not contain any elements at all, LAST returns NULL.

If you plan to use the PL/SQL table to fill rows sequentially from, say, the first row, you will want to make sure to use the NVL function (see Chapter 13, Numeric, LOB, and Miscellaneous Functions) to convert the NULL to a zero, as shown in this example.

The following block uses a cursor FOR loop to transfer data from the database to a PL/SQL table of records. When the first record is fetched, the company_table is empty, so the LAST operator will return NULL. NVL converts that value to zero. I then add one and I am on my way:

FOR company_rec IN company_cur

LOOP

   /* Get last row used and add one. */

   next_row := NVL (company_table.LAST, 0) + 1;

   /* Set the (next_row) values for ID. */

   company_table(next_row).company_id :=

         company_rec.company_id;

END LOOP;

10.8.2.6 The NEXT function

The NEXT function returns the next greater index after the specified index at which some element is defined in the specified PL/SQL table. The specification for the function is:

FUNCTION NEXT (index_in IN INTEGER) RETURN INTEGER;

You call NEXT as follows:

next_index := employee_table.NEXT (curr_index);

Remember that PL/SQL tables are sparse: if the tenth and 2005th rows are defined, there is no guarantee that the 11th row is also defined. NEXT gives you a way to find the next defined element, "skipping over" any undefined row numbers.

The table.NEXT procedure will return NULL if there aren't any elements defined after the specified row.

10.8.2.7 The PRIOR function

The PRIOR function returns the prior greater index after the specified index at which some element is defined in the specified PL/SQL table. The specification for the function is:

FUNCTION PRIOR (index_in IN INTEGER) RETURN INTEGER;

You call PRIOR as follows:

prev_index := employee_table.PRIOR (curr_index);

Remember that, as we described in the preceding section, PL/SQL tables are sparse. PRIOR gives you a way to find the previously defined element, "skipping over" any undefined row numbers.

The table.PRIOR procedure will return NULL if there aren't any elements defined before the specified row.

10.9 Working with PL/SQL Tables

The remainder of this chapter provides you with lots of examples of ways to use PL/SQL tables in your applications.

10.9.1 Transferring Database Information to PL/SQL Tables

You cannot use a SQL SELECT statement to transfer data directly from a database table to a PL/SQL table. You need to take a programmatic approach. A cursor FOR loop usually makes the most sense for this process, which requires the following steps:

1.    Define a PL/SQL table TYPE for each datatype found in the columns of the database table.

2.    Declare PL/SQL tables which will each receive the contents of a single column.

3.    Declare the cursor against the database table.

4.    Execute the FOR loop. The body of the loop will contain a distinct assignment of one column into one PL/SQL table.

In PL/SQL Release 2.3, this process would be much simpler. You could define a PL/SQL table with the same structure as the database table by creating a table-based record. Prior to that release, unfortunately, you need a separate PL/SQL table for each column. You do not, on the other hand, need a separate table TYPE for each column. If you have two date columns, for example, you can declare two separate PL/SQL tables both based on the same TYPE.

In the following example I load the company ID, incorporation date, and filing date from the database table to three different PL/SQL tables. Notice that there are only two types of PL/SQL tables declared:

/* Filename on companion disk: db2tab1.sql (see db2tab2.sql for the PL/SQL Release 2.3 version of same transfer) */

DECLARE

   /* The cursor against the database table. */

   CURSOR company_cur

   IS

       SELECT company_id, incorp_date, filing_date FROM company;

   /* The PL/SQL table TYPE and declaration for the primary key. */

   TYPE company_keys_tabtype IS

      TABLE OF company.company_id%TYPE NOT NULL

      INDEX BY BINARY_INTEGER;

   company_keys_table primary_keys_tabtype;

   /* Sincle PL/SQL table TYPE for two different PL/SQL tables. */

   TYPE date_tabtype IS

      TABLE OF DATE

      INDEX BY BINARY_INTEGER;

   incorp_date_table date_tabtype;

   filing_date_table date_tabtype;

   /* Variable to keep track of number of rows loaded. */

   num_company_rows BINARY_INTEGER := 0;

BEGIN

   /* The cursor FOR loop */

   FOR company_rec IN company_cur

   LOOP

      /* Increment to the next row in the two, coordinated tables. */

      num_company_rows := num_company_rows + 1;

      /* Set the row values for ID and dates. */

      company_keys_table (num_company_rows) := company_rec.company_id;

      incorp_date_table (num_company_rows) := company_rec.incorp_date;

      filing_date_table (num_company_rows) := company_rec.filing_date;

   END LOOP;

END;

10.9.3 Displaying a PL/SQL Table

When you work with PL/SQL tables, you often want to verify the contents of the table. The usual verification method is to display the contents of each row using DBMS_OUTPUT. This sounds like a simple enough task. In the most basic scenario where you have a sequentially filled table, the code is indeed straightforward.

The following procedures shows the small amount of code required to display a table which has rows 1 through n defined, where n is passed as a parameter to the procedure. The procedure displays a VARCHAR2 table; to display DATE or NUMBER tables, you simply need to use TO_CHAR to convert the value in the call to PUT_LINE:

/* Filename on companion disk: disptab1.sp */

PROCEDURE display_table

   (table_in IN , -- Placeholder for real table type.

    number_of_rows_in IN INTEGER)

IS

BEGIN

   /* For each row in the table ... */

   FOR table_row IN 1 .. number_of_rows_in

   LOOP

      /* Display the message, including the row number */

      DBMS_OUTPUT.PUT_LINE

         ('Value in row ' || TO_CHAR (table_row) || ': ' ||

          table_in (table_row));

   END LOOP;

END;

To put this display_table module to use, you will need to create a different version of this procedure for each different type of table, because the table is passed as a parameter. That table's type must be declared in the parameter list in place of the text.

Of course, not all PL/SQL tables can be filled in ways which are displayed as easily as the one shown above. To start with, the display_table procedure makes many assumptions about its table profile (although very few tables actually fit this profile). These include the following:

·         The first defined row of the table is row one. The FOR loop always starts at one.

·         All rows between one and number_of_rows_in are defined. There is no exception handler for NO_DATA_FOUND.

·         The number_of_rows_in is a positive number. The loop does not even consider the possibility of negative row numbers.

Very few PL/SQL tables actually fit this profile. Even a traditional, sequentially filled table might start its rows at some arbitrary value; a PL/SQL table might be sparsely filled; you might know the starting row (lowest value) and the ending row (maximum value), but not really know which of the rows between those end points are defined. A table with the Oracle error codes would have all of these characteristics.