Re: PL/SQL Loop not quite looping

From: Tim X <timx_at_spamto.devnul.com>
Date: 09 Apr 2003 20:07:23 +1000
Message-ID: <87u1d8q90k.fsf@tiger.rapttech.com.au>

david.mcmullen_at_mail.va.gov (davidmac) 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!!!
>
> 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;
> /

Not sure if I totally understand what you are trying to do, but it looks to me like you are expecting the records you select to somehow be related to the low and high customer number ranges, but nowhere is this relationship established. Maybe this is closer to what you want (off top of head and untested)

create or replace procedure proc_41(low_cust IN NUMBER,

                                    high_cust IN NUMBER)

is
  cursor cur_41 is
    select cust_id, count(*) cnt from purchase_order     where cust_id between low_cust and high_cust     group by cust_id;
  answer cur_41%ROWTYPE;
begin
  dbms_output.enable;
  if low_cust > high_cust then

     dbms_output.put_line('Out of range');
     return;

  end if;
  for answer in cur_41 loop
    dbms_output.put_line(to_char(answer.cust_id)||' has '

                         ||to_char(answer.cnt)||' orders');

  end loop;
exception
  when others then
    dbms_output.put_line('Error: '||SQLERRM); end;
/

Note that dbms_output has a limited output buffer size with a maximum setting of 1Mb and youmay need to set it to its maximum as the default is quite small.

Tim

Tim Cross
The e-mail address on this message is FALSE (obviously!). My real e-mail is to a company in Australia called rapttech and my login is tcross - if you really need to send mail, you should be able to work it out! Received on Wed Apr 09 2003 - 05:07:23 CDT