Oracle PL/SQL Language Pocket Reference

Oracle PL/SQL Language Pocket ReferenceSearch this book
Previous: 1.10 Exception Handling Chapter 1
Oracle PL/SQL Language Pocket Reference
Next: 1.12 Named Program Units
 

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:

new_emp_rec.hire_date := SYSDATE;
SELECT emp_id,dept,title,hire_date,college_recruit
  INTO new_emp_rec
  FROM emp
 WHERE surname = 'LI'
FETCH emp_cur INTO new_emp_rec;
FETCH emp_cur INTO new_emp_rec.emp_id, 
   new_emp_rec.name;

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


Previous: 1.10 Exception Handling Oracle PL/SQL Language Pocket ReferenceNext: 1.12 Named Program Units
1.10 Exception Handling  1.12 Named Program Units

The Oracle Library Navigation

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

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference