Re: PL/SQL nested loop problem

B. Williams wrote:
> I have some code that uses for loops and I chaged the outer loop into a
> simple loop and inner loop into a while loop, but the out put is coming out
> different. The outer loop is supposed to run 3 times while the inner loops
> is supposed to run 6 times. Wouls someone look at my code and tell me what I
> have done wrong. Thanks in advance.
>
> Original code
> set serveroutput on
> declare
> v_test NUMBER := 0;
> begin
> <<outer_loop>>
> for i IN 1..3 loop
> dbms_output.put_line('Outer Loop');
> dbms_output.put_line('i = ' || i);
> dbms_output.put_line('v_test = ' || v_test);
> v_test := v_test + 1;
> <<inner_loop>>
> for j IN 1..2 loop
> dbms_output.put_line('Inner Loop');
> dbms_output.put_line('j = ' || j);
> dbms_output.put_line('i = ' || i);
> dbms_output.put_line('v_test = ' || v_test);
> end loop Inner_Loop;
> end loop outer_loop;
> end;
> /
>
> Modified code
>
> set serveroutput on
> declare
> i PLS_INTEGER := 0;
> j PLS_INTEGER := 1;
> v_test NUMBER := 0;
> begin
> loop
> i := i + 1;
> dbms_output.put_line('Outer Loop');
> dbms_output.put_line('i = ' || i);
> dbms_output.put_line('v_test = ' || v_test);
> v_test := v_test + 1;
> exit when i = 3;
> while j <= 2 loop
> dbms_output.put_line('Inner Loop');
> dbms_output.put_line('j = ' || j);
> dbms_output.put_line('i = ' || i);
> dbms_output.put_line('v_test = ' || v_test);
> j := j + 1;
> end loop;
> end loop;
> end;
> /

Are you being paid by the hour? If it was working why did you mess with it? The only advice it seems you need is to leave well enough alone when things are working.

Now to what is wrong ... look at where you put exit when i = 3. It will always exit before it runs the inner loop. And since i never returns to 0 it will never run properly again. It gets to 3 and then goes to 4.

And

You've made the same mistake with J ... it can go up but it is never reset to 1. And even if it was your logic is flawed. The first time through J start with a value of 1 which meets the criterion. It then becomes 2. Then what happens to J?

You need to walk your code with a pencil and a piece of paper.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org