Examine this function:
CREATE OR REPLACE FUNCTION CALC_PLAYER_AVG
(V_ID in PLAYER_BAT_STAT.PLAYER_ID%TYPE)
RETURN NUMBER
IS
V_AVG NUMBER;
BEGIN
SELECT HITS / AT_BATS
INTO V_AVG
FROM PLAYER_BAT_STAT
WHERE PLAYER_ID = V_ID;
RETURN (V_AVG);
END;
Which statement will successfully invoke this function in SQL *Plus?
A.
SELECT CALC_PLAYER_AVG(PLAYER_ID)
FROM PLAYER_BAT_STAT;
B.
EXECUTE CALC_PLAYER_AVG(31);
C.
CALC_PLAYER(‘RUTH’);
D.
CALC_PLAYER_AVG(31);
E.
START CALC_PLAYER_AVG(31)
Explanation:
A function can be invoked in SELECT Statement provided that the function does not modify any database tables. The function must use positional notation to pass values to the formal parameters. The formal parameters must be of the IN mode. They should return data types acceptable to SQL and they should not include any transaction, session, or system control statements.
Incorrect Answers
B: You can’t call a function in this way, in this way you can call a procedure, because function must return a value, to call a function using EXECUTE command you should declare a bind variable using the VARIABLE command then assign the value returned from the function to this variable, in the following way:
SQL> VARIABLE v_get_value NUMBER
SQL> EXECUTE :v_get_value := CALC_PLAYER_AVG(31)
PL/SQL procedure successfully completed.
SQL> PRINT v_get_value
V_GET_VALUE
———–
C: Again this way can’t be use for calling a function in PL/SQL block because the function return a value and this values must be assigned to PL/SQL variable or to bind variable. Like this
DECLARE
v_get_from_fn NUMBER;
BEGIN
v_get_from := CALC_PLAYER_AVG(31);
END;
/
D: Same as C:
E: START is use to execute a script.
A
b
B is wrong. You can’t use EXECUTE directly, but you can create a variable x in SQL*Plus (or Developer) and use EXECUTE to assign the result to this variable. As this is not the way b) is trying to do it, only answer a) will work properly
If we think your point of view, I have one question just answer yourself.
Question goes like this, function must return a value, In option B where are you going to store returned value?
Ans A
SELECT CALC_PLAYER_AVG(PLAYER_ID)
FROM PLAYER_BAT_STAT;
a