Oracle 11g Language & PL/SQL New Features Oracle 11g New Features Tips by Burleson Consulting July 13, 2008

Oracle 11g SQL New Features Tips

Oracle has provided some powerful new capabilities to the PL/SQL language in the 11g database.  Some are small changes that enhance the languages readability and usability.  Others are full blown features that have a dramatic impact on the application performance.  This chapter will introduce the new PL/SQL language features focusing on the powerful new language additions.  The new features include:

• PL/SQL Sequence Access Enhancements

• New SIMPLE Number Types

• The CONTINUE Clause

• PL/SQL Code Inlining

• Compound Triggers and Trigger Enhancements

• The Cross-Session PL/SQL Result Cache

In the 11g database, the PL/SQL compiler will not compile native code directly.  Prior versions of the database would compile the code in two steps, requiring a C compiler for the last step.  This was problematic on systems that were not allowed to have a compiler present due to security requirements.  Because the PL/SQL compiler now produces the native code itself, all users can take advantage of the performance increase provide by native code.

PL/SQL Sequence Access 

Prior to the Oracle 11g database, you would grab the next sequence number using a SQL statement such as below.

declare
  n_num number;
begin
  select seq_1.nextval into n_num from dual;
  dbms_output.put_line('Seq: '||n_num);
end;
/

With 11g you can assign a number the NEXTVAL or CURRVAL directly.  This results in easier to understand code and better performance.

declare
  n_num number;
begin
  n_num := seq_1.nextval;
  dbms_output.put_line('Seq: '||n_num);
  dbms_output.put_line('Cur: '||seq_1.currval
end;
/ 

Seq: 3
Cur: 3

You can use the NEXTVAL and CURRVAL sequence functions anywhere you would use a number type.

Simple Number Types

Numbers are large and take extra processing to use.  Oracle has provided the PLS_INTEGER as a more efficient number type for integer numbers. 11g Oracle provides an even more efficient number type called a SIMPLE_INTEGER. 

A SIMPLE_INTEGER is a subtype of a PLS_INTEGER.  Like the PLS_INTEGER, the SIMPLE_INTEGER has a range of -2147483648 to 2147483648 however the SIMPLE_INTEGER has a NOT NULL constraint.  The difference is that the SIMPLE_INTEGER does not check bounds nor does it check for a NULL value.  What this means to the programmer is that a SIMPLE_INTEGER will wrap from positive to negative when the bounds are exceeded and not throw an error.  A PLS_INTEGER will throw an error when the bounds are exceeded.

A SIMPLE_FLOAT is the same as a BINARY_FLOAT except that it has a NOT NULL constraint.

A SIMPLE_DOUBLE is the same as a BINARY_DOUBLE except that it has a NOT NULL constraint.

So why use these SIMPLE number types?  Because they do not check bounds or NULLs, they are more efficient.  If the PL/SQL code is natively compiles, the number computations on SIMPLE_INTEGERs can be performed in hardware making them substantially more efficient.

The CONTINUE Statement

The CONTINUE statement is used to control program flow within a loop.  When the CONTINUE statement is executed, that loop interaction is stopped and flow continues at the top of the loop.  Below is an example of using an IF/THEN statement, a CONTINUE statement and a CONTINUE WHEN statement.

declare
  n_num number := 0;
begin
  loop
    n_num := n_num + 1;
    if n_num < 5 then
      dbms_output.put_line('Num: '||n_num);
    end if;
    exit when n_num > 9;
  end loop;
end;
/ 

Num: 1
Num: 2
Num: 3
Num: 4 

PL/SQL procedure successfully completed.

Using the CONTINUE statement:

declare
  n_num number := 0;
begin
  loop
    n_num := n_num + 1;
    if n_num => 5 then contunie;
    end if;
    dbms_output.put_line('Num: '||n_num);
    exit when n_num > 9;
  end loop;
end;
/

Num: 1
Num: 2
Num: 3
Num: 4

PL/SQL procedure successfully completed.

Using the CONTINUE WHEN statement:

declare
  n_num number := 0;
begin
  loop
    n_num := n_num + 1;
    continue when n_num => 5;
    dbms_output.put_line('Num: '||n_num);
    exit when n_num > 9;
  end loop;
end;
/

Num: 1
Num: 2
Num: 3
Num: 4

PL/SQL procedure successfully completed.

If you use labels to identify loops, the CONTINUE statement can be used to stop the iteration of a specific labeled loop.  Normally it stops the iteration of the current inner loop.  If you use a label to stop the iteration of an outer loop, cursors opened in the inner loop will automatically be closed.

 

This is an excerpt from the new book Oracle 11g New Features: Expert Guide to the Important New Features by John Garmany, Steve Karam, Lutz Hartmann, V. J. Jain, Brian Carr. You can buy it direct from the publisher for 30% off.