057-061

Previous

Table of Contents

Next

The OTHERS Exception Handler

The OTHERS exception handler functions as a catch-all exception handler for a PL/SQL block. The OTHERS exception handler is used as follows :

 WHEN OTHERS THEN      System_Error_Handler (<parameters>); 

Care must be taken when raising user -defined exceptions in a block that uses the OTHERS exception handler. If the user-defined exception is to be raised to a calling procedure, an exception handler for the user-defined exception should be defined that explicitly re-raises the exception, as follows:

 EXCEPTION    WHEN StudentNotEligibleForAid THEN         RAISE;    WHEN OTHERS THEN         System_Error_Handler (<parameters>); 

The OTHERS exception handler should follow any other exception handlers. Any exception not handled by another exception handler is handled by the OTHERS exception handler.

IF-THEN-ELSE Logic

PL/SQL introduced to Oracle developers the ability to control the flow of code using IF-THEN-ELSE logic. This type of logic is very similar to the logic found in other languages, as shown by the following code block:

 IF <condition> THEN    <statements> ELSIF <condition> THEN    <statements> ELSE    <statements> END IF; 

The NULL Statement

Not to be confused with a NULL value, the NULL statement is often placed in code to indicate that the code is to take no action. The NULL statement does nothing.

Looping Constructs

In addition to the CURSOR FOR loop that was discussed previously, PL/SQL supports FOR and WHILE loops , as well as allowing developers to use a generic loop structure.

The FOR Loop

As you can see, the FOR loop in PL/SQL is quite similar to FOR loops in other languages:

 FOR x IN 1..50 LOOP     <statements> END LOOP; 

The lower and upper bounds of the FOR loop must evaluate to integers. If the lower and upper bounds of the loop are equal, the loop will execute only once. If the lower bound is greater than the upper bound, the loop will not execute at all. Variables may be substituted for both the lower and upper bounds of the loop.

To run a loop from a high value to a low value, the syntax is slightly different from other languages, as shown by this example:

 FOR x IN REVERSE 1..50 LOOP     <statements> END LOOP; 

Just like a CURSOR FOR loop, the loop index variable (in this case x ) never has to be declared.

The number of iterations of a FOR loop can be easily calculated before the loop is entered by checking the difference between the upper and lower bounds. A FOR loop can execute between zero and MAXINT (the largest integer supported by your hardware and operating system) times.

The LOOP Statement

The PL/SQL LOOP statement can be used alone to create the precise loop structure you need. For instance, you might create a loop that always executes at least once, as follows:

 LOOP     <statements>    IF <condition> THEN         EXIT;     END IF; END LOOP; 

The WHILE Loop

As with the FOR loop, the WHILE Loop is quite similar to a WHILE loop in other languages. For example:

 WHILE MoreRowsToProcess = TRUE LOOP    <statements> END LOOP; 

A WHILE loop can execute from zero to an infinite number of times. The number of iterations of a WHILE loop can never be known until after the loop has finished executing.

The EXIT Statement

PL/SQL provides the EXIT statement to end a loop prematurely. The functionality of the EXIT statement will allow you to exit a single loop or all loops currently executing. If you wish to be able to use the statement to accomplish either type of exit, each of your loops must be given a label. Listing 2.17 illustrates the use of the EXIT statement with multiple loops.

Listing 2.17 Using an EXIT statement with multiple loops.

 <<outer_loop>> LOOP    <<inner_loop>>    LOOP       IF <condition> THEN           EXIT inner_loop;       END IF;       IF <condition> THEN           EXIT outer_loop;       END IF:    END LOOP; END LOOP; 

In addition to this functionality, the EXIT statement can also be followed by a WHEN condition, instead of using IF-THEN logic to test the condition. For example:

 EXIT WHEN Students_cur%NOTFOUND; 

Stored Objects

The release of PL/SQL 2.0 and Oracle7 revolutionized the Oracle world. Previously, all code for enforcing business rules was stored at the application level, often leading to multiple occurrences of the same code in different applications. This was a nightmare when a business rule changed, since it was extremely difficult to track which applications enforced which rules.

Oracle7 allowed developers to store code at the database level, introducing stored procedures, functions, and packages, as well as database triggers. These objects must exist in only one place (inside the database). Not only did this reduce maintenance costs and duration, it also speeded application development time because code only needed to be written once. In many IS departments that use Oracle7, most or all enforcement of business rules is now handled through triggers and stored procedures.

Previous

Table of Contents

Next