PL/SQL: Oracle 11g new enhancements (Part 1 of 2)
Automatic subprogram in-lining
CREATE OR REPLACE PROCEDURE inline_demo
( a NUMBER
, b NUMBER ) IS
PRAGMA INLINE(add_numbers,'YES');
BEGIN
FOR i IN 1..10000 LOOP
dbms_output.put_line(add_function(8,3));
END LOOP;
END;
/
Inlining a subprogram replaces the call to the external subprogram with a copy of the subprogram. This almost always improves program performance. You could instruct the compiler to inline subprograms by using the PRAGMA INLINE compiler directive in PL/SQL starting with the Oracle 11g Database. You must set the PRAGMA when you have the PLSQL_OPTIMIZE_LEVEL parameter set to 2.
A continue statement
BEGIN FOR i IN 1..5 LOOP dbms_output.put_line('First statement, index is ['||i||'].'); IF MOD(i,2) = 0 THEN CONTINUE;
END IF; dbms_output.put_line('Second statement, index is ['||i||'].');
END LOOP; END; /
A cross-session PL/SQL function result cache
The cross-session PL/SQL function result cache is a mechanism to share frequently accessed functions in the SGA between sessions. Prior to the Oracle 11g Database, each call to a function with the same actual parameters, or run-time values, was cached once per session. The only work-around to that functionality required you to code the access methods.
You designate either of the following to cache results:
RESULT_CACHE clause
or
RESULT_CACHE RELIES_ON(table_name)
The RELIES_ON clause places a limitation on the cached result.
Dynamic SQL enhancements
Dynamic SQL still has two varieties in the Oracle 11g Database. You have Native Dynamic SQL, also known as NDS, and the DBMS_SQL built-in package. Both have been improved in this release.
Native Dynamic SQL
In Oracle 11g, native dynamic SQL now supports dynamic statements larger than 32KB by accepting CLOB. You access it them in lieu of a SQL statement by using the following syntax:
OPEN cursor_name FOR dynamic_string;
The dynamic string can be a CHAR, VARCHAR2, or CLOB. It cannot be a Unicode NCHAR or NVARCHAR2. This removes the prior restriction that limited the size of dynamically built strings
The DBMS_SQL Built-in Package
Several changes have improved the utility of the DBMS_SQL package. Starting with Oracle 11g, you can now use all NDS-supported datatypes. Also, you can now use the PARSE procedure to work with statements larger than 32KB. This is done by using a CLOB datatype. The CLOB replaces the prior work-around that used a table of VARCHAR2 datatypes (typically VARCHAR2A or VARCHAR2S).
Fortunately, the DBMS_SQL package continues to support the work-around, but you should consider moving forward to the better solution.
DBMS_SQL has added two new functions: the TO_REFCURSOR and TO_CURSOR_NUMBER functions. They let you transfer reference cursors to cursors and vice versa. There naturally are some words of wisdom on using these. You must open either the cursor or system reference cursor before using them, and after running them you cannot access their old structures. Basically, the code reassigns the interactive reference from the cursor to system reference cursor or from the system reference cursor to the cursor.
The Oracle 11g Database brings changes in how name and positional notation work in both SQL and PL/SQL. They actually now work the same way in both SQL and PL/SQL. This fixes a longstanding quirk in the Oracle database.
PL/SQL Calls
Previously, you had two choices. You could list all the parameters in their positional order or address some to all parameters by named reference. You can now use positional reference, named reference, or a mix of both.
The following function will let you experiment with the different approaches. The function accepts three optional parameters and returns the sum of three numbers.
CREATE OR REPLACE FUNCTION add_three_numbers ( a NUMBER := 0, b NUMBER := 0, c NUMBER := 0 ) RETURN NUMBER IS BEGIN RETURN a + b + c; END; / Positional Notation You call the function using positional notation by BEGIN dbms_output.put_line(add_three_numbers(3, 4, 5)); END; / Named NotationMixed Notation You call the function using a mix of both positional and named notation by BEGIN dbms_output.put_line(add_three_numbers(3, c => 4, b => 5)); END; /
There is a restriction on mixed notation. All positional notation actual parameters must occur first and in the same order as they are defined by the function signature. You cannot provide a position value after a named value.
Exclusionary Notation
As mentioned, you can also exclude one or more of the actual parameters when the formal parameters are defined as optional. All parameters in the ADD_THREE_NUMBERS function are optional. The following example passes a value to the first parameter by positional reference, and the third parameter by named reference:
BEGIN
dbms_output.put_line(add_three_numbers(3,c => 4));
END;
/
When you opt to not provide an actual parameter, it acts as if you’re passing a null value. This is known as exclusionary notation. This has been the recommendation for years to list the optional variables last in function and procedure signatures. Now, you can exclude one or a couple but not all optional parameters. This is a great improvement, but be careful how you exploit it.
SQL Call Notation
Previously, you had only one choice. You had to list all the parameters in their positional order because you couldn’t use named reference in SQL. This is fixed in Oracle 11g; now you can call them just as you do from a PL/SQL block. The following demonstrates mixed notation in a SQL call:
SELECT add_three_numbers(3,c => 4,b => 5) FROM dual;
As in earlier releases you can only call functions that have IN mode–only variables from SQL statements. You cannot call a function from SQL when any of its formal parameters are defined as IN OUT or OUT mode–only variables. This is because you must pass a variable reference when a parameter has an OUT mode. Functions return a reference to OUT mode variables passed as actual parameters.
Comments
Post a Comment