Cursor FOR Loop

Cursor for loop simplifies the PL/SQL program where PL/SQL itself take care most of the things which includes cursor steps and variable declaration.

Cursor for loop can be an implicit or explicit cursor. If the sole purpose to use the SQL statement once in the program then SQL statement can be specified inside the cursor “For Loop”. This form of the cursor is called Implicit Cursor for Loop.

If our requirement to use the SQL multiple time as explained in below example, then it is good to use Explicit Cursor for Loop, to avoid the re-writing of the SQL. Even through Explicit cursor is associated with a Cursor for Loop, its execution cycle is controlled by managed by Cursor for Loop internally.

Cursor "FOR LOOP" implicitly declares a loop index of %ROWTYPE record variable, which is act as a pointer to the result set processed by associated SQL statement. Value of the result set accessed by using this index variable followed by (.) dot and column name from the select list. This variable is local to the loop.

SET SERVEROUTPUT ON
DECLARE
  CURSOR C_EMP IS
    SELECT empno,RPAD(ename,10) ename, sal
    FROM emp
    WHERE deptno = 20;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Salary before update');
  FOR r_emp in C_EMP LOOP
    DBMS_OUTPUT.PUT_LINE(r_emp.empno||' - '||r_emp.ename||' - '||TO_CHAR(r_emp.sal,99999.99));
  END LOOP;
  FOR r_emp in C_EMP LOOP
    UPDATE emp SET sal = sal * 1.05 WHERE empno = r_emp.empno;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(CHR(10)||'Salary after update');
  FOR r_emp in C_EMP LOOP
    DBMS_OUTPUT.PUT_LINE(r_emp.empno||' - '||r_emp.ename||' - '||TO_CHAR(r_emp.sal,99999.99));
  END LOOP;
  ROLLBACK;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Exception - OTHERS');
END;
/

anonymous block completed
Salary before update
7369 - SMITH      -    800.00
7566 - JONES      -   2975.00
7788 - SCOTT      -   3000.00
7876 - ADAMS      -   1100.00
7902 - FORD       -   3000.00

Salary after update
7369 - SMITH      -    840.00
7566 - JONES      -   3123.75
7788 - SCOTT      -   3150.00
7876 - ADAMS      -   1155.00
7902 - FORD       -   3150.00