exam magic - ExamMagic.com Exam 1Z0-147 Title Ver Oracle 9i...

Info icon This preview shows pages 1–4. Sign up to view the full content.

View Full Document Right Arrow Icon
ExamMagic.com Exam : 1Z0-147 Title : Oracle 9i: Program with PL/SQL Ver : 7.0
Image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
1Z0-147 WWW.ExamMa gic.COM - 1: 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) Answer: A 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 www.ExamMagic.com
Image of page 2