Understanding Records in PL/SQL: Benefits and Limitations

In the past blog post, I discussed scalar data types (An Introduction to Commonly Used PL/SQL Data Types). In this blog post, I’ll move on to composite data types, with a focus on records.

Here’s a quick review:
Scalar data types store single values without internal components. They represent one value, such as a number, string, or date.
Composite data types can store multiple values, such as records and collections.

A record is a composite data structure, meaning it is made up of multiple related elements or components. According to the Oracle documentation, a record is a group of related data items stored in fields, where each field corresponds to a table column.

The %ROWTYPE attribute lets you declare a record that represents a row in a database table. This makes your code more flexible — even if the table structure changes (for example, if new columns are added), your record declaration remains valid.

Defining and Declaring Records
Here’s an example from the documentation:

DECLARE
-- Declare a record type with 3 fields.
  TYPE rec1_t IS RECORD (field1 VARCHAR2(16), field2 NUMBER, field3 DATE);
-- For any fields declared NOT NULL, we must supply a default value.
  TYPE rec2_t IS RECORD (id INTEGER NOT NULL := -1, name VARCHAR2(64) NOT NULL := '[anonymous]');

-- Declare record variables of the types declared above.
  rec1 rec1_t;
  rec2 rec2_t;

-- Declare a record variable that can hold a row from the EMPLOYEES table.
-- The fields of the record automatically match the names and types of the columns.
-- Don't need a TYPE declaration in this case.
  rec3 employees%ROWTYPE;

-- Or we can mix fields that are table columns with user-defined fields.
  TYPE rec4_t IS RECORD (first_name employees.first_name%TYPE, last_name employees.last_name%TYPE, rating NUMBER);
  rec4 rec4_t;

BEGIN
-- Read and write fields using dot notation
  rec1.field1 := 'Yesterday';
  rec1.field2 := 65;
  rec1.field3 := TRUNC(SYSDATE-1);

-- We didn't fill in the NAME field, so it takes the default value declared above.
  dbms_output.put_line(rec2.name);
END;
/

There are actually three ways to declare a record in PL/SQL.

Table-Based Record
You can create a record based on an existing table by using the %ROWTYPE attribute. Each field in the record automatically corresponds to a column in that table.

Example:

CREATE TABLE employees (
    emp_id          INTEGER,
    emp_name        VARCHAR2(200),
    emp_department  VARCHAR2(200)
);

DECLARE
    employees_rec    employees%ROWTYPE;
BEGIN
    ...
END;

Cursor-Based Record
First of all, a cursor is like a pointer to a result set (the rows returned by a query).

You can declare a record based on a cursor using the %ROWTYPE attribute, where each field corresponds to the columns in the cursor’s SELECT statement.

Example:

DECLARE
    CURSOR employees_cur IS
        SELECT * 
        FROM employees
        WHERE emp_department = 'marketing';

    emp_in_marketing_rec employees_cur%ROWTYPE;
BEGIN
    ...
END;

Programmer-Defined Record
If you don’t need a record that exactly matches a table or cursor, you can define your own custom record type using TYPE...RECORD.

Example:

DECLARE
    TYPE employees_rectype IS RECORD (
        emp_id        employees.emp_id%TYPE,
        emp_name      employees.emp_name%TYPE,
        emp_address   VARCHAR2(200)
    );

    employees_info_rec employees_rectype;
BEGIN
    ...
END;

Operations on Records
Working with records instead of individual fields allows you to write cleaner and more concise code.

Here are some operations that are currently supported by PL/SQL.

Record-Level Operations

• Copying: Contents can be copied from one record to another.
• NULL Assignment: A NULL value can be assigned to a record.
• Passing Records: A record can be defined and passed as an argument to procedures or functions.
• Returning Records: A record can be returned from a function.

However, some limitations exist. For example, the IS NULL condition does not work on entire records. You need to check each field individually to determine if it contains a NULL value.

Field-Level Operations
When accessing a field within a record (either to read or update its value), you must use dot notation.

Syntax
record_name.field_name

If you are referencing a record from a package, you can include the package name before the record name:

package_name.record_name.field_name

Records offer significant advantages by allowing us to work with related data more concisely and efficiently, rather than handling individual fields. However, it’s important to keep their limitations in mind and use them carefully, leveraging the available methods to achieve optimal results.