PL/SQL Record

Summary: in this tutorial, you will learn about the PL/SQL record that is a composite data structure, which allows you to manage your data in program more efficiently.

What is a PL/SQL Record

A PL/SQL record is a composite data structure that is a group of related data stored in fields. Each field in the PL/SQL record has its own name and data type.

Declaring a PL/SQL Record

PL/SQL provides three ways to declare a record: table-based record, cursor-based record and programmer-defined records.

Declaring Table-based Record

To declare a table-based record you use a table name with %ROWTYPE attribute. The fields of the PL/SQL record has the same name and data type as the column of the table.

The following illustrates table-based record declaration:

DECLARE
   table_based_record table_name%ROWTYPE;
Code language: SQL (Structured Query Language) (sql)

After having the table-based record, you can use it in various ways, for example in SQL SELECT statement as follows:

SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
  r_emp employees%ROWTYPE;
  n_emp_id  employees.employee_id%TYPE := 200;
BEGIN
  SELECT *
  INTO r_emp
  FROM employees
  WHERE employee_id = n_emp_id;
  -- print out the employee's first name
  DBMS_OUTPUT.PUT_LINE(r_emp.first_name);
END;
/
Code language: SQL (Structured Query Language) (sql)

In the above example:

• First, we defined a record based on employees table in HR sample database.
• Second, we used the SELECT statement to retrieve the employee information of the employee id 200 and populate the data into the r_emp record .
• Third, we print out the first name of the selected employee from the r_emp employee record.

Declaring Programmer-defined Record

To declare programmer-defined record, first you have to define a record type by using TYPE statement with the fields of record explicitly. Then, you can declare a record based on record type that you’ve defined.

The following illustrates the syntax of the defining programmer-defined record with TYPE statement:

TYPE type_name IS RECORD
   (field1 data_type1 [NOT NULL] := [DEFAULT VALUE],
    field2 data_type2 [NOT NULL] := [DEFAULT VALUE],
    ...
    fieldn data_type3 [NOT NULL] := [DEFAULT VALUE]
    );
Code language: SQL (Structured Query Language) (sql)

The data type of field can be any of the following:

• Scalar type ( VARCHAR2, NUMBER…).
• Anchor declaration %TYPE.
• %ROW type, in this case we have a nested record.
• SUBTYPE
• PL/SQL collection types.
• Cursor variable REF CURSOR.

Once you define the record type, you can declare a record based on the record type as follows:

record_name type_name;
Code language: SQL (Structured Query Language) (sql)

The following example demonstrates how to declare programmer-defined record:

SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
  TYPE t_name IS RECORD(
     first_name employees.first_name%TYPE,
     last_name  employees.last_name%TYPE
  );
  r_name   t_name; -- name record
  n_emp_id employees.employee_id%TYPE := 200;
BEGIN
  SELECT first_name,
         last_name
  INTO r_name
  FROM employees
  WHERE employee_id = n_emp_id;
  -- print out the employee's name
  DBMS_OUTPUT.PUT_LINE(r_name.first_name || ',' || r_name.last_name );
END;
/
Code language: SQL (Structured Query Language) (sql)

Declaring Cursor-based Record

You can define a record based on a cursor. First, you must define a cursor. And then you use %ROWTYPE with the cursor variable to declare a record. The fields of the record correspond to the columns in the cursor SELECT statement.

The following is an example of declaring a record based on a cursor.

SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
  CURSOR cur_emp IS
    SELECT *
    FROM employees
    WHERE employee_id = 200;

  emp_rec cur_emp%ROWTYPE;
BEGIN
  NULL;
END;
/
Code language: SQL (Structured Query Language) (sql)

Working with PL/SQL Record

After having a PL/SQL record, you can work with a record as a whole or you can work with individual field of the record.

Working with PL/SQL record at record level

At record level, you can do the following:

• You can assign a PL/SQL record to another PL/SQL record. The pair of PL/SQL records must have the same number of fields and the data type of each field has to be convertible.
• You can assign a PL/SQL record NULL value by assigning an uninitialized record.
• A PL/SQL record can be used as an argument of parameter in a function
• You can return a PL/SQL record from a function
• To check if the record is NULL, you have to check each individual field of the record.
• To compare two records, you have to compare each individual field of each record.

Here is an example of working with PL/SQL record at record level:

SET serveroutput ON SIZE 1000000;
DECLARE
  TYPE t_name IS RECORD(
    first_name employees.first_name%TYPE,
    last_name employees.last_name%TYPE
  );
  r_name      t_name;
  r_name2     t_name;
  r_name_null t_name;
  n_emp_id employees.employee_id%TYPE := 200;
BEGIN
  -- assign employee's infomation to record
  SELECT first_name,
         last_name
  INTO r_name
  FROM employees
  WHERE employee_id = n_emp_id;

  -- assign record to another record
  r_name2 := r_name;
  -- print out the employee's name
  DBMS_OUTPUT.PUT_LINE(r_name2.first_name || ',' || r_name2.last_name);

  -- assign record to NULL
  r_name2 := r_name_null; 

  -- check NULL for each individual field
  IF r_name2.first_name IS NULL AND
     r_name2.last_name IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('Record r_name2 is NULL');
  END IF;

END;
/
Code language: SQL (Structured Query Language) (sql)

Working with PL/SQL record at field level

As you see in the above example, we can reference to a field of a record by using dot notation (.) as follows:

record_name.field
Code language: SQL (Structured Query Language) (sql)

If you reference to a record variable in different package or schema you need to explicitly specify those information as shown below:

[schema_name.][package_name.]record_name.field
Code language: SQL (Structured Query Language) (sql)

You can use the assignment operator ( :=) to change the value of field of a record that you reference to.

For the nested record you need to use extra dot notation ( .)

The following example demonstrates how to use PL/SQL record a field level:

DECLARE
  TYPE t_address IS RECORD(
    house_number VARCHAR2(6),
    street       VARCHAR2(50),
    phone        VARCHAR2(15),
    region       VARCHAR2(10),
    postal_code  VARCHAR2(10),
    country      VARCHAR2(25)
  );

  TYPE t_contact IS RECORD(
    home     t_address,
    business t_address
  );
  r_contact t_contact;
BEGIN
  r_contact.business.house_number := '500';
  r_contact.business.street       := 'Oracle Parkway';
  r_contact.business.region       := 'CA';
  r_contact.business.postal_code  := '94065';
  r_contact.business.country      := 'USA';
  r_contact.business.phone        := '+1.800.223.1711';
END;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you’ve learned how to use PL/SQL record to manipulate data more efficiently, and to make your code cleaner and easier to maintain.