You can include user-written PL/SQL functions in SQL expressions. (You must be using PL/SQL release 2.1 or higher.) By using PL/SQL functions in SQL statements, you can do the following:
PL/SQL functions must be created as top-level functions or declared within a package specification before they can be named within a SQL statement. Stored PL/SQL functions are used in the same manner as built-in Oracle functions (such as SUBSTR or ABS).
PL/SQL functions can be placed wherever an Oracle function can be placed within a SQL statement, or, wherever expressions can occur in SQL. For example, they can be called from the following:
You cannot call stored PL/SQL functions from a CHECK constraint clause of a CREATE or ALTER TABLE statement or use them to specify a default value for a column. These situations require an unchanging definition.
Note:
Unlike functions, which are called as part of an expression, procedures are called as statements. Therefore, PL/SQL procedures are not directly callable from SQL statements. However, functions called from a PL/SQL statement or referenced in a SQL expression can call a PL/SQL procedure.
Use the following syntax to reference a PL/SQL function from SQL:
[[schema.]package.]function_name[@dblink][(param_1...param_n)]
For example, to reference a function you created that is called My_func, in the My_funcs_pkg package, in the Scott schema, that takes two numeric parameters, you could call the following:
SELECT Scott.My_funcs_pkg.My_func(10,20) FROM dual;
If only one of the optional schema or package names is given, then the first identifier can be either a schema name or a package name. For example, to determine whether Payroll in the reference Payroll.Tax_rate is a schema or package name, Oracle Database proceeds as follows:
You can also refer to a stored top-level function using any synonym that you have defined for it.
In SQL statements, the names of database columns take precedence over the names of functions with no parameters. For example, if schema Scott creates the following two objects:
CREATE TABLE Emp_tab(New_sal NUMBER ...); CREATE FUNCTION New_sal RETURN NUMBER IS ...;
Then, in the following two statements, the reference to New_sal refers to the column Emp_tab.New_sal:
SELECT New_sal FROM Emp_tab; SELECT Emp_tab.New_sal FROM Emp_tab;
To access the function new_sal, enter the following:
SELECT Scott.New_sal FROM Emp_tab;
For example, to call the Tax_rate PL/SQL function from schema Scott, run it against the Ss_no and sal columns in Tax_table, and place the results in the variable Income_tax, specify the following:
Note:
You may need to set up data structures similar to the following for certain examples to work: CREATE TABLE Tax_table ( Ss_no NUMBER, Sal NUMBER); CREATE OR REPLACE FUNCTION tax_rate (ssn IN NUMBER, salary IN NUMBER) RETURN NUMBER IS sal_out NUMBER; BEGIN sal_out := salary * 1.1; END;
DECLARE
Tax_id NUMBER;
Income_tax NUMBER;
BEGIN
SELECT scott.tax_rate (Ss_no, Sal)
INTO Income_tax
FROM Tax_table
WHERE Ss_no = Tax_id;
END;
These sample calls to PL/SQL functions are allowed in SQL expressions:
Circle_area(Radius) Payroll.Tax_rate(Empno) scott.Payroll.Tax_rate@boston_server(Dependents, Empno)
To pass any number of arguments to a function, supply the arguments within the parentheses. You must use positional notation; named notation is not supported. For functions that do not accept arguments, use ().
The stored function Gross_pay initializes two of its formal parameters to default values using the DEFAULT clause. For example:
CREATE OR REPLACE FUNCTION Gross_pay
(Emp_id IN NUMBER,
St_hrs IN NUMBER DEFAULT 40,
Ot_hrs IN NUMBER DEFAULT 0) RETURN NUMBER AS
...
When calling Gross_pay from a procedural statement, you can always accept the default value of St_hrs. This is because you can use named notation, which lets you skip parameters. For example:
IF Gross_pay(Eenum, Ot_hrs => Otime) > Pay_limit THEN ...
However, when calling Gross_pay from a SQL expression, you cannot accept the default value of St_hrs, unless you accept the default value of Ot_hrs. This is because you cannot use named notation.
To call a PL/SQL function from SQL, you must either own or have EXECUTE privileges on the function. 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.
To be callable from SQL expressions, a user-defined PL/SQL function must meet the following basic requirements:
For example, the following stored function meets the basic requirements:
Note:
You may need to set up the following data structures for certain examples to work:
CREATE TABLE Payroll(Srate NUMBER, Orate NUMBER, Acctno NUMBER);CREATE FUNCTION Gross_pay (Emp_id IN NUMBER, St_hrs IN NUMBER DEFAULT 40, Ot_hrs IN NUMBER DEFAULT 0) RETURN NUMBER AS 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;
The purity of a stored subprogram (function or procedure) 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 subprogram is called from a SQL query or DML statement.
In releases prior to Oracle8i, Oracle Database leveraged the PL/SQL compiler to enforce restrictions during the compilation of a stored subprogram or a SQL statement. Starting with Oracle8i, the compile-time restrictions were relaxed, and a smaller set of restrictions are enforced during execution.
This change provides uniform support for stored subprograms written in PL/SQL, Java, and C, and it allows programmers the most flexibility possible.
When a SQL statement is run, checks are made to see if it is logically embedded within the execution of an already running SQL statement. This occurs if the statement is run from a trigger or from a subprogram that was in turn called from the already running SQL statement. In these cases, further checks occur to determine if the new SQL statement is safe in the specific context.
The following restrictions are enforced on subprograms:
These restrictions apply regardless of what mechanism is used to run the SQL statement inside the subprogram or trigger. For example:
You can avoid these restrictions if the execution of the new SQL statement is not logically embedded in the context of the already running statement. PL/SQL's autonomous transactions provide one escape (see "Autonomous Transactions" ). 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 the following:
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 FUNCTION F1 (P1 NUMBER) RETURN NUMBER DETERMINISTIC IS BEGIN RETURN P1 * 2; END;
You may place this keyword in the following places:
You should 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 calls to functions that are marked DETERMINISTIC without any other action being required. The following features require that any function used with them be declared DETERMINISTIC:
The preceding functions features attempt to use previously calculated results rather than calling the function when it is possible to do so.
Functions that fall in the following categories should typically be DETERMINISTIC:
Oracle Database cannot require that you should explicitly declare functions in the preceding categories as DETERMINISTIC without breaking existing applications, but the use of the keyword might be a wise choice of style within your application.
Keep the following points in mind when you create DETERMINISTIC functions:
See Also:
Oracle Database SQL Reference for an account of CREATE FUNCTION restrictions
Oracle Database's parallel execution feature divides the work of executing a SQL statement across multiple processes. Functions called from a SQL statement which is run in parallel may have a separate copy run in each of these processes, with each copy called 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 new 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.
For query (SELECT) statements in Oracle Database versions prior to 8.1.5, the parallel query optimization looked to see if a function was noted as RNPS and WNPS in a PRAGMA RESTRICT_REFERENCES declaration; those functions that were marked as both RNPS and WNPS could be run in parallel. Functions defined with a CREATE FUNCTION statement had their code implicitly examined to determine if they were pure enough; parallelized execution might occur even though a pragma cannot be specified on these functions.
For DML statements in Oracle Database versions prior to 8.1.5, the parallelization optimization looked to see if a function was noted as having all four of RNDS, WNDS, RNPS and WNPS specified in a PRAGMA RESTRICT_REFERENCES declaration; those functions that were marked as neither reading nor writing to either the database or package variables could run in parallel. Again, those functions defined with a CREATE FUNCTION statement had their code implicitly examined to determine if they were actually pure enough; parallelized execution might occur even though a pragma cannot be specified on these functions.
Oracle Database versions 8.1.5 and later continue to parallelize those functions that earlier versions recognize as parallelizable. 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 FUNCTION F1 (P1 NUMBER) RETURN NUMBER PARALLEL_ENABLE IS BEGIN RETURN P1 * 2; END;
A PL/SQL function defined with CREATE FUNCTION may 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 calls 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 runtime 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.
In Oracle Database versions prior to 8.1.5 (Oracle8i), programmers used the pragma RESTRICT_REFERENCES to assert the purity level of a subprogram. In subsequent versions, use the hints parallel-enable and deterministic, instead, to communicate subprogram purity to Oracle Database.
You can remove RESTRICT_REFERENCES from your code. However, this pragma remains available for backward compatibility in situations where one of the following is true:
An existing PL/SQL application can thus continue using the pragma even on new functionality, to ease integration with the existing code. Do not use the pragma in a wholly new application.
If you use the pragma RESTRICT_REFERENCES, place it in a package specification, not in a package body. It must follow the declaration of a subprogram (function or procedure), but it need not follow immediately. Only one pragma can reference a given subprogram declaration.
Note:
The pragma RESTRICT_REFERENCES applies to both functions and procedures. Purity levels are important for functions, but also for procedures that are called by functions.
To code the pragma RESTRICT_REFERENCES, use the following syntax:
PRAGMA RESTRICT_REFERENCES (
Function_name, WNDS [, WNPS] [, RNDS] [, RNPS] [, TRUST] );
Where:
| Keyword | Description |
|---|---|
| WNDS | The subprogram writes no database state (does not modify database tables). |
| >RNDS | The subprogram reads no database state (does not query database tables). |
| WNPS | The subprogram writes no package state (does not change the values of packaged variables). |
| RNPS | The subprogram reads no package state (does not reference the values of packaged variables). |
| TRUST | The other restrictions listed in the pragma are not enforced; they are simply assumed to be true. This allows easy calling from functions that have RESTRICT_REFERENCES declarations to those that do not. |
You can pass the arguments in any order. If any SQL statement inside the subprogram body violates a rule, then you get an error when the statement is parsed.
In the following example, the function compound neither reads nor writes database or package state; therefore, you can assert the maximum purity level. Always assert the highest purity level that a subprogram allows. That way, the PL/SQL compiler never rejects the subprogram unnecessarily.
Note:
You may need to set up the following data structures for certain examples here to work:
CREATE TABLE Accts ( Yrs NUMBER, Amt NUMBER, Acctno NUMBER, Rte NUMBER);
CREATE PACKAGE Finance AS -- package specification
FUNCTION Compound
(Years IN NUMBER,
Amount IN NUMBER,
Rate IN NUMBER) RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES (Compound, WNDS, WNPS, RNDS, RNPS);
END Finance;
CREATE PACKAGE BODY Finance AS --package body
FUNCTION Compound
(Years IN NUMBER,
Amount IN NUMBER,
Rate IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN Amount * POWER((Rate / 100) + 1, Years);
END Compound;
-- no pragma in package body
END Finance;
Later, you might call compound from a PL/SQL block, as follows:
DECLARE Interest NUMBER; Acct_id NUMBER; BEGIN SELECT Finance.Compound(Yrs, Amt, Rte) -- function call INTO Interest FROM Accounts WHERE Acctno = Acct_id;
The keyword TRUST in the RESTRICT_REFERENCES syntax allows easy calling from functions that have RESTRICT_REFERENCES declarations to those that do not. When TRUST is present, the restrictions listed in the pragma are not actually enforced, but rather are simply assumed to be true.
When calling from a section of code that is using pragmas to one that is not, there are two likely usage styles. One is to place a pragma on the routine to be called, for example on a "call specification" for a Java method. Then, calls from PL/SQL to this method will complain if the method is less restricted than the calling subprogram. For example:
CREATE OR REPLACE PACKAGE P1 IS
FUNCTION F1 (P1 NUMBER) RETURN NUMBER IS
LANGUAGE JAVA NAME 'CLASS1.METHODNAME(int) return int';
PRAGMA RESTRICT_REFERENCES(F1,WNDS,TRUST);
FUNCTION F2 (P1 NUMBER) RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES(F2,WNDS);
END;
CREATE OR REPLACE PACKAGE BODY P1 IS
FUNCTION F2 (P1 NUMBER) RETURN NUMBER IS
BEGIN
RETURN F1(P1);
END;
END;
Here, F2 can call F1, as F1 has been declared to be WNDS.
The other approach is to mark only the caller, which may then make a call to any subprogram without complaint. For example:
CREATE OR REPLACE PACKAGE P1a IS
FUNCTION F1 (P1 NUMBER) RETURN NUMBER IS
LANGUAGE JAVA NAME 'CLASS1.METHODNAME(int) return int';
FUNCTION F2 (P1 NUMBER) RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES(F2,WNDS,TRUST);
END;
CREATE OR REPLACE PACKAGE BODY P1a IS
FUNCTION F2 (P1 NUMBER) RETURN NUMBER IS
BEGIN
RETURN F1(P1);
END;
END;
Here, F2 can call F1 because while F2 is promised to be WNDS (because TRUST is specified), the body of F2 is not actually examined to determine if it truly satisfies the WNDS restriction. Because F2 is not examined, its call to F1 is allowed, even though there is no PRAGMA RESTRICT_REFERENCES for F1.
Static INSERT, UPDATE, and DELETE statements do not violate RNDS if these statements do not explicitly read any database states, such as columns of a table. However, dynamic INSERT, UPDATE, and DELETE statements always violate RNDS, regardless of whether or not the statements explicitly read database states.
The following INSERT violates RNDS if it is executed dynamically, but it does not violate RNDS if it is executed statically.
INSERT INTO my_table values(3, 'SCOTT');
The following UPDATE always violates RNDS statically and dynamically, because it explicitly reads the column name of my_table.
UPDATE my_table SET id=777 WHERE name='SCOTT';
PL/SQL lets you overload packaged (but not standalone) functions: You can use the same name for different functions if their formal parameters differ in number, order, or datatype family.
However, a RESTRICT_REFERENCES pragma can apply to only one function declaration. Therefore, a pragma that references the name of overloaded functions always applies to the nearest preceding function declaration.
In this example, the pragma applies to the second declaration of valid:
CREATE PACKAGE Tests AS
FUNCTION Valid (x NUMBER) RETURN CHAR;
FUNCTION Valid (x DATE) RETURN CHAR;
PRAGMA RESTRICT_REFERENCES (valid, WNDS);
END;
PL/SQL packages usually consume user global area (UGA) memory corresponding to the number of package variables and cursors in the package. This limits scalability, because the memory increases linearly with the number of users. The solution is to allow some packages to be marked as SERIALLY_REUSABLE (using pragma syntax).
For serially reusable packages, the package global memory is not kept in the UGA for each user; rather, it is kept in a small pool and reused for different users. This means that the global memory for such a package is only used within a unit of work. At the end of that unit of work, the memory can therefore be released to the pool to be reused by another user (after running the initialization code for all the global variables).
The unit of work for serially reusable packages is implicitly a call to the server; for example, an OCI call to the server, or a PL/SQL RPC call from a client to a server, or an RPC call from a server to another server.
The state of a nonreusable package (one not marked SERIALLY_REUSABLE) persists for the lifetime of a session. A package state includes global variables, cursors, and so on.
The state of a serially reusable package persists only for the lifetime of a call to the server. On a subsequent call to the server, if a reference is made to the serially reusable package, then Oracle Database creates a new instantiation of the serially reusable package and initializes all the global variables to NULL or to the default values provided. Any changes made to the serially reusable package state in the previous calls to the server are not visible.
Note:
Creating a new instantiation of a serially reusable package on a call to the server does not necessarily imply that Oracle Database allocates memory or configures the instantiation object. Oracle Database looks for an available instantiation work area (which is allocated and configured) for this package in a least-recently used (LRU) pool in the SGA. At the end of the call to the server, this work area is returned back to the LRU pool. The reason for keeping the pool in the SGA is that the work area can be reused across users who have requests for the same package.
Because the state of a non-reusable package persists for the lifetime of the session, this locks up UGA memory for the whole session. In applications, such as Oracle Office, a log-on session can typically exist for days together. Applications often need to use certain packages only for certain localized periods in the session and would ideally like to de-instantiate the package state in the middle of the session, after they are done using the package.
With SERIALLY_REUSABLE packages, application developers have a way of modelling their applications to manage their memory better for scalability. Package state that they care about only for the duration of a call to the server should be captured in SERIALLY_REUSABLE packages.
A package can be marked serially reusable by a pragma. The syntax of the pragma is:
PRAGMA SERIALLY_REUSABLE;
A package specification can be marked serially reusable, whether or not it has a corresponding package body. If the package has a body, then the body must have the serially reusable pragma, if its corresponding specification has the pragma; it cannot have the serially reusable pragma unless the specification also has the pragma.
A package that is marked SERIALLY_REUSABLE has the following properties:
Note:
If the application programmer makes a mistake and depends on a package variable that is set in a previous unit of work, then the application program can fail. PL/SQL cannot check for such cases.
This section presents a few examples of serially reusable packages.
This example has a serially reusable package specification (there is no body).
CONNECT Scott/Tiger CREATE OR REPLACE PACKAGE Sr_pkg IS PRAGMA SERIALLY_REUSABLE; N NUMBER := 5; -- default initialization END Sr_pkg;
Suppose your Enterprise Manager (or SQL*Plus) application issues the following:
CONNECT Scott/Tiger # first CALL to server BEGIN Sr_pkg.N := 10; END; # second CALL to server BEGIN DBMS_OUTPUT.PUT_LINE(Sr_pkg.N); END;
This program prints:
5
Note:
If the package had not had the pragma SERIALLY_REUSABLE, the program would have printed '10'.
This example has both a package specification and package body, which are serially reusable.
CONNECT Scott/Tiger
DROP PACKAGE Sr_pkg;
CREATE OR REPLACE PACKAGE Sr_pkg IS
PRAGMA SERIALLY_REUSABLE;
TYPE Str_table_type IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
Num NUMBER := 10;
Str VARCHAR2(200) := 'default-init-str';
Str_tab STR_TABLE_TYPE;
PROCEDURE Print_pkg;
PROCEDURE Init_and_print_pkg(N NUMBER, V VARCHAR2);
END Sr_pkg;
CREATE OR REPLACE PACKAGE BODY Sr_pkg IS
-- the body is required to have the pragma because the
-- specification of this package has the pragma
PRAGMA SERIALLY_REUSABLE;
PROCEDURE Print_pkg IS
BEGIN
DBMS_OUTPUT.PUT_LINE('num: ' || Sr_pkg.Num);
DBMS_OUTPUT.PUT_LINE('str: ' || Sr_pkg.Str);
DBMS_OUTPUT.PUT_LINE('number of table elems: ' || Sr_pkg.Str_tab.Count);
FOR i IN 1..Sr_pkg.Str_tab.Count LOOP
DBMS_OUTPUT.PUT_LINE(Sr_pkg.Str_tab(i));
END LOOP;
END;
PROCEDURE Init_and_print_pkg(N NUMBER, V VARCHAR2) IS
BEGIN
-- init the package globals
Sr_pkg.Num := N;
Sr_pkg.Str := V;
FOR i IN 1..n LOOP
Sr_pkg.Str_tab(i) := V || ' ' || i;
END LOOP;
-- print the package
Print_pkg;
END;
END Sr_pkg;
SET SERVEROUTPUT ON;
Rem SR package access in a CALL:
BEGIN
-- initialize and print the package
DBMS_OUTPUT.PUT_LINE('Initing and printing pkg state..');
Sr_pkg.Init_and_print_pkg(4, 'abracadabra');
-- print it in the same call to the server.
-- we should see the initialized values.
DBMS_OUTPUT.PUT_LINE('Printing package state in the same CALL...');
Sr_pkg.Print_pkg;
END;
Initing and printing pkg state..
num: 4
str: abracadabra
number of table elems: 4
abracadabra 1
abracadabra 2
abracadabra 3
abracadabra 4
Printing package state in the same CALL...
num: 4
str: abracadabra
number of table elems: 4
abracadabra 1
abracadabra 2
abracadabra 3
abracadabra 4
REM SR package access in subsequent CALL:
BEGIN
-- print the package in the next call to the server.
-- We should that the package state is reset to the initial (default) values.
DBMS_OUTPUT.PUT_LINE('Printing package state in the next CALL...');
Sr_pkg.Print_pkg;
END;
Statement processed.
Printing package state in the next CALL...
num: 10
str: default-init-str
number of table elems: 0
This example demonstrates that any open cursors in serially reusable packages get closed automatically at the end of a work boundary (which is a call). Also, in a new call, these cursors need to be opened again.
REM For serially reusable pkg: At the end work boundaries
REM (which is currently the OCI call boundary) all open
REM cursors will be closed.
REM
REM Because the cursor is closed - every time we fetch we
REM will start at the first row again.
CONNECT Scott/Tiger
DROP PACKAGE Sr_pkg;
DROP TABLE People;
CREATE TABLE People (Name VARCHAR2(20));
INSERT INTO People VALUES ('ET');
INSERT INTO People VALUES ('RAMBO');
CREATE OR REPLACE PACKAGE Sr_pkg IS
PRAGMA SERIALLY_REUSABLE;
CURSOR C IS SELECT Name FROM People;
END Sr_pkg;
SQL> SET SERVEROUTPUT ON;
SQL>
CREATE OR REPLACE PROCEDURE Fetch_from_cursor IS
Name VARCHAR2(200);
BEGIN
IF (Sr_pkg.C%ISOPEN) THEN
DBMS_OUTPUT.PUT_LINE('cursor is already open.');
ELSE
DBMS_OUTPUT.PUT_LINE('cursor is closed; opening now.');
OPEN Sr_pkg.C;
END IF;
-- fetching from cursor.
FETCH sr_pkg.C INTO name;
DBMS_OUTPUT.PUT_LINE('fetched: ' || Name);
FETCH Sr_pkg.C INTO name;
DBMS_OUTPUT.PUT_LINE('fetched: ' || Name);
-- Oops forgot to close the cursor (Sr_pkg.C).
-- But, because it is a Serially Reusable pkg's cursor,
-- it will be closed at the end of this CALL to the server.
END;
EXECUTE fetch_from_cursor;
cursor is closed; opening now.
fetched: ET
fetched: RAMBO
Returning Large Amounts of Data from a Function >>
