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 offield_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 isNULL
.
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