10.2 Calling Packaged Functions in SQL
10.2 Calling Packaged Functions in SQL In Oracle8 i , Oracle has made changes in the way you define programs and assert their purity levels , or lack of side effects. 10.2.1 RESTRICT_REFERENCES PragmaBack in Oracle 7.3, PL/SQL offered a new pragma (compiler directive) called RESTRICT_REFERENCES. This pragma was used to assert to the PL/SQL compiler the purity level of a packaged procedure or function. The RESTRICT_REFERENCES pragma had to be included in the package specification if you were to use that program inside a SQL statement (directly or indirectly). This pragma has always been a major headache for PL/SQL developers, and as of Oracle 8.1 it is no longer required. The SQL engine will determine at the time you run your SQL statement whether or not the function call is valid. Many of the restrictions on how and when you can use functions in SQL have been relaxed , but the following rules still apply:
The last three restrictions can be avoided if you take advantage of PL/SQL 8.1's autonomous transaction feature (described in Chapter 2 ) in your function. With this approach, the function executes in its own transaction space, so a commit or DML statement has no impact on the "calling" SQL statement. Oracle 8.1 also offers much better error messages. Instead of the double-negative, mind-bending: ORA-06571: Function TOTCOMP does not guarantee not to update database you will see this much more sensible message: ORA-14551: cannot perform a DML operation inside a query You can still include the PRAGMA RESTRICT_REFERENCES if you wish. You might, for example, want to use the PL/SQL compiler as a verification mechanism. It will tell you if your function has the side effects that you would expect, informing you of potential complications in underlying layers of code. 10.2.2 Related New DevelopmentsOracle has added several new options to the way you define programs and assert their purity levels to make it easier to deploy stored code in the new world of integration with Java and C, as well as with parallel databases. 10.2.2.1 The TRUST optionYou no longer need to use PRAGMA RESTRICT_REFERENCES to enable PL/SQL programs for use in SQL; the runtime engine will figure it out all by itself. However, what if you want to call Java or C routines from within SQL? You can now build wrappers around code written in those languages to make them accessible in PL/SQL. But the SQL engine cannot analyze those programs to determine freedom from side effects. Oracle has, therefore, added a new option in the PRAGMA RESTRICT_REFERENCES statement: TRUST. The complete syntax for this pragma is now as follows : PRAGMA RESTRICT_REFERENCES ( function_name , WNDS [, WNPS] [, RNDS] [, RNPS] [, TRUST] ); The TRUST option makes it easier to call Java and C routines from functions that have a RESTRICT_REFERENCES pragma. When TRUST is used in the pragma, the restrictions listed in that pragma are not actually enforced. They are, instead, simply trusted to be true. Now that's flexibility! You can deploy the TRUST option on the top-level program that then calls other programs, or you can use TRUST with each of the lower-level programs, allowing you to then assert whatever purity levels are necessary on routines that call them. Let's look at some examples. When calling from a section of code that is using pragmas to a section 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. Calls from PL/SQL to this method will then complain if the method is less restricted than the calling function. The following package declares two programs. The maxsal function is based on a Java method; it uses the TRUST option to assert WNDS ( writes no database state) and RNDS (reads no database state). The analyze_compensation procedure, which needs to assert WNDS so that it can be called from within a SQL query (indirectly), can then call maxsal. Note that if I wanted to assert WNPS or use this procedure in a function that is called in a WHERE clause, it would be rejected because the underlying pragma does not assert this purity level: CREATE OR REPLACE PACKAGE personnel IS FUNCTION maxsal (ssn_in IN VARCHAR2) RETURN NUMBER IS LANGUAGE JAVA NAME 'Person.max_salary (Java.lang.String) return long'; PRAGMA RESTRICT_REFERENCES (maxsal, WNDS, RNDS, TRUST); PROCEDURE analyze_compensation (ssn_in IN VARCHAR2); PRAGMA RESTRICT_REFERENCES (analyze_compensation, WNDS); END; In the next example, I have rewritten the personnel package specification to demonstrate the second usage style. Here, the top-level program, analyze_compensation, is the only program with a RESTRICT_REFERENCES pragma. I still won't have any problem calling maxsal and deploying it inside SQL, because I have added the TRUST option: CREATE OR REPLACE PACKAGE personnel IS FUNCTION maxsal (ssn_in IN VARCHAR2) RETURN NUMBER IS LANGUAGE JAVA NAME 'Person.max_salary (Java.lang.String) return long'; PROCEDURE analyze_compensation (ssn_in IN VARCHAR2); PRAGMA RESTRICT_REFERENCES ( analyze_compensation, WNDS, WNPS, TRUST); END; The SQL runtime engine will not even check the purity level of maxsal; it has been instructed to "trust" the program. 10.2.2.2 Deterministic functionsWhat, you might ask, is a deterministic function ? It's a program that doesn't have any free will. If you pass it X and Y values for its arguments, it always returns the same value(s). According to Oracle documentation:
Here is an example of a deterministic function: CREATE FUNCTION ceo_compensation ( stock_increase IN NUMBER, layoffs IN NUMBER) RETURN NUMBER IS BEGIN RETURN 1000000 * (layoffs + stock_increase); END; Even if the company's assembly line killed three workers due to faulty wiring, as long as the stock price goes up and the CEO lays off a bunch of people, he receives a huge paycheck. What's the big deal about this sort of function? If Oracle can be sure that the function will return the same value for a given set of arguments, then it can avoid re-executing the function if it remembers the value from the last execution with those arguments. If you tell Oracle that a function is deterministic, this declaration becomes an optimization hint. The Oracle8 i engine then knows that this function does not need to be called repetitively for the same arguments. It can instead cache the results and then simply use those results again and again. This feature is especially useful within a parallelized query or parallelized DML statement. In addition, two new features in Oracle8 i require that any function used with them be declared deterministic:
How do you declare a function to have this property? Simply place the DETERMINISTIC keyword after the return value type in the header of your function. In the following block, I have redefined the ceo_compensation function to be deterministic: CREATE FUNCTION ceo_compensation ( stock_increase IN NUMBER, layoffs IN NUMBER) RETURN NUMBER DETERMINISTIC IS BEGIN RETURN 1000000 * (layoffs + stock_increase); END; You can add this DETERMINISTIC clause in a header of any of the following:
Do not place the DETERMINISTIC keyword in the header of the function in the package body or object type body. You can also declare a procedure to be deterministic. There are several situations in which deterministic functions are not required but are strongly recommended by Oracle:
10.2.2.3 PARALLEL_ENABLE functionsThe execution of a SQL statement can involve many distinct actions (for example, updating multiple indexes on an INSERT). Oracle's parallel execution feature allows these multiple operations to be executed simultaneously on different processes. If a function is called in a SQL statement that is run in parallel, then Oracle may make and run a separate copy of the function in each process. Oracle's parallel execution feature divides the work of executing a SQL statement across multiple processes. Functions called from a SQL statement that 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 handled by that process. Each process has its own copy of package data structures, as well as Java STATIC class attributes. Oracle cannot assume that it is safe to parallelize the execution of user -defined functions if they might possibly modify any of those variables. Prior to Oracle8 i , a packaged function could be run in a parallel DML statement if its RESTRICT_REFERENCES pragma asserted all purity levels: WNDS, RNDS, WNPS, and RNPS. Those same purity levels also needed to be applicable to a standalone function if it were to be called in parallelized SQL. A parallel query required RNPS and WNPS, in addition to WNDS. In Oracle8 i , since RESTRICT_REFERENCES is no longer required and parallel function execution is still desired, a new and optional keyword, PARALLEL_ENABLE, has been added for the program header. This keyword is placed before the IS or AS keyword as shown in this example: CREATE FUNCTION ceo_compensation ( stock_increase IN NUMBER, layoffs IN NUMBER) RETURN NUMBER PARALLEL_ENABLE IS BEGIN RETURN 1000000 * (layoffs + stock_increase); END; / You can use this keyword in a function defined in a CREATE FUNCTION statement, in a function's declaration in a CREATE PACKAGE statement, or on a method's declaration in a CREATE TYPE statement. You do not repeat this keyword in the function's or method's body in a CREATE PACKAGE BODY or CREATE TYPE BODY statement. You don't have to use the PARALLEL_ENABLE keyword to identify functions that are eligible for parallel execution; Oracle suggests, however, that you use it, rather than a RESTRICT_REFERENCES pragma, when you need to achieve this effect. Here are some things to keep in mind:
|
 | |
Team-Fly  |
| Top |
EAN: N/A
Pages: 107