Oracle PL/SQL Language Pocket Reference

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.

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