Using PL/SQL Records in SQL Statements
Pages: 1, 2
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.
Take a look at the example in Example 3. Table 1 explains the interesting parts of the
retire_them_now procedure (written and run at a low-tech company that never went public nor saw its value crash, enabling them to now offer early, paid retirement to
everyone over 40 years of age!).
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
Declare an exception, enabling us to trap by name an error that occurs during the bulk insert.
Declare an associative array, each row of which contains a record having the same structure as the employees table.
Load up the array with the information for all employees who are over 40 years of age.
The turbo-charged insert mechanism,
Typical code you'd write to trap any error that was raised during the bulk insert and display or deal with each error individually.
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
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;
Prior to Oracle 9i Release 2, this same functionality would require listing the columns explicitly, as shown in Example 4.
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
You can also take advantage of rows when using the
RETURNING clause in both
RETURNING clause allows you to retrieve and return
information that's processed in the DML statement without using a separate, subsequent
query. Record-based functionality for
RETURNING means that you can return multiple
pieces of information into a record, rather than individual variables. An example of this feature for
DELETEs is shown in Example 5.
RETURNING into a record from a
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
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
Next, suppose that we execute a
UPDATE that modifies more than one
row. In this case, we can use the
RETURNING clause to obtain information from each of
the individual rows modified by using
BULK COLLECT to populate a collection of
records! This technique is shown in Example 6.
RETURNING multiple rows of information from an
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;
Again, this is a significant improvement over Version 9.0.1, in which you would have
to declare a separate collection for each value specified in the
RETURNING clause, and
then populate each separately. A fragment of this approach is shown in Example 7:
RETURNING multiple rows of data from an
UPDATE statement in
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
There's no doubt that using records in
DML statements results in greatly reduced code
volume and therefore increased productivity. Is there, however, a penalty to be paid in
runtime execution of this leaner code? Our tests (see Table 2) show for the most part that there's no measurable difference between field and record-based operations.
Table 2. Scripts to examine performance impact of record binding.
What is tested?
Insert with record for table with sequence-generated primary key.
Insert with record on table with non-sequence primary key.
Insert with record on table with many columns with non-sequence primary key.
Bulk insert with record on table with non-sequence primary key.
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>.NEXTVALin your record specification, so you must execute an "external" query (usually against the Oracle "dual" table) prior to the
INSERTitself, 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
INSERTis 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
WHENclause on your triggers to avoid extraneous execution (when
OLDvalues are the same). See the genwhen.sql script for a utility that will generate the appropriate
WHENclause 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.
Bryn Llewellyn is PL/SQL Product Manager, Database and Application Server Technologies Development Group, at Oracle Corporation Headquarters.
O'Reilly & Associates recently released (September 2002) Oracle PL/SQL Programming, 3rd Edition.
Sample Chapter 10, Dates and Timestamps, is available free online.
For more information, or to order the book, click here.
Return to the O'Reilly Network.