CREATE OR REPLACE PROCEDURE in ODB11G

Question

I have a table:

CUST_ID;Bon;Netturnover

   1;2;440   
   1;1;500   
   2;3;9000   
   3;1;2500

I created a procedure but it does not work:

CREATE OR REPLACE PROCEDURE TEST_Proc
(P_ID OUT NUMBER, F1 IN NUMBER, M1 IN NUMBER) AS 
BEGIN
SELECT CUST_ID INTO P_ID FROM test WHERE CUST_ID = P_ID
GROUP BY CUST_ID
HAVING SUM(Bon)< F1 AND SUM(Netturnover) > M1;
END TEST_Proc;

EXECUTE TEST_Proc (3,450);

Error message:

• ORA-01403: no data
• ORA-06512: the "SYSTEM.TEST_PROC", the place at # 4
• ORA-06512: the , the place at # 9

What is the problem?

Basically, I’d like to get information like this:

SELECT CUST_ID FROM test 
GROUP BY CUST_ID
HAVING SUM(Bon)< 3 AND SUM(Netturnover) > 450;

... just automatically with a procedure.

Answer 1

First, you are calling with only two values, but you need three:

declare
    p_id number;
begin
    p_id := 3;
    EXECUTE TEST_Proc (pid, 450, ??);
end;

Then, your query is really strange, because you are passing back the value passed in. Okay, call it an IN OUT parameter. More importantly, the subquery may return no rows, and you don't handle that.

You could do:

CREATE OR REPLACE PROCEDURE TEST_Proc (
    in_out_P_ID IN OUT NUMBER,
    in_F1 IN NUMBER,
    in_M1 IN NUMBER
) AS 
BEGIN
    SELECT (CASE WHEN SUM(Bon)< in_F1 AND SUM(Netturnover) > in_M1
                 THEN CUST_ID
            END) INTO P_ID
    FROM test
    WHERE CUST_ID = in_out_P_ID
END TEST_Proc;

Note that the parameter names are prefixed, to help avoid collisions with column names.