Re: PL/SQL Loop not quite looping

davidmac wrote:

> 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;
> /

Your cursor bears no relationship to the value of i in your loop.

The cursor grabs every record in the table. What are the chances that the cust_id will equal i?

Daniel Morgan Received on Tue Apr 08 2003 - 11:00:33 CDT