PRAGMA RESTRICT_REFERENCE
When a stand alone user defined function is used in a SQL statement, there is no guarantee that the function does not update the database. This guarantee can be made by:
Inspecting the code, or by
Executing a SQL statement with the function. If the SQL parse identifies an INSERT, UPDATE, or DELETE, the execution will fail. You do not have to execute the specific path for the statement to fail. If the code contains ANY table modification SQL, you will know it.
If user defined functions are used to any extent it is advisable to encapsulate them within a package and designate this package as "pure", that is, the functions in the package do not modify the database. The "purity" of the package is denoted with the PL/SQL pragma: RESTRICT_REFERENCES. This pragma has one mandatory argument and three optional arguments. The optional arguments define the level of the "purity" of the function.
RESTRICT_REFERENCES Pragma Syntax
v-----|
PRAGMA RESTRICT_REFERENCES-----function_name--,----RNDS---)----><
|---DEFAULT ---| WNDS
RNPS
WNPS
TRUST
Click on each parameter in BLUE for an explanation of its use ...
Usage Notes
You can declare the pragma RESTRICT_REFERENCES only in a package spec or object type spec.
You can specify up to four constraints (RNDS, RNPS, WNDS, WNPS) To call the function from parallel queries, you must specify all four constraints.
No constraint implies another. For example, WNPS does not imply RNPS.
When you specify TRUST, the function body is not checked for violations of the constraints listed in the pragma.
The function is trusted not to violate them.
If you specify DEFAULT instead of a function name, the pragma applies to all functions in the package spec or object type spec (including, in the latter case, the system-defined constructor).
You can still declare the pragma for individual functions. Such pragmas override the default pragma.
A RESTRICT_REFERENCES pragma can apply to only one function declaration. So, a pragma that references the name of overloaded functions always applies to the nearest foregoing function declaration.
The following code segment illustrates a package that provides procedures and functions in employee data and uses the pragma RESTRICT_REFERENCES.
create or replace package emp_pkg is
function highest_paid_employee return emp.emp%name;
-- returns the highest paid employee name
function round_salary(n varchar2) return number;
-- returns the ROUNDED salary of the employee
pragma restrict_references(round_salary, WNDS);
end emp_pkg;
The following is a list of topics covered in this section.
Click on the item for a more in depth look.
Requirements for Calling Functions in SQL
Restrictions on User-Defined Functions in SQL
Replacing DECODEs with IF Statements
PRAGMA RESTRICT_REFERENCES
Requirements for Calling Functions in SQL
----------------------------------------------
There are several requirements that a programmer-defined PL/SQL function must meet in order to be callable from within a SQL statement:
The function must be stored in the database. A function defined in a client-side PL/SQL environment cannot be called from within SQL; there would be no way for SQL to resolve the reference to the function.
All of the function’s parameters must use the IN mode. Neither IN OUT nor OUT parameters are allowed in SQL-embedded stored functions—you should never have IN OUT and OUT parameters in functions. Whether or not you are going to use that function inside a SQL statement, such parameters constitute side effects of the main purpose of the function, which is to return a single value.
The datatypes of the function’s parameters, as well as the datatype of the RETURN clause of the function, must be recognized within the Oracle Server. While all of the Oracle Server datatypes are valid within PL/SQL, PL/SQL has added new datatypes that are not (yet) supported in the database. These datatypes include BOOLEAN, BINARY_INTEGER, associative arrays, PL/SQL records, and programmer-defined subtypes.
Prior to Oracle8i, functions defined in packages must have an associated RESTRICT_REFEFRENCES pragma defined for them. If you want to call from SQL a function defined in a package, you will need to add a pragma to the package specification asserting explicitly that this function is valid for SQL execution.
Note: By default, user-defined functions that execute in SQL operate on a single row of data, not on an entire column of data that crosses rows, as the group functions SUM, MIN, and AVG do. It is possible to write aggregate functions to be called inside SQL, but this requires taking advantage of the ODCIAggregate interface, which is part of Oracle’s Extensibility Framework.
Restrictions on User-Defined Functions in SQL
-------------------------------------------------
In order to guard against nasty side effects and unpredictable behavior, the Oracle RDBMS makes it impossible for your stored function in SQL to take any of the following actions:
The stored function may not modify database tables. It cannot execute an INSERT, DELETE, or UPDATE statement. Note that this restriction is relaxed if your function is defined as an autonomous transaction; in this case, any changes made in your function occur independently of the outer transaction in which the query was executed.
A stored function that is called remotely or through a parallelized action may not read or write the values of package variables. The Oracle Server does not support side effects that cross user sessions.
A stored function can update the values of package variables only if that function is called in a select list, or a VALUES or SET clause. If the stored function is called in a WHERE or GROUP BY clause, it cannot write package variables.
Prior to Oracle8, you cannot call RAISE_APPLICATION_ERROR from within the stored function.
The stored function may not call another module (stored procedure or function) that breaks any of the preceding rules. A function is only as pure as the most impure module that it calls.
The stored function may not reference a view that breaks any of the preceding rules. A view is a stored SELECT statement; that view’s SELECT may use stored functions.
Replacing DECODEs With IF Statements
----------------------------------
The DECODE function offers IF-like capabilities in the nonprocedural SQL environment provided by the Oracle Server. You can use the DECODE syntax to create matrix reports with a fixed number of columns or to perform complex IF-THENELSE logic within a query. The downside to DECODE is that it can be difficult to write and very difficult to maintain. Consider the following example of using DECODE to determine whether a date is within the prescribed range and, if so, to add to the count of rows that fulfill this requirement:
SELECT FC.year_number,
SUM (DECODE (GREATEST (ship_date, FC.q1_sdate),
ship_date,
DECODE (LEAST (ship_date, FC.q1_edate),
ship_date, 1,
0),
0)) Q1_results,
SUM (DECODE (GREATEST (ship_date, FC.q2_sdate),
ship_date,
DECODE (LEAST (ship_date, FC.q2_edate),
ship_date, 1,
0),
0)) Q2_results,
SUM (DECODE (GREATEST (ship_date, FC.q3_sdate),
ship_date,
DECODE (LEAST (ship_date, FC.q3_edate),
ship_date, 1,
0),
0)) Q3_results,
SUM (DECODE (GREATEST (ship_date, FC.q4_sdate),
ship_date,
DECODE (LEAST (ship_date, FC.q4_edate),
ship_date, 1,
0),
0)) Q4_results
FROM orders O,
fiscal_calendar FC
GROUP BY year_number;
The result set for this query might look like this:
YEAR NUMBER Q1 RESULTS Q2 RESULTS Q3 RESULTS Q4 RESULTS
------------ ---------- ---------- ---------- ----------
1993 12000 14005 22000 40000
1994 10000 15000 21000 55004
While it is very handy to use DECODE to produce such a report, the SQL required to accomplish the task is more than a little frightening. Try to interpret the Q1 RESULTS nested DECODE:
If the ship date is greater than or equal to the first quarter start date and less than or equal to the first quarter end date, then add one to the sum of the total number of orders shipped in that quarter. Otherwise, add zero.
Unfortunately, unless you are experienced in interpreting DECODE statements, you may find it difficult to glean this understanding from that convoluted SQL statement. The repetition in that single SELECT also cries out for modularization, which can be done with the following stored function (incr_in_range means “increment if in the range”):
FUNCTION incr_in_range
(ship_date_in IN DATE, sdate_in IN DATE, edate_in IN DATE)
RETURN INTEGER
IS
BEGIN
IF ship_date_in BETWEEN sdate_in AND edate_in
THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END;
With the incr_in_range function, that long SELECT statement simply becomes:
SELECT FC.year_number,
SUM (incr_in_range (ship_date, q1_sdate, q1_edate)) Q1_results,
SUM (incr_in_range (ship_date, q2_sdate, q2_edate)) Q2_results,
SUM (incr_in_range (ship_date, q3_sdate, q3_edate)) Q3_results,
SUM (incr_in_range (ship_date, q4_sdate, q4_edate)) Q4_results
FROM orders O,
fiscal_calendar FC
GROUP BY year_number;
This stored function gets rid of the code redundancy and makes the SELECT statement much more readable. In addition, this function could be used in other SQL statements to perform the same logic.
The PRAGMA RESTRICT_REFERENCES (Oracle8 and earlier)
------------------------------------------------------
Prior to Oracle8i, if you wanted to invoke within SQL a function that was defined inside a package specification, you would have had to provide a RESTRICT_REFERENCES pragma (a compiler directive or instruction) for that function. This pragma asserts the “purity level” of the function, in essence promising Oracle that the function has the specified side effects (or, more to the point, lack thereof).
Working with the RESTRICT_REFERENCES pragma can be very frustrating, so it was a great relief to many a PL/SQL developer when in Oracle8i this pragma was made unnecessary. However, this section talks briefly about some of the rules associated with this pragma for those still using Oracle8 and earlier.
You need a separate PRAGMA statement for each packaged function you wish to use in a SQL statement, and it must come after the function declaration in the package specification. To assert a purity level with the pragma, use the following syntax:
PRAGMA RESTRICT_REFERENCES
(function_name, WNDS [, WNPS] [, RNDS] [, RNPS])
where function_name is the name of the function whose purity level you wish to assert, and the four codes have the following meanings:
WNDS - Writes No Database State. Asserts that the function does not modify any database tables.
WNPS - Writes No Package State. Asserts that the function does not modify any package variables.
RNDS - Reads No Database State. Asserts that the function does not read any database tables.
RNPS - Reads No Package State. Asserts that the function does not read any package variables.
Here is an example of two different purity-level assertions for functions in the company_financials package:
PACKAGE company_financials
IS
FUNCTION company_type (type_code_in IN VARCHAR2)
RETURN VARCHAR2;
FUNCTION company_name (
company_id_in IN company.company_id%TYPE)
RETURN VARCHAR2;
PRAGMA RESTRICT_REFERENCES (
company_type, WNDS, RNDS, WNPS, RNPS);
PRAGMA RESTRICT_REFERENCES (company_name, WNDS, WNPS, RNPS);
END company_financials;
In this package, the company_name function reads from the database to obtain the name for the specified company. Notice that both pragmas were placed together at the bottom of the package specification; the pragma does not need to immediately follow the function specification. Also the WNPS and RNPS arguments were specified for both functions. Oracle recommends that you assert the highest possible purity levels so that the compiler will never reject the function unnecessarily.
Note: If a function you want to call in SQL calls a procedure or function in a package, you must also provide a RESTRICT_REFERENCES pragma for that program. You cannot call a procedure directly in SQL, but if it is going to be executed indirectly from within SQL, it still must follow the rules.
If your function violates its pragma, you will receive the PLS-00452 error: subprogram ‘program’ violates its associated pragma. Suppose, for example, that the body of the company_financials package looks like this:
CREATE OR REPLACE PACKAGE BODY company_financials
IS
FUNCTION company_type (type_code_in IN VARCHAR2)
RETURN VARCHAR2
IS
v_sal NUMBER;
BEGIN
SELECT sal INTO v_sal FROM emp WHERE empno = 1;
RETURN 'bigone';
END;
FUNCTION company_name (company_id_in IN company.company_id%TYPE)
RETURN VARCHAR2
IS
BEGIN
UPDATE emp SET sal = 0;
RETURN 'bigone';
END;
END company_financials;
When you attempt to compile this package body you will get the following error:
3/4 PLS-00452: Subprogram 'COMPANY_TYPE' violates its associated pragma
because the company_type function reads from the database and the RNDS purity level has been asserted. If you remove the SELECT statement, you will then receive this error:
11/4 PLS-00452: Subprogram 'COMPANY_NAME' violates its associated pragma
because the company_name function updates the database and you have asserted the WNDS level. You will sometimes look at your function and say, “Hey, I absolutely do not violate my purity level. There is no UPDATE, DELETE, or UPDATE around.” Maybe not. But there is a good chance that you are calling a built-in package or in some other way breaking the rules. Again, if you are running Oracle8i and above, you no longer need to deal with RESTRICT_REFERENCES. The runtime engine will automatically check your code for any violations.
When a stand alone user defined function is used in a SQL statement, there is no guarantee that the function does not update the database. This guarantee can be made by:
Inspecting the code, or by
Executing a SQL statement with the function. If the SQL parse identifies an INSERT, UPDATE, or DELETE, the execution will fail. You do not have to execute the specific path for the statement to fail. If the code contains ANY table modification SQL, you will know it.
If user defined functions are used to any extent it is advisable to encapsulate them within a package and designate this package as "pure", that is, the functions in the package do not modify the database. The "purity" of the package is denoted with the PL/SQL pragma: RESTRICT_REFERENCES. This pragma has one mandatory argument and three optional arguments. The optional arguments define the level of the "purity" of the function.
RESTRICT_REFERENCES Pragma Syntax
v-----|
PRAGMA RESTRICT_REFERENCES-----function_name--,----RNDS---)----><
|---DEFAULT ---| WNDS
RNPS
WNPS
TRUST
Click on each parameter in BLUE for an explanation of its use ...
Usage Notes
You can declare the pragma RESTRICT_REFERENCES only in a package spec or object type spec.
You can specify up to four constraints (RNDS, RNPS, WNDS, WNPS) To call the function from parallel queries, you must specify all four constraints.
No constraint implies another. For example, WNPS does not imply RNPS.
When you specify TRUST, the function body is not checked for violations of the constraints listed in the pragma.
The function is trusted not to violate them.
If you specify DEFAULT instead of a function name, the pragma applies to all functions in the package spec or object type spec (including, in the latter case, the system-defined constructor).
You can still declare the pragma for individual functions. Such pragmas override the default pragma.
A RESTRICT_REFERENCES pragma can apply to only one function declaration. So, a pragma that references the name of overloaded functions always applies to the nearest foregoing function declaration.
The following code segment illustrates a package that provides procedures and functions in employee data and uses the pragma RESTRICT_REFERENCES.
create or replace package emp_pkg is
function highest_paid_employee return emp.emp%name;
-- returns the highest paid employee name
function round_salary(n varchar2) return number;
-- returns the ROUNDED salary of the employee
pragma restrict_references(round_salary, WNDS);
end emp_pkg;
The following is a list of topics covered in this section.
Click on the item for a more in depth look.
Requirements for Calling Functions in SQL
Restrictions on User-Defined Functions in SQL
Replacing DECODEs with IF Statements
PRAGMA RESTRICT_REFERENCES
Requirements for Calling Functions in SQL
----------------------------------------------
There are several requirements that a programmer-defined PL/SQL function must meet in order to be callable from within a SQL statement:
The function must be stored in the database. A function defined in a client-side PL/SQL environment cannot be called from within SQL; there would be no way for SQL to resolve the reference to the function.
All of the function’s parameters must use the IN mode. Neither IN OUT nor OUT parameters are allowed in SQL-embedded stored functions—you should never have IN OUT and OUT parameters in functions. Whether or not you are going to use that function inside a SQL statement, such parameters constitute side effects of the main purpose of the function, which is to return a single value.
The datatypes of the function’s parameters, as well as the datatype of the RETURN clause of the function, must be recognized within the Oracle Server. While all of the Oracle Server datatypes are valid within PL/SQL, PL/SQL has added new datatypes that are not (yet) supported in the database. These datatypes include BOOLEAN, BINARY_INTEGER, associative arrays, PL/SQL records, and programmer-defined subtypes.
Prior to Oracle8i, functions defined in packages must have an associated RESTRICT_REFEFRENCES pragma defined for them. If you want to call from SQL a function defined in a package, you will need to add a pragma to the package specification asserting explicitly that this function is valid for SQL execution.
Note: By default, user-defined functions that execute in SQL operate on a single row of data, not on an entire column of data that crosses rows, as the group functions SUM, MIN, and AVG do. It is possible to write aggregate functions to be called inside SQL, but this requires taking advantage of the ODCIAggregate interface, which is part of Oracle’s Extensibility Framework.
Restrictions on User-Defined Functions in SQL
-------------------------------------------------
In order to guard against nasty side effects and unpredictable behavior, the Oracle RDBMS makes it impossible for your stored function in SQL to take any of the following actions:
The stored function may not modify database tables. It cannot execute an INSERT, DELETE, or UPDATE statement. Note that this restriction is relaxed if your function is defined as an autonomous transaction; in this case, any changes made in your function occur independently of the outer transaction in which the query was executed.
A stored function that is called remotely or through a parallelized action may not read or write the values of package variables. The Oracle Server does not support side effects that cross user sessions.
A stored function can update the values of package variables only if that function is called in a select list, or a VALUES or SET clause. If the stored function is called in a WHERE or GROUP BY clause, it cannot write package variables.
Prior to Oracle8, you cannot call RAISE_APPLICATION_ERROR from within the stored function.
The stored function may not call another module (stored procedure or function) that breaks any of the preceding rules. A function is only as pure as the most impure module that it calls.
The stored function may not reference a view that breaks any of the preceding rules. A view is a stored SELECT statement; that view’s SELECT may use stored functions.
Replacing DECODEs With IF Statements
----------------------------------
The DECODE function offers IF-like capabilities in the nonprocedural SQL environment provided by the Oracle Server. You can use the DECODE syntax to create matrix reports with a fixed number of columns or to perform complex IF-THENELSE logic within a query. The downside to DECODE is that it can be difficult to write and very difficult to maintain. Consider the following example of using DECODE to determine whether a date is within the prescribed range and, if so, to add to the count of rows that fulfill this requirement:
SELECT FC.year_number,
SUM (DECODE (GREATEST (ship_date, FC.q1_sdate),
ship_date,
DECODE (LEAST (ship_date, FC.q1_edate),
ship_date, 1,
0),
0)) Q1_results,
SUM (DECODE (GREATEST (ship_date, FC.q2_sdate),
ship_date,
DECODE (LEAST (ship_date, FC.q2_edate),
ship_date, 1,
0),
0)) Q2_results,
SUM (DECODE (GREATEST (ship_date, FC.q3_sdate),
ship_date,
DECODE (LEAST (ship_date, FC.q3_edate),
ship_date, 1,
0),
0)) Q3_results,
SUM (DECODE (GREATEST (ship_date, FC.q4_sdate),
ship_date,
DECODE (LEAST (ship_date, FC.q4_edate),
ship_date, 1,
0),
0)) Q4_results
FROM orders O,
fiscal_calendar FC
GROUP BY year_number;
The result set for this query might look like this:
YEAR NUMBER Q1 RESULTS Q2 RESULTS Q3 RESULTS Q4 RESULTS
------------ ---------- ---------- ---------- ----------
1993 12000 14005 22000 40000
1994 10000 15000 21000 55004
While it is very handy to use DECODE to produce such a report, the SQL required to accomplish the task is more than a little frightening. Try to interpret the Q1 RESULTS nested DECODE:
If the ship date is greater than or equal to the first quarter start date and less than or equal to the first quarter end date, then add one to the sum of the total number of orders shipped in that quarter. Otherwise, add zero.
Unfortunately, unless you are experienced in interpreting DECODE statements, you may find it difficult to glean this understanding from that convoluted SQL statement. The repetition in that single SELECT also cries out for modularization, which can be done with the following stored function (incr_in_range means “increment if in the range”):
FUNCTION incr_in_range
(ship_date_in IN DATE, sdate_in IN DATE, edate_in IN DATE)
RETURN INTEGER
IS
BEGIN
IF ship_date_in BETWEEN sdate_in AND edate_in
THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END;
With the incr_in_range function, that long SELECT statement simply becomes:
SELECT FC.year_number,
SUM (incr_in_range (ship_date, q1_sdate, q1_edate)) Q1_results,
SUM (incr_in_range (ship_date, q2_sdate, q2_edate)) Q2_results,
SUM (incr_in_range (ship_date, q3_sdate, q3_edate)) Q3_results,
SUM (incr_in_range (ship_date, q4_sdate, q4_edate)) Q4_results
FROM orders O,
fiscal_calendar FC
GROUP BY year_number;
This stored function gets rid of the code redundancy and makes the SELECT statement much more readable. In addition, this function could be used in other SQL statements to perform the same logic.
The PRAGMA RESTRICT_REFERENCES (Oracle8 and earlier)
------------------------------------------------------
Prior to Oracle8i, if you wanted to invoke within SQL a function that was defined inside a package specification, you would have had to provide a RESTRICT_REFERENCES pragma (a compiler directive or instruction) for that function. This pragma asserts the “purity level” of the function, in essence promising Oracle that the function has the specified side effects (or, more to the point, lack thereof).
Working with the RESTRICT_REFERENCES pragma can be very frustrating, so it was a great relief to many a PL/SQL developer when in Oracle8i this pragma was made unnecessary. However, this section talks briefly about some of the rules associated with this pragma for those still using Oracle8 and earlier.
You need a separate PRAGMA statement for each packaged function you wish to use in a SQL statement, and it must come after the function declaration in the package specification. To assert a purity level with the pragma, use the following syntax:
PRAGMA RESTRICT_REFERENCES
(function_name, WNDS [, WNPS] [, RNDS] [, RNPS])
where function_name is the name of the function whose purity level you wish to assert, and the four codes have the following meanings:
WNDS - Writes No Database State. Asserts that the function does not modify any database tables.
WNPS - Writes No Package State. Asserts that the function does not modify any package variables.
RNDS - Reads No Database State. Asserts that the function does not read any database tables.
RNPS - Reads No Package State. Asserts that the function does not read any package variables.
Here is an example of two different purity-level assertions for functions in the company_financials package:
PACKAGE company_financials
IS
FUNCTION company_type (type_code_in IN VARCHAR2)
RETURN VARCHAR2;
FUNCTION company_name (
company_id_in IN company.company_id%TYPE)
RETURN VARCHAR2;
PRAGMA RESTRICT_REFERENCES (
company_type, WNDS, RNDS, WNPS, RNPS);
PRAGMA RESTRICT_REFERENCES (company_name, WNDS, WNPS, RNPS);
END company_financials;
In this package, the company_name function reads from the database to obtain the name for the specified company. Notice that both pragmas were placed together at the bottom of the package specification; the pragma does not need to immediately follow the function specification. Also the WNPS and RNPS arguments were specified for both functions. Oracle recommends that you assert the highest possible purity levels so that the compiler will never reject the function unnecessarily.
Note: If a function you want to call in SQL calls a procedure or function in a package, you must also provide a RESTRICT_REFERENCES pragma for that program. You cannot call a procedure directly in SQL, but if it is going to be executed indirectly from within SQL, it still must follow the rules.
If your function violates its pragma, you will receive the PLS-00452 error: subprogram ‘program’ violates its associated pragma. Suppose, for example, that the body of the company_financials package looks like this:
CREATE OR REPLACE PACKAGE BODY company_financials
IS
FUNCTION company_type (type_code_in IN VARCHAR2)
RETURN VARCHAR2
IS
v_sal NUMBER;
BEGIN
SELECT sal INTO v_sal FROM emp WHERE empno = 1;
RETURN 'bigone';
END;
FUNCTION company_name (company_id_in IN company.company_id%TYPE)
RETURN VARCHAR2
IS
BEGIN
UPDATE emp SET sal = 0;
RETURN 'bigone';
END;
END company_financials;
When you attempt to compile this package body you will get the following error:
3/4 PLS-00452: Subprogram 'COMPANY_TYPE' violates its associated pragma
because the company_type function reads from the database and the RNDS purity level has been asserted. If you remove the SELECT statement, you will then receive this error:
11/4 PLS-00452: Subprogram 'COMPANY_NAME' violates its associated pragma
because the company_name function updates the database and you have asserted the WNDS level. You will sometimes look at your function and say, “Hey, I absolutely do not violate my purity level. There is no UPDATE, DELETE, or UPDATE around.” Maybe not. But there is a good chance that you are calling a built-in package or in some other way breaking the rules. Again, if you are running Oracle8i and above, you no longer need to deal with RESTRICT_REFERENCES. The runtime engine will automatically check your code for any violations.
No comments:
Post a Comment