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