Page 194
IF (val = `0') THEN -- is value a digit? END IF; -- zero? IF (val = `1') THEN -- maybe a 1? END IF; -- 1? IF (val = `9') THEN -- maybe a 9? END IF; -- end of test
You could use nested IF ELSE statements, but this becomes pretty ugly, what with all the indentation. Instead, use ELSIF to make your code more efficient. As soon as one of the conditions is true, its antecedent is executed and the entire block is exited.
IF (val = `0') THEN -- is value a digit? ELSIF (val = `1') THEN ELSIF (val = `2') THEN ELSIF (val = `9') THEN ELSE -- not a number END IF; -- end of test
NOTE |
PL/SQL doesn't have a SWITCH statement (as in C), nora CASE statement (as in Pascal), nor a computed GOTO (like FORTRAN). Simulate these constructs with the sequential IF. |
Listing 10.4 shows a simple example written to estimate the author's 1996 Federal tax liability (from IRS Publication 15, Circular E, Employer's Tax Guide, Rev. January 1997).
Listing 10.4 fedtax.sqlSequential IF Logic Used to Detect Value Ranges
SET SERVEROUTPUT ON; -- assumes annual payroll, married DECLARE num_wh NUMBER := &num_witholding; wh_amount NUMBER; gross NUMBER := &annual_gross_salary; liab NUMBER; adj_gross NUMBER; BEGIN DBMS_OUTPUT.ENABLE; wh_amount := num_wh * 2550; -- annual allowance adj_gross := gross - wh_amount; IF adj_gross <= 6425 THEN liab := 0; ELSIF adj_gross <= 44250 THEN liab := (adj_gross - 6425) * 0.15; ELSIF adj_gross <= 89675 THEN
Page 195
liab := 5673.75 + (adj_gross - 44250) * 0.28; ELSIF adj_gross <= 151850 THEN liab := 18392.75 + (adj_gross - 89675) * 0.31; ELSIF adj_gross <= 267900 THEN liab := 37667 + (adj_gross - 151850) * 0.36; ELSE -- over max liab := 79445 + (adj_gross - 267900) * 0.396; END IF; -- test adjusted gross DBMS_OUTPUT.PUT_LINE(`FEDERAL TAX LIABILITY: ` || TO_CHAR(liab)); END; /
When you run the function, you are prompted for the bind variable values, and the server returns:
Enter value for num_witholding: 4 old 2: num_wh NUMBER := &num_witholding; new 2: num_wh NUMBER := 4; Enter value for annual_gross_salary: 60000 old 4: gross NUMBER := &annual_gross_salary; new 4: gross NUMBER := 60000; FEDERAL TAX LIABILITY: 7227.75 PL/SQL procedure successfully completed.
The parentheses around the expression to be tested are optional. They are recommended, however, especially for complex expressions.
Unconditional BranchingPL/SQL supports unconditional branching with GOTO. You specify the target of the jump with a label, just like in BASIC and Assembler. You may jump anywhere within the present block or the enclosing block. Generally, the use of GOTO is frowned upon, as it leads to unstructured code. Such programs usually become unmaintainable rather quickly. The syntax is illustrated in Listing 10.5.
Listing 10.5 okgoto.sqlUsing GOTO
BEGIN BEGIN GOTO MID; -- forward reference OK because it's in scope END; <<MID>> NULL; END; /
The label MID is denoted by the enclosing double angle brackets. Notice that the label is not terminated by a semicolon.
This compiles successfully. However, Listing 10.6 illustrates a different scenario.
Page 196
Listing 10.6 badgoto.sqlImproper Use of GOTO
BEGIN GOTO MID; -- invalid! BEGIN <<MID>> NULL; END; END; /
You get a compilation error because the label is out of scope:
ERROR at line 1: ORA-06550: line 2, column 8: PLS-00201: identifier `MID' must be declared ORA-06550: line 2, column 3: PL/SQL: Statement ignored
The outer block does not know about the existence of anything inside an inner block.
Notice the NULL statement. This is a perfectly valid statement that only acts as a placeholder. You can't have a label immediately before any flavor of END statement (as in END IF). You'll get a compilation error. You remedy this problem by using the NULL statement.
You cannot jump from one block to another block at the same level inside an enclosing block, as with Listing 10.7.
Listing 10.7 bad2goto.sqlGOTO Cannot Be Used Between Blocks at the Same Level Within an Enclosing Block
BEGIN BEGIN GOTO OTHER; -- invalid! END; BEGIN <<OTHER>> -- out of scope NULL; END; END; /
This restriction also holds true for conditional statements. You can't jump from the antecedent to the consequent (see Listing 10.8).
Listing 10.8 bad3goto.sqlYou Can't Jump Between the Antecedent and Consequent
BEGIN IF (TRUE) THEN -- always do the antecedent GOTO CONSEQUENT;
Page 197
ELSE -- consequent <<CONSEQUENT>> NULL; END IF; -- of jump example END; /
This time Oracle senses you're doing something weird and gives you the following:
ERROR at line 3: ORA-06550: line 3, column 5: PLS-00375: illegal GOTO statement; this GOTO cannot branch to label `CONSEQUENT' ORA-06550: line 4, column 3: PL/SQL: Statement ignored
Also, Oracle won't detect infinite loops, as with
BEGIN <<INFINITE_LOOP>> GOTO INFINITE_LOOP; END;
CAUTION |
This tiny bit of code will throw your SQL*Plus session into an infinite loop! You'll have to ask your DBA to kill the session if you are foolish enough to do this, especially in a Windows 3.x environment, which will promptly lock up until the session is killed. If you're running Windows 95 or Windows NT you can blow away the SQL*Plus session, losing some resources (such as allocated memory or available handles in use by the session) on your machine in the process. |
Labels and Scoping RulesThe scope of a variable declaration is local to the enclosing block. The variable can generally only be referenced within the confines of the block or sub-block in which it is defined. Variables declared in the parameter list of a subprogram may only be referenced from within that subprogram. Additionally, any variables declared within the body of a subprogram are strictly local to that subprogram. It's OK for an inner block to rename a variable; it is still a new and different variable from the one in the outer scope.
At runtime, when a block goes out of scope, the variables, cursors, and similar constructs local to it are deallocated. When the block is again in scope, the variables are recreated and initialized to whatever values were specified in their declaration. You'll take a closer look at this later on in the section on stored procedures.
The general rule of thumb to use when deciding where best to place a declaration is to constrict the scope to only those blocks that will ever need to reference it. The object will therefore only exist when absolutely needed, conserving memory. Balance this memory-conserving heuristic with any need for object persistence or time savings required by allocating objects once up front.