User-defined record types and record variables v16

You can declare records based on a table definition using the %ROWTYPE attribute, as shown in Using %ROWTYPE in record declarations. You can also define a new record structure that isn't tied to a particular table definition.

You use the TYPE IS RECORD statement to create the definition of a record type. A record type is a definition of a record made up of one or more identifiers and their corresponding data types. You can't use a record type by itself to manipulate data.

Syntax

The syntax for a TYPE IS RECORD statement is:

TYPE <rec_type> IS RECORD ( <fields> )

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

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

Where:

• rec_type is an identifier assigned to the record type.
• field_name is the identifier assigned to the field of the record type.
• data_type specifies the data type of field_name.
• 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 you don't specify a default, then the default is NULL.

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

The following is 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. Once declared, you can't then use a record to hold data.

Use dot notation to reference 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.

Example

This emp_sal_query procedure uses 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;

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

The following is the output from executing this stored procedure:

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

• On this page
• Syntax
• Example