Re: PL/SQL Loop not quite looping

david.mcmullen_at_mail.va.gov said...
> 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;
> /
>

Forgive me if I misunderstand what you're trying to do ... but isn't this more like what you want?

create or replace procedure proc_41 (
  low_cust in number,
  hi_cust in number
) is
begin
  dbms_output.enable(1000000);
  --
  select 1 from purchase_order
  where cust_id between low_cust and hi_cust;   --
  dbms_output.put_line(
    'Customer ' || cust_id || ' has a P.O.'); exception
  when no_data_found then
    dbms_output.put_line(
      'Customer ' || cust_id || ' does not have a P.O.'); end proc_41;

Maybe you can consider making it a function instead of a procedure so you can return yes/no type answer.

-- 
/Karsten
DBA > retired > DBA