home | O'Reilly's CD bookshelfs | FreeBSD | Linux | Cisco | Cisco Exam  


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 <type name> IS TABLE OF <datatype>
   INDEX BY BINARY_INTEGER;

With PL/SQL Release 2.3, the <datatype> 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:

<table name>(<index expression>).<field name>

where <table name> is the name of the table, <index expression> is an expression (constant, variable, or computed expression) which evaluates to a number and <field name> 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:

<function name>(<argument list>)(<index expression>).<field name>

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:

    <table name>.<operation>
  • An operation which takes a row index for an argument:

    <table name>.<operation>(<index number> [, <index_number>])

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 <table type>, 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.


Previous: 10.7 Clearing the PL/SQL Table Oracle PL/SQL Programming, 2nd Edition Next: 10.9 Working with PL/SQL Tables
10.7 Clearing the PL/SQL Table Book Index 10.9 Working with PL/SQL Tables

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference