#

| Chapter 1 Oracle PL/SQL Language Pocket Reference |

## 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 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

1.10 Exception Handling |  | 1.12 Named Program Units

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.