#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    8
    Rep Power
    0

    PL/SQL loop types and their behavior


    Code:
    set serveroutput on;
    
    DECLARE
            num number(1) :=1;
            num2 number(1) := 1;
    BEGIN
            WHILE num < 5 LOOP
                    WHILE num2 < 5 LOOP
                            DBMS_OUTPUT.PUT_LINE( num || '+' || num2 || '=' || (num + num2) );
                            num2 := num2 + 1;
                    END LOOP;
                    num := num + 1;
            END LOOP;
    END;
    /
    the output for this loop is:
    1+1=2
    2+2=4
    3+3=6
    4+4=8

    this is what I wanted. Both variables are incrementing together.

    However, when I try to use a FOR loop to do the same thing:

    Code:
    set serveroutput on;
    
    DECLARE
            num number(1) :=1;
            num2 number(1) := 1;
    BEGIN
            FOR i in 1..4 LOOP
                    FOR i in 1..4 LOOP
                            DBMS_OUTPUT.PUT_LINE( num || '+' || num2 || '=' || (num + num2) );
                            num2 := num2 + 1;
                    END LOOP;
                    num := num + 1;
            END LOOP;
    END;
    /
    I get the result:

    1+1=2
    1+2=3
    1+3=4
    1+4=5
    2+5=7
    2+6=8
    2+7=9
    2+8=10
    3+9=12
    DECLARE
    *
    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error: number precision too large
    ORA-06512: at line 8


    I understand the output means the inner loop is running its course before the outer loop is started and then continuing on to exceed its limit, but I don't understand why this is or how to make it so I get the same results as the WHILE loops. Any help is appreciated.
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    857
    Rep Power
    388

    Cool


    Originally Posted by AndroidZ
    ... Etc ..., but I don't understand why this is or how to make it so I get the same results as the WHILE loops.
    If you want both variables to increment together, you only need one FOR..LOOP.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    8
    Rep Power
    0
    I should mention, the problem I'm working on requires two for loops, one for each variable.

    EDIT: After adding 'outer' and 'inner' to the output to track the loops, the pattern is 1 outer, 4 inner, so the inner loop is running all the way through for each iteration of the outer loop, but I still haven't been able to remedy this.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    8
    Rep Power
    0
    I feel like such a fool... All I needed to do was change the inner loop to
    Code:
    FOR i in 1..1
    .
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    Originally Posted by AndroidZ
    I feel like such a fool... All I needed to do was change the inner loop to
    Code:
    FOR i in 1..1
    .
    Which negates the need for a for loop as previously pointed out.

IMN logo majestic logo threadwatch logo seochat tools logo