PL/SQL Loop not quite looping

From: davidmac <david.mcmullen_at_mail.va.gov>
Date: 8 Apr 2003 08:40:27 -0700
Message-ID: <6e439c43.0304080740.75df4ff8@posting.google.com>

I have a procedure that is supposed to loop through a range of numbers and analyze the series against the contents of the cursor.

As the loop progresses, as soon as a match is made between the cursor and the range of numbers, the loop continues but does not successfully match any of the other items in the cursor.

Any help is GREATLY Appreciated!!!

Here is an example:

Customer 90001 has a purchase order
Customer 90002 does not have a purchase order Customer 90003 does not have a purchase order Customer 90004 does not have a purchase order(wrong) Customer 90005 does not have a purchase order(wrong) Customer 90006 does not have a purchase order Customer 90007 does not have a purchase order(wrong) Customer 90008 does not have a purchase order

Here is the code:

CREATE OR REPLACE procedure proc_41

(low_cust in number,
hi_cust in number)

is

cursor curs_41
is
select * from purchase_order;

answer curs_41%rowtype;
i integer;

BEGIN
DBMS_OUTPUT.ENABLE;
open curs_41;
 if low_cust < hi_cust -- tests for valid range (second number higher than first)
 then

    for i in low_cust .. hi_cust --executes loop

    LOOP
      FETCH curs_41 INTO answer; --loads cursor into variable table

      Exit when curs_41%notfound;    
       IF answer.cust_id = i
       THEN
         dbms_output.put_line('Customer ' || i ||' has a purchase
order');
         ELSE
      dbms_output.put_line('Customer ' || i || ' does not have a
purchase order');
      END IF;

    END LOOP;  else
 dbms_output.put_line('Invalid Range');
 end if;
close curs_41;
end;
/ Received on Tue Apr 08 2003 - 10:40:27 CDT