User-Defined Record Types and Record Variables v13
Records can be declared based upon a table definition using the %ROWTYPE
attribute as shown in Using %ROWTYPE in Record Declarations. This section describes how a new record structure can be defined 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 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
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 is specified, then the default 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 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, a record can then 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.
The 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, the %TYPE
attribute can be used 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