Using PL/SQL Records in SQL Statements by Steven Feuerstein, coauthor of Oracle PL/SQL Programming, 3rd Edition and Bryn Llewellyn 04/22/2003

Editor's note: In Oracle 9i Release 2 Developments for PL/SQL Collections, Steven Feuerstein and Bryn Llewellyn demonstrated how to use multi-level collections. In this final installment of their series on new Oracle 9i features, the pair shows how, with Oracle 9i Release 2, you can now use the PL/SQL RECORD datatype inside SQL statements to employ records in UPDATE, INSERT, DELETE, and SELECT statements. This capability, while available for many years, was limited in its usefulness because it wasn't possible to use records inside SQL statements.

The PL/SQL RECORD Datatype

A PL/SQL RECORD is a composite datatype. In contrast to a scalar datatype like NUMBER, a record is composed of multiple pieces of information, called fields. Records can be declared using relational tables or explicit cursors as "templates" with the %ROWTYPE declaration attribute. You can also declare records based on TYPEs that you define yourself. Records are very handy constructs for PL/SQL developers.

The easiest way to define a record is by using the %ROWTYPE syntax in your declaration. For example, the following statement:

DECLARE
   bestseller books%ROWTYPE;

creates a record that has a structure corresponding to the books table; for every column in the table, there's a field in the record with the same name and datatype as the column. The %ROWTYPE keyword is especially valuable because the declaration is guaranteed to match the corresponding schema-level template and is immune to schema-level changes in definition of the shape of the table. If we change the structure of the books table, all we have to do is recompile the preceding code and bestseller will take on the new structure of that table.

Asecond way to declare a record is to define your own RECORD TYPE. One advantage of a user-defined TYPE is that you can take advantage of native PL/SQL datatypes as well as derived values in the field list, as shown here:

DECLARE
   TYPE extra_book_info_t 
      IS RECORD (
      title books.title%TYPE,
      is_bestseller BOOLEAN,
      reviewed_by names_list
   );
   first_book extra_book_info_t;

Notice that the preceding user-defined record datatype includes a field ("title") that's based on the column definition of a database table, a field ("is_bestseller") based on a scalar data type (PL/SQL Boolean flag), and a collection (list of names of people who reviewed Oracle PL/SQL Programming, 3rd Edition.

Next, we can declare a record based on this type (you don't use %ROWTYPE in this case, because you're already referencing a type to perform the declaration). Once you've declared a record, you can then manipulate the data in these fields (or the record as a whole) as you can see here:

DECLARE
   bestseller books%ROWTYPE;
   required_reading books%ROWTYPE;
BEGIN
   -- Modify a field value
   bestseller.title := 
     'ORACLE PL/SQL PROGRAMMING';

   -- Copy one record to another
   required_reading :=
      bestseller;
END;

Note that in the preceding code we've used the structure of the books table to define our PL/SQL records, but the assignment to the title field didn't in any way affect data inside that table. You should also be aware that while you can assign one record to another, you couldn't perform comparisons or computations on records. Neither of these statements will compile:

BEGIN
   IF bestseller = 
      required_reading
   THEN ...

BEGIN
   left_to_read :=
      bestseller -
      required_reading;

You can also pass records as arguments to procedures and functions. This technique allows you to shrink down the size of a parameter list (pass a single record instead of a lengthy and cumbersome list of individual values). And if you're using %ROWTYPE to declare the argument, the "shape" of the record (numbers and types of fields) will adjust automatically with changes to the underlying cursor or table. Here's an example of a function with a record in the parameter list:

CREATE OR REPLACE PROCEDURE
   calculate_royalties (
      book_in IN books%ROWTYPE,
      quarter_end_in IN DATE
   )
IS ...

Prior to Oracle 9i Release 2, it was only possible to use a record in conjunction with a SQL statement in one way: on the receiving end of a SELECT INTO or FETCH INTO statement. For example:

DECLARE
   bestseller books%ROWTYPE;
BEGIN
   SELECT * 
     INTO bestseller
     FROM books
    WHERE title = 
     'ORACLE PL/SQL PROGRAMMING';
END;

Related Reading Oracle PL/SQL Programming By Steven Feuerstein Table of Contents Index Sample Chapter Read Online--Safari Search this book on Safari:   Only This Book All of Safari Code Fragments only

This is very convenient syntax, but it unfortunately just leaves us all hungry for the full range of record-smart SQL, most importantly the ability to perform INSERT and UPDATE operations with a record (as opposed to having to "break out" all the individual fields of that record). In summary, before Oracle 9i Release 2, records offered significant advantages for developers, but also left us frustrated because of the limitations on their usage. Oracle 9i Release 2 goes a long way in relieving (but not completely curing us of) our frustrations.

Oracle 9i Release 2 Record Improvements

In response to developer requests, Oracle has now made it possible for us to do any of the following with static SQL (such as, SQL statements that are fully specified at the time your code is compiled):

• Use collections of records as the target in a BULK COLLECT INTO statement. You no longer need to fetch into a series of individual, scalar-type collections.

• Insert a row into a table using a record. You no longer need to list the individual fields in the record separately, matching them up with the columns in the table.

• Update a row in a table using a record. You can now take advantage of the special SET ROW syntax to update the entire row with the contents of a record with a minimum of typing.

• Use a record to retrieve information from the RETURNING clause of an UPDATE, DELETE, or INSERT.

Some restrictions do remain at Version 9.2.0 for records in SQL, including:

• You can't use the EXECUTE IMMEDIATE statement (Native Dynamic SQL) in connection with record-based INSERT, UPDATE, or DELETE statements. (It's supported for SELECT, as stated earlier.)

• With DELETE and UPDATE...RETURNING, the column-list must be written explicitly in the SQL statement.

• In the bulk syntax case, you can't reference fields of the in-bind table of records elsewhere in the SQL statement (especially in the where clause).

But why dwell on the negative? Let's explore this great new functionality with a series of examples, all of which will rely on the employees table, defined in the hr schema that's installed in the seed database. The script to create this schema is demo/schema/human_resources/hr_cre.sql under the Oracle Home directory.

The samples also rely on common features such as an index-by-*_integer table, records of employees%rowtype and a procedure to show the rows of such a table. These are implemented in the Emp_Utl package.

SELECT with RECORD Bind

As we noted earlier, while it was possible before 9.2.0 to SELECT INTO a record, you couldn't BULK SELECT INTO a collection of records. The resulting code was often very tedious to write and not as efficient as would be desired. Suppose, for example, that we'd like to retrieve all employees hired before June 25, 1997, and then give them all big, fat raises. A very straightforward way to write the logic for this is shown in Example 1.

DECLARE
   v_emprec    employees%ROWTYPE;
   v_emprecs   emp_util.emprec_tab_t;

   CURSOR cur
   IS
      SELECT *
        FROM employees
       WHERE hire_date < TO_DATE(
          '25-JUN-1997', 'DD-MON-YYYY');

   i BINARY_INTEGER := 0;
BEGIN
   OPEN cur;

   LOOP
      FETCH cur INTO v_emprec;
      EXIT WHEN cur%NOTFOUND OR cur%ROWCOUNT > 10;
      i := i + 1;
      v_emprecs (i) := v_emprec;
   END LOOP;

   emp_util.give_raise (v_emprecs);
END;

To use BULK COLLECT with records prior to Oracle 9i Release 2, however, we'd need to select each element in the select list into its own collection; this technique is shown in Example 2. The complete code for this block may be seen in bulkcollect8i.sql and is more than 80 lines long! It's approaching what is feasible to maintain, and feels especially uncomfortable because of the artificial requirement to compromise the natural modeling approach by slicing the desired table of records vertically into N tables of scalars.

DECLARE
   TYPE employee_ids_t IS 
      TABLE OF employees.employee_id%TYPE
      INDEX BY BINARY_INTEGER;
   ...
   v_employee_ids   employee_ids_t;
   ...
   v_emprecs emp_util.emprec_tab_t;

   CURSOR cur
   IS
      SELECT employee_id,
           ...
        FROM employees
       WHERE hire_date >= TO_DATE(
          '25-JUN-1997', 'DD-MON-YYYY');

BEGIN
   OPEN cur;
   FETCH cur BULK COLLECT 
    INTO v_employee_ids,
       ...
    LIMIT 10;
   CLOSE cur;

   FOR j IN 1 .. v_employee_ids.LAST
   LOOP
      v_emprecs (j).employee_id := 
       v_employee_ids (j);
      ...
   END LOOP;

   emp_util.give_raise (v_emprecs);
END;

DECLARE
   v_emprecs   
     emp_util.emprec_tab_t;

   CURSOR cur
   IS
      SELECT *
        FROM employees
       WHERE hire_date < '25-JUN-97';
BEGIN
   OPEN cur;
   FETCH cur BULK COLLECT 
    INTO v_emprecs LIMIT 10;
   CLOSE cur;
   emp_util.give_raise (v_emprecs);
END;

CREATE OR REPLACE PROCEDURE
   give_raise (schema_in IN VARCHAR2)
IS 
   v_emprecs   
     emp_util.emprec_tab_t;

   cur SYS_REFCURSOR; 
BEGIN
   OPEN cur FOR 
     'SELECT * FROM ' ||
     schema_in || '.employees' ||
     'WHERE hire_date < :date_limit'
     USING '25-JUN-97';

   FETCH cur BULK COLLECT 
    INTO v_emprecs LIMIT 10;

   CLOSE cur;
   emp_util.give_raise (
      schema_in, v_emprecs);
END;