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.
These functions and procedures are described in detail later in this chapter.
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:
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.
<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';
IF names_table (old_name_row + 1).last_name = 'SMITH' THEN
<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:
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.
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):
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:
The following statement, for example, returns TRUE if the 15th row of the company_tab PL/SQL table is defined:
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.
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.
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:
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.
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.
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.
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;
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.
FUNCTION PRIOR (index_in IN INTEGER) RETURN INTEGER;
You call PRIOR as follows:
prev_index := employee_table.PRIOR (curr_index);
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.