You can use the %ROWTYPE attribute in variable declarations as a datatype specifier. Variables declared using %ROWTYPE are treated like those declared using a datatype name. For more information, see "Using %ROWTYPE" .
{cursor_name | cursor_variable_name | table_name}%ROWTYPE
emp_rec emp%ROWTYPE;
The column values returned by the SELECT statement are stored in fields. To reference a field, you use dot notation. For example, you might reference the deptno field as follows:
IF emp_rec.deptno = 20 THEN ...
You can assign the value of an expression to a specific field, as the following example shows:
emp_rec.sal := average * 1.15;
There are two ways to assign values to all fields in a record at once. First, PL/SQL allows aggregate assignment between entire records if their declarations refer to the same table or cursor.
Second, you can assign a list of column values to a record by using the SELECT or FETCH statement. The column names must appear in the order in which they were defined by the CREATE TABLE or CREATE VIEW statement. Select-items fetched from a cursor associated with %ROWTYPE must have simple names or, if they are expressions, must have aliases.
DECLARE emp_rec emp%ROWTYPE; CURSOR c1 IS SELECT deptno, dname, loc FROM dept; dept_rec c1%ROWTYPE;
In the next example, you select a row from the emp table into a %ROWTYPE record:
DECLARE emp_rec emp%ROWTYPE; ... BEGIN SELECT * INTO emp_rec FROM emp WHERE empno = my_empno; IF (emp_rec.deptno = 20) AND (emp_rec.sal > 2000) THEN ... END IF; END;