PL/SQL %ROWTYPE Attribute

Oracle PL/SQL %ROWTYPE is a very useful attribute that can be used to fetch rows from a table and store the information in a record. This is especially helpful when you need to loop through a result set and process each row individually.

The PL/SQL %ROWTYPE attribute provides a record type that represents a row in a table. The record can store an entire row of data selected from the table or fetched from a cursor or cursor variable.

The Oracle PL/SQL %ROWTYPE can be used in conjunction with the SELECT statement to fetch rows from a table and store the information in a record.

Syntax:

record_variable cursor_name%ROWTYPE;

Example:

declare
 cursor c1 is
  select lastname, departmentid
  from employee
  where departmentid = 33;
 v_rec c1%ROWTYPE;
begin
open c1;
loop
 fetch c1 INTO v_rec;
 exit when c1%NOTFOUND;
 DBMS_OUTPUT.PUT_LINE('Lastname: '||v_rec.lastname);
end loop; 
close c1; 
end;

If you wanted to fetch all of the rows from the table and store the information in a record, you could use the following PL/SQL Anonymous block:

DECLARE
l_employee employees%ROWTYPE;
BEGIN
FOR l_employee IN (SELECT * FROM employees)
LOOP
-- Do something with the employee record.
END LOOP;
END;
/