CURSOR FOR Loop

The CURSOR FOR loop is defined for explicit cursors or can be implemented directly on the SELECT statement. Generally, the CURSOR FOR loop is used to retrieve and manipulate each record fetched from a cursor.

The syntax for using the CURSOR FOR loop is as follows:

FOR record_index IN cursor_name
LOOP
{.statements.}
END LOOP;

The CURSOR FOR loop automatically does the following:

1. It implicitly declares a %ROWTYPE attribute and then uses it as a loop index.
2. It opens the cursor itself.
3. It fetches a row from the cursor for each loop iteration.
4. It closes the cursor automatically when all rows have been fetched or processed.

From the above statements, it can be concluded that the OPEN, FETCH, and CLOSE
statements are not necessary for using the CURSOR FOR loop.

Example:

DECLARE
CURSOR Emp_Cur IS SELECT * FROM Emp WHERE Sal > 2000;
Ctr NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Employees having salary more than 3000’);
DBMS_OUTPUT.PUT_LINE(‘-------------------------------------------------------’);
FOR employee_rec IN Emp_Cur LOOP
DBMS_OUTPUT.PUT_LINE(‘Employee ID: ’ ||
employee_rec.Empno);
DBMS_OUTPUT.PUT_LINE(‘Employee Name: ’ ||
employee_rec.Ename);
DBMS_OUTPUT.PUT_LINE(‘Employee Salary: ’ ||
employee_rec.Sal);
DBMS_OUTPUT.PUT_LINE(‘Employee Commission: ’ ||
employee_rec.Comm);
DBMS_OUTPUT.PUT_LINE(‘================’);
Ctr := Emp_Cur%ROWCOUNT;
END LOOP;
DBMS_OUTPUT.PUT_LINE(ctr || ‘ Employees have salary more than 3000’);
DBMS_OUTPUT.PUT_LINE(‘-------------------------------------------------------’);
END;
/

The CURSOR FOR loop can also accept parameters. It follows the same rules as the manual
cursor. For example, if the cursor is defined with a parameter, it must be opened with a
parameter; otherwise, PL/SQL will raise an exception.

Example:

DECLARE
CURSOR Emp_Cur(Desg IN VARCHAR) IS
SELECT * FROM Emp
WHERE Job = Desg;
BEGIN
DBMS_OUTPUT.PUT_LINE(‘------------------------------------------------’);
FOR Emp_rec IN Emp_Cur (‘&Designation’) LOOP
DBMS_OUTPUT.PUT_LINE(‘Name: ’ || Emp_rec.Ename || ‘ ’ ||
‘Salary: ’ || Emp_rec.Sal);
END LOOP;
END;
/

The cursor Emp_Cur will be executed and the message "Enter value for designation:" will
be prompted. Enter the value for the designation as MANAGER.

As a result, the cursor will retrieve the records of the employees that have the designation as MANAGER.