Function in PL SQL (Oracle) Error

0
votes
CREATE OR REPLACE FUNCTION ComputeFreight(subtotal NUMBER)
RETURN NUMBER
IS
freightCharge NUMBER;
BEGIN
    IF subtotal <= 15000 THEN
    freightCharge := (subtotal * .10);
    ELSIF subtotal > 15000 THEN 
    freightCharge := (subtotal * .15);
    RETURN(freightCharge);
    END IF;
END;

DECLARE
  subtotal NUMBER := 15000;
BEGIN
  DBMS_OUTPUT.PUT_LINE( ComputeFreight(subtotal) );
END;

I don't know whats wrong with my code but it always shows this error:

Invalid statement

Invalid statement

ORA-06503: PL/SQL: Function returned without value ORA-06512: at "SQL_JQQXUMCMKFMRHRPZCYFNBEVTN.COMPUTEFREIGHT", line 12 ORA-06512: at line 4 ORA-06512: at "SYS.DBMS_SQL", line 1721

2
sqloracleplsql
In your example, NUMBER is 15000. In this case, the first branch of the IF statement evaluates to true; freightCharge gets the value 1500, and the IF statement terminates. The RETURN statement is never reached. You could add a RETURN statement in the first branch of IF, but in general functions with more than one RETURN statement are "spaghetti code" (code that is difficult to understand and maintain). The option suggested in GurV's solution is far superior. Also, I assume this is "practice" for IF statements; you don't really need one for this problem. - mathguy

2 Answers

3
votes

The return statement should be outside the if-else:

CREATE OR REPLACE FUNCTION ComputeFreight(subtotal NUMBER)
RETURN NUMBER
IS
freightCharge NUMBER;
BEGIN
    IF subtotal <= 15000 THEN
        freightCharge := (subtotal * .10);
    ELSIF subtotal > 15000 THEN 
        freightCharge := (subtotal * .15);
    END IF;
    RETURN(freightCharge);    -- here
END;
2
votes

If this is practice for the IF statement, that's fine (with GurV's suggested placement of the RETURN statement). However, if you actually need this in some sort of production, you may want to simplify the function and make it more efficient. Something like this:

CREATE OR REPLACE FUNCTION ComputeFreight(subtotal NUMBER)
RETURN NUMBER
IS
BEGIN
    RETURN subtotal * (CASE WHEN subtotal <= 15000 THEN 0.1 ELSE 0.15 END);
END;