[Chapter 5] 5.2 Sequential Control StatementsChapter 5Conditional and Sequential Control 5.2 Sequential Control StatementsCertain PL/SQL control structures offer structured methods for processing executable statements in your program. You use an IF statement to test a condition to determine which parts of the code to execute. You use one of the LOOP variations (described in Chapter 7, Loops) to execute a section of code more than once. In addition to these well-structured approaches to program control, PL/SQL offers two other statements to handle out of the ordinary requirements for sequential processing: GOTO and NULL. The GOTO statement allows you to perform unconditional branching to another executable statement in the same execution section of a PL/SQL block. The NULL statement gives you a way to tell the compiler to do...absolutely nothing. The following sections explain the implementation of the GOTO and NULL statements in PL/SQL. As with other constructs in the language, use them with care and use them appropriately, and your programs will be stronger for it. 5.2.1 The GOTO Statement The GOTO statement performs unconditional branching to a named label. The general format for a GOTO statement is: GOTO label_name;where label_name is the name of a label. This GOTO label is defined in the program as follows:<<label_name>> You must surround the label name with double enclosing angle brackets (<< >>). In this case, the label names a loop. This label can then be appended to the END LOOP statement, making the termination of the loop more visible. You can also issue an EXIT statement for a particular labeled loop from within another (enclosed) loop. Finally, you can GOTO that loop label, even though it was "designed" for a loop. (Chapter 7 describes the details of loop processing.) When PL/SQL encounters a GOTO statement, it immediately shifts control to the first executable statement following the label. Contrary to popular opinion (including mine), the GOTO statement can come in handy. There are cases where a GOTO statement can simplify the logic in your program. On the other hand, PL/SQL provides so many different control constructs and modularization techniques that you can almost always find a better way to do something than with a GOTO. There are several restrictions regarding the GOTO statement, described in the sections below. 5.2.1.1 At least one executable statement must follow a labelA label itself is not an executable statement (notice that it does not have a semicolon (;) after the label brackets), so it cannot take the place of one. All of the uses of the loop label in the following blocks are illegal because the labels are not followed by an executable statement: IF status_inout = 'COMPLETED' THEN <<all_done>> /* Illegal! */ ELSE schedule_activity; END IF; DECLARE CURSOR company_cur IS ...; BEGIN FOR company_rec IN company_cur LOOP apply_bonuses (company_rec.company_id); <<loop_termination>> /* Illegal! */ END LOOP; END; FUNCTION new_formula (molecule_in IN NUMBER) RETURN VARCHAR2 IS BEGIN ... construct formula for molecule ... RETURN formula_string; <<all_done>> /* Illegal! */ END;5.2.1.2 Target labels and scope of GOTO The target label must be in the same scope as the GOTO statement. In the context of the GOTO statement, each of the following constructs maintains its own scope: functions, procedures, anonymous blocks, IF statements, LOOP statements, and exception handlers. All of the code examples below generate the same PL/SQL error: PLS-00375: illegal GOTO statement; this GOTO cannot branch to label IF conditions. The only way to enter an IF statement is through an evaluation to TRUE of an IF condition. Therefore, this code produces an error: GOTO label_inside_IF; IF status = 'NEW' THEN <<label_inside_IF>> /* Out of scope! */ show_new_one; END IF;BEGIN statements. The only way to enter a block-within-a-block is through the sub-block's BEGIN statement. PL/SQL insists on orderly entrances and exits. This code produces an error because it doesn't comply with this structure: GOTO label_inside_subblock; BEGIN <<label_inside_subblock>> /* Crosses block boundary! */ NULL; END;Scope of IF statements. Each IF clause of the IF statement is its own scope. A GOTO may not transfer from one clause to another. This code produces an error: IF status = 'NEW' THEN <<new_status>> GOTO old_status; /* Crosses IF clause boundary! */ ELSIF status = 'OLD' THEN <<old_status>> GOTO new_status; /* Crosses IF clause boundary! */ END IF;Don't jump into the middle of a loop. You cannot jump into the middle of a loop with a GOTO. This code produces an error: FOR month_num IN 1 .. 12 LOOP <<do_a_month>> schedule_activity (month_num); END LOOP; GOTO do_a_month; /* Can't go back into loop. */ Don't GOTO a local module. You also cannot issue a GOTO inside of a module to a label in the main body. This code produces an error: DECLARE FUNCTION local_null IS BEGIN <<descrip_case_statement>> NULL; END; BEGIN GOTO descrip_case_statement; /* Label not visible here. */ END;5.2.1.3 Target labels and PL/SQL blocksThe target label must be in the same part of the PL/SQL block as the GOTO statement. A GOTO in the executable section may not go to a label in the exception section. Similarly, a GOTO in the exception section may not go to a label in the executable section. A GOTO in an exception handler may reference a label in the same handler. The code example below generates the same PL/SQL error shown in the previous section (PLS-00375): BEGIN /* || The label and GOTO must be in the same section! */ GOTO out_of_here; EXCEPTION WHEN OTHERS THEN <<out_of_here>> /* Out of scope! */ NULL; END; 5.2.2 The NULL Statement Usually when you write a statement in a program, you want it to do something. There are cases, however, when you want to tell PL/SQL to do absolutely nothing, and that is where the NULL statement comes in handy. The NULL statement has the following format: NULL; Well, you wouldn't want a do-nothing statement to be complicated, would you? The NULL statement is simply the reserved word followed by a semicolon (;) to indicate that this is a statement and not the NULL value reserved word. The NULL statement does nothing except pass control to the next executable statement. Why would you want to use the NULL statement? There are several reasons, described in the following sections. 5.2.2.1 Improving the readability of your program There are many situations in your program where you logically do not want to take any action. In most of these cases, PL/SQL will let you write nothing and the program will execute as you wish. The only drawback is the ambiguity surrounding this solution: it is not clear to a person examining the program that you knowingly did not take any action. Consider the IF statement. When you write an IF statement you do not have to include an ELSE clause. To produce a report based on a selection, you can code: IF :report.selection = 'DETAIL' THEN exec_detail_report; END IF;What should the program have been doing if the report selection is not `DETAIL'? One would assume that the program was supposed to do nothing. But because this is not explicitly stated in the code, one is left to wonder if perhaps there was an oversight. If, on the other hand, you include an explicit ELSE clause that does nothing, you state very clearly, "Don't worry, I thought about this possibility and I really want nothing to happen." IF :report.selection = 'DETAIL' THEN exec_detail_report; ELSE NULL; END IF;5.2.2.2 Nullifying the effect of a raised exceptionThe optional exception section of a program contains one or more exception handlers. These handlers trap and handle errors that have been raised in your program. The structure and flow of the exception section is similar in structure and flow to a conditional case statement, as follows: EXCEPTION WHEN <exception_name1> THEN executable_statements; WHEN <exception_nameN> THEN executable_statements; WHEN OTHERS THEN executable_statements; END;If <exception_name1> is raised, then execute its statements; if <exception_nameN> is raised, then execute its statements; and so on. The WHEN OTHERS clause handles any exceptions not handled in the previous WHEN clauses (it is just like the ELSE clause of the IF statement). You can use the NULL statement to make sure that a raised exception halts execution of the current PL/SQL block, but does not propagate any exceptions to enclosing blocks: PROCEDURE calc_avg_sales BEGIN :sales.avg := :sales.month1 / :sales.total; EXCEPTION WHEN ZERO_DIVIDE THEN :sales.avg := 0; RAISE FORM_TRIGGER_FAILURE; WHEN OTHERS THEN NULL; END;If total sales are zero, then an exception is raised, the average is set to zero, and the trigger processing in Oracle Forms is halted. If any other exceptions occur (such as VALUE_ERROR, which would be raised if the number generated by the calculation is larger than the sales.avg item allows), then the procedure does nothing and processing continues. Even though the WHEN OTHERS clause of the exception section is like the ELSE clause of an IF statement, the impact of a NULL statement is very different in each of these statements. The addition of an ELSE NULL clause to the IF statement has an impact only on the readability of the code. The IF statement executes in precisely the same way it would have without the ELSE NULL clause. When you include an exception handler with the NULL executable statement, you are saying, in effect: "End processing in the current PL/SQL block and move to the enclosing block, but otherwise take no action." This is very different from leaving out the exception handler altogether. If there is no exception handler, then PL/SQL raises the exception in the enclosing block again, and continues to do so until the last block, at which point it becomes an unhandled exception and halts the program. A "null" exception handler passes control back to the enclosing block, but does not cause the exception to be raised again. See Chapter 8, Exception Handlers, for more detailed information about exceptions. 5.2.2.3 Supporting top-down design of modulesWith top-down design (also known as top-down decomposition), you move from a general description of your system through step-by-step refinements of that idea to the modules which implement that system, and finally to the code that implements the modules. By moving through levels of abstraction, your mind concentrates on a relatively small number of issues at a time and can better process the details. From a programming perspective, you implement top-down design by creating "stubs," or dummy programs. A stub will have the name and parameter list you need, but not have anything under the covers. Using stubs you define the API (application programmatic interface), which indicates the way that the different modules connect to each other. In order for a PL/SQL program to compile, it must have at least one executable statement. The smallest, most nonintrusive program you can build will therefore be composed of a single NULL statement. Here are sample stubs for both a procedure and a function: PROCEDURE revise_timetable (year_in IN NUMBER) IS BEGIN NULL; END; FUNCTION company_name (company_id_in IN NUMBER) RETURN VARCHAR2 IS BEGIN RETURN NULL; END;The NULL statement gives you a way to quickly cobble together the programmatic interface you need to formulate the functional hierarchy of your application. I'd like to say that this is half the battle, but I am not really sure that is so. You still have to figure out how to fill in all of those stubs. 5.2.2.4 Using NULL with GOTO to avoid additional statement execution In some cases, you can pair NULL with GOTO to avoid having to execute additional statements. Most of you will never have to use the GOTO statement; there are very few occasions where it is truly needed. If you ever do use GOTO, however, you should remember that when you GOTO a label, at least one executable statement must follow that label. In the following example, I use a GOTO statement to quickly move to the end of my program if the state of my data indicates that no further processing is required: PROCEDURE process_data (data_in IN orders%ROWTYPE, data_action IN VARCHAR2) IS BEGIN -- First in series of validations. IF data_in.ship_date IS NOT NULL THEN status := validate_shipdate (data_in.ship_date); IF status != 0 THEN GOTO end_of_procedure; END IF; -- Second in series of validations. IF data_in.order_date IS NOT NULL THEN status := validate_orderdate (data_in.order_date); IF status != 0 THEN GOTO end_of_procedure; END IF; ... more validations ... << end_of_procedure >> NULL; END;With this approach, if I encounter an error in any single section, I use the GOTO to bypass all remaining validation checks. Because I do not have to do anything at the termination of the procedure, I place a NULL statement after the label because at least one statement is required there. 5.1 Conditional Control Statements6. Database Interaction and Cursors Copyright (c) 2000 O'Reilly & Associates. All rights reserved.