1.11 Records in PL/SQL

A PL/SQL record is a data structure composed of multiple pieces of information called fields. To use a record, you must first define it and declare a variable of this type.

There are three types of records: table-based, cursor-based, and programmer-defined.

1.11.1 Declaring Records

You define and declare records either in the declaration section of a PL/SQL block or globally, via a package specification.

You do not have to explicitly define table-based or cursor-based records, as they are implicitly defined with the same structure as a table or a cursor. Variables of these types are declared via the %ROWTYPE attribute. The record's fields correspond to the table's columns or the columns in the SELECT list. For example:

DECLARE
   -- Declare table-based record for company table.
   comp_rec  company%ROWTYPE

   CURSOR comp_summary_cur IS
      SELECT C.company_id,SUM(S.gross_sales) gross
        FROM company C ,sales S
       WHERE C.company_id = S.company_id;

   -- Declare a cursor-based record.
   comp_summary_rec  comp_summary_cur%ROWTYPE;

Programmer-defined records must be explicitly defined with the TYPE statement in the PL/SQL declaration section or in a package specification. Variables of this type can then be declared as shown here:

DECLARE
   TYPE name_rectype IS RECORD(
      prefix       VARCHAR2(15)
      ,first_name  VARCHAR2(30)
      ,middle_name VARCHAR2(30)
      ,sur_name    VARCHAR2(30)
      ,suffix      VARCHAR2(10) );

   TYPE employee_rectype IS RECORD (
      emp_id       NUMBER(10) NOT NULL
      ,mgr_id      NUMBER(10)
      ,dept_no     dept.deptno%TYPE
      ,title       VARCHAR2(20)
      ,name        empname_rectype
      ,hire_date   DATE := SYSDATE
      ,fresh_out   BOOLEAN );

   -- Declare a variable of this type. 
   new_emp_rec employee_rectype;
BEGIN

1.11.2 Referencing Fields of Records

Individual fields are referenced via dot notation:

record_name.field_name 

For example:

employee.first_name

Individual fields within a record can be read from or written to. They can appear on either the left or right side of the assignment operator:

BEGIN
   insurance_start_date := new_emp_rec.hire_date + 
      30;
   new_emp_rec.fresh_out := FALSE;
   ...

1.11.3 Record Assignment

An entire record can be assigned to another record of the same type, but one record cannot be compared to another record via Boolean operators. This is a valid assignment:

shipto_address_rec := customer_address_rec

This is not a valid comparison:

IF shipto_address_rec = customer_address_rec
THEN
   ...
END IF;

The individual fields of the records need to be compared instead.

Values can be assigned to records or to the fields within a record in four different ways:

• The assignment operator can be used to assign a value to a field:

  new_emp_rec.hire_date := SYSDATE;

• You can SELECT INTO a whole record or the individual fields:

  SELECT emp_id,dept,title,hire_date,college_recruit
    INTO new_emp_rec
    FROM emp
   WHERE surname = 'LI'

• You can FETCH INTO a whole record or the individual fields:

  FETCH emp_cur INTO new_emp_rec;
  FETCH emp_cur INTO new_emp_rec.emp_id, 
     new_emp_rec.name;

• You can assign all of the fields of one record variable to another record variable of the same type:

  IF rehire THEN
   new_emp_rec := former_emp_rec;
  ENDIF;

  This aggregate assignment technique works only for records declared with the same TYPE statement.

1.11.4 Nested Records

Nested records are records contained in fields that are records themselves. Nesting records is a powerful way to normalize data structures and hide complexity within PL/SQL programs. For example:

DECLARE
   -- Define a record.
   TYPE phone_rectype IS RECORD (
      area_code  VARCHAR2(3),
      exchange   VARCHAR2(3),
      phn_number VARCHAR2(4),
      extension  VARCHAR2(4));

   -- Define a record composed of records.
   TYPE contact_rectype IS RECORD (
      day_phone#  phone_rectype,
      eve_phone#  phone_rectype,
      cell_phone# phone_rectype);

-- Declare a variable for the nested record.
 auth_rep_info_rec contact_rectype;
BEGIN