Oracle PLSQL 11g New Features

Oracle PLSQL 11g New Features

  1. sequences have never been directly supported in PL/SQL. In versions prior to 11g, assigning a variable from a sequence (i.e. retrieving NEXTVAL or CURRVAL) is a SQL-only operation (we typically use a SELECT..INTO..FROM DUAL construct). In 11g, however, Oracle has added sequence fetching (using NEXTVAL and CURRVAL) to the list of valid PL/SQL expressions.

n := s.NEXTVAL;

  1. Oracle 11g now supports named notation in SQL calls to PL/SQL functions.

SQL> SELECT function_name(p_arg1 => 10,

2                       p_arg2 => 20,

3                       p_arg3 => 30)

4  FROM   dual;

  1. most programming languages have multiple control commands that enable us to control program-flow within a loop. PL/SQL has supported the GOTO and EXIT statements for many years, but in 11g, Oracle has added the CONTINUE statement. This statement enables us to terminate the current iteration of a loop and return to the start of the next cycle.

The easiest way to understand the CONTINUE statement is by example. We will begin by coding a simple FOR loop with five iterations. At the end of each iteration we will output a message, but on the even-numbered iterations, we will skip to the start of the next iteration.

SQL> BEGIN

2     FOR i IN 1 .. 5 LOOP

3        IF i IN (2,4) THEN

4           CONTINUE;

5        END IF;

6        DBMS_OUTPUT.PUT_LINE(‘Reached on line ‘ || TO_CHAR(i));

7     END LOOP;

8  END;

9  /

Reached on line 1

Reached on line 3

Reached on line 5

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s