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 return data types acceptable by SQL.
Functions should not include any transaction, session, or system control statements.
Functions should have formal parameters of the IN mode.
Functions should not modify any database tables, if called as part of a SELECT statement.
Functions should not modify or read the same table, if called as part of an UPDATE or DELETE statement.
Functions should use only positional notation to pass values to the formal parameters.