This chapter explains how to turn sets of statements into reusable subprograms. Subprograms are the building blocks of modular, maintainable applications.
Topics:
A PL/SQL subprogram is a named PL/SQL block that can be invoked with a set of parameters. A subprogram can be either a procedure or a function. Typically, you use a procedure to perform an action and a function to compute and return a value.
You can create a subprogram either at schema level, inside a package, or inside a PL/SQL block (which can be another subprogram).
A subprogram created at schema level is a standalone stored subprogram. You create it with the CREATE
PROCEDURE
or CREATE
FUNCTION
statement. It is stored in the database until you drop it with the DROP
PROCEDURE
or DROP
FUNCTION
statement.
A subprogram created inside a package is a packaged subprogram. It is stored in the database until you drop the package with the DROP
PACKAGE
statement.
A subprogram created inside a PL/SQL block is a nested subprogram. You can either declare and define it at the same time, or you can declare it first (forward declaration) and then define it later in the same block. A nested subprogram is stored in the database only if it is nested within a standalone or packaged subprogram.
See Also:
CREATE PROCEDURE Statement for more information about creating standalone stored procedures
CREATE FUNCTION Statement for more information about creating standalone stored functions
CREATE PACKAGE Statement for more information about creating standalone stored functions
Procedure Declaration and Definition for more information about creating procedures inside PL/SQL blocks
Function Declaration and Definition for more information about creating functions inside PL/SQL blocks
A subprogram call has this form:
subprogram_name [ (parameter [, parameter]... ) ]
A procedure call is a PL/SQL statement. For example:
raise_salary(employee_id, amount);
A function call is part of an expression. For example:
IF salary_ok(new_salary, new_title) THEN ...
See Also:
Declaring and Passing Subprogram Parameters for more information about subprogram callsSubprograms let you extend the PL/SQL language.
Procedure calls are like new statements. Function calls are like new expressions and operators.
Subprograms let you break a program into manageable, well-defined modules.
You can use top-down design and the stepwise refinement approach to problem solving.
Subprograms promote re-usability.
Once tested, a subprogram can be reused in any number of applications. You can invoke PL/SQL subprograms from many different environments, so that you need not rewrite them each time you use a new language or use a new API to access the database.
Subprograms promote maintainability.
You can change the internal details of a subprogram without changing the other subprograms that invoke it. Subprograms are an important component of other maintainability features, such as packages and object types.
Dummy subprograms ("stubs") let you defer the definition of procedures and functions until after you have tested the main program.
You can design applications from the top down, thinking abstractly, without worrying about implementation details.
Subprograms can be grouped into PL/SQL packages.
Packages make code even more reusable and maintainable, and can be used to define an API.
You can hide the implementation details of subprograms by placing them in PL/SQL packages.
You can define subprograms in a package body without declaring their specifications in the package specification. However, such subprograms can be invoked only from inside the package. At least one statement must appear in the executable part of a subprogram. The NULL
statement meets this requirement.
A subprogram always has a name, and can have a parameter list.
Like every PL/SQL block, a subprogram has an optional declarative part, a required executable part, and an optional exception-handling part, and can specify PRAGMA
AUTONOMOUS_TRANSACTION
, which makes it autonomous (independent).
The declarative part of a subprogram does not begin with the keyword DECLARE
, as the declarative part of a non-subprogram block does. The declarative part contains declarations of types, cursors, constants, variables, exceptions, and nested subprograms. These items are local to the subprogram and cease to exist when the subprogram completes execution.
The executable part of a subprogram contains statements that assign values, control execution, and manipulate data.
The exception-handling part of a subprogram contains code that handles run-time errors.
Example 8-1 declares and defines a procedure (at the same time) inside an anonymous block. The procedure has the required executable part and the optional exception-handling part, but not the optional declarative part. The executable part of the block invokes the procedure.
Example 8-1 Declaring, Defining, and Invoking a Simple PL/SQL Procedure
-- Declarative part of block begins DECLARE in_string VARCHAR2(100) := 'This is my test string.'; out_string VARCHAR2(200); -- Procedure declaration and definition begins PROCEDURE double (original IN VARCHAR2, new_string OUT VARCHAR2) IS -- Declarative part of procedure (optional) goes here -- Executable part of procedure begins BEGIN new_string := original || ' + ' || original; -- Executable part of procedure ends -- Exception-handling part of procedure begins EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE('Output buffer not long enough.'); END; -- Exception-handling part of procedure ends -- Procedure declaration and definition ends -- Declarative part of block ends -- Executable part of block begins BEGIN double(in_string, out_string); -- Procedure invocation DBMS_OUTPUT.PUT_LINE(in_string || ' - ' || out_string); END; -- Executable part of block ends /
A procedure and a function have the same structure, except that:
A function heading must include a RETURN
clause that specifies the data type of the return value. A procedure heading cannot have a RETURN
clause.
A function must have at least one RETURN
statement in its executable part. In a procedure, the RETURN
statement is optional. For details, see RETURN Statement.
Only a function heading can include the following options:
Option | Description |
---|---|
DETERMINISTIC option |
Helps the optimizer avoid redundant function calls. |
PARALLEL_ENABLED option |
Allows the function to be used safely in slave sessions of parallel DML evaluations. |
PIPELINED option |
Returns the results of a table function iteratively. |
RESULT_CACHE option |
Stores function results in the PL/SQL function result cache. |
RESULT_CACHE clause |
Specifies the data sources on which the results of a function. |
See Also:
Procedure Declaration and Definition for the syntax of procedure declarations and definitions
Function Declaration and Definition for the syntax of function declarations and definitions, including descriptions of the items in the preceding table
Declaring and Passing Subprogram Parameters for more information about subprogram parameters
Using the PL/SQL Function Result Cache for more information about the RESULT_CACHE
option and the RESULT_CACHE
clause
The RETURN
statement (not to be confused with the RETURN
clause, which specifies the data type of the return value of a function) immediately ends the execution of the subprogram that contains it and returns control to the caller. Execution continues with the statement following the subprogram call.
A subprogram can contain several RETURN
statements. The subprogram need not end with a RETURN
statement. Executing any RETURN
statement completes the subprogram immediately.
In a procedure, a RETURN
statement cannot contain an expression and does not return a value.
In a function, a RETURN
statement must contain an expression. When the RETURN
statement executes, the expression is evaluated, and its value is assigned to the function identifier. The function identifier acts like a variable of the type specified in the RETURN
clause.
The expression in a function RETURN
statement can be arbitrarily complex. For example:
CREATE OR REPLACE FUNCTION half_of_square(original NUMBER)
RETURN NUMBER IS
BEGIN
RETURN (original * original)/2 + (original * 4);
END half_of_square;
/
A function must have at least one execution path that leads to a RETURN
statement.
Example 8-2 declares and defines a function (at the same time) inside an anonymous block. The function has the optional declarative part and the required executable part, but not the optional exception-handling part. The executable part of the block invokes the function.
Example 8-2 Declaring, Defining, and Invoking a Simple PL/SQL Function
-- Declarative part of block begins DECLARE -- Function declaration and definition begins FUNCTION square (original NUMBER) RETURN NUMBER -- RETURN clause AS -- Declarative part of function begins original_squared NUMBER; -- Declarative part of function ends -- Executable part of function begins BEGIN original_squared := original * original; RETURN original_squared; -- RETURN statement -- Exception-handling part of function (optional) goes here END; -- Executable part of function ends -- Function declaration and definition ends -- Declarative part of block ends -- Executable part of block begins BEGIN DBMS_OUTPUT.PUT_LINE(square(100)); -- Function invocation END; -- Executable part of block ends /
In a block, you can create multiple nested subprograms. If they invoke each other, you need forward declaration, because a subprogram must be declared before it can be invoked. With forward declaration, you declare a subprogram, but do not define it until after you have defined the other subprograms that invoke it. A forward declaration and its corresponding definition must appear in the same block.
The block in Example 8-3 creates two procedures that invoke each other.
Example 8-3 Creating Nested Subprograms that Invoke Each Other
DECLARE -- Declare proc1 (forward declaration): PROCEDURE proc1(number1 NUMBER); -- Declare and define proc 2: PROCEDURE proc2(number2 NUMBER) IS BEGIN proc1(number2); END; -- Define proc 1: PROCEDURE proc1(number1 NUMBER) IS BEGIN proc2 (number1); END; BEGIN NULL; END; /
A subprogram heading can declare formal parameters. Each formal parameter declaration can specify a mode and a default value. When you invoke the subprogram, you can pass actual parameters to it.
Topics:
Formal parameters are the variables declared in the subprogram header and referenced in its execution part. Actual parameters are the variables or expressions that you pass to the subprogram when you invoke it. Corresponding formal and actual parameters must have compatible data types.
A good programming practice is to use different names for formal and actual parameters, as in Example 8-4.
Example 8-4 Formal Parameters and Actual Parameters
DECLARE emp_num NUMBER(6) := 120; bonus NUMBER(6) := 100; merit NUMBER(4) := 50; PROCEDURE raise_salary ( emp_id NUMBER, -- formal parameter amount NUMBER -- formal parameter ) IS BEGIN UPDATE employees SET salary = salary + amount WHERE employee_id = emp_id; END raise_salary; BEGIN raise_salary(emp_num, bonus); -- actual parameters raise_salary(emp_num, merit + bonus); -- actual parameters END; /
When you invoke a subprogram, PL/SQL evaluates each actual parameter and assigns its value to the corresponding formal parameter. If necessary, PL/SQL implicitly converts the data type of the actual parameter to the data type of the corresponding formal parameter before the assignment (this is why corresponding formal and actual parameters must have compatible data types). For information about implicit conversion, see Implicit Conversion.
A good programming practice is to avoid implicit conversion, either by using explicit conversion (explained in Explicit Conversion) or by declaring the variables that you intend to use as actual parameters with the same data types as their corresponding formal parameters. For example, suppose that pkg
has this specification:
PACKAGE pkg IS
PROCEDURE s (n IN PLS_INTEGER);
END pkg;
The following invocation of pkg
.s
avoids implicit conversion:
DECLARE
y PLS_INTEGER :=1;
BEGIN
pkg.s(y);
END;
The following invocation of pkg
.s
causes implicit conversion:
DECLARE
y INTEGER :=1;
BEGIN
pkg.s(y);
END;
Note:
The specifications of many packages and types that Oracle supplies declare formal parameters with the following notation:i1 IN VARCHAR2 CHARACTER SET ANY_CS i2 IN VARCHAR2 CHARACTER SET i1%CHARSET
Do not use this notation when declaring your own formal or actual parameters. It is reserved for Oracle implementation of the supplied packages types.
Parameter modes define the action of formal parameters. The three parameter modes are IN
(the default), OUT
, and IN
OUT
.
Any parameter mode can be used with any subprogram. Avoid using the OUT
and IN
OUT
modes with functions. To have a function return multiple values is poor programming practice. Also, make functions free from side effects, which change the values of variables not local to the subprogram.
Topics:
An IN
parameter lets you pass a value to the subprogram being invoked. Inside the subprogram, an IN
parameter acts like a constant. It cannot be assigned a value.
You can pass a constant, literal, initialized variable, or expression as an IN parameter.
An IN
parameter can be initialized to a default value, which is used if that parameter is omitted from the subprogram call. For more information, see Specifying Default Values for Subprogram Parameters.
An OUT
parameter returns a value to the caller of a subprogram. Inside the subprogram, an OUT
parameter acts like a variable. You can change its value and reference the value after assigning it, as in Example 8-5.
DECLARE emp_num NUMBER(6) := 120; bonus NUMBER(6) := 50; emp_last_name VARCHAR2(25); PROCEDURE raise_salary (emp_id IN NUMBER, amount IN NUMBER, emp_name OUT VARCHAR2) IS BEGIN UPDATE employees SET salary = salary + amount WHERE employee_id = emp_id; SELECT last_name INTO emp_name FROM employees WHERE employee_id = emp_id; END raise_salary; BEGIN raise_salary(emp_num, bonus, emp_last_name); DBMS_OUTPUT.PUT_LINE ('Salary was updated for: ' || emp_last_name); END; /
You must pass a variable, not a constant or an expression, to an OUT
parameter. Its previous value is lost unless you specify the NOCOPY
keyword or the subprogram exits with an unhandled exception. See Specifying Default Values for Subprogram Parameters.
The initial value of an OUT
parameter is NULL
; therefore, the data type of an OUT
parameter cannot be a subtype defined as NOT
NULL
, such as the built-in subtype NATURALN
or POSITIVEN
. Otherwise, when you invoke the subprogram, PL/SQL raises VALUE_ERROR
.
Before exiting a subprogram, assign values to all OUT
formal parameters. Otherwise, the corresponding actual parameters will be null. If you exit successfully, PL/SQL assigns values to the actual parameters. If you exit with an unhandled exception, PL/SQL does not assign values to the actual parameters.
An IN
OUT
parameter passes an initial value to a subprogram and returns an updated value to the caller. It can be assigned a value and its value can be read. Typically, an IN
OUT
parameter is a string buffer or numeric accumulator, that is read inside the subprogram and then updated.
The actual parameter that corresponds to an IN
OUT
formal parameter must be a variable, not a constant or an expression.
If you exit a subprogram successfully, PL/SQL assigns values to the actual parameters. If you exit with an unhandled exception, PL/SQL does not assign values to the actual parameters.
Table 8-1 summarizes the characteristics of parameter modes.
IN | OUT | IN OUT |
---|---|---|
The default |
Must be specified |
Must be specified |
Passes a value to the subprogram |
Returns a value to the caller |
Passes an initial value to the subprogram and returns an updated value to the caller |
Formal parameter acts like a constant |
Formal parameter acts like an uninitialized variable |
Formal parameter acts like an initialized variable |
Formal parameter cannot be assigned a value |
Formal parameter must be assigned a value |
Formal parameter should be assigned a value |
Actual parameter can be a constant, initialized variable, literal, or expression |
Actual parameter must be a variable |
Actual parameter must be a variable |
Actual parameter is passed by reference (the caller passes the subprogram a pointer to the value) |
Actual parameter is passed by value (the subprogram passes the caller a copy of the value) unless |
Actual parameter is passed by value (the caller passes the subprogram a copy of the value and the subprogram passes the caller a copy of the value) unless |
By initializing formal IN
parameters to default values, you can pass different numbers of actual parameters to a subprogram, accepting the default values for omitted actual parameters. You can also add new formal parameters without having to change every call to the subprogram.
If an actual parameter is omitted, the default value of its corresponding formal parameter is used.
You cannot skip a formal parameter by omitting its actual parameter. To omit the first parameter and specify the second, use named notation (see Passing Actual Subprogram Parameters with Positional, Named, or Mixed Notation).
You cannot assign NULL
to an uninitialized formal parameter by omitting its actual parameter. You must either assign NULL
as a default value or pass NULL
explicitly.
Example 8-6 illustrates the use of default values for subprogram parameters.
Example 8-6 Procedure with Default Parameter Values
DECLARE emp_num NUMBER(6) := 120; bonus NUMBER(6); merit NUMBER(4); PROCEDURE raise_salary (emp_id IN NUMBER, amount IN NUMBER DEFAULT 100, extra IN NUMBER DEFAULT 50) IS BEGIN UPDATE employees SET salary = salary + amount + extra WHERE employee_id = emp_id; END raise_salary; BEGIN -- Same as raise_salary(120, 100, 50) raise_salary(120); -- Same as raise_salary(120, 100, 25) raise_salary(emp_num, extra => 25); END; /
If the default value of a formal parameter is an expression, and you provide a corresponding actual parameter when you invoke the subprogram, the expression is not evaluated, as in Example 8-7.
Example 8-7 Formal Parameter with Expression as Default Value
DECLARE cnt pls_integer := 0; FUNCTION dflt RETURN pls_integer IS BEGIN cnt := cnt + 1; RETURN 42; END dflt; -- Default is expression PROCEDURE p(i IN pls_integer DEFAULT dflt()) IS BEGIN DBMS_Output.Put_Line(i); END p; BEGIN FOR j IN 1..5 LOOP p(j); -- Actual parameter is provided END loop; DBMS_Output.Put_Line('cnt: '||cnt); p(); -- Actual parameter is not provided DBMS_Output.Put_Line('cnt: '||cnt); END;
The output of Example 8-7 is:
1 2 3 4 5 Cnt: 0 42 Cnt: 1
When invoking a subprogram, you can specify the actual parameters using either positional, named, or mixed notation. Table 8-2 compares these notations.
Table 8-2 PL/SQL Subprogram Parameter Notations
Notation | Description | Usage Notes |
---|---|---|
Positional |
Specify the same parameters in the same order as the procedure declares them. |
Compact and readable, but has these disadvantages:
|
Named |
Specify the name and value of each parameter, using the association operator, |
More verbose than positional notation, but easier to read and maintain. You can sometimes avoid changing your code if the procedure's parameter list changes (for example, if parameters are reordered or a new optional parameter is added). Safer than positional notation when you invoke an API that you did not define, or define an API for others to use. |
Mixed |
Start with positional notation, then use named notation for the remaining parameters. |
Recommended when you invoke procedures that have required parameters followed by optional parameters, and you must specify only a few of the optional parameters. |
Example 8-8 shows equivalent subprogram calls using positional, named, and mixed notation.
Example 8-8 Subprogram Calls Using Positional, Named, and Mixed Notation
SQL> DECLARE 2 emp_num NUMBER(6) := 120; 3 bonus NUMBER(6) := 50; 4 PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER) IS 5 BEGIN 6 UPDATE employees SET salary = 7 salary + amount WHERE employee_id = emp_id; 8 END raise_salary; 9 BEGIN 10 -- Positional notation: 11 raise_salary(emp_num, bonus); 12 -- Named notation (parameter order is insignificant): 13 raise_salary(amount => bonus, emp_id => emp_num); 14 raise_salary(emp_id => emp_num, amount => bonus); 15 -- Mixed notation: 16 raise_salary(emp_num, amount => bonus); 17 END; 18 / PL/SQL procedure successfully completed. SQL> REM Clean up SQL> ROLLBACK; Rollback complete. SQL> SQL> CREATE OR REPLACE FUNCTION compute_bonus (emp_id NUMBER, bonus NUMBER) 2 RETURN NUMBER 3 IS 4 emp_sal NUMBER; 5 BEGIN 6 SELECT salary INTO emp_sal 7 FROM employees 8 WHERE employee_id = emp_id; 9 RETURN emp_sal + bonus; 10 END compute_bonus; 11 / Function created.
SQL> SELECT compute_bonus(120, 50) FROM DUAL; -- positional 2 SELECT compute_bonus(bonus => 50, emp_id => 120) FROM DUAL; -- named 3 SELECT compute_bonus(120, bonus => 50) FROM DUAL; -- mixed 4 SQL>
PL/SQL lets you overload local subprograms, packaged subprograms, and type methods. You can use the same name for several different subprograms as long as their formal parameters differ in number, order, or data type family.
Example 8-9 defines two subprograms with the same name, initialize
. The procedures initialize different types of collections. Because the processing in these two procedures is the same, it is logical to give them the same name.
You can place the two initialize
procedures in the same block, subprogram, package, or object type. PL/SQL determines which procedure to invoke by checking their formal parameters. The version of initialize
that PL/SQL uses depends on whether you invoke the procedure with a date_tab_typ
or num_tab_typ
parameter.
Example 8-9 Overloading a Subprogram Name
DECLARE TYPE date_tab_typ IS TABLE OF DATE INDEX BY PLS_INTEGER; TYPE num_tab_typ IS TABLE OF NUMBER INDEX BY PLS_INTEGER; hiredate_tab date_tab_typ; sal_tab num_tab_typ; PROCEDURE initialize (tab OUT date_tab_typ, n INTEGER) IS BEGIN FOR i IN 1..n LOOP tab(i) := SYSDATE; END LOOP; END initialize; PROCEDURE initialize (tab OUT num_tab_typ, n INTEGER) IS BEGIN FOR i IN 1..n LOOP tab(i) := 0.0; END LOOP; END initialize; BEGIN initialize(hiredate_tab, 50); -- Invokes first (date_tab_typ) version initialize(sal_tab, 100); -- Invokes second (num_tab_typ) version END; /
For an example of an overloaded procedure in a package, see Example 10-3.
Topics:
You can overload subprograms if their formal parameters differ only in numeric data type. This technique is useful in writing mathematical application programming interfaces (APIs), because several versions of a function can use the same name, and each can accept a different numeric type. For example, a function that accepts BINARY_FLOAT
might be faster, while a function that accepts BINARY_DOUBLE
might provide more precision.
To avoid problems or unexpected results passing parameters to such overloaded subprograms:
Ensure that the expected version of a subprogram is invoked for each set of expected parameters. For example, if you have overloaded functions that accept BINARY_FLOAT
and BINARY_DOUBLE
, which is invoked if you pass a VARCHAR2
literal such as '5.0'?
Qualify numeric literals and use conversion functions to make clear what the intended parameter types are. For example, use literals such as 5
.0f
(for BINARY_FLOAT
), 5
.0d
(for BINARY_DOUBLE
), or conversion functions such as TO_BINARY_FLOAT
, TO_BINARY_DOUBLE
, and TO_NUMBER
.
PL/SQL looks for matching numeric parameters in this order:
PLS_INTEGER
(or BINARY_INTEGER
, an identical data type)
NUMBER
BINARY_FLOAT
BINARY_DOUBLE
A VARCHAR2
value can match a NUMBER
, BINARY_FLOAT
, or BINARY_DOUBLE
parameter.
PL/SQL uses the first overloaded subprogram that matches the supplied parameters. For example, the SQRT
function takes a single parameter. There are overloaded versions that accept a NUMBER
, a BINARY_FLOAT
, or a BINARY_DOUBLE
parameter. If you pass a PLS_INTEGER
parameter, the first matching overload is the one with a NUMBER
parameter.
The SQRT
function that takes a NUMBER
parameter is likely to be slowest. To use a faster version, use the TO_BINARY_FLOAT
or TO_BINARY_DOUBLE
function to convert the parameter to another data type before passing it to the SQRT
function.
If PL/SQL must convert a parameter to another data type, it first tries to convert it to a higher data type. For example:
The ATAN2
function takes two parameters of the same type. If you pass parameters of different types—for example, one PLS_INTEGER
and one BINARY_FLOAT
—PL/SQL tries to find a match where both parameters use the higher type. In this case, that is the version of ATAN2
that takes two BINARY_FLOAT
parameters; the PLS_INTEGER
parameter is converted upwards.
A function takes two parameters of different types. One overloaded version takes a PLS_INTEGER
and a BINARY_FLOAT
parameter. Another overloaded version takes a NUMBER
and a BINARY_DOUBLE
parameter. If you invoke this function and pass two NUMBER
parameters, PL/SQL first finds the overloaded version where the second parameter is BINARY_FLOAT
. Because this parameter is a closer match than the BINARY_DOUBLE
parameter in the other overload, PL/SQL then looks downward and converts the first NUMBER
parameter to PLS_INTEGER
.
You cannot overload the following subprograms:
Standalone subprograms
Subprograms whose formal parameters differ only in mode; for example:
PACKAGE pkg IS PROCEDURE s (p IN VARCHAR2); PROCEDURE s (p OUT VARCHAR2); END pkg;
Subprograms whose formal parameters differ only in subtype; for example:
PACKAGE pkg IS PROCEDURE s (p INTEGER); PROCEDURE s (p REAL); END pkg;
INTEGER
and REAL
are subtypes of NUMBER
, so they belong to the same data type family.
Functions that differ only in return value data type, even if the data types are in different families; for example:
PACKAGE pkg IS FUNCTION f (p INTEGER) RETURN BOOLEAN; FUNCTION f (p INTEGER) RETURN INTEGER; END pkg;
The PL/SQL compiler catches overloading errors as soon as it can determine that it will be unable to tell which subprogram was invoked. When subprograms have identical headings, the compiler catches the overloading error when you try to compile the subprograms themselves (if they are local) or when you try to compile the package specification that declares them (if they are packaged); otherwise, it catches the error when you try to compile an ambiguous invocation of a subprogram.
When you try to compile the package specification in Example 8-10, which declares subprograms with identical headings, you get compile-time error PLS-00305.
Example 8-10 Package Specification with Overloading Violation that Causes Compile-Time Error
PACKAGE pkg1 IS PROCEDURE s (p VARCHAR2); PROCEDURE s (p VARCHAR2); END pkg1;
Although the package specification in Example 8-11 violates the rule that you cannot overload subprograms whose formal parameters differ only in subtype, you can compile it without error.
Example 8-11 Package Specification with Overloading Violation that Compiles Without Error
PACKAGE pkg2 IS SUBTYPE t1 IS VARCHAR2(10); SUBTYPE t2 IS VARCHAR2(10); PROCEDURE s (p t1); PROCEDURE s (p t2); END pkg2;
However, when you try to compile an invocation of pkg2
.s
, such as the one in Example 8-12, you get compile-time error PLS-00307.
Example 8-12 Invocation of Improperly Overloaded Subprogram
PROCEDURE p IS
a pkg.t1 := 'a';
BEGIN
pkg.s(a) -- Causes compile-time error PLS-00307;
END p;
Suppose that you correct the overloading violation in Example 8-11 by giving the formal parameters of the overloaded subprograms different names, as follows:
PACKAGE pkg2 IS SUBTYPE t1 IS VARCHAR2(10); SUBTYPE t2 IS VARCHAR2(10); PROCEDURE s (p1 t1); PROCEDURE s (p2 t2); END pkg2;
Now you can compile an invocation of pkg2
.s
without error if you specify the actual parameter with named notation. For example:
PROCEDURE p IS
a pkg.t1 := 'a';
BEGIN
pkg.s(p1=>a); -- Compiles without error
END p;
If you specify the actual parameter with positional notation, as in Example 8-12, you still get compile-time error PLS-00307.
The package specification in Example 8-13 violates no overloading rules and compiles without error. However, you can still get compile-time error PLS-00307 when invoking its overloaded procedure, as in the second invocation in Example 8-14.
Figure 8-1 shows how the PL/SQL compiler resolves subprogram calls. When the compiler encounters a subprogram call, it tries to find a declaration that matches the call. The compiler searches first in the current scope and then, if necessary, in successive enclosing scopes. The compiler looks more closely when it finds one or more subprogram declarations in which the subprogram name matches the name of the called subprogram.
To resolve a call among possibly like-named subprograms at the same level of scope, the compiler must find an exact match between the actual and formal parameters. They must match in number, order, and data type (unless some formal parameters were assigned default values). If no match is found or if multiple matches are found, the compiler generates a semantic error.
Figure 8-1 How the PL/SQL Compiler Resolves Calls
Example 8-15 invokes the enclosing procedure swap
from the function balance
, generating an error because neither declaration of swap
within the current scope matches the procedure call.
Example 8-15 Resolving PL/SQL Procedure Names
DECLARE PROCEDURE swap (n1 NUMBER, n2 NUMBER) IS num1 NUMBER; num2 NUMBER; FUNCTION balance (bal NUMBER) RETURN NUMBER IS x NUMBER := 10; PROCEDURE swap (d1 DATE, d2 DATE) IS BEGIN NULL; END; PROCEDURE swap (b1 BOOLEAN, b2 BOOLEAN) IS BEGIN NULL; END; BEGIN DBMS_OUTPUT.PUT_LINE('The following raises an exception'); -- swap(num1, num2); -- wrong number or types of arguments in call to 'SWAP' RETURN x; END balance; BEGIN NULL;END swap; BEGIN NULL; END; /
The AUTHID
property of a stored PL/SQL unit affects the name resolution and privilege checking of SQL statements that the unit issues at run time. The AUTHID
property does not affect compilation, and has no meaning for units that have no code, such as collection types.
AUTHID
property values are exposed in the static data dictionary view *_PROCEDURES
. For units for which AUTHID
has meaning, the view shows the value CURRENT_USER
or DEFINER
; for other units, the view shows NULL
.
For stored PL/SQL units that you create or alter with the following statements, you can use the optional AUTHID
clause to specify either CURRENT_USER
or DEFINER
. The default is DEFINER
.
A unit whose AUTHID
value is CURRENT_USER
is called an invoker's rights unit, or IR unit. A unit whose AUTHID
value is DEFINER
is called a definer's rights unit, or DR unit. An anonymous block always behaves like an IR unit. A trigger or view always behaves like a DR unit.
The AUTHID
property of a unit determines whether the unit is IR or DR, and it affects both name resolution and privilege checking at run time:
The context for name resolution is CURRENT_SCHEMA
.
The privileges checked are those of the CURRENT_USER
and the enabled roles.
When a session starts, CURRENT_SCHEMA
has the value of the schema owned by SESSION_USER
, and CURRENT_USER
has the same value as SESSION_USER
. (To get the current value of CURRENT_SCHEMA
, CURRENT_USER
, or SESSION_USER
, use the SYS_CONTEXT
function, documented in Oracle Database SQL Language Reference.)
CURRENT_SCHEMA
can be changed during the session with the SQL statement ALTER
SESSION
SET
CURRENT_SCHEMA
. CURRENT_USER
cannot be changed programmatically, but it might change when a PL/SQL unit or a view is pushed onto, or popped from, the call stack.
Note:
Oracle recommends against issuingALTER
SESSION
SET
CURRENT_SCHEMA
from within a stored PL/SQL unit.During a server call, when a DR unit is pushed onto the call stack, the database stores the currently enabled roles and the current values of CURRENT_USER
and CURRENT_SCHEMA
. It then changes both CURRENT_USER
and CURRENT_SCHEMA
to the owner of the DR unit, and enables only the role PUBLIC
. (The stored and new roles and values are not necessarily different.) When the DR unit is popped from the call stack, the database restores the stored roles and values. In contrast, when an IR unit is pushed onto, or popped from, the call stack, the values of CURRENT_USER
and CURRENT_SCHEMA
, and the currently enabled roles do not change.
For dynamic SQL statements issued by a PL/SQL unit, name resolution and privilege checking are done only once, at run time. For static SQL statements, name resolution and privilege checking are done twice: first, when the PL/SQL unit is compiled, and then again at run time. At compilation time, the AUTHID
property has no effect—both DR and IR units are treated like DR units. At run time, however, the AUTHID
property determines whether a unit is IR or DR, and the unit is treated accordingly.
Topics:
Scenario: Suppose that you want to create an API whose procedures have unrestricted access to its tables, but you want to prevent ordinary users from selecting table data directly, and from changing it with INSERT
, UPDATE
, and DELETE
statements.
Solution: In a special schema, create the tables and the procedures that comprise the API. By default, each procedure is a DR unit, so you need not specify AUTHID
DEFINER
when you create it. To other users, grant the EXECUTE
privilege, but do not grant any privileges that allow data access.
Scenario: Suppose that you want to write a PL/SQL procedure that presents compilation errors to a developer. The procedure will join the static data dictionary views ALL_SOURCE
and ALL_ERRORS
and use the procedure DBMS_OUTPUT
.PUT_LINE
to show a window of numbered source lines around each error, following the list of errors for that window. You want all developers to be able to execute the procedure, and you want the procedure to treat each developer as the CURRENT_USER
with respect to ALL_SOURCE
and ALL_ERRORS
.
Solution: When you create the procedure, specify AUTHID
CURRENT_USER
. Grant the EXECUTE
privilege to PUBLIC
. Because the procedure is an IR unit, ALL_SOURCE
and ALL_ERRORS
will operate from the perspective of the user who invokes the procedure.
Note:
Another solution is to make the procedure a DR unit and grant its owner theSELECT
privilege on both DBA_SOURCE
and DBA_ERRORS
. However, this solution is harder to program, and far harder to audit with respect to the criterion that a user must never see source code for units for which he or she does not have the EXECUTE
privilege.The SQL command SET
ROLE
succeeds only if there are no DR units on the call stack. If at least one DR unit is on the call stack, issuing the SET
ROLE
command causes ORA-06565.
Note:
To execute theSET
ROLE
command from PL/SQL, you must use dynamic SQL, preferably the EXECUTE
IMMEDIATE
statement. For information about this statement, see Using the EXECUTE IMMEDIATE Statement.The PL/SQL compiler must resolve all references to tables and other objects at compile time. The owner of an IR subprogram must have objects in the same schema with the right names and columns, even if they do not contain any data. At run time, the corresponding objects in the invoker's schema must have matching definitions. Otherwise, you get an error or unexpected results, such as ignoring table columns that exist in the invoker's schema but not in the schema that contains the subprogram.
Sometimes, the run-time name resolution rules for an IR unit (that cause different invocations to resolve the same unqualified name to different objects) are not desired. Rather, it is required that a specific object be used on every invocation. Nevertheless, an IR unit is needed for other reasons. For example, it might be critical that privileges are evaluated with respect to the CURRENT_USER
. Under these circumstances, qualify the name with the schema that owns the object.
Notice that an unqualified name that intends to denote a public synonym is exposed to the risk of capture if the schema of the CURRENT_USER
has a colliding name. A public synonym can be qualified with "PUBLIC". You must enclose PUBLIC in double quotation marks. For example:
SELECT sysdate INTO today FROM "PUBLIC".DUAL;
Note:
Oracle recommends against issuing the SQL statementALTER
SESSION
SET
CURRENT_SCHEMA
from within a stored PL/SQL unit.For IR subprograms executed within a view expression, the user who created the view, not the user who is querying the view, is considered to be the current user. This rule also applies to database triggers.
Note:
IfSYS_CONTEXT
is used directly in the defining SQL statement of a view, then the value it returns for CURRENT_USER
is the querying user and not the owner of the view.You can create a database link to use invoker's rights:
CREATE DATABASE LINK link_name CONNECT TO CURRENT_USER USING connect_string;
A current-user link lets you connect to a remote database as another user, with that user's privileges. To connect, the database uses the username of the current user (who must be a global user). Suppose an IR subprogram owned by user OE
references the following database link. If global user HR
invokes the subprogram, it connects to the Dallas database as user HR
, who is the current user.
CREATE DATABASE LINK dallas CONNECT TO CURRENT_USER USING ...
If it were a definer's rights subprogram, the current user would be OE
, and the subprogram would connect to the Dallas database as global user OE
.
To define object types for use in any schema, specify the AUTHID
CURRENT_USER
clause. For information about object types, see Oracle Database Object-Relational Developer's Guide.
Suppose that user HR
creates the object type in Example 8-16.
Example 8-16 Creating an Object Type with AUTHID CURRENT USER
CREATE TYPE person_typ AUTHID CURRENT_USER AS OBJECT ( person_id NUMBER, person_name VARCHAR2(30), person_job VARCHAR2(10), STATIC PROCEDURE new_person_typ ( person_id NUMBER, person_name VARCHAR2, person_job VARCHAR2, schema_name VARCHAR2, table_name VARCHAR2), MEMBER PROCEDURE change_job (SELF IN OUT NOCOPY person_typ, new_job VARCHAR2) ); / CREATE TYPE BODY person_typ AS STATIC PROCEDURE new_person_typ ( person_id NUMBER, person_name VARCHAR2, person_job VARCHAR2, schema_name VARCHAR2, table_name VARCHAR2) IS sql_stmt VARCHAR2(200); BEGIN sql_stmt := 'INSERT INTO ' || schema_name || '.' || table_name || ' VALUES (HR.person_typ(:1, :2, :3))'; EXECUTE IMMEDIATE sql_stmt USING person_id, person_name, person_job; END; MEMBER PROCEDURE change_job (SELF IN OUT NOCOPY person_typ, new_job VARCHAR2) IS BEGIN person_job := new_job; END; END; /
Then user HR
grants the EXECUTE
privilege on object type person_typ
to user OE
:
GRANT EXECUTE ON person_typ TO OE;
Finally, user OE
creates an object table to store objects of type person_typ
, then invokes procedure new_person_typ
to populate the table:
CREATE TABLE person_tab OF hr.person_typ; BEGIN hr.person_typ.new_person_typ(1001, 'Jane Smith', 'CLERK', 'oe', 'person_tab'); hr.person_typ.new_person_typ(1002, 'Joe Perkins', 'SALES','oe', 'person_tab'); hr.person_typ.new_person_typ(1003, 'Robert Lange', 'DEV', 'oe', 'person_tab'); 'oe', 'person_tab'); END; /
The calls succeed because the procedure executes with the privileges of its current user (OE
), not its owner (HR
).
For subtypes in an object type hierarchy, the following rules apply:
If a subtype does not explicitly specify an AUTHID
clause, it inherits the AUTHID
of its supertype.
If a subtype does specify an AUTHID
clause, its AUTHID
must match the AUTHID
of its supertype. Also, if the AUTHID
is DEFINER
, both the supertype and subtype must have been created in the same schema.
An IR instance method executes with the privileges of the invoker, not the creator of the instance. Suppose that person_typ
is an IR object type as created in Example 8-16, and that user HR
creates p1
, an object of type person_typ
. If user OE
invokes instance method change_job
to operate on object p1
, the current user of the method is OE
, not HR
, as shown in Example 8-17.
Example 8-17 Invoking an IR Instance Methods
-- OE creates a procedure that invokes change_job CREATE PROCEDURE reassign (p IN OUT NOCOPY hr.person_typ, new_job VARCHAR2) AS BEGIN p.change_job(new_job); -- executes with the privileges of oe END; / -- OE grants EXECUTE to HR on procedure reassign GRANT EXECUTE ON reassign to HR; -- HR passes a person_typ object to the procedure reassign DECLARE p1 person_typ; BEGIN p1 := person_typ(1004, 'June Washburn', 'SALES'); oe.reassign(p1, 'CLERK'); -- current user is oe, not hr END; /
A recursive subprogram is one that invokes itself. Each recursive call creates a new instance of any items declared in the subprogram, including parameters, variables, cursors, and exceptions. Likewise, new instances of SQL statements are created at each level in the recursive descent.
Be careful where you place a recursive call. If you place it inside a cursor FOR
loop or between OPEN
and CLOSE
statements, another cursor is opened at each call, which might exceed the limit set by the database initialization parameter OPEN_CURSORS
.
There must be at least two paths through a recursive subprogram: one that leads to the recursive call and one that does not. At least one path must lead to a terminating condition. Otherwise, the recursion continues until PL/SQL runs out of memory and raises the predefined exception STORAGE_ERROR
.
Recursion is a powerful technique for simplifying the design of algorithms. Basically, recursion means self-reference. In a recursive mathematical sequence, each term is derived by applying a formula to preceding terms. The Fibonacci sequence (0, 1, 1, 2, 3, 5, 8, 13, 21, ...), is an example. Each term in the sequence (after the second) is the sum of the two terms that immediately precede it.
In a recursive definition, something is defined as simpler versions of itself. Consider the definition of n factorial (n!), the product of all integers from 1 to n:
n! = n * (n - 1)!
Although PL/SQL is a powerful, flexible language, some tasks are more easily done in another language. Low-level languages such as C are very fast. Widely used languages such as Java have reusable libraries for common design patterns.
You can use PL/SQL call specifications to invoke external subprograms written in other languages, making their capabilities and libraries available from PL/SQL. For example, you can invoke Java stored procedures from any PL/SQL block, subprogram, or package. For more information about Java stored procedures, see Oracle Database Java Developer's Guide.
If the following Java class is stored in the database, it can be invoked as shown in Example 8-18.
import java.sql.*; import oracle.jdbc.driver.*; public class Adjuster { public static void raiseSalary (int empNo, float percent) throws SQLException { Connection conn = new OracleDriver().defaultConnection(); String sql = "UPDATE employees SET salary = salary * ? WHERE employee_id = ?"; try { PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setFloat(1, (1 + percent / 100)); pstmt.setInt(2, empNo); pstmt.executeUpdate(); pstmt.close(); } catch (SQLException e) {System.err.println(e.getMessage());} } }
The class Adjuster
has one method, which raises the salary of an employee by a given percentage. Because raiseSalary
is a void
method, you publish it as a procedure using the call specification shown in Example 8-18 and then can invoke the procedure raise_salary
from an anonymous PL/SQL block.
Example 8-18 Invoking an External Procedure from PL/SQL
CREATE OR REPLACE PROCEDURE raise_salary (empid NUMBER, pct NUMBER) AS LANGUAGE JAVA NAME 'Adjuster.raiseSalary(int, float)'; / DECLARE emp_id NUMBER := 120; percent NUMBER := 10; BEGIN -- get values for emp_id and percent raise_salary(emp_id, percent); -- invoke external subprogram END; /
Java call specifications cannot be declared as nested procedures, but can be specified in object type specifications, object type bodies, PL/SQL package specifications, PL/SQL package bodies, and as top level PL/SQL procedures and functions.
Example 8-19 shows a call to a Java function from a PL/SQL procedure.
Example 8-19 Invoking a Java Function from PL/SQL
-- the following invalid nested Java call spec throws PLS-00999 -- CREATE PROCEDURE sleep (milli_seconds in number) IS -- PROCEDURE java_sleep (milli_seconds IN NUMBER) AS ... -- Create Java call spec, then call from PL/SQL procedure CREATE PROCEDURE java_sleep (milli_seconds IN NUMBER) AS LANGUAGE JAVA NAME 'java.lang.Thread.sleep(long)'; / CREATE PROCEDURE sleep (milli_seconds in number) IS -- the following nested PROCEDURE spec is not legal -- PROCEDURE java_sleep (milli_seconds IN NUMBER) -- AS LANGUAGE JAVA NAME 'java.lang.Thread.sleep(long)'; BEGIN DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.get_time()); java_sleep (milli_seconds); DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.get_time()); END; /
External C subprograms are used to interface with embedded systems, solve engineering problems, analyze data, or control real-time devices and processes. External C subprograms extend the functionality of the database server, and move computation-bound programs from client to server, where they execute faster. For more information about external C subprograms, see Oracle Database Advanced Application Developer's Guide.
The fewer side effects a function has, the better it can be optimized within a query, particularly when the PARALLEL_ENABLE
or DETERMINISTIC
hints are used.
To be callable from SQL statements, a stored function (and any subprograms that it invokes) must obey the following purity rules, which are meant to control side effects:
When invoked from a SELECT
statement or a parallelized INSERT
, UPDATE
, or DELETE
statement, the function cannot modify any database tables.
When invoked from an INSERT
, UPDATE
, or DELETE
statement, the function cannot query or modify any database tables modified by that statement.
When invoked from a SELECT
, INSERT
, UPDATE
, or DELETE
statement, the function cannot execute SQL transaction control statements (such as COMMIT
), session control statements (such as SET
ROLE
), or system control statements (such as ALTER
SYSTEM
). Also, it cannot execute DDL statements (such as CREATE
) because they are followed by an automatic commit.
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 purity rule violations at compile time, use the RESTRICT_REFERENCES
pragma to assert that a function does not read or write database tables or package variables (for syntax, see RESTRICT_REFERENCES Pragma).
In Example 8-20, the RESTRICT_REFERENCES
pragma asserts that packaged function credit_ok
writes no database state (WNDS
) and reads no package state (RNPS
).
Example 8-20 RESTRICT_REFERENCES Pragma
CREATE PACKAGE loans AS
FUNCTION credit_ok RETURN BOOLEAN;
PRAGMA RESTRICT_REFERENCES (credit_ok, WNDS, RNPS);
END loans;
/
A static INSERT
, UPDATE
, or DELETE
statement always violates WNDS
, and if it reads columns, it also violates RNDS
(reads no database state). A dynamic INSERT
, UPDATE
, or DELETE
statement always violates both WNDS
and RNDS
.
To optimize a subprogram call, the PL/SQL compiler can choose between two methods of parameter passing. with the BY
VALUE
method, the value of an actual parameter is passed to the subprogram. With the BY
REFERENCE
method, only a pointer to the value is passed; the actual and formal parameters reference the same item.
The NOCOPY
compiler hint increases the possibility of aliasing (that is, having two different names refer to the same memory location). This can occur when a global variable appears as an actual parameter in a subprogram call and then is referenced within the subprogram. The result is indeterminate because it depends on the method of parameter passing chosen by the compiler.
In Example 8-21, procedure ADD_ENTRY
refers to varray LEXICON
both as a parameter and as a global variable. When ADD_ENTRY
is invoked, the identifiers WORD_LIST
and LEXICON
point to the same varray.
Example 8-21 Aliasing from Passing Global Variable with NOCOPY Hint
DECLARE TYPE Definition IS RECORD ( word VARCHAR2(20), meaning VARCHAR2(200)); TYPE Dictionary IS VARRAY(2000) OF Definition; lexicon Dictionary := Dictionary(); PROCEDURE add_entry (word_list IN OUT NOCOPY Dictionary) IS BEGIN word_list(1).word := 'aardvark'; lexicon(1).word := 'aardwolf'; END; BEGIN lexicon.EXTEND; add_entry(lexicon); DBMS_OUTPUT.PUT_LINE(lexicon(1).word); END; /
The program prints aardwolf
if the compiler obeys the NOCOPY
hint. The assignment to WORD_LIST
is done immediately through a pointer, then is overwritten by the assignment to LEXICON
.
The program prints aardvark
if the NOCOPY
hint is omitted, or if the compiler does not obey the hint. The assignment to WORD_LIST
uses an internal copy of the varray, which is copied back to the actual parameter (overwriting the contents of LEXICON
) when the procedure ends.
Aliasing can also occur when the same actual parameter appears more than once in a subprogram call. In Example 8-22, n2
is an IN
OUT
parameter, so the value of the actual parameter is not updated until the procedure exits. That is why the first PUT_LINE
prints 10 (the initial value of n
) and the third PUT_LINE
prints 20. However, n3
is a NOCOPY
parameter, so the value of the actual parameter is updated immediately. That is why the second PUT_LINE
prints 30.
Example 8-22 Aliasing Passing Same Parameter Multiple Times
DECLARE n NUMBER := 10; PROCEDURE do_something ( n1 IN NUMBER, n2 IN OUT NUMBER, n3 IN OUT NOCOPY NUMBER) IS BEGIN n2 := 20; DBMS_OUTPUT.put_line(n1); -- prints 10 n3 := 30; DBMS_OUTPUT.put_line(n1); -- prints 30 END; BEGIN do_something(n, n, n); DBMS_OUTPUT.put_line(n); -- prints 20 END; /
Because they are pointers, cursor variables also increase the possibility of aliasing. In Example 8-23, after the assignment, emp_cv2
is an alias of emp_cv1
; both point to the same query work area. The first fetch from emp_cv2
fetches the third row, not the first, because the first two rows were already fetched from emp_cv1
. The second fetch from emp_cv2
fails because emp_cv1
is closed.
Example 8-23 Aliasing from Assigning Cursor Variables to Same Work Area
DECLARE TYPE EmpCurTyp IS REF CURSOR; c1 EmpCurTyp; c2 EmpCurTyp; PROCEDURE get_emp_data (emp_cv1 IN OUT EmpCurTyp, emp_cv2 IN OUT EmpCurTyp) IS emp_rec employees%ROWTYPE; BEGIN OPEN emp_cv1 FOR SELECT * FROM employees; emp_cv2 := emp_cv1; FETCH emp_cv1 INTO emp_rec; -- fetches first row FETCH emp_cv1 INTO emp_rec; -- fetches second row FETCH emp_cv2 INTO emp_rec; -- fetches third row CLOSE emp_cv1; DBMS_OUTPUT.put_line('The following raises an invalid cursor'); -- FETCH emp_cv2 INTO emp_rec; -- raises invalid cursor when get_emp_data is invoked END; BEGIN get_emp_data(c1, c2); END; /
The PL/SQL function result caching mechanism provides a language-supported and system-managed means for caching the results of PL/SQL functions in a shared global area (SGA), which is available to every session that runs your application. The caching mechanism is both efficient and easy to use, and it relieves you of the burden of designing and developing your own caches and cache-management policies.
To enable result-caching for a function, use the RESULT_CACHE
clause. When a result-cached function is invoked, the system checks the cache. If the cache contains the result from a previous call to the function with the same parameter values, the system returns the cached result to the invoker and does not reexecute the function body. If the cache does not contain the result, the system executes the function body and adds the result (for these parameter values) to the cache before returning control to the invoker.
Note:
If function execution results in an unhandled exception, the exception result is not stored in the cache.The cache can accumulate very many results—one result for every unique combination of parameter values with which each result-cached function was invoked. If the system needs more memory, it ages out (deletes) one or more cached results.
You can specify the database objects that are used to compute a cached result, so that if any of them are updated, the cached result becomes invalid and must be recomputed. The best candidates for result-caching are functions that are invoked frequently but depend on information that changes infrequently or never.
Topics:
To make a function result-cached, do the following:
In the function declaration, include the option RESULT_CACHE
.
In the function definition:
Include the RESULT_CACHE
clause.
In the optional RELIES_ON
clause, specify any tables or views on which the function results depend.
For the syntax of the RESULT_CACHE
and RELIES_ON
clauses, see Function Declaration and Definition.
In Example 8-24, the package department_pks
declares and then defines a result-cached function, get_dept_info
, which returns the average salary and number of employees in a given department. get_dept_info
depends on the database table EMPLOYEES
.
Example 8-24 Declaration and Definition of Result-Cached Function
-- Package specification CREATE OR REPLACE PACKAGE department_pks IS TYPE dept_info_record IS RECORD (average_salary NUMBER, number_of_employees NUMBER); -- Function declaration FUNCTION get_dept_info (dept_id NUMBER) RETURN dept_info_record RESULT_CACHE; END department_pks; / CREATE OR REPLACE PACKAGE BODY department_pks AS -- Function definition FUNCTION get_dept_info (dept_id NUMBER) RETURN dept_info_record RESULT_CACHE RELIES_ON (EMPLOYEES) IS rec dept_info_record; BEGIN SELECT AVG(SALARY), COUNT(*) INTO rec FROM EMPLOYEES WHERE DEPARTMENT_ID = dept_id; RETURN rec; END get_dept_info; END department_pks; / DECLARE dept_id NUMBER := 50; avg_sal NUMBER; no_of_emp NUMBER; BEGIN avg_sal := department_pks.get_dept_info(50).average_salary; no_of_emp := department_pks.get_dept_info(50).number_of_employees; DBMS_OUTPUT.PUT_LINE('dept_id = ' ||dept_id); DBMS_OUTPUT.PUT_LINE('average_salary = '|| avg_sal); DBMS_OUTPUT.PUT_LINE('number_of_employees = ' ||no_of_emp); END; /
You invoke the function get_dept_info
as you invoke any function. For example, the following call returns the average salary and the number of employees in department number 10:
department_pks.get_dept_info(10);
The following call returns only the average salary in department number 10:
department_pks.get_dept_info(10).average_salary;
If the result for get_dept_info(10)
is already in the result cache, the result is returned from the cache; otherwise, the result is computed and added to the cache. Because the RELIES_ON
clause specifies EMPLOYEES
, any update to EMPLOYEES
invalidates all cached results for department_pks.get_dept_info
, relieving you of programming cache invalidation logic everywhere that EMPLOYEES
might change.
When developing an application that uses a result-cached function, make no assumptions about the number of times the body of the function will execute for a given set of parameter values.
Some situations in which the body of a result-cached function executes are:
The first time a session on this database instance invokes the function with these parameter values
When the cached result for these parameter values is invalid
A cached result becomes invalid when any database object specified in the RELIES_ON
clause of the function definition changes.
When the cached results for these parameter values have aged out
If the system needs memory, it might discard the oldest cached values.
When the function bypasses the cache (see Bypassing the Result Cache)
To be result-cached, a function must meet all of the following criteria:
It is not defined in a module that has invoker's rights or in an anonymous block.
It is not a pipelined table function.
It has no OUT
or IN
OUT
parameters.
No IN
parameter has one of the following types:
BLOB
CLOB
NCLOB
REF
CURSOR
Collection
Object
Record
The return type is none of the following:
BLOB
CLOB
NCLOB
REF
CURSOR
Object
Record or PL/SQL collection that contains one of the preceding unsupported return types
It is recommended that a result-cached function also meet the following criteria:
It has no side effects.
For example, it does not modify the database state, or modify the external state by invoking DBMS_OUTPUT
or sending eEmail.
It does not depend on session-specific settings.
For more information, see Making Result-Cached Functions Handle Session-Specific Settings.
It does not depend on session-specific application contexts.
For more information, see Making Result-Cached Functions Handle Session-Specific Application Contexts.
The best candidates for result-caching are functions that are invoked frequently but depend on information that changes infrequently (as might be the case in the first example). Result-caching avoids redundant computations in recursive functions.
Examples:
Consider an application that has configuration parameters that can be set at either the global level, the application level, or the role level. The application stores the configuration information in the following tables:
-- Global Configuration Settings CREATE TABLE global_config_params (name VARCHAR2(20), -- parameter NAME value VARCHAR2(20), -- parameter VALUE PRIMARY KEY (name) ); -- Application-Level Configuration Settings CREATE TABLE app_level_config_params (app_id VARCHAR2(20), -- application ID name VARCHAR2(20), -- parameter NAME value VARCHAR2(20), -- parameter VALUE PRIMARY KEY (app_id, name) ); -- Role-Level Configuration Settings CREATE TABLE role_level_config_params (role_id VARCHAR2(20), -- application (role) ID name VARCHAR2(20), -- parameter NAME value VARCHAR2(20), -- parameter VALUE PRIMARY KEY (role_id, name) );
For each configuration parameter, the role-level setting overrides the application-level setting, which overrides the global setting. To determine which setting applies to a parameter, the application defines the PL/SQL function get_value
. Given a parameter name, application ID, and role ID, get_value
returns the setting that applies to the parameter.
The function get_value
is a good candidate for result-caching if it is invoked frequently and if the configuration information changes infrequently. To ensure that a committed change to global_config_params
, app_level_config_params
, or role_level_config_params
invalidates the cached results of get_value
, include their names in the RELIES_ON
clause.
Example 8-25 shows a possible definition for get_value
.
Example 8-25 Result-Cached Function that Returns Configuration Parameter Setting
CREATE OR REPLACE FUNCTION get_value (p_param VARCHAR2, p_app_id NUMBER, p_role_id NUMBER ) RETURN VARCHAR2 RESULT_CACHE RELIES_ON (role_level_config_params, app_level_config_params, global_config_params ) IS answer VARCHAR2(20); BEGIN -- Is parameter set at role level? BEGIN SELECT value INTO answer FROM role_level_config_params WHERE role_id = p_role_id AND name = p_param; RETURN answer; -- Found EXCEPTION WHEN no_data_found THEN NULL; -- Fall through to following code END; -- Is parameter set at application level? BEGIN SELECT value INTO answer FROM app_level_config_params WHERE app_id = p_app_id AND name = p_param; RETURN answer; -- Found EXCEPTION WHEN no_data_found THEN NULL; -- Fall through to following code END; -- Is parameter set at global level? SELECT value INTO answer FROM global_config_params WHERE name = p_param; RETURN answer; END;
A recursive function for finding the nth term of a Fibonacci series that mirrors the mathematical definition of the series might do many redundant computations. For example, to evaluate fibonacci(7)
, the function must compute fibonacci(6)
and fibonacci(5)
. To compute fibonacci(6)
, the function must compute fibonacci(5)
and fibonacci(4)
. Therefore, fibonacci(5)
and several other terms are computed redundantly. Result-caching avoids these redundant computations. A RELIES_ON
clause is unnecessary.
CREATE OR REPLACE FUNCTION fibonacci (n NUMBER)
RETURN NUMBER RESULT_CACHE IS
BEGIN
IF (n =0) OR (n =1) THEN
RETURN 1;
ELSE
RETURN fibonacci(n - 1) + fibonacci(n - 2);
END IF;
END;
/
Topics:
Making Result-Cached Functions Handle Session-Specific Settings
Making Result-Cached Functions Handle Session-Specific Application Contexts
Hot-Patching PL/SQL Units on Which Result-Cached Functions Depend
Each time a result-cached function is invoked with different parameter values, those parameters and their result are stored in the cache. Subsequently, when the same function is invoked with the same parameter values (that is, when there is a cache hit), the result is retrieved from the cache, instead of being recomputed.
The rules for parameter comparison for a cache hit differ from the rules for the PL/SQL "equal to" (=) operator, as follows:
Cache Hit Rules | "Equal To" Operator Rules |
---|---|
NULL is the same as NULL |
NULL = NULL evaluates to NULL . |
Non-null scalars are the same if and only if their values are identical; that is, if and only if their values have identical bit patterns on the given platform. For example, CHAR values 'AA' and 'AA ' are not the same. (This rule is stricter than the rule for the "equal to" operator.) |
Non-null scalars can be equal even if their values do not have identical bit patterns on the given platform; for example, CHAR values 'AA' and 'AA ' are equal. |
In some situations, the cache is bypassed. When the cache is bypassed:
The function computes the result instead of retrieving it from the cache.
The result that the function computes is not added to the cache.
Some examples of situations in which the cache is bypassed are:
The cache is unavailable to all sessions.
For example, the database administrator has disabled the use of the result cache during application patching (as in Hot-Patching PL/SQL Units on Which Result-Cached Functions Depend).
A session is performing a DML statement on a table or view that was specified in the RELIES_ON
clause of a result-cached function. The session bypasses the result cache for that function until the DML statement is completed (either committed or rolled back), and then resumes using the cache for that function.
Cache bypass ensures the following:
The user of each session sees his or her own uncommitted changes.
The PL/SQL function result cache has only committed changes that are visible to all sessions, so that uncommitted changes in one session are not visible to other sessions.
If a function depends on settings that might vary from session to session (such as NLS_DATE_FORMAT
and TIME ZONE
), make the function result-cached only if you can modify it to handle the various settings.
Consider the following function:
CREATE OR REPLACE FUNCTION get_hire_date (emp_id NUMBER) RETURN VARCHAR RESULT_CACHE RELIES_ON (HR.EMPLOYEES) IS date_hired DATE; BEGIN SELECT hire_date INTO date_hired FROM HR.EMPLOYEES WHERE EMPLOYEE_ID = emp_id; RETURN TO_CHAR(date_hired); END; /
The preceding function, get_hire_date
, uses the TO_CHAR
function to convert a DATE
item to a VARCHAR
item. The function get_hire_date
does not specify a format mask, so the format mask defaults to the one that NLS_DATE_FORMAT
specifies. If sessions that call get_hire_date
have different NLS_DATE_FORMAT
settings, cached results can have different formats. If a cached result computed by one session ages out, and another session recomputes it, the format might vary even for the same parameter value. If a session gets a cached result whose format differs from its own format, that result will probably be incorrect.
Some possible solutions to this problem are:
Change the return type of get_hire_date
to DATE
and have each session invoke the TO_CHAR
function.
If a common format is acceptable to all sessions, specify a format mask, removing the dependency on NLS_DATE_FORMAT
. For example:
TO_CHAR(date_hired, 'mm/dd/yy');
Add a format mask parameter to get_hire_date
. For example:
CREATE OR REPLACE FUNCTION get_hire_date (emp_id NUMBER, fmt VARCHAR) RETURN VARCHAR RESULT_CACHE RELIES_ON (HR.EMPLOYEES) IS date_hired DATE; BEGIN SELECT hire_date INTO date_hired FROM HR.EMPLOYEES WHERE EMPLOYEE_ID = emp_id; RETURN TO_CHAR(date_hired, fmt); END; /
An application context, which can be either global or session-specific, is a set of attributes and their values. A PL/SQL function depends on session-specific application contexts if it does at least one of the following:
Directly invokes the built-in function SYS_CONTEXT
, which returns the value of a specified attribute in a specified context
Indirectly invokes SYS_CONTEXT
by using Virtual Private Database (VPD) mechanisms for fine-grained security
(For information about VPD, see Oracle Database Security Guide.)
The PL/SQL function result-caching feature does not automatically handle dependence on session-specific application contexts. If you must cache the results of a function that depends on session-specific application contexts, you must pass the application context to the function as a parameter. You can give the parameter a default value, so that not every user must specify it.
In Example 8-27, assume that a table, config_tab
, has a VPD policy that translates this query:
SELECT value FROM config_tab WHERE name = param_name;
To this query:
SELECT value FROM config_tab
WHERE name = param_name
AND app_id = SYS_CONTEXT('Config', 'App_ID');
Example 8-27 Result-Cached Function that Depends on Session-Specific Application Context
CREATE OR REPLACE FUNCTION get_param_value (param_name VARCHAR, appctx VARCHAR DEFAULT SYS_CONTEXT('Config', 'App_ID') ) RETURN VARCHAR RESULT_CACHE RELIES_ON (config_tab) IS rec VARCHAR(2000); BEGIN SELECT value INTO rec FROM config_tab WHERE Name = param_name; END; /
PL/SQL provides the function result cache, but you choose the caching granularity. To understand the concept of granularity, consider the Product_Descriptions
table in the Order Entry (OE
) sample schema:
NAME NULL? TYPE ---------------------- -------- --------------- PRODUCT_ID NOT NULL NUMBER(6) LANGUAGE_ID NOT NULL VARCHAR2(3) TRANSLATED_NAME NOT NULL NVARCHAR2(50) TRANSLATED_DESCRIPTION NOT NULL NVARCHAR2(2000)
The table has the name and description of each product in several languages. The unique key for each row is PRODUCT_ID,LANGUAGE_ID
.
Suppose that you want to define a function that takes a PRODUCT_ID
and a LANGUAGE_ID
and returns the associated TRANSLATED_NAME
. You also want to cache the translated names. Some of the granularity choices for caching the names are:
One name at a time (finer granularity)
One language at a time (coarser granularity)
Table 8-3 Comparison of Finer and Coarser Caching Granularity
Finer Granularity | Coarser Granularity |
---|---|
Each function result corresponds to one logical result. |
Each function result contains many logical subresults. |
Stores only data that is needed at least once. |
Might store data that is never used. |
Each data item ages out individually. |
One aged-out data item ages out the whole set. |
Does not allow bulk loading optimizations. |
Allows bulk loading optimizations. |
In each of the following four examples, the function productName
takes a PRODUCT_ID
and a LANGUAGE_ID
and returns the associated TRANSLATED_NAME
. Each version of productName
caches translated names, but at a different granularity.
In Example 8-28, get_product_name_1
is a result-cached function. Whenever get_product_name_1
is invoked with a different PRODUCT_ID
and LANGUAGE_ID
, it caches the associated TRANSLATED_NAME
. Each call to get_product_name_1
adds at most one TRANSLATED_NAME
to the cache.
Example 8-28 Caching One Name at a Time (Finer Granularity)
CREATE OR REPLACE FUNCTION get_product_name_1 (prod_id NUMBER, lang_id VARCHAR2)
RETURN NVARCHAR2
RESULT_CACHE RELIES_ON (Product_Descriptions)
IS
result VARCHAR2(50);
BEGIN
SELECT translated_name INTO result
FROM Product_Descriptions
WHERE PRODUCT_ID = prod_id
AND LANGUAGE_ID = lang_id;
RETURN result;
END;
In Example 8-29, get_product_name_2
defines a result-cached function, all_product_names
. Whenever get_product_name_2
invokes all_product_names
with a different LANGUAGE_ID
, all_product_names
caches every TRANSLATED_NAME
associated with that LANGUAGE_ID
. Each call to all_product_names
adds every TRANSLATED_NAME
of at most one LANGUAGE_ID
to the cache.
Example 8-29 Caching Translated Names One Language at a Time (Coarser Granularity)
CREATE OR REPLACE FUNCTION get_product_name_2 (prod_id NUMBER, lang_id VARCHAR2)
RETURN NVARCHAR2
IS
TYPE product_names IS TABLE OF NVARCHAR2(50) INDEX BY PLS_INTEGER;
FUNCTION all_product_names (lang_id NUMBER) RETURN product_names
RESULT_CACHE RELIES_ON (Product_Descriptions)
IS
all_names product_names;
BEGIN
FOR c IN (SELECT * FROM Product_Descriptions
WHERE LANGUAGE_ID = lang_id) LOOP
all_names(c.PRODUCT_ID) := c.TRANSLATED_NAME;
END LOOP;
RETURN all_names;
END;
BEGIN
RETURN all_product_names(lang_id)(prod_id);
END;
Cached results are stored in the system global area (SGA). In an Oracle RAC environment, each database instance has a private function result cache, available only to sessions on that instance.
The access pattern and work load of an instance determine the set of results in its private cache; therefore, the private caches of different instances can have different sets of results.
If a required result is missing from the private cache of the local instance, the body of the function executes to compute the result, which is then added to the local cache. The result is not retrieved from the private cache of another instance.
Although each database instance might have its own set of cached results, the mechanisms for handling invalid results are Oracle RAC environment-wide. If results were invalidated only in the local instance's result cache, other instances might use invalid results. For example, consider a result cache of item prices that are computed from data in database tables. If any of these database tables is updated in a way that affects the price of an item, the cached price of that item must be invalidated in every database instance in the Oracle RAC environment.
The PL/SQL function result cache shares its administrative and manageability infrastructure with the Result Cache, which is described in Oracle Database Performance Tuning Guide.
The database administrator can use the following to manage the Result Cache:
RESULT_CACHE_MAX_SIZE
and RESULT_CACHE_MAX_RESULT
initialization parameters
RESULT_CACHE_MAX_SIZE
specifies the maximum amount of SGA memory (in bytes) that the Result Cache can use, and RESULT_CACHE_MAX_RESULT
specifies the maximum percentage of the Result Cache that any single result can use. For more information about these parameters, see Oracle Database Reference and Oracle Database Performance Tuning Guide.
See Also:
Oracle Database Reference for more information about RESULT_CACHE_MAX_SIZE
Oracle Database Reference for more information about RESULT_CACHE_MAX_RESULT
Oracle Database Performance Tuning Guide for more information about Result Cache concepts
DBMS_RESULT_CACHE
package
The DBMS_RESULT_CACHE
package provides an interface to allow the DBA to administer that part of the shared pool that is used by the SQL result cache and the PL/SQL function result cache. For more information about this package, see Oracle Database PL/SQL Packages and Types Reference.
Dynamic performance views:
[G]V$RESULT_CACHE_STATISTICS
[G]V$RESULT_CACHE_MEMORY
[G]V$RESULT_CACHE_OBJECTS
[G]V$RESULT_CACHE_DEPENDENCY
See Oracle Database Reference for more information about [G]V$RESULT_CACHE_STATISTICS
, [G]V$RESULT_CACHE_MEMORY
, [G]V$RESULT_CACHE_OBJECTS
, and [G]V$RESULT_CACHE_DEPENDENCY
.
When you hot-patch a PL/SQL unit on which a result-cached function depends (directly or indirectly), the cached results associated with the result-cached function might not be automatically flushed in all cases.
For example, suppose that the result-cached function P1
.foo()
depends on the packaged subprogram P2
.bar()
. If a new version of the body of package P2
is loaded, the cached results associated with P1
.foo()
are not automatically flushed.
Therefore, this is the recommended procedure for hot-patching a PL/SQL unit:
Put the result cache in bypass mode and flush existing results:
BEGIN DBMS_RESULT_CACHE.Bypass(TRUE); DBMS_RESULT_CACHE.Flush; END; /
In an Oracle RAC environment, perform this step for each database instance.
Patch the PL/SQL code.
Resume using the result cache:
BEGIN DBMS_RESULT_CACHE.Bypass(FALSE); END; /
In an Oracle RAC environment, perform this step for each database instance.