Records can be declared based on a table definition by using the %ROWTYPE attribute as shown in Use %ROWTYPE in record declarations. This topic describes how to define a new record structure that is not tied to any particular table definition.

The TYPE IS RECORD statement is used to create the definition of a record type. A record type is a definition of a record comprised of one or more identifiers and their corresponding data types. A record type cannot, by itself, be used to manipulate data.

The following example shows the syntax for a TYPE IS RECORD statement:

TYPE rec_type IS RECORD ( fields )

fields is a comma-separated list of one or more field definitions in the following form:

field_name data_type [NOT NULL][{:= | DEFAULT} default_value]

The following table describes parameters in the preceding statement.

Parameter Description
rec_type rec_type is an identifier assigned to the record type.
field_name field_name is the identifier assigned to the field of the record type.
data_type data_type specifies the data type of field_name.
DEFAULT default_value The DEFAULT clause assigns a default data value for the corresponding field. The data type of the default expression must match the data type of the column. If no default value is specified, the default value is NULL.

A record variable or simply put, a record, is an instance of a record type. A record is declared from a record type. The properties of the record such as field names and types are inherited from the record type.

The following example shows the syntax for a record declaration:

record rectype

record is an identifier assigned to the record variable. rectype is the identifier of a previously defined record type. After being declared, a record can be used to hold data.

Dot notation is used to make reference to the fields in the record.

record.field

record is a previously declared record variable and field is the identifier of a field belonging to the record type from which record is defined.

emp_sal_query is again modified - this time using a user-defined record type and record variable.

CREATE OR REPLACE PROCEDURE emp_sal_query (
    p_empno         IN emp.empno%TYPE
)
IS
    TYPE emp_typ IS RECORD (
        ename       emp.ename%TYPE,
        job         emp.job%TYPE,
        hiredate    emp.hiredate%TYPE,
        sal         emp.sal%TYPE,
        deptno      emp.deptno%TYPE
    );
    r_emp           emp_typ;
    v_avgsal        emp.sal%TYPE;
BEGIN
    SELECT ename, job, hiredate, sal, deptno
        INTO r_emp.ename, r_emp.job, r_emp.hiredate, r_emp.sal, r_emp.deptno
        FROM emp WHERE empno = p_empno;
    DBMS_OUTPUT.PUT_LINE('Employee # : ' || p_empno);
    DBMS_OUTPUT.PUT_LINE('Name       : ' || r_emp.ename);
    DBMS_OUTPUT.PUT_LINE('Job        : ' || r_emp.job);
    DBMS_OUTPUT.PUT_LINE('Hire Date  : ' || r_emp.hiredate);
    DBMS_OUTPUT.PUT_LINE('Salary     : ' || r_emp.sal);
    DBMS_OUTPUT.PUT_LINE('Dept #     : ' || r_emp.deptno);

    SELECT AVG(sal) INTO v_avgsal
        FROM emp WHERE deptno = r_emp.deptno;
    IF r_emp.sal > v_avgsal THEN
        DBMS_OUTPUT.PUT_LINE('Employee''s salary is more than the '
            || 'department average of ' || v_avgsal);
    ELSE
        DBMS_OUTPUT.PUT_LINE('Employee''s salary does not exceed the '
            || 'department average of ' || v_avgsal);
    END IF;
END;

Note that instead of specifying data type names, you can use the %TYPE attribute for the field data types in the record type definition.

The following output is generated after this stored procedure is executed:

EXEC emp_sal_query(7698);

Employee # : 7698
Name       : BLAKE
Job        : MANAGER
Hire Date  : 01-MAY-81 00:00:00
Salary     : 2850.00
Dept #     : 30
Employee's salary is more than the department average of 1566.67