A few days ago I spoke with another developer about SQL embedded into PL/SQL code and how function calls are handled in the WHERE clause of SQL statements. There is sometimes confusion who (PL/SQL engine or SQL engine) is executing it, I thought it’s a good idea to write a posting about it.
Last year I have already blogged about it in Caution when using PL/SQL functions in a SQL statement, but that was in the context of writing a stand alone SQL statement for a report, … but what actually happens if you have a SQL embedded into PL/SQL like the following example procedure code in a package
PROCEDURE processEmps ( pDepartmentId IN NUMBER ) IS BEGIN FOR rEMP IN ( SELECT EMPLOYEE_ID FROM EMPLOYEES WHERE SALARY > getAvgDeptSalary(pDepartmentId) ) LOOP NULL; END LOOP; END processEmps;
(Note: getAvgDeptSalary isn’t a very good example, because you could also do that with SQL only, but it’s just an example)
Very simple. A function in the WHERE clause which uses the procedure parameter pDepartmentId. What would you expect how the code/SQL is executed?