1 Kazigore

Exit When In Pl/Sql What Is The Assignment Operator

4
PL/SQL Control Structures

One ship drives east and another drives west
With the selfsame winds that blow.
'Tis the set of the sails and not the gales
Which tells us the way to go. --Ella Wheeler Wilcox

This chapter shows you how to structure the flow of control through a PL/SQL program. You learn how statements are connected by simple but powerful control structures that have a single entry and exit point. Collectively, these structures can handle any situation. Their proper use leads naturally to a well-structured program.

This chapter discusses the following topics:

Overview of PL/SQL Control Structures
Conditional Control: IF and CASE Statements
Iterative Control: LOOP and EXIT Statements
Sequential Control: GOTO and NULL Statements

Overview of PL/SQL Control Structures

According to the structure theorem, any computer program can be written using the basic control structures shown in Figure 4-1. They can be combined in any way necessary to deal with a given problem.

Figure 4-1 Control Structures


Text description of the illustration pls81008_control_structures.gif

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

Conditional Control: IF and CASE Statements

Often, it is necessary to take alternative actions depending on circumstances. The 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. There are three forms of statements: , , and . The statement is a compact way to evaluate a single condition and choose between many alternative actions.

IF-THEN Statement

The simplest form of statement associates a condition with a sequence of statements enclosed by the keywords and (not ), as follows:

IF condition THEN sequence_of_statements END IF;

The sequence of statements is executed only if the condition is true. If the condition is false or null, the statement does nothing. In either case, control passes to the next 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 place brief statements on a single line, as in

IF x > y THEN high := x; END IF;

IF-THEN-ELSE Statement

The second form of statement adds the keyword followed by an alternative sequence of statements, as follows:

IF condition THEN sequence_of_statements1 ELSE sequence_of_statements2 END IF;

The sequence of statements in the clause is executed only if the condition is false or null. Thus, the clause ensures that a sequence of statements is executed. In the following example, the first statement is executed when the condition is true, but the second statement is executed when the condition is false or null:

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

The and clauses can include statements. That is, statements can be nested, as the following example shows:

IF trans_type = 'CR' THEN UPDATE accounts SET balance = balance + credit WHERE ... ELSE IF new_balance >= minimum_balance THEN UPDATE accounts SET balance = balance - debit WHERE ... ELSE RAISE insufficient_funds; END IF; END IF;

IF-THEN-ELSIF Statement

Sometimes you want to select an action from several mutually exclusive alternatives. The third form of statement uses the keyword (not ) to introduce additional conditions, as follows:

IF condition1 THEN sequence_of_statements1 ELSIF condition2 THEN sequence_of_statements2 ELSE sequence_of_statements3 END IF;

If the first condition is false or null, the clause tests another condition. An statement can have any number of clauses; the final clause is optional. Conditions are evaluated one by one from top to bottom. If any condition is true, its associated sequence of statements is executed and control passes to the next statement. If all conditions are false or null, the sequence in the clause is executed. Consider the following example:

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

If the value of is larger than 50000, the first and second conditions are true. Nevertheless, is assigned the proper value of 1500 because the second condition is never tested. When the first condition is true, its associated statement is executed and control passes to the statement.

CASE Statement

Like the statement, the statement selects one sequence of statements to execute. However, to select the sequence, the statement uses a selector rather than multiple Boolean expressions. (Recall from Chapter 2 that a selector is an expression whose value is used to select one of several alternatives.) To compare the and statements, consider the following code that outputs descriptions of school grades:

IF grade = 'A' THEN dbms_output.put_line('Excellent'); ELSIF grade = 'B' THEN dbms_output.put_line('Very Good'); ELSIF grade = 'C' THEN dbms_output.put_line('Good'); ELSIF grade = 'D' THEN dbms_output. put_line('Fair'); ELSIF grade = 'F' THEN dbms_output.put_line('Poor'); ELSE dbms_output.put_line('No such grade'); END IF;

Notice the five Boolean expressions. In each instance, we test whether the same variable, , is equal to one of five values: , , , , or . Let us rewrite the preceding code using the statement, as follows:

CASE grade WHEN 'A' THEN dbms_output.put_line('Excellent'); WHEN 'B' THEN dbms_output.put_line('Very Good'); WHEN 'C' THEN dbms_output.put_line('Good'); WHEN 'D' THEN dbms_output.put_line('Fair'); WHEN 'F' THEN dbms_output.put_line('Poor'); ELSE dbms_output.put_line('No such grade'); END CASE;

The statement is more readable and more efficient. So, when possible, rewrite lengthy statements as statements.

The statement begins with the keyword . The keyword is followed by a selector, which is the variable in the last example. The selector expression can be arbitrarily complex. For example, it can contain function calls. Usually, however, it consists of a single variable. The selector expression is evaluated only once. The value it yields can have any PL/SQL datatype other than , , an object type, a PL/SQL record, an index-by-table, a varray, or a nested table.

The selector is followed by one or more clauses, which are checked sequentially. The value of the selector determines which clause is executed. If the value of the selector equals the value of a -clause expression, that clause is executed. For instance, in the last example, if equals , the program outputs . Execution never falls through; if any clause is executed, control passes to the next statement.

The clause works similarly to the clause in an statement. In the last example, if the grade is not one of the choices covered by a clause, the clause is selected, and the phrase is output. The clause is optional. However, if you omit the clause, PL/SQL adds the following implicit clause:

ELSE RAISE CASE_NOT_FOUND;

If the statement selects the implicit clause, PL/SQL raises the predefined exception . So, there is always a default action, even when you omit the clause.

The keywords terminate the statement. These two keywords must be separated by a space. The statement has the following form:

[<<label_name>>] CASE selector WHEN expression1 THEN sequence_of_statements1; WHEN expression2 THEN sequence_of_statements2; ... WHEN expressionN THEN sequence_of_statementsN; [ELSE sequence_of_statementsN+1;] END CASE [label_name];

Like PL/SQL blocks, statements can be labeled. The label, an undeclared identifier enclosed by double angle brackets, must appear at the beginning of the statement. Optionally, the label name can also appear at the end of the statement.

Exceptions raised during the execution of a statement are handled in the usual way. That is, normal execution stops and control transfers to the exception-handling part of your PL/SQL block or subprogram.

An alternative to the statement is the expression, where each clause is an expression. For details, see "CASE Expressions".

Searched CASE Statement

PL/SQL also provides a searched statement, which has the form:

[<<label_name>>] CASE WHEN search_condition1 THEN sequence_of_statements1; WHEN search_condition2 THEN sequence_of_statements2; ... WHEN search_conditionN THEN sequence_of_statementsN; [ELSE sequence_of_statementsN+1;] END CASE [label_name];

The searched statement has no selector. Also, its clauses contain search conditions that yield a Boolean value, not expressions that can yield a value of any type. An example follows:

CASE WHEN grade = 'A' THEN dbms_output.put_line('Excellent'); WHEN grade = 'B' THEN dbms_output.put_line('Very Good'); WHEN grade = 'C' THEN dbms_output.put_line('Good'); WHEN grade = 'D' THEN dbms_output.put_line('Fair'); WHEN grade = 'F' THEN dbms_output.put_line('Poor'); ELSE dbms_output.put_line('No such grade'); END CASE;

The search conditions are evaluated sequentially. The Boolean value of each search condition determines which clause is executed. If a search condition yields , its clause is executed. If any clause is executed, control passes to the next statement, so subsequent search conditions are not evaluated.

If none of the search conditions yields , the clause is executed. The clause is optional. However, if you omit the clause, PL/SQL adds the following implicit clause:

ELSE RAISE CASE_NOT_FOUND;

Exceptions raised during the execution of a searched statement are handled in the usual way. That is, normal execution stops and control transfers to the exception-handling part of your PL/SQL block or subprogram.

Guidelines for PL/SQL Conditional Statements

Avoid clumsy statements like those in the following example:

IF new_balance < minimum_balance THEN overdrawn := TRUE; ELSE overdrawn := FALSE; END IF; ... IF overdrawn = TRUE THEN RAISE insufficient_funds; END IF;

This code disregards two useful facts. First, the value of a Boolean expression can be assigned directly to a Boolean variable. So, you can replace the first statement with a simple assignment, as follows:

overdrawn := new_balance < minimum_balance;

Second, a Boolean variable is itself either true or false. So, you can simplify the condition in the second statement, as follows:

IF overdrawn THEN ...

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

IF condition1 THEN | IF condition1 THEN statement1; | statement1; ELSE | ELSIF condition2 THEN IF condition2 THEN | statement2; statement2; | ELSIF condition3 THEN ELSE | statement3; IF condition3 THEN | END IF; statement3; | END IF; | END IF; | END IF; |

These statements are logically equivalent, but the first statement obscures the flow of logic, whereas the second statement reveals it.

If you are comparing a single expression to multiple values, you can simplify the logic by using a single statement instead of an with several clauses.

Iterative Control: LOOP and EXIT Statements

statements let you execute a sequence of statements multiple times. There are three forms of statements: , , and .

LOOP

The simplest form of statement is the basic (or infinite) loop, which encloses a sequence of statements between the keywords and , 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 further processing is undesirable or impossible, you can use an statement to complete the loop. You can place one or more statements anywhere inside a loop, but nowhere outside a loop. There are two forms of statements: and .

EXIT

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

LOOP ... IF credit_rating < 3 THEN ... EXIT; -- exit loop immediately END IF; END LOOP; -- control resumes here

The next example shows that you cannot use the statement to complete a PL/SQL block:

BEGIN ... IF credit_rating < 3 THEN ... EXIT; -- not allowed END IF; END;

Remember, the statement must be placed inside a loop. To complete a PL/SQL block before its normal end is reached, you can use the statement. For more information, see "Using the RETURN Statement".

EXIT-WHEN

The statement lets a loop complete conditionally. When the statement is encountered, the condition in the clause is evaluated. If the condition is 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 is true, the loop cannot complete. So, a statement inside the loop must change the value of the condition. In the last example, if the statement returns a row, the condition is false. When the statement fails to return a row, the condition is true, the loop completes, and control passes to the statement.

The statement replaces a simple statement. For example, compare the following statements:

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

These statements are logically equivalent, but the statement is easier to read and understand.

Loop Labels

Like PL/SQL blocks, loops can be labeled. The label, an undeclared identifier enclosed by double angle brackets, must appear at the beginning of the statement, as follows:

<<label_name>> LOOP sequence_of_statements END LOOP;

Optionally, the label name can also appear at the end of the statement, as the following example shows:

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

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

With either form of statement, you can complete not only the current loop, but any enclosing loop. Simply label the enclosing loop that you want to complete. Then, use the label in an statement, as follows:

<<outer>> LOOP ... LOOP ... EXIT outer WHEN ... -- exit both loops END LOOP; ... END LOOP outer;

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

WHILE-LOOP

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

WHILE condition LOOP sequence_of_statements END LOOP;

Before each iteration of the loop, the condition is evaluated. If the condition is true, the sequence of statements is executed, then control resumes at the top of the loop. If the condition is false or null, the loop is bypassed and control passes to the next statement. An example follows:

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

The number of iterations depends on the condition and is unknown until the loop completes. The condition is tested at the top of the loop, so the sequence might execute zero times. In the last example, if the initial value of is larger than 25000, the condition is false and the loop is bypassed.

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

LOOP sequence_of_statements EXIT WHEN boolean_expression; END LOOP;

To ensure that a loop executes at least once, use an initialized Boolean variable in the condition, as follows:

done := FALSE; WHILE NOT done LOOP sequence_of_statements done := boolean_expression; END LOOP;

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

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

FOR-LOOP

Whereas the number of iterations through a loop is unknown until the loop completes, the number of iterations through a loop is known before the loop is entered. loops iterate over a specified range of integers. The range is part of an iteration scheme, which is enclosed by the keywords and . A double dot () serves as the range operator. The syntax follows:

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

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

As the next 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..3 LOOP -- assign the values 1,2,3 to i sequence_of_statements -- executes three times END LOOP;

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

FOR i IN 3..3 LOOP -- assign the value 3 to i sequence_of_statements -- executes one time END LOOP;

By default, iteration proceeds upward from the lower bound to the higher bound. However, as the example below shows, if you use the keyword , iteration proceeds downward from the higher bound to the lower bound. After each iteration, the loop counter is decremented. Nevertheless, you write the range bounds in ascending (not descending) order.

FOR i IN REVERSE 1..3 LOOP -- assign the values 3,2,1 to i sequence_of_statements -- executes three times END LOOP;

Inside a loop, the loop counter can be referenced like a constant but cannot be assigned values, as the following example shows:

FOR ctr IN 1..10 LOOP IF NOT finished THEN INSERT INTO ... VALUES (ctr, ...); -- legal factor := ctr * 2; -- legal ELSE ctr := 10; -- not allowed END IF; END LOOP;

Iteration Schemes

The bounds of a loop range can be literals, variables, or expressions but must evaluate to numbers. Otherwise, PL/SQL raises the predefined exception . The lower bound need not be 1, as the examples below show. However, the loop counter increment (or decrement) must be 1.

j IN -5..5 k IN REVERSE first..last step IN 0..TRUNC(high/low) * 2

Internally, PL/SQL assigns the values of the bounds to temporary variables, and, if necessary, rounds the values to the nearest integer. The magnitude range of a is -2**31 .. 2**31. So, if a bound evaluates to a number outside that range, you get a numeric overflow error when PL/SQL attempts the assignment, as the following example shows:

DECLARE hi NUMBER := 2**32; BEGIN FOR j IN 1..hi LOOP -- causes a 'numeric overflow' error ... END LOOP; END;

Some languages provide a clause, which lets you specify a different increment (5 instead of 1 for example). PL/SQL has no such structure, but you can easily build one. Inside the loop, simply multiply each reference to the loop counter by the new increment. In the following example, you assign today's date to elements 5, 10, and 15 of an index-by table:

DECLARE TYPE DateList IS TABLE OF DATE INDEX BY BINARY_INTEGER; dates DateList; k CONSTANT INTEGER := 5; -- set new increment BEGIN FOR j IN 1..3 LOOP dates(j*k) := SYSDATE; -- multiply loop counter by increment END LOOP; ... END;

Dynamic Ranges

PL/SQL lets you determine the loop range dynamically at run time, as the following example shows:

SELECT COUNT(empno) INTO emp_count FROM emp; FOR i IN 1..emp_count LOOP ... END LOOP;

The value of is unknown at compile time; the statement returns the value at run time.

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

-- limit becomes 1 FOR i IN 2..limit LOOP sequence_of_statements -- executes zero times END LOOP; -- control passes here

Scope Rules

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

FOR ctr IN 1..10 LOOP ... END LOOP; sum := ctr - 1; -- not allowed

You need not explicitly declare the loop counter because it is implicitly declared as a local variable of type . The next example shows that the local declaration hides any global declaration:

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

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

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

The same scope rules apply to nested loops. Consider the example below. Both loop counters have the same name. So, to reference the outer loop counter from the inner loop, you must use a label and dot notation, as follows:

<<outer>> FOR step IN 1..25 LOOP FOR step IN 1..10 LOOP ... IF outer.step > 15 THEN ... END LOOP; END LOOP outer;

Using the EXIT Statement

The statement lets a loop complete prematurely. For example, the following loop normally executes ten times, but as soon as the statement fails to return a row, the loop completes no matter how many times it has executed:

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

Suppose you must exit from a nested loop prematurely. You can complete not only the current loop, but any enclosing loop. Simply label the enclosing loop that you want to complete. Then, use the label in an statement to specify which loop to exit, as follows:

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

Sequential Control: GOTO and NULL Statements

Unlike the and statements, the and statements are not crucial to PL/SQL programming. The structure of PL/SQL is such that the statement is seldom needed. Occasionally, it can simplify logic enough to warrant its use. The statement can improve readability by making the meaning and action of conditional statements clear.

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

GOTO Statement

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

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

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

BEGIN ... <<update_row>> BEGIN UPDATE emp SET ... ... END; ... GOTO update_row; ... END;

The label in the following example is not allowed because it does not precede an executable statement:

DECLARE done BOOLEAN; BEGIN ... FOR i IN 1..50 LOOP IF done THEN GOTO end_loop; END IF; ... <<end_loop>> -- not allowed END LOOP; -- not an executable statement END;

To debug the last example, just add the statement, as follows:

FOR i IN 1..50 LOOP IF done THEN GOTO end_loop; END IF; ... <<end_loop>> NULL; -- an executable statement END LOOP;

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

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

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

Restrictions

Some possible destinations of a statement are not allowed. Specifically, a statement cannot branch into an statement, statement, statement, or sub-block. For example, the following statement is not allowed:

BEGIN ... GOTO update_row; -- can't branch into IF statement ... IF valid THEN ... <<update_row>> UPDATE emp SET ... END IF; END;

As the example below shows, a statement cannot branch from one statement clause to another. Likewise, a statement cannot branch from one statement clause to another.

BEGIN ... IF valid THEN ... GOTO update_row; -- can't branch into ELSE clause ELSE ... <<update_row>> UPDATE emp SET ... END IF; END;

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

BEGIN ... IF status = 'OBSOLETE' THEN GOTO delete_part; -- can't branch into sub-block END IF; ... BEGIN ... <<delete_part>> DELETE FROM parts WHERE ... END; END;

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

DECLARE ... PROCEDURE compute_bonus (emp_id NUMBER) IS BEGIN ... GOTO update_row; -- can't branch out of subprogram END; BEGIN ... <<update_row>> UPDATE emp SET ... END;

Finally, a statement cannot branch from an exception handler into the current block. For example, the following statement is not allowed:

DECLARE ... pe_ratio REAL; BEGIN ... SELECT price / 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; -- can't branch into current block END;

However, a statement can branch from an exception handler into an enclosing block.

NULL Statement

The statement does nothing other than pass control to the next statement. In a conditional construct, the statement tells readers that a possibility has been considered, but no action is necessary. In the following example, the 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; END;

In statements or other places that require at least one executable statement, the statement to satisfy the syntax. In the following example, the statement emphasizes that only top-rated employees get bonuses:

IF rating > 90 THEN compute_bonus(emp_id); ELSE NULL; END IF;

Also, the statement is a handy way to create stubs when designing applications from the top down. A stub is dummy subprogram that lets you defer the definition of a procedure or function until you test and debug the main program. In the following example, the statement meets the requirement that at least one statement must appear in the executable part of a subprogram:

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

The statement executes a sequence of statements depending on the value of a condition. There are three forms of statements: , , and . For a description of the syntax of the statement, see "IF Statement".

The statement is a compact way to evaluate a single condition and choose between many alternative actions. It makes sense to use when there are three or more alternatives to choose from. For a description of the syntax of the statement, see "CASE Statement".

Using the IF-THEN Statement

The simplest form of statement associates a condition with a sequence of statements enclosed by the keywords and (not ) as illustrated in Example 4-1.

The sequence of statements is executed only if the condition is . If the condition is or , the statement does nothing. In either case, control passes to the next statement.

Example 4-1 Using a Simple IF-THEN Statement

DECLARE sales NUMBER(8,2) := 10100; quota NUMBER(8,2) := 10000; bonus NUMBER(6,2); emp_id NUMBER(6) := 120; BEGIN IF sales > (quota + 200) THEN bonus := (sales - quota)/4; UPDATE employees SET salary = salary + bonus WHERE employee_id = emp_id; END IF; END; /

Using the IF-THEN-ELSE Statement

The second form of statement adds the keyword followed by an alternative sequence of statements, as shown in Example 4-2.

The statements in the clause are executed only if the condition is or . The statement ensures that one or the other sequence of statements is executed. In the Example 4-2, the first statement is executed when the condition is , and the second statement is executed when the condition is or .

Example 4-2 Using a Simple IF-THEN-ELSE Statement

DECLARE sales NUMBER(8,2) := 12100; quota NUMBER(8,2) := 10000; bonus NUMBER(6,2); emp_id NUMBER(6) := 120; BEGIN IF sales > (quota + 200) THEN bonus := (sales - quota)/4; ELSE bonus := 50; END IF; UPDATE employees SET salary = salary + bonus WHERE employee_id = emp_id; END; /

statements can be nested as shown in Example 4-3.

Example 4-3 Nested IF Statements

DECLARE sales NUMBER(8,2) := 12100; quota NUMBER(8,2) := 10000; bonus NUMBER(6,2); emp_id NUMBER(6) := 120; BEGIN IF sales > (quota + 200) THEN bonus := (sales - quota)/4; ELSE IF sales > quota THEN bonus := 50; ELSE bonus := 0; END IF; END IF; UPDATE employees SET salary = salary + bonus WHERE employee_id = emp_id; END; /

Using the IF-THEN-ELSIF Statement

Sometimes you want to choose between several alternatives. You can use the keyword (not or ) to introduce additional conditions, as shown in Example 4-4.

If the first condition is or , the clause tests another condition. An statement can have any number of clauses; the final clause is optional. Conditions are evaluated one by one from top to bottom. If any condition is , its associated sequence of statements is executed and control passes to the next statement. If all conditions are false or , the sequence in the clause is executed, as shown in Example 4-4.

Example 4-4 Using the IF-THEN-ELSEIF Statement

DECLARE sales NUMBER(8,2) := 20000; bonus NUMBER(6,2); emp_id NUMBER(6) := 120; BEGIN IF sales > 50000 THEN bonus := 1500; ELSIF sales > 35000 THEN bonus := 500; ELSE bonus := 100; END IF; UPDATE employees SET salary = salary + bonus WHERE employee_id = emp_id; END; /

If the value of is larger than 50000, the first and second conditions are . Nevertheless, is assigned the proper value of 1500 because the second condition is never tested. When the first condition is , its associated statement is executed and control passes to the statement.

Another example of an -- statement is Example 4-5.

Example 4-5 Extended IF-THEN Statement

DECLARE grade CHAR(1); BEGIN grade := 'B'; IF grade = 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent'); ELSIF grade = 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good'); ELSIF grade = 'C' THEN DBMS_OUTPUT.PUT_LINE('Good'); ELSIF grade = 'D' THEN DBMS_OUTPUT. PUT_LINE('Fair'); ELSIF grade = 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor'); ELSE DBMS_OUTPUT.PUT_LINE('No such grade'); END IF; ENd; /

Using CASE Statements

Like the statement, the statement selects one sequence of statements to execute. However, to select the sequence, the statement uses a selector rather than multiple Boolean expressions. A selector is an expression whose value is used to select one of several alternatives.

To compare the and statements, consider the code in Example 4-5 that outputs descriptions of school grades. Note the five Boolean expressions. In each instance, we test whether the same variable, , is equal to one of five values: , , , , or . You can rewrite the code inExample 4-5 using the statement, as shown in Example 4-6.

Example 4-6 Using the CASE-WHEN Statement

DECLARE grade CHAR(1); BEGIN grade := 'B'; CASE grade WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent'); WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good'); WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Good'); WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair'); WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor'); ELSE DBMS_OUTPUT.PUT_LINE('No such grade'); END CASE; END; /

The statement is more readable and more efficient. When possible, rewrite lengthy statements as statements.

The statement begins with the keyword . The keyword is followed by a selector, which is the variable in the last example. The selector expression can be arbitrarily complex. For example, it can contain function calls. Usually, however, it consists of a single variable. The selector expression is evaluated only once. The value it yields can have any PL/SQL datatype other than , , an object type, a PL/SQL record, an index-by-table, a varray, or a nested table.

The selector is followed by one or more clauses, which are checked sequentially. The value of the selector determines which clause is executed. If the value of the selector equals the value of a -clause expression, that clause is executed. For instance, in the last example, if equals , the program outputs . Execution never falls through; if any clause is executed, control passes to the next statement.

The clause works similarly to the clause in an statement. In the last example, if the grade is not one of the choices covered by a clause, the clause is selected, and the phrase is output. The clause is optional. However, if you omit the clause, PL/SQL adds the following implicit clause:

There is always a default action, even when you omit the clause. If the statement does not match any of the clauses and you omit the clause, PL/SQL raises the predefined exception .

The keywords terminate the statement. These two keywords must be separated by a space. The statement has the following form:

Like PL/SQL blocks, statements can be labeled. The label, an undeclared identifier enclosed by double angle brackets, must appear at the beginning of the statement. Optionally, the label name can also appear at the end of the statement.

Exceptions raised during the execution of a statement are handled in the usual way. That is, normal execution stops and control transfers to the exception-handling part of your PL/SQL block or subprogram.

An alternative to the statement is the expression, where each clause is an expression. For details, see "CASE Expressions".

Searched CASE Statement

PL/SQL also provides a searched statement, similar to the simple statement, which has the form shown in Example 4-7.

The searched statement has no selector. Also, its clauses contain search conditions that yield a Boolean value, not expressions that can yield a value of any type. as shown in Example 4-7.

Example 4-7 Using the Searched CASE Statement

DECLARE grade CHAR(1); BEGIN grade := 'B'; CASE WHEN grade = 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent'); WHEN grade = 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good'); WHEN grade = 'C' THEN DBMS_OUTPUT.PUT_LINE('Good'); WHEN grade = 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair'); WHEN grade = 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor'); ELSE DBMS_OUTPUT.PUT_LINE('No such grade'); END CASE; END; -- rather than using the ELSE in the CASE, could use the following -- EXCEPTION -- WHEN CASE_NOT_FOUND THEN -- DBMS_OUTPUT.PUT_LINE('No such grade'); /

The search conditions are evaluated sequentially. The Boolean value of each search condition determines which clause is executed. If a search condition yields , its clause is executed. If any clause is executed, control passes to the next statement, so subsequent search conditions are not evaluated.

If none of the search conditions yields , the clause is executed. The clause is optional. However, if you omit the clause, PL/SQL adds the following implicit clause:

Exceptions raised during the execution of a searched statement are handled in the usual way. That is, normal execution stops and control transfers to the exception-handling part of your PL/SQL block or subprogram.

Guidelines for PL/SQL Conditional Statements

Avoid clumsy statements like those in the following example:











The value of a Boolean expression can be assigned directly to a Boolean variable. You can replace the first statement with a simple assignment:

A Boolean variable is itself either true or false. You can simplify the condition in the second statement:

When possible, use the clause instead of nested statements. Your code will be easier to read and understand. Compare the following statements:












These statements are logically equivalent, but the second statement makes the logic clearer.

To compare a single expression to multiple values, you can simplify the logic by using a single statement instead of an with several clauses.

Leave a Comment

(0 Comments)

Your email address will not be published. Required fields are marked *