Examine this code:
CREATE OR REPLACE FUNCTION calc_sal(p_salary NUMBER) RETURN NUMBER
IS
v_raise NUMBER(4,2) DEFAULT 1.08;
BEGIN
RETURN v_raise * p_salary;
END calc_sal;
/
Which statement accurately call the stored function CALC_SAL? (Choose two)
A.
UPDATE employees (calc_sal(salary))
SET salary = salary * calc_sal(salary);
B.
INSERT calc_sal(salary) INTO employees
WHERE department_id = 60;
C.
DELETE FROM employees(calc_sal(salary))
WHERE calc_sal(salary) > 1000;
D.
SELECT salary, calc_sal(salary)
FROM employees
WHERE department_id = 60;
E.
SELECT last_name, salary, calc_sal(salary)
FROM employees ORDER BY
calc_sal(salary);
Explanation:
D: This function is using the function in the column list of the select statement. This function does not perform any DML or violate any of the restriction on functions that are invoked as part of a SQL expression.
E: This function is correctly using the function in the column list and the ORDER BY clause of the select statement.
Functions can be invoked in the column list, WHERE, GROUP BY, HAVING and ORDER BY clauses of a SELECT statement.
Restrictions on functions that are invoked as part of SQL expressions are:Functions should be stored in the database.
Functions should retrun data types accepted by SQL. Functions should not include any transaction,session , or system control statments. Functions should have formal parameters of the IN mode. Functions should not modify any database tables,if called as part of a SELECT statment. Functions should not modify or read the same table,if called as part of an UPDATE or DELETE. Functions should use only positional to pass values to the formal parameters.Incorrect Answers
A: Functions can only be invoked in the in the SET and WHERE clause of an UPDATE statement.
B: Functions can only be invoked in the VALUES clause of an INSERT statement. C: Functions can only be invoked in the WHERE clause of a DELETE statement.