Using PL/SQL Records in SQL Statementsby Steven Feuerstein, coauthor of Oracle PL/SQL Programming, 3rd Edition and Bryn Llewellyn
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
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
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
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 ("
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
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
statement. For example:
DECLARE bestseller books%ROWTYPE; BEGIN SELECT * INTO bestseller FROM books WHERE title = 'ORACLE PL/SQL PROGRAMMING'; END;
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
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)
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 INTOstatement. 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 ROWsyntax to update the entire row with the contents of a record with a minimum of typing.
Use a record to retrieve information from the
RETURNINGclause of an
Some restrictions do remain at Version 9.2.0 for records in SQL, including:
You can't use the
EXECUTE IMMEDIATEstatement (Native Dynamic SQL) in connection with record-based
DELETEstatements. (It's supported for
SELECT, as stated earlier.)
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
Also In This Series
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
employees%rowtype and a procedure to show the rows of such a table. These
are implemented in the
SELECT with RECORD Bind
As we noted earlier, while it was possible before 9.2.0 to
SELECT INTO a record, you
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.
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
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
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
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.
Pages: 1, 2