Oracle® Database PL/SQL User's Guide and Reference 10g Release 2 (10.2) Part Number B14261-01 |
|
|
View PDF |
To be callable from SQL statements, a stored function must obey certain purity rules, which control side-effects. See "Controlling Side Effects of PL/SQL Subprograms". The fewer side-effects a function has, the better it can be optimized within a query, particular when the PARALLEL_ENABLE
or DETERMINISTIC
hints are used. The same rules that apply to the function itself also apply to any functions or procedures that it calls.
If any SQL statement inside the function body violates a rule, you get an error at run time (when the statement is parsed). To check for violations of the rules at compile time, you can use the compiler directive PRAGMA RESTRICT_REFERENCES
. This pragma asserts that a function does not read and/or write database tables and/or package variables. Functions that do any of these read or write operations are difficult to optimize, because any call might produce different results or encounter errors.
For more information, see Oracle Database Application Developer's Guide - Fundamentals.
Syntax
restrict_references pragma ::=
Keyword and Parameter Description
Specifies that the pragma applies to all subprograms in the package spec or object type spec. You can still declare the pragma for individual subprograms. Such pragmas override the default pragma.
function_name
A user-defined function or procedure.
Signifies that the statement is a compiler directive. Pragmas are processed at compile time, not at run time. They do not affect the meaning of a program; they convey information to the compiler.
Asserts that the subprogram reads no database state (does not query database tables).
Asserts that the subprogram reads no package state (does not reference the values of packaged variables)
Asserts that the subprogram can be trusted not to violate one or more rules. This value is needed for functions written in C or Java that are called from PL/SQL, since PL/SQL cannot verify them at run time.
Asserts that the subprogram writes no database state (does not modify tables).
Asserts that the subprogram writes no package state (does not change the values of packaged variables).
Usage Notes
You can declare the pragma RESTRICT_REFERENCES
only in a package spec or object type spec. You can specify up to four constraints (RNDS
, RNPS
, WNDS
, WNPS
) in any order. To call a function from parallel queries, you must specify all four constraints. No constraint implies another. Typically, this pragma is specified for functions. If a function calls procedures, then specify the pragma for those procedures as well.
When you specify TRUST
, the function body is not checked for violations of the constraints listed in the pragma. The function is trusted not to violate them. Skipping these checks can improve performance.
If you specify DEFAULT
instead of a subprogram name, the pragma applies to all subprograms in the package spec or object type spec (including the system-defined constructor for object types). You can still declare the pragma for individual subprograms, overriding the default pragma.
A RESTRICT_REFERENCES
pragma can apply to only one subprogram declaration. A pragma that references the name of overloaded subprograms always applies to the most recent subprogram declaration.
For examples, see the following:
Related Topics