Introduction Introduction
PL/SQL New Features in Oracle 11g
Oracle 11g has introduced a number of new features and improvements for PL/SQL. This introduction briefly describes features not covered in this book and points you to specific chapters for features that are within scope of this book. The list of features described here is also available in the “Whats New in PL/SQL?” section of the PL/SQL Language Reference manual offered as part of Oracle help available online.
The new PL/SQL features and enhancements are as follows:
- Enhancements to regular expression built-in SQL functions
- SIMPLE_INTEGER, SIMPLE_FLOAT, and SIMPLE_DOUBLE datatypes
- CONTINUE statement
- Sequences in PL/SQL expressions
- Dynamic SQL enhancements
- Named and mixed notation in PL/SQL subprogram invocations
- Cross-session PL/SQL function result cache
- More control over triggers
- Compound triggers
- Database resident connection pool
- Automatic subprogram inlining
- PL/Scope
- PL/SQL hierarchical profiler
- PL/SQL native compiler generates native code directly
Enhancements to Regular Expression Built-In SQL Functions
In this release Oracle has introduced a new regular expression built-in function, REGEXP_COUNT. It returns the number of times a specified search pattern appears in a source string.
For Example
SELECT REGEXP_COUNT ('Oracle PL/SQL By Example Updated for Oracle 11g', 'ora', 1, 'i') FROM dual; REGEXP_COUNT('ORACLEPL/SQLBYEXAMPLEUPDATEDFORORACLE11G','ORA',1,'I') -------------------------------------------------------------------- 2
The REGEXP_COUNT function returns how many times the search pattern 'ora' appears in the source string 'Oracle PL/SQL...' 1 indicates the position of the source string where the search begins, and 'i' indicates case-insensitive matching.
The existing regular expression built-in functions, REGEXP_INSTR and REGEXP_SUBSTR, have a new parameter called SUBEXPR. This parameter represents a subexpression in a search pattern. Essentially it is a portion of a search pattern enclosed in parentheses that restricts pattern matching, as illustrated in the following example.
For Example
SELECT REGEXP_INSTR ('Oracle PL/SQL By Example Updated for Oracle 11g', '((ora)(cle))', 1, 2, 0, 'i') FROM dual; REGEXP_INSTR('ORACLEPL/SQLBYEXAMPLEUPDATEDFORORACLE11G',...) ------------------------------------------------------------ 38
The REGEXP_INSTR function returns the position of the first character in the source string 'Oracle PL/SQL...' corresponding to the second occurrence of the first subexpression 'ora' in the seach pattern (ora)(cle). 1 indicates the position of the source string where the search begins, 2 indicates the occurrence of the subexpression in the source string, 0 indicates that the position returned corresponds to the position of the first character where the match occurs, and 'i' indicates case-insensitive matching and REGEXP_SUBSTR.
SIMPLE_INTEGER, SIMPLE_FLOAT, and SIMPLE_DOUBLE Datatypes
These datatypes are predefined subtypes of the PLS_INTEGER, BINARY_FLOAT, and BINARY_DOUBLE, respectively. As such, they have the same range as their respective base types. In addition, these subtypes have NOT NULL constraints.
These subtypes provide significant performance improvements over their respective base types when the PLSQL_CODE_TYPE parameter is set to NATIVE. This is because arithmetic operations for these subtypes are done directly in the hardware layer. Note that when PLSQL_CODE_TYPE is set to INTERPRETED (the default value), the performance gains are significantly smaller. This is illustrated by the following example.
For Example
SET SERVEROUTPUT ON DECLARE v_pls_value1 PLS_INTEGER := 0; v_pls_value2 PLS_INTEGER := 1; v_simple_value1 SIMPLE_INTEGER := 0; v_simple_value2 SIMPLE_INTEGER := 1; -- Following are used for elapsed time calculation -- The time is calculated in 100th of a second v_start_time NUMBER; v_end_time NUMBER; BEGIN -- Perform calculations with PLS_INTEGER v_start_time := DBMS_UTILITY.GET_TIME; FOR i in 1..50000000 LOOP v_pls_value1 := v_pls_value1 + v_pls_value2; END LOOP; v_end_time := DBMS_UTILITY.GET_TIME; DBMS_OUTPUT.PUT_LINE ('Elapsed time for PLS_INTEGER: ' (v_end_time - v_start_time)); -- Perform the same calculations with SIMPLE_INTEGER v_start_time := DBMS_UTILITY.GET_TIME; FOR i in 1..50000000 LOOP v_simple_value1 := v_simple_value1 + v_simple_value2; END LOOP; v_end_time := DBMS_UTILITY.GET_TIME; DBMS_OUTPUT.PUT_LINE ('Elapsed time for SIMPLE_INTEGER: ' (v_end_time - v_start_time)); END;
This script compares the performance of the PLS_INTEGER datatype with its subtype SIMPLE_INTEGER via a numeric FOR loop. Note that for this run the PLSQL_CODE_TYPE parameter is set to its default value, INTERPRETED.
Elapsed time for PLS_INTEGER: 147 Elapsed time for SIMPLE_INTEGER: 115 PL/SQL procedure successfully completed.
CONTINUE Statement
Similar to the EXIT statement, the CONTINUE statement controls loop iteration. Whereas the EXIT statement causes a loop to terminate and passes control of the execution outside the loop, the CONTINUE statement causes a loop to terminate its current iteration and passes control to the next iteration of the loop. The CONTINUE statement is covered in detail in Chapter 7, “Iterative Control—Part 2.”
Sequences in PL/SQL Expressions
Prior to Oracle 11g, the sequence pseudocolumns CURRVAL and NEXTVAL could be accessed in PL/SQL only through queries. Starting with Oracle 11g, these pseudocolumns can be accessed via expressions. This change not only improves PL/SQL source code, it also improves runtime performance and scalability.
For Example
CREATE SEQUENCE test_seq START WITH 1 INCREMENT BY 1; Sequence created. SET SERVEROUTPUT ON DECLARE v_seq_value NUMBER; BEGIN v_seq_value := test_seq.NEXTVAL; DBMS_OUTPUT.PUT_LINE ('v_seq_value: 'v_seq_value); END;
This script causes an error when executed in Oracle 10g:
v_seq_value := test_seq.NEXTVAL; * ERROR at line 4: ORA-06550: line 4, column 28: PLS-00357: Table,View Or Sequence reference 'TEST_SEQ.NEXTVAL' not allowed in this context ORA-06550: line 4, column 4: PL/SQL: Statement ignored
and it completes successfully when executed in Oracle 11g:
v_seq_value: 1 PL/SQL procedure successfully completed.
Consider another example that illustrates performance improvement when the PL/SQL expression is used to manipulate sequences:
For Example
SET SERVEROUTPUT ON DECLARE v_seq_value NUMBER; -- Following are used for elapsed time calculation v_start_time NUMBER; v_end_time NUMBER; BEGIN -- Retrieve sequence via SELECT INTO statement v_start_time := DBMS_UTILITY.GET_TIME; FOR i in 1..10000 LOOP SELECT test_seq.NEXTVAL INTO v_seq_value FROM dual; END LOOP; v_end_time := DBMS_UTILITY.GET_TIME; DBMS_OUTPUT.PUT_LINE ('Elapsed time to retrieve sequence via SELECT INTO: ' (v_end_time-v_start_time)); -- Retrieve sequence via PL/SQL expression v_start_time := DBMS_UTILITY.GET_TIME; FOR i in 1..10000 LOOP v_seq_value := test_seq.NEXTVAL; END LOOP; v_end_time := DBMS_UTILITY.GET_TIME; DBMS_OUTPUT.PUT_LINE ('Elapsed time to retrieve sequence via PL/SQL expression: ' (v_end_time-v_start_time)); END; Elapsed time to retrieve sequence via SELECT INTO: 52 Elapsed time to retrieve sequence via PL/SQL expression: 43 PL/SQL procedure successfully completed.
Dynamic SQL Enhancements
In this version, Oracle has introduced a number of enhancements to the native dynamic SQL and DBMS_SQL package.
Native dynamic SQL enables you to generate dynamic SQL statements larger than 32K. In other words, it supports the CLOB datatype. Native dynamic SQL is covered in detail in Chapter 17, “Native Dynamic SQL.”
The DBMS_SQL package now supports all datatypes that native dynamic SQL supports. This includes the CLOB datatype. In addition, two new functions, DBMS_SQL.TO_REFCURSOR and DBMS_SQL.TO_CURSOR_NUMBER, enable you to switch between the native dynamic SQL and DBMS_SQL package.
Named and Mixed Notation in PL/SQL Subprogram Invocations
Prior to Oracle 11g, a SQL statement invoking a function had to specify the parameters in positional notation. In this release, mixed and named notations are allowed as well. Examples of positional, named, and mixed notations can be found in Chapter 21, “Packages,” and Chapter 23, “Object Types in Oracle.”
Consider the following example:
For Example
CREATE OR REPLACE FUNCTION test_function (in_val1 IN NUMBER, in_val2 IN VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN (in_val1' - 'in_val2); END; Function created. SELECT test_function(1, 'Positional Notation') col1, test_function(in_val1 => 2, in_val2 => 'Named Notation') col2, test_function(3, in_val2 => 'Mixed Notation') col3 FROM dual; COL1 COL2 COL3 ----------------------- ------------------ ------------------ 1 - Positional Notation 2 - Named Notation 3 - Mixed Notation
Note that mixed notation has a restriction: positional notation may not follow named notation. This is illustrated by the following SELECT:
SELECT test_function(1, 'Positional Notation') col1, test_function(in_val1 => 2, in_val2 => 'Named Notation') col2, test_function(in_val1 => 3, 'Mixed Notation') col3 FROM dual; test_function(in_val1 => 3, 'Mixed Notation') col3 * ERROR at line 4: ORA-06553: PLS-312: a positional parameter association may not follow a named association
Cross-Session PL/SQL Function Result Cache
A result-cached function is a function whose parameter values and result are stored in the cache. This means that when such a function is invoked with the same parameter values, its result is retrieved from the cache instead of being computed again. This caching mechanism is known as single-session caching because each session requires its own copy of the cache where function parameters and its results are stored.
Starting with Oracle 11, the caching mechanism for result-cached functions has been expanded to cross-session caching. In other words, the parameter values and results of the result-cached function are now stored in the shared global area (SGA) and are available to any session. Note that when an application is converted from single-session caching to cross-session caching, it requires more SGA but considerably less total system memory.
Consider the following example, which illustrates how a result-cached function may be created:
For Example
-- Package specification CREATE OR REPLACE PACKAGE test_pkg AS -- User-defined record type TYPE zip_record IS RECORD (zip VARCHAR2(5), city VARCHAR2(25), state VARCHAR2(2)); -- Result-cached function FUNCTION get_zip_info (in_zip NUMBER) RETURN zip_record RESULT_CACHE; END test_pkg; / -- Package body CREATE OR REPLACE PACKAGE BODY test_pkg AS -- Result-cached function FUNCTION get_zip_info (in_zip NUMBER) RETURN zip_record RESULT_CACHE RELIES_ON (ZIPCODE) IS rec zip_record; BEGIN SELECT zip, city, state INTO rec FROM zipcode WHERE zip = in_zip; RETURN rec; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN null; END get_zip_info; END test_pkg; /
Note the use of the RESULT_CACHE and RELIES_ON clauses. RESULT_CACHE specifies that the function is a result-cached function, and RELIES_ON specifies any tables and/or views that the function results depend on.
More Control over Triggers
Starting with Oracle 11g, the CREATE OR REPLACE TRIGGER clause may include ENABLE, DISABLE, and FOLLOWS options. The ENABLE and DISABLE options allow you to create a trigger in the enabled or disabled state, respectively. The FOLLOWS option allows you to specify the order in which triggers fire. Note that the FOLLOWS option applies to triggers that are defined on the same table and fire at the same timing point. Triggers are covered in detail in Chapter 13, “Triggers.”
Compound Triggers
A compound trigger is a new type of trigger that allows you to combine different types of triggers into one trigger. Specifically, you can combine the following:
- A statement trigger that fires before the firing statement
- A row trigger that fires before each row that the firing statement affects
- A row trigger that fires after each row that the firing statement affects
- A statement trigger that fires after the firing statement
This means that a single trigger may fire at different times when a transaction occurs. Compound triggers are covered in detail in Chapter 14, “Compound Triggers.”
Database Resident Connection Pool
Database Resident Connection Pool (DRCP) provides a connection pool that is shared by various middle-tier processes. The new package, DBMS_CONNECTION_POOL, enables database administrators to start and stop DRCP and configure its parameters.
Automatic Subprogram Inlining
The PL/SQL compiler translates PL/SQL code into machine code. Starting with Oracle 10g, the PL/SQL compiler can use the performance optimizer when compiling PL/SQL code. The performance optimizer enables the PL/SQL compiler to rearrange PL/SQL code to enhance performance. The optimization level used by the PL/SQL compiler is controlled by the PLSQL_OPTIMIZE_LEVEL parameter. Its values range from 0 to 2, where 2 is the default value. This means that the PL/SQL compiler performs optimization by default.
Starting with Oracle 11g, the PL/SQL compiler can perform subprogram inlining. Subprogram inlining substitutes a subprogram invocation with an actual copy of the called subprogram. This is achieved by specifying PRAGMA INLINE or setting the PLSQL_OPTIMIZE_LEVEL parameter to a new value, 3. When PLSQL_OPTIMIZE_LEVEL is set to 3, the PL/SQL compiler performs automatic subprogram inlining where appropriate. However, in some instances, the PL/SQL compiler may choose not to perform subprogram inlining because it believes it is undesirable.
The use of PRAGMA INLINE is illustrated in the following example. Note that in this example, PLSQL_OPTIMIZE_LEVEL has been set to its default value, 2.
For Example
SET SERVEROUTPUT ON DECLARE v_num PLS_INTEGER := 1; v_result PLS_INTEGER; -- Following are used for elapsed time calculation v_start_time NUMBER; v_end_time NUMBER; -- Define function to test PRAGMA INLINE FUNCTION test_inline_pragma (in_num1 IN PLS_INTEGER, in_num2 IN PLS_INTEGER) RETURN PLS_INTEGER IS BEGIN RETURN (in_num1 + in_num2); END test_inline_pragma; BEGIN -- Test function with INLINE PRAGMA enabled v_start_time := DBMS_UTILITY.GET_TIME; FOR i in 1..10000000 LOOP PRAGMA INLINE (test_inline_pragma, 'YES'); v_result := test_inline_pragma (1, i); END LOOP; v_end_time := DBMS_UTILITY.GET_TIME; DBMS_OUTPUT.PUT_LINE ('Elapsed time when PRAGMA INLINE enabled: ' (v_end_time-v_start_time)); -- Test function with PRAGMA INLINE disabled v_start_time := DBMS_UTILITY.GET_TIME; FOR i in 1..10000000 LOOP PRAGMA INLINE (test_inline_pragma, 'NO'); v_result := test_inline_pragma (1, i); END LOOP; v_end_time := DBMS_UTILITY.GET_TIME; DBMS_OUTPUT.PUT_LINE ('Elapsed time when INLINE PRAGMA disabled: ' (v_end_time-v_start_time)); END; Elapsed time when PRAGMA INLINE enabled: 59 Elapsed time when PRAGMA INLINE disabled: 220 PL/SQL procedure successfully completed.
Note that PRAGMA INLINE affects every call to the specified subprogram when PRAGMA INLINE is placed immediately before one of the following:
- Assignment
- Call
- Conditional
- CASE
- CONTINUE-WHEN
- EXECUTE IMMEDIATE
- EXIT-WHEN
- LOOP
- RETURN
PL/Scope
PL/Scope gathers and organizes data about user-defined identifiers used in PL/SQL code. This tool is used primarily in interactive development environments such as SQL Developer or Jdeveloper rather than directly in PL/SQL.
PL/SQL Hierarchical Profiler
PL/SQL hierarchical profiler enables you to profile PL/SQL applications. In other words, it gathers statistical information about the application such as execution times for SQL and PL/SQL, the number of calls to a particular subprogram made by the application, and the amount of time spent in the subprogram itself.
The hierarchical profiler is implemented via the Oracle-supplied package DBMS_HPROF, which is covered in Chapter 24, “Oracle Supplied Packages.”
PL/SQL Native Compiler Generates Native Code Directly
In this version of Oracle, the PL/SQL native compiler can generate native code directly. Previously, PL/SQL code was translated into C code, which then was translated by the C compiler into the native code. In some cases, this improves performance significantly. The PL/SQL compiler type is controlled via the PLSQL_CODE_TYPE parameter, which can be set to either INTERPRETED (the default value) or NATIVE.
© Copyright Pearson Education. All rights reserved.