Oracle Magazine - May/June 2012

Working with
Records

Part 7 in a series of articles on understanding
and using PL/SQL

The Oracle PL/SQL language was designed to be a portable, high-performance transaction processing language that is tightly integrated with the SQL language. It is rare, indeed, to find a PL/SQL program that does not either read from or make changes to tables in a database. Tables are made up of rows of data, each consisting of one or more columns, so it stands to reason that Oracle Database would make it as easy as possible to work with those rows of data inside a PL/SQL program. And it does precisely that through its implementation of the record.

A record is a composite datatype, which means that it can hold more than one piece of information, as compared to a scalar datatype, such as a number or string. It’s rare, in fact, that the data with which you are working is just a single value, so records and other composite datatypes are likely to figure prominently in your PL/SQL programs.

This article explores how you declare
records, populate them with rows from a
table, and even insert or change an entire
row in a table by using a record. It also takes
a look at user-defined record types, which
enable you to work with records that are not
necessarily related to a relational table.

DECLARE A RECORD WITH %ROW T YPE

PL/SQL makes it very easy to declare records that have the same structure as a table, a view, or the result set of a cursor by offering the %ROWT YPE attribute.

Suppose I have an employees table in an application that looks like this:

SQL> DESCRIBE omag_employees Name Null? Type ———————————————— —————————— ————————————————— EMPLOYEE_ID NOT NULL NUMBER( 38) LAST_NAME VARCHAR2(100) SALARY NUMBER

Each row in the table consists of three columns, and each column has its own datatype. The following query retrieves all the columns in all the rows in the table:

SELECT employee_id, last_name, salary FROM omag_employees

I want to write a block of code that retrieves a single row of data from omag_ employees for an employee ID and then work with the column values in that row. I could declare a variable for each column and then fetch into those variables, as follows:

CREATE PROCEDURE process_employee (
employee_id_in IN
omag_employees.employee_id%TYPE)
IS
l_employee_id
omag_employees.employee_id%TYPE;
l_last_name
omag_employees.last_name%TYPE;
l_salary
omag_employees.salary%TYPE;

BEGIN
SELECT employee_id,
last_name,
salary

Pseudorecords in Database Triggers

Row-level triggers defined on tables can reference pseudorecords named NEW and OLD (you can override these default names with the REFERENCING clause of the trigger). They are called pseudorecords because they are similar in structure to a record defined on a table with %RO W T YPE but are restricted in their usage.

Both of the pseudorecords contain a field for every column in the table on which the trigger is defined. When you execute an INSERT or UPDATE statement, the NEW pseudorecord’s fields contain the “post” values of the columns (the values after the INSERT or UPDATE has taken place).

When you execute a DELETE or UPDATE statement, the OLD pseudorecord’s fields contain the “pre” values of the columns—how the row looks before the statement executes.

I can, for example, use pseudorecords to validate business rules, determine whether a column value has changed, and more. In the following trigger, I enforce a salary freeze; no one is allowed to get a raise during these tough economic times:

BEFORE INSERT
ON omag_employees
FOR EACH ROW
DECLARE
BEGIN
IF : NEW.salary > : OLD.salary
THEN
RAISE_APPLICATION_ERROR (
-20000,

'Salary freeze in effect: '|| ' no increases allowed!');

END IF; END omag_employees_freeze_trg;

CREATE OR REPLACE TRIGGER omag_employees_freeze_trg

There are, however, record features that do not apply to pseudorecords. I cannot, for example, pass a pseudorecord as an argument to a subprogram, even if the parameter for that subprogram is defined as tablename%RO W T YPE, where tablename is the name of the table that causes the trigger to be fired.