Using %ROWTYPE with cursors (PL/SQL)

The %ROWTYPE attribute is used to define a record with fields corresponding to all of the columns that are fetched from a cursor or cursor variable. Each field assumes the data type of its corresponding column.

The %ROWTYPE attribute is prefixed by a cursor name or a cursor variable name. The syntax is record cursor%ROWTYPE, where record is an identifier that is assigned to the record, and cursor is an explicitly declared cursor within the current scope.

The following example shows how to use a cursor with the %ROWTYPE attribute to retrieve department information about each employee in the EMP table.

CREATE OR REPLACE PROCEDURE emp_info
IS
    CURSOR empcur IS SELECT ename, deptno FROM emp;
    myvar           empcur%ROWTYPE;
BEGIN
    OPEN empcur;
    LOOP
        FETCH empcur INTO myvar;
        EXIT WHEN empcur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE( myvar.ename || ' works in department '
            || myvar.deptno );
    END LOOP;
    CLOSE empcur;
END;

A call to this procedure (CALL emp_info;) returns the following sample output:

SMITH works in department 20
ALLEN works in department 30
WARD works in department 30
JONES works in department 20
MARTIN works in department 30
BLAKE works in department 30
CLARK works in department 10
SCOTT works in department 20
KING works in department 10
TURNER works in department 30
ADAMS works in department 20
JAMES works in department 30
FORD works in department 20
MILLER works in department 10