4. About PL/SQL Versions

Each version of the Oracle database comes with its own corresponding version of PL/SQL. As you use more up-to-date versions of PL/SQL, an increasing array of functionality will be available to you. One of our biggest challenges as PL/SQL programmers is simply "keeping up." We need to constantly educate ourselves about the new features in each versionfiguring out how to use them and how to apply them to our applications, and determining which new techniques are so useful that we should modify existing applications to take advantage of them.

1.4.1. Oracle Database 10g New Features
As we mentioned earlier, Oracle Database 10g PL/SQL offers three very powerful and important new areas of functionality:

Automatic, transparent optimization of code

Compile-time warnings

Preprocessor support

In Oracle Database 10g Release 1, PL/SQL also extends the flexibility of collections, both in FORALL statements and for high-level set processing of nested tables. These and other new capabilities are described briefly in the following subsections, and more thoroughly in the appropriate chapters.

1.4.1.1 Optimized compiler
PL/SQL’s optimizing compiler, introduced in Oracle Database 10g Release 1 can improve runtime performance dramatically, imposing a relatively slight overhead at compile time. Fortunately, the benefits of optimization apply both to interpreted and to natively compiled PL/SQL (a feature introduced in the previous release) because optimizations are applied by analyzing patterns in source code.

The optimizing compiler is enabled by default. However, you may want to alter its behavioreither by lowering its aggressiveness or by disabling it entirely. For example, if, in the course of normal operations, your system must perform recompilation of many lines of code, or if an application generates many lines of dynamically executed PL/SQL, the overhead of optimization may be unacceptable. Keep in mind, though, that Oracle’s tests show that the optimizer doubles the runtime performance of computationally intensive PL/SQL.

To change the optimizer settings for the entire database, set the database parameter PLSQL_OPTIMIZE_LEVEL. Valid settings are:

No optimization

Moderate optimization, such as eliminating superfluous code or exceptions

2 (default)

Aggressive optimization beyond level 1, including rearranging source code

These settings are also modifiable for the current session; for example:

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 0;

Oracle retains optimizer settings on a module-by-module basis. When you recompile a particular module with nondefault settings, the settings will "stick," allowing you to recompile later on using REUSE SETTINGS. For example:

ALTER PROCEDURE bigproc COMPILE PLSQL_OPTIMIZE_LEVEL = 0;

and then:

ALTER PROCEDURE bigproc COMPILE REUSE SETTINGS;

1.4.1.2 Compile-time warnings
Starting with Oracle Database 10g Release 1 you can enable additional compile-time warnings to help make your programs more robust. These warnings highlight potential problems that are not severe enough to raise an exception but may result in runtime errors or poor performance.

To enable these warnings, you need to set the database initialization parameter PLSQL_WARNINGS. This parameter can be set globally in the SPFILE, in your session with the ALTER SESSION command, or with the built-in procedure DBMS_WARNING.

For example, to enable all warnings in your session, execute:

ALTER SESSION SET plsql_warnings = ‘enable:all’

1.4.1.3 Conditional compilation
Introduced in Oracle Database 10g Release 1, conditional compilation allows the compiler to compile selected parts of a program based on conditions you provide with the $IF directive. Conditional compilation can come in very handy when you need to write a program that automatically takes advantage of version-specific features; you can, for example, run special code during test and debug phases.

PL/SQL’s conditional compilation feature allows you to insert conditional logic that is processed before sending the code to the compiler. The following block shows the use of the $IF selection directive to test the values of preset flags to determine if tracing logic should be compiled into the procedure:

    CREATE OR REPLACE PROCEDURE calculate_totals IS
    BEGIN
    $IF $$oe_debug AND $$oe_trace_level >= 5
    $THEN
       DBMS_OUTPUT.PUT_LINE (‘Tracing at level 5 or higher’);
    $END
       NULL;
    END calculate_totals;

1.4.1.4 Support for nonsequential collections in FORALL
You can use collections to improve the performance of SQL operations executed iteratively by using bulk binds. Bulk binding with FORALL reduces the number of context switches between the PL/SQL engine and the SQL engine. With previous releases, the collections used with FORALL had to be densely filled (all rows between the first and last defined). However, starting with Oracle Database 10g Release 1, if there are nonconsecutive index values because of deletions, you can use the INDICES OF syntax to skip over the deleted elements:

FORALL i IN INDICES OF inactives
DELETE FROM ledger WHERE acct_no = inactives(i);

In addition, with Oracle Database 10g, if you are interested in the values of a sparse collection of integers instead of the indices, you can use the VALUES OF syntax:

FORALL i IN VALUES OF inactives_list
DELETE FROM ledger WHERE acct_no = inactives(i);

1.4.1.5 Improved datatype support
Oracle provides a variety of datatypes to store 32-bit whole numbers: BINARY_INTEGER, INTEGER, INT, SMALLINT, NATURAL, NATURALN, POSITIVE, POSITIVEN, SIGNTYPE, and PLS_INTEGER. Prior to Oracle Database 10g, all of these except PLS_INTEGER were manipulated using the same C-language arithmetic library as the NUMBER datatype. PLS_INTEGER, though, uses machine arithmetic, which is up to three times faster than library arithmetic.

In Oracle Database 10g, the distinction among these datatypes has been eliminated, and all these whole number datatypes now use the speedier machine arithmetic. Binary integer datatypes store signed integers in the range of -231 + 1 to 231 – 1. The subtypes include NATURAL (0 through 231) and POSITIVE (1 through 231) together with the NOT NULL variations NATURALN and POSITIVEN. SIGNTYPE is restricted to three values (-1, 0, 1). PLS_INTEGER is an unconstrained subtype (alias) of BINARY_INTEGER.

Oracle Database 10g Release 1 introduced IEEE 754 compliant floating-point numbers to both SQL and PLSQL. These subtypes are the single-precision BINARY_FLOAT and the double-precision BINARY_DOUBLE. These datatypes require less memory and use native machine arithmetic, thus performing better for scientific or engineering applications that are compute-intensive or that require comparison to infinity or NaN (Not a Number). These two datatypes have binary precision instead of the decimal precision used in the NUMBER family. So, financial applications that are concerned with rounding errors or that require decimal precision should probably not use these floating-point datatypes.

1.4.1.6 Backtrace an exception to its line number
Oracle Database 10g Release 1 added the FORMAT_ERROR_BACKTRACE function to the DBMS_UTILITY package. This function fills a long-standing and very frustrating hole in PL/SQL: when handling an error, how can you find the line number on which the error was originally raised?

In earlier releases, the only way to do this was to allow your exception to go unhandled and then view the full error trace stack. Now, you can (and should) call DBMS_UTILITY.FORMAT_ERROR_BACKTRACE instead to obtain that stack and manipulate it programmatically within your program. Here is a very simple example demonstrating a call to this function:

    CREATE OR REPLACE PROCEDURE my_procedure IS
    BEGIN
       run_some_logic;
    EXCEPTION
       WHEN OTHERS THEN
          Write_to_log (
             DBMS_UTILITY.format_error_backtrace);
          RAISE;
    END;
    /

1.4.1.7 Set operators for nested tables
The SQL language has long offered the ability to apply set operations (UNION, INTERSECT, and MINUS) to the result sets of queries. You can now use similar high-level, very powerful operators against nested tables (and only nested tables) in your PL/SQL programs. You can also perform equality comparisons between nested tables.

The following block of code offers a quick example of many of these new features:

    DECLARE
      TYPE nested_type IS TABLE OF NUMBER;
      nt1 nested_type := nested_type(1,2,3);
      nt2 nested_type := nested_type(3,2,1);
      nt3 nested_type := nested_type(2,3,1,3);
      nt4 nested_type := nested_type(1,2,4);
      answer nested_type;
    BEGIN
      answer := nt1 MULTISET UNION nt4; — (1,2,3,1,2,4)
      answer := nt1 MULTISET UNION nt3; — (1,2,3,2,3,1,3)

answer := nt1 MULTISET UNION DISTINCT nt3; — (1,2,3)

answer := nt2 MULTISET INTERSECT nt3; — (3,2,1)

answer := nt3 MULTISET EXCEPT nt2; — (3)

answer := SET(nt3); — (2,3,1)

      IF 3 MEMBER OF (nt3 MULTISET EXCEPT nt2) THEN
         dbms_output.put_line(‘3 is in the answer set’);
      END IF;
    END;

1.4.1.8 Support for regular expressions
Oracle Database 10g supports the use of regular expressions inside PL/SQL code via four new built-in functions: REGEXP_LIKE, REGEXP_INSTR, REGEXP_SUBSTR, and REGEXP_REPLACE. Regular expression support is a very exciting development for PL/SQL; the REGEXP_REPLACE function adds especially helpful functionality.

Using REGEXP_SUBSTR, you can do such things as extract text that matches a phone number pattern from a string. Using REGEXP_REPLACE, you can reformat that phone number in place. The following code block illustrates both functions:

    DECLARE
       my_string VARCHAR2(60)
          := ‘The phone number 999-888-7777 is not ours.’;
       phone_number VARCHAR2(12);
       phone_number_pattern VARCHAR2(60)
          := ‘([[:digit:]]{3})-([[:digit:]]{3}-[[:digit:]]{4})’;
    BEGIN
       –Extract and display the phone number, if there is one.
       phone_number := REGEXP_SUBSTR(my_string, phone_number_pattern);
       DBMS_OUTPUT.PUT_LINE(phone_number);

       –Reformat the phone number
       my_string := REGEXP_REPLACE(
          my_string, phone_number_pattern, ‘(1) 2’);

       –Show the newly formatted string
       DBMS_OUTPUT.PUT_LINE(my_string);
    END;
    /

The output from this code block is:

999-888-7777
The phone number (999) 888-7777 is not ours.

Oracle has done innovative work here! We have seen regular expression implementations in other database products, and typically such implementations don’t extend beyond the ability to use regular expressions for searching, such as in the LIKE predicate of a SQL SELECT statement. Oracle lets us do more than just search, and we expect to see many creative solutions involving these new regular expression features.

1.4.1.9 Programmer-defined quoting mechanism
Starting with Oracle Database 10g Release 1, you can define your own quoting mechanism for string literals in both SQL and PL/SQL. Use the characters q’ (q followed by a single quote) to note the programmer-defined delimiter for your string literal. Oracle interprets the character following the q’ as the delimiter for your string. NCHAR and NVARCHAR delimiters are preceded with the letter nq as in nq’^nchar string^’.

This technique can simplify your code when single quotes appear within a string, such as the literals in a SQL statement. If you define your delimiter with one of the four bracketing characters, ( [ { <, you need to use the right-side version of that bracketing character as the closing delimiter. For example q'[ needs to be closed with ]’.

1.4.1.10 Many new built-in packages
Oracle continues to add many new built-in or supplied packages with each release of the database. Here are some of the most significant packages in Oracle Database 10g for PL/SQL developers:

DBMS_SCHEDULER

Represents a major update to DBMS_JOB. DBMS_SCHEDULER provides much improved functionality for scheduling and executing jobs defined via stored procedures.

DBMS_CRYPTO

Offers the ability to encrypt and decrypt common Oracle datatypes, including RAWs, BLOBs, and CLOBs. It also provides globalization support for encrypting data across different character sets.

DBMS_MONITOR

Provides an API to control additional tracing and statistics gathering of sessions.

DBMS_WARNING

Provides an API into the PL/SQL compiler warnings module, allowing you to read and change settings that control which warnings are suppressed, displayed, or treated as errors.

Kaynak: Oracle PL/SQL Programming
belgesi-1164

Belgeci