Example 1. Give raises to employees using single row fetches.

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;

There's no problem understanding this logic, but depending on the quantity of data involved, this could be a very inefficient implementation. We'd really love to take advantage of the recent (Oracle 8i) addition of the BULK COLLECT syntax (allowing us to fetch multiple rows with a single pass to the database); we might see an order of magnitude improvement.

Example 2. BULK COLLECT into separate collections.

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;

Note: The clause limit 10 is equivalent to where rownum <= 10.

With Oracle 9i Release 2, our program becomes much shorter, intuitive, and maintainable. What you see here is all we need to write to take advantage of BULK COLLECT to populate a single associative array of records:

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;

Note: Once again, the clause limit 10 is equivalent to where rownum <= 10.

Even more wonderful, we can now combine BULK COLLECT fetches into records with native dynamic SQL. Here's an example, in which we give raises to employees for a specific schema:

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;

SYS_REFCURSOR is a pre-defined weak REF CURSOR type that was added to the PL/SQL language in Oracle 9i Release 1.

INSERT with RECORD Bind

PL/SQL developers are demanding, no doubt about that. Even though Oracle can add all sorts of cool, new functionality into PL/SQL, we'll still find something missing, something else we so dearly need. For years, one of our favorite "wish-we-had's" was the ability to insert a row into a table using a record. Prior to Oracle 9i Release 2, if we had put our data into a record, it would then be necessary to "explode" the record into its individual fields when performing the insert, as in:

DECLARE
   v_emprec employees%ROWTYPE   
      := emp_util.get_one_row;
BEGIN
   INSERT INTO employees_retired (
     employee_id, 
     last_name, 
     ...)
   VALUES (
     v_emprec.employee_id, 
     v_emprec.last_name,
     ...);
END;

This is very cumbersome coding; it certainly is something we would have liked to avoid. In Oracle 9i Release 2, we can now take advantage of simple, intuitive, and compact syntax to bind an entire record to a row in an insert. This is shown here:

DECLARE
  v_emprec employees%rowtype 
    := Emp_Util.Get_One_Row;
BEGIN
  INSERT INTO employees_retired
    VALUES v_emprec;
END;

Notice that we don't put the record inside parentheses. You are, unfortunately, not able to use this technique with Native Dynamic SQL. You can, on the other hand, insert using a record in the highly efficient FORALL statement. This technique is valuable when you're inserting a large number of rows.

Example 3. Bulk INSERTing with a record.

 1  CREATE OR REPLACE PROCEDURE retire_them_now
 2  IS
 3     bulk_errors   EXCEPTION;
 4     PRAGMA EXCEPTION_INIT (bulk_errors, -24381);
 5     TYPE employees_t IS TABLE OF employees%ROWTYPE
 6        INDEX BY PLS_INTEGER;
 7     retirees      employees_t;
 8  BEGIN
 9     FOR rec IN (SELECT *
10                   FROM employees
11                  WHERE hire_date < ADD_MONTHS (SYSDATE, -1 * 18 * 40))
12     LOOP
13        retirees (SQL%ROWCOUNT) := rec;
14     END LOOP;
15     FORALL indx IN retirees.FIRST .. retirees.LAST
16        SAVE EXCEPTIONS
17        INSERT INTO employees
18          VALUES retirees (indx);
19  EXCEPTION
20     WHEN bulk_errors
21     THEN
22        FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
23        LOOP
24           DBMS_OUTPUT.PUT_LINE ( 'Error from element #' ||
25              TO_CHAR(SQL%BULK_EXCEPTIONS(j).error_index) || ': ' ||
26              SQLERRM(SQL%BULK_EXCEPTIONS(j).error_code));
27        END LOOP;
28* END;

Table 1. Description of the retire_them_now procedure.

Prior to Oracle 9i Release 2, you could use the FORALL syntax, but it would have been necessary to create and populate a separate collection for each column, and then reference individual columns and collections in the INSERT statement.

UPDATE SET ROW with RECORD Bind

Oracle 9i Release 2 now gives you an easy and powerful way to update an entire row in a table from a record: the SET ROW clause. The ROW keyword is functionally equivalent to *. It's most useful when the source of the row is one table and the target is a different table with the same column specification-for example, in a scenario where rows in an application table are updated once or many times and may eventually be deleted, and where the latest state of each row (including when it's been deleted) must be reflected in an audit table. (Ideally we'd use MERGE with a RECORD bind, but this isn't supported yet.)

The new syntax for the Static SQL, single row case is obvious and compact:

DECLARE
   v_emprec employees%ROWTYPE   
     := emp_util.get_one_row;
BEGIN
   v_emprec.salary 
     := v_emprec.salary * 1.2;

   UPDATE employees_2
      SET ROW = v_emprec
    WHERE employee_id = 
          v_emprec.employee_id;
END;

Example 4. Pre-Oracle9i Release 2 update of entire row.

DECLARE
   v_emprec   employees%ROWTYPE   := emp_util.get_one_row;
BEGIN
   v_emprec.salary := v_emprec.salary * 1.2;

   UPDATE employees
      SET first_name = v_emprec.first_name,
          last_name = v_emprec.last_name,
          email = v_emprec.email,
          phone_number = v_emprec.phone_number,
          hire_date = v_emprec.hire_date,
          job_id = v_emprec.job_id,
          salary = v_emprec.salary,
          commission_pct = v_emprec.commission_pct,
          manager_id = v_emprec.manager_id,
          department_id = v_emprec.department_id
    WHERE employee_id = v_emprec.employee_id;
END;

Now, it would certainly be nice to be able to use the SET ROW syntax in a FORALL statement, as follows:

DECLARE
   v_emprecs emp_util.emprec_tab_t 
      := emp_util.get_many_rows;
BEGIN
   -- This will not work, due to: 
   -- PLS-00436: 
   --   implementation restriction: 
   --   cannot reference fields of 
   --   BULK In-BIND table of records.
   FORALL j IN 
      v_emprecs.FIRST .. v_emprecs.LAST
      UPDATE employees
         SET ROW = v_emprecs (j)
       WHERE employee_id = 
          v_emprecs (j).employee_id;
END;

Sadly, this code fails to compile with the error: "PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records." Instead, we must write:

DECLARE
   v_emprecs emp_util.emprec_tab_t 
      := emp_util.get_many_rows;

   TYPE employee_id_tab_t IS 
      TABLE OF employees.employee_id%TYPE
      INDEX BY PLS_INTEGER;

   v_employee_ids   employee_id_tab_t;
BEGIN
   -- Transfer just the IDs into their own
   -- collection for use in the WHERE clause
   -- of the UPDATE statement.
   FOR j IN v_emprecs.FIRST .. v_emprecs.LAST
   LOOP
      v_employee_ids (j) :=
       v_emprecs (j).employee_id;
   END LOOP;

   FORALL j IN 
      v_emprecs.FIRST .. v_emprecs.LAST
      UPDATE employees
         SET ROW = v_emprecs (j)
       WHERE employee_id = v_employee_ids (j);
END;

DELETE and UPDATE with RETURNING with RECORD Bind

Example 5. RETURNING into a record from a DELETE statement.

DECLARE
   v_emprec   employees%ROWTYPE;
BEGIN
   DELETE FROM employees
         WHERE employee_id = 100
     RETURNING employee_id, first_name, last_name, email, phone_number,
               hire_date, job_id, salary, commission_pct, manager_id,
               department_id
          INTO v_emprec;

   emp_util.show_one (v_emprec);
END;

You can also retrieve less than a full row of information by relying on programmer-defined record types, as this next example shows:

DECLARE
   TYPE key_info_rt IS RECORD (
      id   NUMBER,
      nm   VARCHAR2 (100)
   );

   v_emprec key_info_rt;
BEGIN
   DELETE FROM employees
         WHERE employee_id = 100
     RETURNING employee_id, first_name
          INTO v_emprec;
   ...
END;

You must still list the individual columns or derived values in the RETURNING clause, making the integration a bit less than ideal (for example, Oracle could and perhaps will some day allow us to write RETURNING ROW INTO v_emprec). Nevertheless, this is a significant improvement over Version 9.0.1, where a RECORD could not be used as the target for INTO, requiring us to provide a long list of individual variables to hold the values returned from the DML statement.

Example 6. RETURNING multiple rows of information from an UPDATE statement.

DECLARE
   v_emprecs   emp_util.emprec_tab_t;
BEGIN
   UPDATE    employees
         SET salary = salary * 1.1
       WHERE hire_date < = '25-JUN-97'
   RETURNING employee_id, first_name, last_name, email, phone_number,
             hire_date, job_id, salary, commission_pct, manager_id,
             department_id
        BULK COLLECT INTO v_emprecs;

   emp_util.show_all (v_emprecs);
END;

Example 7. RETURNING multiple rows of data from an UPDATE statement in Version 9.0.1.

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;
BEGIN
   UPDATE    employees
         SET salary = salary * 1.1
       WHERE hire_date < = '25-JUN-97'
   RETURNING employee_id, first_name, last_name, email,
             phone_number, hire_date, job_id, salary,
             commission_pct, manager_id, department_id
       BULK COLLECT INTO v_employee_ids, v_first_names, v_last_names, v_emails,
                         v_phone_numbers, v_hire_dates, v_job_ids, v_salarys,
                         v_commission_pcts, v_manager_ids, v_department_ids;

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

   emp_util.show_all (v_emprecs);
END;

Performance Impact of Record Binding

Table 2. Scripts to examine performance impact of record binding.

Depending on extenuating circumstances, however, you can see more of a differential.

For example, any one of the following situations could impact negatively on record-based DML processing time:

• Use of a sequence to generate a primary key. You can't include <sequence>.NEXTVAL in your record specification, so you must execute an "external" query (usually against the Oracle "dual" table) prior to the INSERT itself, to obtain the primary key value and assign it to the appropriate field in the record. See the insrec1.tst script for a demonstration of the impact of this step. One must conclude that a record-based INSERT is simply not a good fit for this scenario.

• Update triggers on individual columns of the table. An update with a record updates all columns of the table. To avoid this problem, make sure that you include a WHEN clause on your triggers to avoid extraneous execution (when NEW and OLD values are the same). See the genwhen.sql script for a utility that will generate the appropriate WHEN clause for each column of a table.

• If, on the other hand, you take advantage of Oracle 9i Release 2's ability to perform bulk collect operations with records (see insrec4.tst), you'll find that record-based operations are consistently and noticeably faster than those relying on individual fields (requiring a separate collection for each field).

Record-based DML was added to the PL/SQL language primarily as a "usability" feature, rather than one related to performance. Part of the challenge of integrating new features into your "box of tricks" is that you need to know when not to use them. In general, if you're already working with and populating records (particularly if you're transferring data from one table to another using records), you'll find this feature to be a wonderful enhancement.

Records: The Way to Go

Records have always been a very powerful programming construct for PL/SQL developers. Use of records reduces code volume and also increases the resiliency of one's code, since a record defined using %ROWTYPE automatically (upon recompilation of the program) adapts to the current structure of the base cursor or table.

The inability to utilize records within SQL DML statements in a PL/SQL program has long been a frustration to developers. With Oracle 9i Release2, another barrier between SQL and PL/SQL has been removed, allowing for ever-smoother programming efforts, higher productivity, and more easily maintained applications.

This article was originally published in the July 2002 issue of Oracle Professional. The material in Feuerstein's articles--and those he cowrote with Bryn Llewellyn--are based on Oracle Corporation white papers originally prepared by Llewellyn for Oracle OpenWorld 2001 in San Francisco and OracleWorld Copenhagen in June 2002, and Feuerstein's book, Oracle PL/SQL Programming, 3rd Edition.