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 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 in the PL/SQL block or a package specification with the TYPE statement. Variables of this type can then be declared:

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