99 function calcbonus pempid number return number

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: 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 Actualtests.com - The Power of Knowing 1Z0-147 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. Answer: B 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 QUESTION 10: Which statement is v...
View Full Document

This document was uploaded on 03/08/2014.

Ask a homework question - tutors are online