Oracle PLSQL 11g New Features
- 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;
- 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;
- 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.
2 FOR i IN 1 .. 5 LOOP
3 IF i IN (2,4) THEN
5 END IF;
6 DBMS_OUTPUT.PUT_LINE(‘Reached on line ‘ || TO_CHAR(i));
7 END LOOP;
Reached on line 1
Reached on line 3
Reached on line 5