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.
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 a 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 with the TYPE statement in the PL/SQL declaration section or in a package specification. Variables of this type can then be declared as shown here:
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
Individual fields are referenced via dot notation:
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; ...
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.
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