A simple stored procedeure not working 1

[mrasad]

Hi

I'm just trying to develope a very simple stored procedeure, but its not working correctly. Code;

CREATE OR REPLACE PROCEDURE test_member (memberID IN Number)
IS
CURSOR member_cur is
SELECT no_of_late
FROM member;
late_number number(3);
BEGIN
open member_cur;
loop
fetch member_cur into late_number;
DBMS_OUTPUT.PUT_LINE('Number of Lates : '

|| late_number);
exit when member_cur%NOTFOUND;
-- exit when member_cur%ROWCOUNT > memberID;
END LOOP;
close member_cur;
END;
--------------------------------
I get a "Procedure created" in sql*plus

I then try to test it (exec test_off(2) << &quot;2&quot; is a memberID). I was expecting then when meberID &quot;2&quot; is placed in the () I should get back on screen the number of lates (a field within a member table), but i don't. I do get a result back, and from what I can see its the results for everything;

Number of Lates : 0
Number of Lates : 0
Number of Lates : 0
Number of Lates : 0
Number of Lates : 0
Number of Lates : 0
Number of Lates : 0
Number of Lates : 1
Number of Lates : 0
Number of Lates : 1
Number of Lates : 1
Number of Lates : 0
Number of Lates : 0
Number of Lates : 0

PL/SQL procedure successfully completed.

Any idea why?

 

[SantaMufasa]

Mr. Asad,

First, I presume when you say, &quot;I then try to test it (exec test_off(2) << &quot;2&quot; is a memberID)&quot;, that you mean &quot;...exec test_member...&quot;, since you used the code, &quot;CREATE OR REPLACE PROCEDURE test_member&quot;, right?

Second, the reason your code does not do what you want is because the incoming procedure parameter &quot;memberID&quot; has no code linkage to what I presume is a column in your &quot;member&quot; table. To achieve the results you want, here is a re-code your procedure:

CREATE OR REPLACE PROCEDURE test_member (mem_in IN Number)
 IS
  CURSOR member_cur is
  SELECT no_of_late
  FROM member
  WHERE memberID = mem_in;
  late_number number(3);
  BEGIN
open member_cur;
loop
        fetch member_cur into late_number;
        DBMS_OUTPUT.PUT_LINE('Number of Lates : '|| late_number);
        exit when member_cur%NOTFOUND;
  --     exit when member_cur%ROWCOUNT > memberID;
    END LOOP;
close member_cur;
 END;

Additionally, here is how I would "tighten" the code to become more concise, yet accomplish the same result:

CREATE OR REPLACE PROCEDURE test_member (mem_in IN Number)
 IS
 BEGIN
  for r in (SELECT no_of_late FROM member WHERE memberID = mem_in) loop
        DBMS_OUTPUT.PUT_LINE('Number of Lates : '||r.no_of_late);
  END LOOP;
 END;
/

I hope this helps.

Mufasa
(aka Dave of Sandy, Utah, USA @ 00:52 (04Jan04) GMT, 17:52 (03Jan04) Mountain Time)

 

[mrasad]

Thank you. I am getting better thanks to guy like you. I knew I had to put some sort code linkage to memberID, but I was not sure where.

Thanks again.

Asad