Re: PL/SQL Loop not quite looping

From: Martin Burbridge <pobox002_at_bebub.com>
Date: Wed, 09 Apr 2003 02:24:26 GMT
Message-ID: <Xns9357E33657A3Apobox002bebubcom@63.240.76.16>

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

Hello David,

This is a very odd relational database you have, shouldn't there be a customer table somewhere? Your procedure simply makes up the customer ids, there is no way of telling whether customer 90002 has no POs or customer 90002 does not exist?

If you did have a customer table you could outer join to it and decode customer id, if its null then output the no PO message.

Based on what you have given though you would have to dynamically generate the ids based on start and end values which is the tricky part. This uses all_objects and rownum to generate up to 23000 on my notebook so ymmv.

It doesn't generate an error if the arguments are the wrong way around, it just uses greatest and least, low -> high. You could generate the error with a union all if you wanted it that way.

There isn't a need for a loop or any pl/sql.

SQL> create table t (i number);

Table created.

SQL> insert into t values (90001);

1 row created.

SQL> insert into t values (90004);

1 row created.

SQL> insert into t values (90005);

1 row created.

SQL> insert into t values (90007);

1 row created.

SQL> var x number
SQL> var y number
SQL> exec :x := 90001; :y := 90008

PL/SQL procedure successfully completed.

SQL> select a.i, t.i,

  2      decode(t.i, null, 'Customer ' || to_char(a.i) || ' has no PO',
  3          'Customer ' || to_char(a.i) || ' has a PO') msg
  4      from t,
  5      (select x + rownum - 1 i
  6      from all_objects,
  7      (select rownum, least(:x,:y) x,
  8             greatest(:x,:y) - least(:x,:y) + 1 n from dual)
  9      where rownum <= n) a

 10 where a.i = t.i (+)
 11 order by a.i;

         I I MSG

---------- ---------- ----------------------------
     90001      90001 Customer 90001 has a PO
     90002            Customer 90002 has no PO
     90003            Customer 90003 has no PO
     90004      90004 Customer 90004 has a PO
     90005      90005 Customer 90005 has a PO
     90006            Customer 90006 has no PO
     90007      90007 Customer 90007 has a PO
     90008            Customer 90008 has no PO

8 rows selected.

SQL> exec :x := 90008; :y := 90001

PL/SQL procedure successfully completed.

SQL> /          I I MSG

---------- ---------- ----------------------------
     90001      90001 Customer 90001 has a PO
     90002            Customer 90002 has no PO
     90003            Customer 90003 has no PO
     90004      90004 Customer 90004 has a PO
     90005      90005 Customer 90005 has a PO
     90006            Customer 90006 has no PO
     90007      90007 Customer 90007 has a PO
     90008            Customer 90008 has no PO

8 rows selected.

SQL> Martin Received on Tue Apr 08 2003 - 21:24:26 CDT