Follow us: Subscribe via RSS Feed Connect on YouTube Connect on YouTube

Pages

Wednesday, 12 July 2017

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;

Share this article :

0 comments:

Post a Comment