Oracle PL/SQL FOR LOOP

LOOP … END LOOP as well as WHILE LOOP … END LOOP, require explicit processing of the cursor with OPEN, FETCH and CLOSE statements

  

With CURSOR FOR LOOP, no opening, fetching, closing of Cursors is required

           Implicitly declares Record Type Variable

Syntax For Oracle PL/SQL FOR LOOP

FOR loop_counter IN [REVERSE] lowest_number..highest_numer

LOOP

   {...statements...}

END LOOP;

FOR LOOP Arguments 

loop_counter

    The loop counter variable.

REVERSE

    Optional. If specified, the loop counter will count in reverse.

lowest_number

    The starting value for loop_counter.

highest_number

    The ending value for loop_counter.

statements

    The statements of code to execute each pass through the loop.

 

Example1:

BEGIN

    FOR no IN 1 .. 3 LOOP

        DBMS_OUTPUT.PUT_LINE('Looping Count : ' || no);

      END LOOP;

END;

 

Output:

Looping Count: 1

Looping Count: 2

Looping Count: 3

 

Example2:

BEGIN

    FOR no IN REVERSE 3 .. 1 LOOP

        DBMS_OUTPUT.PUT_LINE('Looping Count: ' || no);

      END LOOP;

END;

Output

Looping Count: 3

Looping Count: 2

Looping Count: 1

 

Cursor FOR Loop Example3

DECLARE

   CURSOR emp_dept_cur

   IS

      SELECT empno, ename, sal, dept.deptno AS dno, dept.dname

        FROM emp, dept

       WHERE emp.deptno = dept.deptno;

BEGIN

   FOR v_rec IN emp_dept_cur

/* The loop variable v_rec is automatically declared and is allocated the same data type as the ROWTYPE of the cursor */

   LOOP

      DBMS_OUTPUT.put_line('Employee Number :' || v_rec.empno);

      DBMS_OUTPUT.put_line('Employee Name :' || v_rec.ename);

      DBMS_OUTPUT.put_line('Employee Salary    :' || v_rec.esal);

      DBMS_OUTPUT.put_line('Employee Deptno   :' || v_rec.dno);

      DBMS_OUTPUT.put_line('Emp Dept Name :' || v_rec.dname);

   END LOOP;

END;

Using INSERT inside the FOR Loop Example4:

CREATE TABLE mybonus(

  myeno NUMBER(4),

  bonusamt NUMBER(5)

);

 /

DECLARE

   bonus   REAL;

   CURSOR empcur

   IS

      SELECT empno, sal, comm

        FROM emp;

BEGIN

   FOR emprec IN empcur

   LOOP

      bonus := (emprec.sal * 0.05) + (emprec.comm * 0.25);

      INSERT INTO mybonus

           VALUES (emprec.empno, bonus);

   END LOOP;

   COMMIT;

END;