start page | rating of books | rating of authors | reviews | copyrights

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 Reference Next: 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