Examine this code:
CREATE OR REPLACE PACKAGE bonus
IS
g_max_bonus NUMBER := .99;
FUNCTION calc_bonus (p_emp_id NUMBER)
RETURN NUMBER;
FUNCTION calc_salary (p_emp_id NUMBER)
RETURN NUMBER;
END;
/
CREATE OR REPLACE PACKAGE BODY bonus
IS
v_salary employees.salary%TYPE;
v_bonus employees.commission_pct%TYPE;
FUNCTION calc_bonus (p_emp_id NUMBER)
RETURN NUMBER
IS
BEGIN
SELECT salary, commission_pct
INTO v_salary, v_bonus
FROM employees
WHERE employee_id = p_emp_id;
RETURN v_bonus * v_salary;
END calc_bonus
FUNCTION calc_salary (p_emp_id NUMBER)
RETURN NUMBER
IS
BEGIN
SELECT salary, commission_pct
INTO v_salary, v_bonus
FROM employees
WHERE employees
RETURN v_bonus * v_salary + v_salary;
END cacl_salary;
END bonus;
/
Which statement is true?
A.
You can call the BONUS.CALC_SALARY packaged function from an INSERT command
against the EMPLOYEES table.
B.
You can call the BONUS.CALC_SALARY packaged function from a SELECT command against
the EMPLOYEES table.
C.
You can call the BONUS.CALC_SALARY packaged function form a DELETE command against
the EMPLOYEES table.
D.
You can call the BONUS.CALC_SALARY packaged function from an UPDATE command
against the EMPLOYEES table.
Explanation:
For the Oracle server to execute a SQL statement that calls a stored function, it must know the
purity level of a stored functions, that is, whether the functions are free of side effects. Side effects
are changes to database tables or public packaged variables (those declared in a package
specification). Side effects could delay the execution of a query, yield order-dependent (therefore
indeterminate) results, or require that the package state variables be maintained across user
sessions. Various side effects are not allowed when a function is called from a SQL query or DML
statement. Therefore, the following restrictions apply to stored functions called from SQL
expressions:
A function called from a query or DML statement may not end the current transaction, create or
roll back to a savepoint, or alter the system or session
A function called from a query statement or from a parallelized DML statement may not execute
a DML statement or otherwise modify the database
A function called from a DML statement may not read or modify the particular table being
modified by that DML statement