Follow us: Connect on YouTube 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 :

3 comments:

  1. Hi There,


    Seems like I won the lottery here….This is a treasure box of blogs and your folks are like leprechauns! Phenomenal read on Oracle PL/SQL FOR LOOP

    I'd like to be able to log all constraint errors when users are running an application having Oracle as the back-end. That means inserting a record into an Errors Log table in Oracle.

    Is that even possible? In an exception clause, I could do that; however, when a constraint fails, I don't know where I'd be able to run such code to do the insert.

    Is this even possible?

    Thanks for any help you can give.

    Wayne

    Appreciate your effort for making such useful blogs and helping the community.

    Regards,
    Anitha

    ReplyDelete
  2. Hello There,

    Seems like I won the lottery here….This is a treasure box of blogs and your folks are like leprechauns! Phenomenal read on Oracle PL/SQL FOR LOOP!

    I am getting ORA00909 when i try to run my query with the following CASE WHEN statement:

    CASE WHEN AV4.ALLOC_GRP_NO IN (SELECT ALLOC_GRP_NO FROM QRMTIPS.QCTRL_ALLOC_GRP_PROFILE WHERE
    DISP_CD = 'WHDV' GROUP BY ALLOC_GRP_NO
    HAVING COUNT(DISTINCT(MTR_SPLIT_NO)) > 1) THEN SUM(AV4.GAS_VOL,0) ELSE
    NVL(av4.gas_vol, 0) END AS ALLOC_MM_GAS

    Excellent tutorials - very easy to understand with all the details. I hope you will continue to provide more such tutorials.

    Thank you,
    Preethi.

    ReplyDelete
  3. Halo,


    Fully agree on #topic. We’re seeing a lot of projects tackle big complex problems but few seem to have taken into consideration and in particular reasons to adopt.

    Currently I'm trying to make a test using create job as a way to have multiple process on database starting in a loop.
    Basically I'm getting different behaviors when testing on different database machines. I'll define the machine names are A and B.
    If I started running the anonymous block below in the machine A for a loop to create 3 multiple jobs, is working fine, i.e, the create job using
    enabled parameter, created the job and automatically start:
    DECLARE
    L_job_name VARCHAR2(100);
    L_comments VARCHAR2(240);
    L_pls_block VARCHAR2(32000);
    L_thread NUMBER := 1; -- count of jobs
    L_max_threads NUMBER := 3; -- max number of jobs to be started
    L_lot_id NUMBER := 1234; -- add any number just to represent a lot
    BEGIN

    while L_thread <= L_max_threads loop
    ---
    L_job_name := 'job_' || L_lot_id || L_thread;
    L_comments := 'Job test ' || L_lot_id || ' and thread ' || L_thread;
    L_pls_block := 'BEGIN logger.log(''job running of thread' || L_thread || '''); END;';
    ---
    dbms_scheduler.create_job(job_name => L_job_name,
    job_type => 'PLSQL_BLOCK',
    job_action => L_pls_block,
    start_date => SYSTIMESTAMP,
    comments => L_comments,
    enabled => true);

    --DBMS_SCHEDULER.RUN_JOB(L_job_name, TRUE);

    L_thread := L_thread + 1;
    end loop;
    END;
    /

    but if I start the same code in the machine B, nothing is happening and I need to uncomment the line about DBMS_SCHEDULER.RUN_JOB(L_job_name, TRUE);

    I did another test in the same machine B using enabled FALSE when creating the job and during the call from DBMS_SCHEDULER.RUN_JOB changed to false in the user_credentials but only one job runned, i.e, the result was logged from just one job, instead of the 3 jobs that had most be created.

    Do you have an idea about what I need to look? Is there something related about database setup, credentials that I need to take a look?

    It was cool to see your article pop up in my google search for the process yesterday. Great Guide.
    Keep up the good work!



    Thank you,

    morgan

    ReplyDelete