If you're seeing this message, it means we're having trouble loading external resources on our website. Show
If you're behind a web filter, please make sure that the domains *.kastatic.org and *.kasandbox.org are unblocked. 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. Major TopicsOverviewConditional Control: IF StatementsIterative Control: LOOP and EXIT StatementsSequential Control: GOTO and NULL StatementsOverviewAccording to the structure theorem, any computer program can be written using the basic control structures shown in Figure 3-1. They can be combined in any way necessary to deal with a given problem. Figure 3-1 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 false. A condition is any variable or expression that returns a Boolean value ( Conditional Control: IF Statements Often, it is necessary to take alternative actions depending on circumstances. The IF-THEN The simplest form of 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 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 IF x > y THEN high := x; END IF; IF-THEN-ELSE The second form of IF condition THEN sequence_of_statements1 ELSE sequence_of_statements2 END IF; The sequence of statements in the IF trans_type = 'CR' THEN UPDATE accounts SET balance = balance + credit WHERE ... ELSE UPDATE accounts SET balance = balance - debit WHERE ... END IF; The 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 Sometimes you want to select an action from several mutually exclusive alternatives. The third form of 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 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 Guidelines Avoid clumsy DECLARE ... overdrawn BOOLEAN; BEGIN ... IF new_balance < minimum_balance THEN overdrawn := TRUE; ELSE overdrawn := FALSE; END IF; ... IF overdrawn = TRUE THEN RAISE insufficient_funds; END IF; END; 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 overdrawn := new_balance < minimum_balance; Second, a Boolean variable is itself either true or false. So, you can simplify the condition in the second IF overdrawn THEN ... When possible, use the 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. Iterative Control: LOOP and EXIT Statements LOOP The simplest form of 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 EXIT The 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 BEGIN ... IF credit_rating < 3 THEN ... EXIT; -- illegal END IF; END; Remember, the EXIT-WHEN The 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 The IF count > 100 THEN | EXIT WHEN count > 100; EXIT; | END IF; | These statements are logically equivalent, but the 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 <<label_name>> LOOP sequence_of_statements END LOOP; Optionally, the label name can also appear at the end of the <<my_loop>> LOOP ... END LOOP my_loop; When you nest labeled loops, you can use ending label names to improve readability. With either form of <<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 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 Some languages have a LOOP sequence_of_statements EXIT WHEN boolean_expression; END LOOP; To ensure that a 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 WHILE TRUE LOOP | LOOP ... | ... END LOOP; | END LOOP; FOR-LOOP Whereas the number of iterations through a FOR counter IN [REVERSE] lower_bound..higher_bound LOOP sequence_of_statements END LOOP; The range is evaluated when the 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 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 FOR ctr IN 1..10 LOOP IF NOT finished THEN INSERT INTO ... VALUES (ctr, ...); -- legal factor := ctr * 2; -- legal ELSE ctr := 10; -- illegal 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 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 DECLARE hi NUMBER := 2**32; BEGIN FOR j IN 1..hi LOOP -- causes a 'numeric overflow' error ... END LOOP; END; Some languages provide a 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 RangesPL/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 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 RulesThe 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; -- illegal You need not explicitly declare the loop counter because it is implicitly declared as a local variable of type 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 <<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 FOR j IN 1..10 LOOP FETCH c1 INTO emp_rec; EXIT WHEN c1%NOTFOUND; ... END LOOP; Suppose you must exit from a nested <<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 Overuse of GOTO Statement The 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 DECLARE done BOOLEAN; BEGIN ... FOR i IN 1..50 LOOP IF done THEN GOTO end_loop; END IF; ... <<end_loop>> -- illegal END LOOP; -- not an executable statement END; To debug the last example, just add the 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 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 Restrictions Some possible destinations of a BEGIN ... GOTO update_row; -- illegal branch into IF statement ... IF valid THEN ... <<update_row>> UPDATE emp SET ... END IF; END; Also, a BEGIN ... IF valid THEN ... GOTO update_row; -- illegal branch into ELSE clause ELSE ... <<update_row>> UPDATE emp SET ... END IF; END; The next example shows that a
BEGIN ... IF status = 'OBSOLETE' THEN GOTO delete_part; -- illegal branch into sub-block END IF; ... BEGIN ... <<delete_part>> DELETE FROM parts WHERE ... END; END; Also, a DECLARE ... PROCEDURE compute_bonus (emp_id NUMBER) IS BEGIN ... GOTO update_row; -- illegal branch out of subprogram END; BEGIN ... <<update_row>> UPDATE emp SET ... END; Finally, a 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; -- illegal branch into current block END; However, a NULL Statement The EXCEPTION WHEN ZERO_DIVIDE THEN ROLLBACK; WHEN VALUE_ERROR THEN INSERT INTO errors VALUES ... COMMIT; WHEN OTHERS THEN NULL; END; Each clause in an IF rating > 90 THEN compute_bonus(emp_id); ELSE NULL; END IF; Also, the PROCEDURE debit_account (acct_id INTEGER, amount REAL) IS BEGIN NULL; END debit_account; Which type of expression has a value of either true or false?A Boolean expression is a logical statement that is either TRUE or FALSE .
What is the structure that causes a statement or a set of statements to execute repeatedly?A repetition structure causes a statement or set of statements to execute repeatedly. Repetition structures are used to perform the same task over and over. A condition-controlled loop uses a Boolean (true/false) condition to control the number of times that it repeats.
Which structure can execute a set of statements only under certain circumstances?Decision structure:
Decision structure executes a set of statements under certain conditions.
Which structure is a logical design that controls the order in which a set of statements executes?A control structure is a logical design that controls the order in which a set of statements execute.
|