|
Caution: Because SQL is a declarative language, rather than an imperative (or procedural) one, you cannot know how many times a function invoked from a SQL statement will execute—even if the function is written in PL/SQL, an imperative language.If your application requires that a function be executed a certain number of times, do not invoke that function from a SQL statement. Use a cursor instead. For example, if your application requires that a function be called once for each selected row, then open a cursor, select rows from the cursor, and call the function for each row. This guarantees that the number of calls to the function is the number of rows fetched from the cursor. |
To be invoked from a SQL statement, a stored PL/SQL function must be declared either at schema level or in a package specification.
These SQL statements can invoke stored PL/SQL functions:
INSERT
UPDATE
DELETE
SELECT
CALL
(CALL can also invoke a stored PL/SQL procedure.)
To invoke a PL/SQL subprogram from SQL, you must either own or have EXECUTE privileges on the subprogram. To select from a view defined with a PL/SQL function, you must have SELECT privileges on the view. No separate EXECUTE privileges are necessary to select from the view.
For general information about invoking subprograms, including passing parameters, see Oracle Database PL/SQL Language Reference.
Topics:
Invoking PL/SQL subprograms in SQL statements can:
Increase user productivity by extending SQL
Expressiveness of the SQL statement increases where activities are too complex, too awkward, or unavailable with SQL.
Increase query efficiency
Functions used in the WHERE clause of a query can filter data using criteria that must otherwise be evaluated by the application.
Manipulate character strings to represent special data types (for example, latitude, longitude, or temperature).
Provide parallel query execution
If the query is parallelized, then SQL statements in your PL/SQL subprogram might also be run in parallel (using the parallel query option).
A PL/SQL function can appear in a SQL statement wherever a built-in SQL function or an expression can appear in a SQL statement. For example:
Select list of the SELECT statement
Condition of the WHERE or HAVING clause
CONNECT BY, START WITH, ORDER BY, or GROUP BY clause
VALUES clause of the INSERT statement
SET clause of the UPDATE statement
A PL/SQL table function (which returns a collection of rows) can appear in a SELECT statement instead of:
Column name in the SELECT list
Table name in the FROM clause
A PL/SQL function cannot appear in these contexts, which require unchanging definitions:
CHECK constraint clause of a CREATE or ALTER TABLE statement
Default value specification for a column
To be invoked from a SQL expression, a PL/SQL function must satisfy these requirements:
It must be a row function, not a column (group) function; that is, its argument cannot be an entire column.
Its formal parameters must be IN parameters, not OUT or IN OUT parameters.
Its formal parameters and its return value (if any) must have Oracle built-in data types (such as CHAR, DATE, or NUMBER), not PL/SQL data types (such as BOOLEAN, RECORD, or TABLE).
There is an exception to this rule: A formal parameter can have a PL/SQL data type if the corresponding actual parameter is implicitly converted to the data type of the formal parameter (as in Example: PL/SQL Function with Formal Parameter of PL/SQL Data Type, Invoked from a SQL Expression).
The function in Example: PL/SQL Function that Can Appear in a SQL Expression satisfies the preceding requirements.
PL/SQL Function that Can Appear in a SQL Expression
DROP TABLE payroll; -- in case it exists CREATE TABLE payroll ( srate NUMBER, orate NUMBER, acctno NUMBER ); CREATE OR REPLACE FUNCTION gross_pay ( emp_id IN NUMBER, st_hrs IN NUMBER := 40, ot_hrs IN NUMBER := 0 ) RETURN NUMBER IS st_rate NUMBER; ot_rate NUMBER; BEGIN SELECT srate, orate INTO st_rate, ot_rate FROM payroll WHERE acctno = emp_id; RETURN st_hrs * st_rate + ot_hrs * ot_rate; END gross_pay; /
In Example: PL/SQL Function with Formal Parameter of PL/SQL Data Type, Invoked from a SQL Expression, the SQL statement CALL invokes the PL/SQL function f1, whose formal parameter and return value have PL/SQL data type PLS_INTEGER. The CALL statement succeeds because the actual parameter, 2, is implicitly converted to the data type PLS_INTEGER. If the actual parameter had a value outside the range of PLS_INTEGER, the CALL statement would fail.
PL/SQL Function with Formal Parameter of PL/SQL Data Type, Invoked from a SQL Expression
CREATE OR REPLACE FUNCTION f1 ( b IN PLS_INTEGER ) RETURN PLS_INTEGER IS BEGIN RETURN CASE WHEN b > 0 THEN 1 WHEN b <= 0 THEN -1 ELSE NULL END; END f1; / VARIABLE x NUMBER; CALL f1(b=>2) INTO :x; PRINT x;
Result:
X
----------
1
The purity of a stored subprogram refers to the side effects of that subprogram on database tables or package variables. Side effects can prevent the parallelization of a query, yield order-dependent (and therefore, indeterminate) results, or require that package state be maintained across user sessions. Various side effects are not allowed when a function is invoked from a SQL query or DML statement.
Topics related to side effects:
When a new SQL statement is run, checks are made to see if it is logically embedded within the execution of a running SQL statement. This occurs if the statement is run from a trigger or from a subprogram that was in turn invoked from the running SQL statement. In these cases, further checks determine if the new SQL statement is safe in the specific context.
These restrictions are enforced on subprograms:
A subprogram invoked from a query or DML statement might not end the current transaction, create or rollback to a savepoint, or ALTER the system or session.
A subprogram invoked from a query (SELECT) statement or from a parallelized DML statement might not execute a DML statement or otherwise modify the database.
A subprogram invoked from a DML statement might not read or modify the particular table being modified by that DML statement.
These restrictions apply regardless of what mechanism is used to run the SQL statement inside the subprogram or trigger. For example:
They apply to a SQL statement invoked from PL/SQL, whether embedded directly in a subprogram or trigger body, run using the native dynamic mechanism (EXECUTE IMMEDIATE), or run using the DBMS_SQL package.
They apply to statements embedded in Java with SQLJ syntax or run using JDBC.
They apply to statements run with OCI using the callback context from within an "external" C function.
You can avoid these restrictions if the execution of the new SQL statement is not logically embedded in the context of the running statement. PL/SQL autonomous transactions provide one escape. Another escape is available using Oracle Call Interface (OCI) from an external C function, if you create a new connection rather than using the handle available from the OCIExtProcContext argument.
You can use the keywords DETERMINISTIC and PARALLEL_ENABLE in the syntax for declaring a function. These are optimization hints that inform the query optimizer and other software components about:
Functions that need not be invoked redundantly
Functions permitted within a parallelized query or parallelized DML statement
Only functions that are DETERMINISTIC are allowed in function-based indexes and in certain snapshots and materialized views.
A deterministic function depends solely on the values passed into it as arguments and does not reference or modify the contents of package variables or the database or have other side-effects. Such a function produces the same result value for any combination of argument values passed into it.
You place the DETERMINISTIC keyword after the return value type in a declaration of the function. For example:
CREATE OR REPLACE FUNCTION f1 (
p1 NUMBER
) RETURN NUMBER DETERMINISTIC
IS
BEGIN
RETURN p1 * 2;
END;
/
You might place this keyword in these places:
On a function defined in a CREATE FUNCTION statement
In a function declaration in a CREATE PACKAGE statement
On a method declaration in a CREATE TYPE statement
Do not repeat the keyword on the function or method body in a CREATE PACKAGE BODY or CREATE TYPE BODY statement.
Certain performance optimizations occur on invocations of functions that are marked DETERMINISTIC without any other action being required. These features require that any function used with them be declared DETERMINISTIC:
Any user-defined function used in a function-based index.
Any function used in a materialized view, if that view is to qualify for Fast Refresh or is marked ENABLE QUERY REWRITE.
The preceding functions features attempt to use previously calculated results rather than invoking the function when it is possible to do so.
It is good programming practice to make functions that fall into these categories DETERMINISTIC:
Functions used in a WHERE, ORDER BY, or GROUP BY clause
Functions that MAP or ORDER methods of a SQL type
Functions that help determine whether or where a row appears in a result set
Keep these points in mind when you create DETERMINISTIC functions:
The database cannot recognize if the action of the function is indeed deterministic. If the DETERMINISTIC keyword is applied to a function whose action is not truly deterministic, then the result of queries involving that function is unpredictable.
If you change the semantics of a DETERMINISTIC function and recompile it, then existing function-based indexes and materialized views report results for the prior version of the function. Thus, if you change the semantics of a function, you must manually rebuild any dependent function-based indexes and materialized views.
|
See Also: Oracle Database PL/SQL Language Reference forCREATE FUNCTION restrictions |
The Oracle Database parallel execution feature divides the work of executing a SQL statement across multiple processes. Functions invoked from a SQL statement that is run in parallel might have a separate copy run in each of these processes, with each copy invoked for only the subset of rows that are handled by that process.
Each process has its own copy of package variables. When parallel execution begins, these are initialized based on the information in the package specification and body as if a user is logging into the system; the values in package variables are not copied from the original login session. And changes made to package variables are not automatically propagated between the various sessions or back to the original session. Java STATIC class attributes are similarly initialized and modified independently in each process. Because a function can use package (or Java STATIC) variables to accumulate some value across the various rows it encounters, Oracle Database cannot assume that it is safe to parallelize the execution of all user-defined functions.
Oracle Database parallelizes functions that meet certain criteria for being parallelizable; however, the PARALLEL_ENABLE keyword is the preferred way to mark your code as safe for parallel execution. This keyword is syntactically similar to DETERMINISTIC as described in "Declaring a Function"; it is placed after the return value type in a declaration of the function, as in:
CREATE OR REPLACE FUNCTION f1 (
p1 NUMBER
) RETURN NUMBER PARALLEL_ENABLE
IS
BEGIN
RETURN p1 * 2;
END;
/
A PL/SQL function defined with CREATE FUNCTION might still be run in parallel without any explicit declaration that it is safe to do so, if the system can determine that it neither reads nor writes package variables nor invokes any function that might do so. A Java method or C function is never seen by the system as safe to run in parallel, unless the programmer explicitly indicates PARALLEL_ENABLE on the call specification, or provides a PRAGMA RESTRICT_REFERENCES indicating that the function is sufficiently pure.
An additional run-time restriction is imposed on functions run in parallel as part of a parallelized DML statement. Such a function is not permitted to in turn execute a DML statement; it is subject to the same restrictions that are enforced on functions that are run inside a query (SELECT) statement.