Re: PL/SQL Loop not quite looping

david.mcmullen_at_mail.va.gov (davidmac) wrote in message news:<6e439c43.0304080740.75df4ff8_at_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:
>

your logic is a little off. when you FETCH from the cursor, you're only grabbing the next row in the resultset described by the cursor. try something like this:

CREATE OR REPLACE procedure proc_41
(low_cust in purchase_order.cust_id%type, hi_cust in purchase_order.cust_id%type)
is
l_has varchar2(15);
BEGIN
DBMS_OUTPUT.ENABLE;
 if low_cust < hi_cust
 then

    for i in low_cust .. hi_cust --executes loop     LOOP

	 select decode(sign(count(*)), 1, ' has ', ' does not have ')
                into l_has
         from purchase_order
         where cust_id = i;
         dbms_output.put_line('Customer ' || i || l_has || 'a purchase

    END LOOP;
 else
 dbms_output.put_line('Invalid Range');
 end if;
end;
/

of course, this isn't the most efficient way to do it, since it has to execute the select for every number in your range. regular sql with a join against your customer table would be better. Received on Tue Apr 08 2003 - 18:38:20 CDT