Which Of The Following Is Not One Of The Basic Control Structures?

three
Control Structures

I ship drives east and another drives west
With the selfsame winds that accident.
'Tis the ready of the sails and non the gales
Which tells us the way to go.

Ella Wheeler Wilcox

This chapter shows yous how to construction the flow of control through a PL/SQL plan. You acquire how statements are connected by simple merely powerful control structures that have a single entry and get out point. Collectively, these structures tin can handle any state of affairs. And, their proper use leads naturally to a well-structured plan.

Major Topics

Overview
Provisional Command: IF Statements
Iterative Control: LOOP and EXIT Statements
Sequential Control: GOTO and Aught Statements

Overview

According to the structure theorem, whatsoever estimator program can be written using the basic control structures shown in Effigy 3-1. They can be combined in any way necessary to deal with a given trouble.

Figure three-one Control Structures

The selection structure tests a condition, then executes one sequence of statements instead of another, depending on whether the condition is true or fake. A status is any variable or expression that returns a Boolean value (TRUE or Imitation). The iteration structure executes a sequence of statements repeatedly as long as a condition holds true. The sequence construction simply executes a sequence of statements in the order in which they occur.

Conditional Control: IF Statements

Oftentimes, information technology is necessary to take alternative deportment depending on circumstances. The IF statement lets you execute a sequence of statements conditionally. That is, whether the sequence is executed or not depends on the value of a condition. In that location are three forms of IF statements: IF-So, IF-THEN-ELSE, and IF-THEN-ELSIF.

IF-THEN

The simplest course of IF statement associates a condition with a sequence of statements enclosed by the keywords Then and Finish IF (non ENDIF), as follows:

IF status Then    sequence_of_statements; End IF;

The sequence of statements is executed only if the condition yields Truthful. If the condition yields Simulated or NULL, the IF statement does nothing. In either instance, control passes to the side by side statement. An example follows:

IF sales > quota THEN    compute_bonus(empid);    UPDATE payroll SET pay = pay + bonus WHERE empno = emp_id; End IF;

You might want to identify brief IF statements on a single line, as in

IF x > y And so high := 10; END IF;

IF-So-ELSE

The second form of IF argument adds the keyword ELSE followed by an alternative sequence of statements, every bit follows:

IF condition THEN    sequence_of_statements1; ELSE    sequence_of_statements2; Finish IF;

The sequence of statements in the ELSE clause is executed only if the condition yields FALSE or Zippo. Thus, the ELSE clause ensures that a sequence of statements is executed. In the following case, the offset or second UPDATE statement is executed when the condition is true or false, respectively:

IF trans_type = 'CR' THEN    UPDATE accounts SET residual = balance + credit WHERE ... ELSE    UPDATE accounts SET residual = balance - debit WHERE ... END IF;

The And so and ELSE clauses can include IF statements. That is, IF statements can be nested, as the following instance shows:

IF trans_type = 'CR' So    UPDATE accounts SET residuum = residue + credit WHERE ... ELSE    IF new_balance >= minimum_balance So       UPDATE accounts Ready residue = balance - debit WHERE ...    ELSE       RAISE insufficient_funds;    Terminate IF; Cease IF;

IF-So-ELSIF

Sometimes you want to select an action from several mutually exclusive alternatives. The third class of IF statement uses the keyword ELSIF (not ELSEIF) to innovate boosted conditions, as follows:

IF condition1 So    sequence_of_statements1; ELSIF condition2 And then    sequence_of_statements2; ELSE    sequence_of_statements3; END IF;

If the first condition yields Fake or Cipher, the ELSIF clause tests some other status. An IF statement can take any number of ELSIF clauses; the final ELSE clause is optional. Conditions are evaluated one by ane from top to bottom. If whatsoever condition yields TRUE, its associated sequence of statements is executed and control passes to the next argument. If all conditions yield Imitation or Cipher, the sequence in the ELSE clause is executed. Consider the post-obit case:

Begin    ...    IF sales > 50000 And then       bonus := 1500;    ELSIF sales > 35000 THEN       bonus := 500;    ELSE       bonus := 100;    END IF;    INSERT INTO payroll VALUES (emp_id, bonus, ...); Cease;

If the value of sales is more than 50000, the starting time and second conditions are truthful. All the same, bonus is assigned the proper value of 1500 because the second status is never tested. When the first condition yields TRUE, its associated statement is executed and control passes to the INSERT argument.

Guidelines

Avoid clumsy IF statements like those in the following example:

DECLARE    ...    overdrawn  BOOLEAN; BEGIN    ...    IF new_balance < minimum_balance Then       overdrawn := TRUE;    ELSE       overdrawn := Faux;    Terminate IF;    ...    IF overdrawn = TRUE THEN       RAISE insufficient_funds;    End IF; Finish;

This code disregards ii useful facts. Offset, the value of a Boolean expression can be assigned straight to a Boolean variable. So, you can supplant the first IF statement with a elementary assignment, every bit follows:

overdrawn := new_balance < minimum_balance;

2nd, a Boolean variable is itself either true or imitation. Then, you can simplify the condition in the second IF argument, as follows:

IF overdrawn THEN ...

When possible, use the ELSIF clause instead of nested IF statements. That way, your code will be easier to read and empathize. Compare the following IF statements:

IF condition1 THEN                |     IF condition1 So      statement1;                  |        statement1;  ELSE                              |     ELSIF condition2 THEN       IF condition2 THEN           |        statement2;             statement2;            |     ELSIF condition3 THEN       ELSE                         |        statement3;             IF condition3 And so     |     Finish IF;                statement3;        |           END IF;                 |      Terminate IF;                      | END IF;                           |

These statements are logically equivalent, just the kickoff statement obscures the flow of logic, whereas the second statement reveals information technology.

Iterative Command: LOOP and Leave Statements

LOOP statements permit you execute a sequence of statements multiple times. There are three forms of LOOP statements: LOOP, WHILE-LOOP, and FOR-LOOP.

LOOP

The simplest form of LOOP statement is the bones (or infinite) loop, which encloses a sequence of statements between the keywords LOOP and Finish LOOP, as follows:

LOOP    sequence_of_statements; END LOOP;

With each iteration of the loop, the sequence of statements is executed, then control resumes at the top of the loop. If farther processing is undesirable or impossible, you can use an Leave statement to consummate the loop. You lot can identify 1 or more EXIT statements anywhere inside a loop, just nowhere exterior a loop. At that place are ii forms of Exit statements: Go out and Exit-WHEN.

EXIT

The Exit statement forces a loop to consummate unconditionally. When an Exit statement is encountered, the loop completes immediately and control passes to the next statement. An example follows:

LOOP    ...    IF credit_rating < three And so        ...       EXIT;  -- exit loop immediately    END IF; Finish LOOP; -- control resumes here

The adjacent example shows that you cannot use the EXIT statement to consummate a PL/SQL block:

BEGIN    ...    IF credit_rating < 3 THEN       ...       EXIT;  -- illegal    END IF; END;

Remember, the Go out statement must be placed inside a loop. To consummate a PL/SQL block before its normal end is reached, you can use the RETURN statement. For more information, meet "Return Argument".

Get out-WHEN

The EXIT-WHEN statement allows a loop to complete conditionally. When the Go out argument is encountered, the condition in the WHEN clause is evaluated. If the condition yields TRUE, the loop completes and control passes to the next statement after the loop. An example follows:

LOOP    FETCH c1 INTO ...    EXIT WHEN c1%NOTFOUND;  -- exit loop if condition is true    ... END LOOP; Close c1;

Until the condition yields TRUE, the loop cannot complete. So, statements inside the loop must change the value of the condition. In the concluding example, if the FETCH statement returns a row, the status yields FALSE. When the FETCH statement fails to return a row, the condition yields True, the loop completes, and control passes to the CLOSE argument.

The EXIT-WHEN statement replaces a simple IF statement. For example, compare the following statements:

IF count > 100 THEN     |     EXIT WHEN count > 100;    EXIT;                | END IF;                 |

These statements are logically equivalent, merely the EXIT-WHEN statement is easier to read and sympathise.

Loop Labels

Similar PL/SQL blocks, loops can be labeled. The label, an undeclared identifier enclosed past double bending brackets, must appear at the beginning of the LOOP statement, equally follows:

<<label_name>> LOOP    sequence_of_statements; End LOOP;

Optionally, the characterization name tin can also appear at the cease of the LOOP statement, as the post-obit instance shows:

<<my_loop>> LOOP    ... END LOOP my_loop;

When you nest labeled loops, you tin can use ending label names to improve readability.

With either form of EXIT statement, you tin complete not only the current loop, but any enclosing loop. Simply label the enclosing loop that you desire to complete. Then, use the label in an EXIT argument, equally follows:

<<outer>> LOOP    ...    LOOP       ...       Get out outer WHEN ...  -- go out both loops    Cease LOOP;    ... END LOOP outer;

Every enclosing loop up to and including the labeled loop is exited.

WHILE-LOOP

The WHILE-LOOP statement associates a condition with a sequence of statements enclosed by the keywords LOOP and Terminate LOOP, as follows:

WHILE status LOOP    sequence_of_statements; END LOOP;

Before each iteration of the loop, the condition is evaluated. If the condition yields TRUE, the sequence of statements is executed, and so control resumes at the tiptop of the loop. If the condition yields FALSE or Zilch, the loop is bypassed and control passes to the next statement. An instance follows:

WHILE total <= 25000 LOOP    ...    SELECT sal INTO salary FROM emp WHERE ...    total := total + bacon; END LOOP;

The number of iterations depends on the status and is unknown until the loop completes. Since the status is tested at the acme of the loop, the sequence might execute zero times. In the last case, if the initial value of full is greater than 25000, the condition yields FALSE and the loop is bypassed.

Some languages have a LOOP UNTIL or REPEAT UNTIL structure, which tests the condition at the bottom of the loop instead of at the summit. Therefore, the sequence of statements is executed at least in one case. PL/SQL has no such structure, but you can easily build 1, as follows:

LOOP    sequence_of_statements;    EXIT WHEN boolean_expression; END LOOP;

To ensure that a WHILE loop executes at to the lowest degree once, use an initialized Boolean variable in the condition, every bit follows:

washed := Simulated; WHILE NOT washed LOOP    sequence_of_statements;    done := boolean_expression; Cease LOOP;

A argument within the loop must assign a new value to the Boolean variable. Otherwise, you have an infinite loop. For example, the following LOOP statements are logically equivalent:

WHILE TRUE LOOP     |     LOOP    ...              |        ... Terminate LOOP;           |     END LOOP;

FOR-LOOP

Whereas the number of iterations through a WHILE loop is unknown until the loop completes, the number of iterations through a FOR loop is known earlier the loop is entered. FOR loops iterate over a specified range of integers. (Cursor FOR loops, which iterate over the effect set of a cursor, are discussed in Affiliate 5.) The range is part of an iteration scheme, which is enclosed by the keywords FOR and LOOP. A double dot (..) serves as the range operator. The syntax follows:

FOR counter IN [Opposite] lower_bound..higher_bound LOOP    sequence_of_statements; END LOOP;

The range is evaluated when the FOR loop is outset entered and is never re-evaluated.

As the side by side example shows, the sequence of statements is executed once for each integer in the range. After each iteration, the loop counter is incremented.

FOR i IN 1..three LOOP  -- assign the values 1,two,iii to i    sequence_of_statements;  -- executes three times END LOOP;

The following example shows that if the lower spring equals the higher bound, the sequence of statements is executed one time:

FOR i IN iii..3 LOOP  -- assign the value 3 to i    sequence_of_statements;  -- executes 1 fourth dimension Terminate LOOP;

Past default, iteration proceeds upward from the lower bound to the college bound. Yet, if yous apply the keyword Opposite, iteration proceeds downward from the higher leap to the lower spring, every bit the example beneath shows. After each iteration, the loop counter is decremented.

FOR i IN Opposite i..iii LOOP  -- assign the values 3,2,ane to i    sequence_of_statements;  -- executes three times END LOOP;

Nevertheless, yous write the range bounds in ascending (not descending) guild.

Inside a FOR loop, the loop counter can be referenced like a constant. So, the loop counter tin can appear in expressions but cannot exist assigned values, every bit the following example shows:

FOR ctr IN ane..10 LOOP    IF NOT finished Then       INSERT INTO ... VALUES (ctr, ...);  -- legal       factor := ctr * 2;  -- legal    ELSE       ctr := 10;  -- illegal    Cease IF; Stop LOOP;

Iteration Schemes

The premises of a loop range tin can be literals, variables, or expressions but must evaluate to integers. For case, the following iteration schemes are legal:

j IN -5..5 k IN REVERSE first..terminal step IN 0..TRUNC(high/low) * 2 code IN ASCII('A')..ASCII('J')

As you tin can run into, the lower bound need not exist 1. Notwithstanding, the loop counter increment (or decrement) must be i. Some languages provide a STEP clause, which lets you specify a dissimilar increment. An case written in BASIC follows:

FOR J = 5 TO xv Pace 5  :REM assign values 5,10,fifteen to J    sequence_of_statements  -- J has values 5,10,xv Next J

PL/SQL has no such structure, but yous can easily build one. Consider the post-obit example:

FOR j IN five..fifteen LOOP  -- assign values 5,six,7,... to j    IF MOD(j, 5) = 0 THEN  -- pass multiples of v       sequence_of_statements;  -- j has values 5,10,15    Stop IF; Terminate LOOP;

This loop is logically equivalent to the previous BASIC loop. Inside the sequence of statements, the loop counter has only the values 5, ten, and 15.

You might prefer the less elegant just more than efficient method shown in the instance beneath. Within the sequence of statements, each reference to the loop counter is multiplied by the increment.

FOR j IN one..3 LOOP  -- assign values ane,2,3 to j    sequence_of_statements;  -- each j becomes j*five END LOOP;

Dynamic Ranges

PL/SQL lets you make up one's mind the loop range dynamically at run time, equally the following instance shows:

SELECT COUNT(empno) INTO emp_count FROM emp; FOR i IN one..emp_count LOOP    ... Cease LOOP;

The value of emp_count is unknown at compile fourth dimension; the SELECT statement returns the value at run fourth dimension.

What happens if the lower bound of a loop range evaluates to a larger integer than the upper spring? As the adjacent example shows, the sequence of statements within the loop is non executed and control passes to the side by side statement:

-- limit becomes 1 FOR i IN two..limit LOOP    sequence_of_statements;  -- executes naught times End LOOP; -- control passes here

Telescopic Rules

The loop counter is defined only inside the loop. You cannot reference it outside the loop. After the loop is exited, the loop counter is undefined, as the following instance shows:

FOR ctr IN 1..10 LOOP    ... End LOOP; sum := ctr - 1;  -- illegal

Yous need not explicitly declare the loop counter because it is implicitly alleged as a local variable of type INTEGER. The side by side example shows that the local declaration hides any global declaration:

DECLARE    ctr  INTEGER; Brainstorm    ...    FOR ctr IN 1..25 LOOP       ...       IF ctr > 10 Then ...  -- refers to loop counter    END LOOP; END;

To reference the global variable in this instance, you must use a label and dot notation, as follows:

<<main>> DECLARE    ctr  INTEGER;    ... BEGIN    ...    FOR ctr IN 1..25 LOOP       ...       IF main.ctr > ten Then ...  -- refers to global variable    END LOOP; Cease main;

The same scope rules apply to nested FOR loops. Consider the instance below. Both loop counters have the same proper noun. And so, to reference the outer loop counter from the inner loop, you must apply a label and dot notation, as follows:

<<outer>> FOR step IN one..25 LOOP    FOR step IN ane..10 LOOP       ...       IF outer.footstep > 15 THEN ...    Finish LOOP; Stop LOOP outer;

Using the EXIT Statement

The EXIT argument allows a FOR loop to complete prematurely. For example, the following loop normally executes ten times, but every bit soon every bit the FETCH statement fails to return a row, the loop completes no matter how many times information technology has executed:

FOR j IN 1..10 LOOP    FETCH c1 INTO emp_rec;    EXIT WHEN c1%NOTFOUND;    ... Terminate LOOP;

Suppose yous must go out from a nested FOR loop prematurely. You lot can consummate not only the current loop, only whatsoever enclosing loop. But label the enclosing loop that you want to complete. And then, employ the label in an Go out statement to specify which FOR loop to go out, equally follows:

<<outer>> FOR i IN 1..5 LOOP    ...    FOR j IN 1..10 LOOP       FETCH c1 INTO emp_rec;       Leave outer WHEN c1%NOTFOUND;  -- exit both FOR loops       ...    END LOOP; END LOOP outer; -- control passes here

Sequential Control: GOTO and NULL Statements

Unlike the IF and LOOP statements, the GOTO and NULL statements are not crucial to PL/SQL programming. The structure of PL/SQL is such that the GOTO argument is seldom needed. Occasionally, it can simplify logic enough to warrant its use. The NULL statement tin make the significant and action of conditional statements clear and and so improve readability.

Overuse of GOTO statements can result in complex, unstructured lawmaking (sometimes called spaghetti code) that is hard to understand and maintain. So, use GOTO statements sparingly. For example, to branch from a deeply nested structure to an error-handling routine, raise an exception rather than employ a GOTO statement.

GOTO Statement

The GOTO statement branches to a label unconditionally. The characterization must be unique within its scope and must precede an executable statement or a PL/SQL block. When executed, the GOTO statement transfers control to the labeled statement or block. In the post-obit example, you become to an executable argument farther down in a sequence of statements:

Begin    ...    GOTO insert_row;    ...    <<insert_row>>    INSERT INTO emp VALUES ... END;

In the next example, you become to a PL/SQL block farther up in a sequence of statements:

Begin    ...    <<update_row>>    Brainstorm       UPDATE emp SET ...       ...    END;    ...    GOTO update_row;    ... END;

The label <<end_loop>> in the post-obit example is illegal because it does non precede an executable statement:

DECLARE    done  BOOLEAN; BEGIN    ...    FOR i IN i..50 LOOP       IF done And so          GOTO end_loop;       END IF;       ...    <<end_loop>>  -- illegal    Terminate LOOP;  -- not an executable statement Cease;

To debug the terminal instance, simply add the Cypher statement, every bit follows:

DECLARE    done  BOOLEAN; BEGIN    ...    FOR i IN i..l LOOP       IF done THEN          GOTO end_loop;       Finish IF;       ...    <<end_loop>>    NULL;  -- an executable statement    Finish LOOP; End;

As the following case shows, a GOTO statement can branch to an enclosing block from the current block:

DECLARE    my_ename  CHAR(ten); BEGIN    ...    <<get_name>>    SELECT ename INTO my_ename FROM emp WHERE ...    BEGIN       ...       GOTO get_name;  -- branch to enclosing cake    END; End;

The GOTO statement branches to the first enclosing block in which the referenced label appears.

Restrictions

Some possible destinations of a GOTO statement are illegal. Specifically, a GOTO argument cannot branch into an IF statement, LOOP statement, or sub-block. For example, the post-obit GOTO statement is illegal:

BEGIN    ...    GOTO update_row;  -- illegal co-operative into IF argument    ...    IF valid THEN       ...       <<update_row>>       UPDATE emp SET ...    Terminate IF; Terminate;

Also, a GOTO statement cannot branch from i IF argument clause to another, as the following case shows:

BEGIN    ...    IF valid THEN       ...       GOTO update_row;  -- illegal co-operative into ELSE clause    ELSE       ...       <<update_row>>       UPDATE emp SET ...    END IF; END;

The next example shows that a GOTO argument cannot branch from an enclosing block into a sub-block:

Begin    ...    IF condition = 'OBSOLETE' THEN       GOTO delete_part;  -- illegal branch into sub-block    END IF;    ...    BEGIN       ...       <<delete_part>>       DELETE FROM parts WHERE ...    END; Finish;

Also, a GOTO statement cannot branch out of a subprogram, as the following example shows:

DECLARE    ...    Process compute_bonus (emp_id NUMBER) IS    BEGIN       ...       GOTO update_row;  -- illegal co-operative out of subprogram    End; BEGIN    ...    <<update_row>>    UPDATE emp SET ... Finish;

Finally, a GOTO statement cannot branch from an exception handler into the electric current block. For example, the following GOTO argument is illegal:

DECLARE    ...    pe_ratio  REAL; Begin    ...    SELECT cost / NVL(earnings, 0) INTO pe_ratio FROM ...    <<insert_row>>    INSERT INTO stats VALUES (pe_ratio, ...); EXCEPTION    WHEN ZERO_DIVIDE Then       pe_ratio := 0;       GOTO insert_row;  -- illegal branch into electric current block END;

Nonetheless, a GOTO statement tin branch from an exception handler into an enclosing block.

NULL Statement

The NULL statement explicitly specifies inaction; it does nothing other than pass command to the next statement. It tin, however, improve readability. In a construct allowing culling actions, the Cipher statement serves equally a placeholder. It tells readers that the associated alternative has non been overlooked, but that indeed no action is necessary. In the post-obit example, the Zilch statement shows that no action is taken for unnamed exceptions:

EXCEPTION    WHEN ZERO_DIVIDE THEN       ROLLBACK;    WHEN VALUE_ERROR THEN       INSERT INTO errors VALUES ...       COMMIT;    WHEN OTHERS THEN       NULL; Stop;

Each clause in an IF statement must contain at least 1 executable statement. The Zippo statement meets this requirement. So, you can use the Zero statement in clauses that represent to circumstances in which no action is taken. In the following example, the Aught statement emphasizes that only top-rated employees receive bonuses:

IF rating > xc And then    compute_bonus(emp_id); ELSE    Cypher; END IF;

Besides, the Nothing statement is a handy mode to create stubs when designing applications from the summit down. A stub is dummy subprogram that allows you to defer the definition of a procedure or function until you test and debug the main program. In the following example, the Aught statement meets the requirement that at least ane argument must appear in the executable part of a subprogram:

PROCEDURE debit_account (acct_id INTEGER, amount REAL) IS BEGIN    Nix; END debit_account;