AntDB-Oracle Compatibility Developer’s Manual P4–15

Using %ROWTYPE in a record declaration

Using the %TYPE attribute provides an easy way to create variables that are determined by the column data type. By using the %ROWTYPE attribute, you can define a record type that contains fields corresponding to each column in a given table. The data type of each field is the same as the data type of the corresponding column.

Note: Fields in a record do not inherit any other attributes from the column, such as the NOT NULL clause or the DEFAULT clause.

A record is a collection of fields that are named and stored sequentially. A field is similar to a variable in that it has an identifying name and data type, but it also has additional attributes that belong to the record and must be qualified by both the record name with a '.' record name as a qualifier.

A record type can be declared by using the %ROWTYPE attribute. % The ROWTYPE attribute is prefixed with the name of the table. Each column in the named table defines a field of the same name, data type in the record.

record table%ROWTYPE;

record is the identifier assigned to the record. table is the name of the table whose columns we will use to define the fields in the record, and views can also be used to define records. The following example shows the use of emp%ROWTYPE to modify the emp_sal_query procedure from the previous section to create a record named r_emp, rather than declaring a separate variable for each column in the emp table.

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_sal_query (p_empno IN NUMBER)
IS
    r_emp emp%ROWTYPE;
    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;
/
\set PLSQL_MODE off