0272-0275

Previous Table of Contents Next

Page 272

TIP

You can name a loop as shown in the example by using a label such as <<loop_name>> just before the LOOP statement. Although it's not required, labeling does enable you to keep better track of nested loops .

WHILE Loops

The WHILE loop checks the status of any PL/SQL expression that evaluates to TRUE, FALSE, or NULL at the start of each processing cycle. The following is an example of the use of WHILE loops:

 WHILE (expression) LOOP   (loop processing) END LOOP; 

As stated, the program evaluates the expression at the start of each loop cycle. The program performs the loop processing if the expression evaluates to TRUE. A FALSE or NULL evaluation terminates the loop. Iterations through the loop are exclusively determined by the evaluation of the expression.

Numeric FOR Loops

You can control loop iterations with the use of numeric FOR loops. This mechanism enables the developer to establish a range of integers for which the loop will cycle. The following example from the order_total package illustrates numeric FOR loops:

 <<recip_list>>     FOR i in 1..g_line_counter LOOP       (loop processing)     END LOOP recip_list; 

In this example, loop processing cycles over the range of integers 1 through the value of g_line_counter. The value of the loop index i is checked at the start of the loop and incremented at the end of the loop. When i is one greater than g_line_counter, the loop terminates.

Cursor FOR Loops

Cursor FOR loops combine cursor control and conditional control for manipulation of database information. The loop index, cursor OPEN, cursor FETCH, and cursor CLOSE are all implicit when using cursor FOR loops. Consider the following example:

 CURSOR c_line_item IS (sql statement) BEGIN   FOR li_info IN c_line_item LOOP     (retrieved record processing)   END LOOP; END; 

Page 273

As shown, the program explicitly declares the c_line_item cursor before its reference in the FOR loop. When the program enters the FOR loop, the code implicitly opens c_line_item and implicitly creates the li_info record as if the following declaration were made:

 li_info c_line_item%ROWTYPE; 

Once inside the loop, the program can reference the fields of the li_info record that are assigned values by the implicit FETCH inside the FOR loop. Fields of li_info mirror the row retrieved by the c_line_item cursor.

When data is exhausted for the FETCH, c_line_item is implicitly closed.

NOTE

You cannot reference the information contained in li_info from outside the cursor FOR loop.

Iterative Control

The IF-THEN-ELSE structure provides alternative processing paths that depend on certain conditions. For example, consider merchandise orders with multiple-line items where a list of recipients is built. Using conditional and iterative control to build the recipient list, the code is as follows :

 PROCEDURE   init_recip_list IS   recipient_num  NUMBER;   i                       BINARY_INTEGER;   j                       BINARY_INTEGER := 1;   k                      BINARY_INTEGER;   BEGIN     g_out_msg := `init_recip_list';     <<recip_list>>     FOR i in 1..g_line_counter LOOP       IF i = 1 THEN         g_recip_list(j) := g_recipient_num(i);         j := j + 1;         g_recip_list(j) := 0;       ELSE         FOR k in 1..j LOOP           IF g_recipient_num(i) = g_recip_list(k) THEN             exit;           ELSIF k = j THEN             g_recip_list(j) := g_recipient_num(i);             j := j + 1;             g_recip_list(j) := 0;           end IF;         end LOOP;       end IF;     end LOOP recip_list;   END; 

Page 274

In the order_total example, the subprogram init_recip_list builds a list of unique recipient numbers for calculating additional shipping charges. There is a controlling FOR loop that cycles through each recipient number found on a particular order. The g_recip_list array is initialized with the first recipient number, and subsequent numbers are checked against all unique numbers in g_recip_list until a unique list of all recipients is compiled.

Also illustrated in this example is the IF-THEN-ELSE extension ELSIF. This statement provides further conditional control with additional constraint checks within the IF-THEN-ELSE structure. Use of ELSIF also requires a THEN statement in executing logic control.

Another example of iterative control is the use of the EXIT-WHEN statement that allows completion of a LOOP once certain conditions are met. Consider the example of exiting a cursor FETCH loop:

 open c_line_item;   loop     fetch c_line_item     into li_info;     EXIT WHEN (c_line_item%NOTFOUND) or (c_line_item%NOTFOUND is NULL); 

In this example, the LOOP is terminated when no more data is found to satisfy the SELECT statement of cursor c_line_item.

CAUTION

Using %NOTFOUND or %FOUND can cause infinite loops if you do not check for these attributes evaluating to NULL on an EXIT-WHEN logical check.

Exception Handling

PL/SQL exception handling is a mechanism for dealing with runtime errors encountered during procedure execution. Use of this mechanism enables execution to continue if the error is not severe enough to cause procedure termination. The decision to let a procedure continue after an error condition is one you have to make in development as you consider the errors that might arise.

You must define the exception handler within a subprogram specification. Errors cause the program to raise an exception with a transfer of control to the exception-handler block. After the exception handler executes, control returns to the block in which the handler was defined. If there are no more executable statements in the block, control returns to the caller.

User -Defined Exceptions

PL/SQL enables you to define exception handlers in the declarations area of subprogram specifications. You accomplish this by naming an exception:

 ot_failure            EXCEPTION; 

Page 275

In this case, the exception name is ot_failure. Code associated with this handler is written in the EXCEPTION specification area as follows:

 EXCEPTION       when OT_FAILURE then         out_status_code := g_out_status_code;         out_msg         := g_out_msg; 

This exception is defined in the order_total example to capture status and associated data for any NO_DATA_FOUND exceptions encountered in a subprogram. The following is an example of a subprogram exception:

 EXCEPTION       when NO_DATA_FOUND then         g_out_status_code := `FAIL';         RAISE ot_failure; 

Within this exception is the RAISE statement that transfers control back to the ot_failure exception handler. This technique of raising the exception is used to invoke all user-defined exceptions.

System-Defined Exceptions

Exceptions internal to PL/SQL are raised automatically upon error. NO_DATA_FOUND from the previous example is a system-defined exception. Table 12.3 contains a complete list of internal exceptions.

Table 12.3. PL/SQL internal exceptions.

Exception Name	Oracle Error
CURSOR_ALREADY_OPEN	ORA-06511
DUP_VAL_ON_INDEX	ORA-00001
INVALID_CURSOR	ORA-01001
INVALID_NUMBER	ORA-01722
LOGIN_DENIED	ORA-01017
NO_DATA_FOUND	ORA-01403
NOT_LOGGED_ON	ORA-01012
PROGRAM_ERROR	ORA-06501
STORAGE_ERROR	ORA-06500
TIMEOUT_ON_RESOURCE	ORA-00051
TOO_MANY_ROWS	ORA-01422
TRANSACTION_BACKED_OUT	ORA-00061
VALUE_ERROR	ORA-06502
ZERO_DIVIDE	ORA-01476

Previous Table of Contents Next