This topic describes how to define a new record structure that is not tied to a specific table definition.

You can declare records based on a table definition by using the %ROWTYPE attribute. For more information, see Use %ROWTYPE in record declarations.

Record types

A record type is a definition of a record that consists of one or more identifiers and the related data types. A record type itself cannot be used to manipulate data. You can execute the TYPE IS RECORD statement to create a definition of a record type.

The TYPE IS RECORD statement uses the following syntax:

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 the parameters in the preceding statement.

Parameter Description
rec_type rec_type is an identifier that is assigned to the record type.
field_name field_name is an identifier that is assigned to the field of the record type.
data_type data_type specifies the data type of the field_name parameter.
DEFAULT default_value The DEFAULT clause assigns a default data value for the related 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.

Record variables

A record variable is an instance of a record type. A record variable can also be named as a record. 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.

A record declaration uses the following syntax:

record rectype

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

Dot notation (.) is used to reference the fields in the record, as shown in the following syntax:

record.field

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

Modify emp_sal_query by using the 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;

You do not need to specify data type names. Instead, you can use the %TYPE attribute for the field data types in the record type definition.

This stored procedure returns the following output:

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