Stored functions in SQL offer tremendous power. As you might expect, however, power introduces the possibility of abuse and the need for responsible action. In the context of SQL, abuse of power involves the rippling impact of side effects in a function. Consider the following function:
FUNCTION total_comp (salary_in IN employee.salary%TYPE, bonus_in IN employee.bonus%TYPE) RETURN NUMBER IS BEGIN UPDATE employee SET salary = salary_in / 2; RETURN salary_in + NVL (bonus_in, 0); END;
This simple little calculation, introduced at the beginning of the chapter, now also updates the salary of all employees to half of the specified value. This action affects the results of the query from which total_comp might originate; even worse, it affects any other SQL statement in this session.
Along with modification of database tables, modification of package variables is another side effect of stored functions in SQL. Package variables act as globals within a particular session. A function that changes a package variable could have an impact on another stored function or procedure, which in turn could affect a SQL statement using that stored function.
A PL/SQL function could also cause a side effect in the WHERE clause of a query. The query optimizer can reorder the evaluation of predicates in the WHERE clause to minimize the number of rows processed. A function executing in this clause could therefore subvert the query optimization process.
My general recommendation for a function is that it should be narrowly focused on computing and returning a value. But a recommendation is not enough when it comes to database integrity: in order to guard against nasty side effects and upredictable behavior, the Oracle Server makes it impossible for your stored function in SQL to take any of the following actions:
If your function violates any of these rules or is a function defined in a package and is missing its RESTRICT_REFERENCES pragma, you will receive the dreaded ORA-06571 error:
ORA-06571: Function TOTAL_COMP does not guarantee not to update database
As discussed in Section 17.7, "Realities: Calling PL/SQL Functions in SQL" , it can be very difficult at times (and sometimes impossible) to avoid this error. In other situations, however, there is an easy resolution (certainly do check the above list of restrictions).
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.