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

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.