PL/SQL loop types and their behavior

Dev Shed Forums Sponsor:

November 8th, 2012, 05:06 PM

AndroidZ

Registered User

Join Date: Oct 2012

Posts: 8

Time spent in forums: 1 h 44 m 49 sec
Reputation 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.

November 9th, 2012, 02:23 PM

LKBrwn_DBA

Contributing User

Join Date: Sep 2006

Posts: 751

Time spent in forums: 1 Week 2 h 56 m 5 sec
Reputation Power: 348

Quote:

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.

__________________

November 9th, 2012, 11:17 PM

AndroidZ

Registered User

Join Date: Oct 2012

Posts: 8

Time spent in forums: 1 h 44 m 49 sec
Reputation 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.