Re: PL/SQL Loop not quite looping

In message <6e439c43.0304080740.75df4ff8_at_posting.google.com> , davidmac <david.mcmullen_at_mail.va.gov> writes
>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!!!
>

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

I'm impressed that it comes back with any matches! You don't have any kind of link between the query on the purchase order and the range of numbers that you are checking - your FOR loop goes between low and hi, and your query picks up random purchase orders.

How about something along the lines of...

FOR v_cust_id in low_cust..high_cust LOOP   SELECT count(*)
  INTO v_num_po
  PURCHASE_ORDERS
  WHERE cust_id = v_cust_id
  ;

  IF v_num_po > 0 THEN
    dbms_output.put_line('Customer '||v_cust_id||' has a purchase order');
  ELSE
    dbms_output.put_line('Customer '||v_cust_id||' does not have a purchase order');
  END IF;
END LOOP; You could probably do the whole thing with a single UNION query if you were to perform a select against your customers and purchase orders...

-- 
Andy Hardy. PGP ID: 0xA62A4849