PL/SQL has three categories of control statements: conditional selection statements, loop statements and sequential control statements.
PL/SQL categories of control statements are:
Conditional selection statements, which run different statements for different data values.
The conditional selection statements areIF andCASE.
Loop statements, which run the same statements with a series of different data values.
The loop statements are the basicLOOP,FORLOOP, andWHILELOOP.
TheEXIT statement transfers control to the end of a loop. TheCONTINUE statement exits the current iteration of a loop and transfers control to the next iteration. BothEXIT andCONTINUE have an optionalWHEN clause, where you can specify a condition.
Sequential control statements, which are not crucial to PL/SQL programming.
The sequential control statements areGOTO, which goes to a specified statement, andNULL, which does nothing.
Topics
Theconditional selection statements,IF andCASE, run different statements for different data values.
TheIF statement either runs or skips a sequence of one or more statements, depending on a condition. TheIF statement has these forms:
IFTHEN
IFTHENELSE
IFTHENELSIF
TheCASE statement chooses from a sequence of conditions, and runs the corresponding statement. TheCASE statement has these forms:
Simple, which evaluates a single expression and compares it to several potential values.
Searched, which evaluates multiple conditions and chooses the first one that is true.
TheCASE statement is appropriate when a different action is to be taken for each alternative.
Topics
TheIFTHEN statement either runs or skips a sequence of one or more statements, depending on a condition.
TheIFTHEN statement has this structure:
IFcondition THENstatementsEND IF;
If thecondition is true, thestatements run; otherwise, theIF statement does nothing.
For complete syntax, see"IF Statement".
Tip:
Avoid clumsyIF statements such as:
IF new_balance < minimum_balance THEN overdrawn := TRUE;ELSE overdrawn := FALSE;END IF;
Instead, assign the value of theBOOLEAN expression directly to aBOOLEAN variable:
overdrawn := new_balance < minimum_balance;
ABOOLEAN variable is eitherTRUE,FALSE, orNULL. Do not write:
IF overdrawn = TRUE THEN RAISE insufficient_funds;END IF;
Instead, write:
IF overdrawn THEN RAISE insufficient_funds;END IF;
Example 4-1 IF THEN Statement
In this example, the statements betweenTHEN andENDIF run if and only if the value ofsales is greater thanquota+200.
DECLARE PROCEDURE p ( sales NUMBER, quota NUMBER, emp_id NUMBER ) IS bonus NUMBER := 0; updated VARCHAR2(3) := 'No'; BEGINIF sales > (quota + 200) THEN bonus := (sales - quota)/4; UPDATE employees SET salary = salary + bonus WHERE employee_id = emp_id; updated := 'Yes';END IF; DBMS_OUTPUT.PUT_LINE ( 'Table updated? ' || updated || ', ' || 'bonus = ' || bonus || '.' ); END p;BEGIN p(10100, 10000, 120); p(10500, 10000, 121);END;/
Result:
Table updated? No, bonus = 0.Table updated? Yes, bonus = 125.
TheIFTHENELSE statement has this structure:
IFcondition THENstatementsELSEelse_statementsEND IF;
If the value ofcondition is true, thestatements run; otherwise, theelse_statements run. (For complete syntax, see"IF Statement".)
InExample 4-2, the statement betweenTHEN andELSE runs if and only if the value ofsales is greater thanquota+200; otherwise, the statement betweenELSE andENDIF runs.
IF statements can be nested, as inExample 4-3.
Example 4-2 IF THEN ELSE Statement
DECLARE PROCEDURE p ( sales NUMBER, quota NUMBER, emp_id NUMBER ) IS bonus NUMBER := 0; BEGINIF sales > (quota + 200) THEN bonus := (sales - quota)/4;ELSE bonus := 50;END IF; DBMS_OUTPUT.PUT_LINE('bonus = ' || bonus); UPDATE employees SET salary = salary + bonus WHERE employee_id = emp_id; END p;BEGIN p(10100, 10000, 120); p(10500, 10000, 121);END;/
Result:
bonus = 50bonus = 125
Example 4-3 Nested IF THEN ELSE Statements
DECLARE PROCEDURE p ( sales NUMBER, quota NUMBER, emp_id NUMBER ) IS bonus NUMBER := 0; BEGINIF sales > (quota + 200) THEN bonus := (sales - quota)/4;ELSEIF sales > quota THEN bonus := 50;ELSE bonus := 0;END IF;END IF; DBMS_OUTPUT.PUT_LINE('bonus = ' || bonus); UPDATE employees SET salary = salary + bonus WHERE employee_id = emp_id; END p;BEGIN p(10100, 10000, 120); p(10500, 10000, 121); p(9500, 10000, 122);END;/
Result:
bonus = 50bonus = 125bonus = 0
TheIFTHENELSIF statement has this structure:
IFcondition_1 THENstatements_1ELSIFcondition_2 THENstatements_2[ ELSIFcondition_3 THENstatements_3]...[ ELSE else_statements]END IF;
TheIFTHENELSIF statement runs the firststatements for whichcondition is true. Remaining conditions are not evaluated. If nocondition is true, theelse_statements run, if they exist; otherwise, theIFTHENELSIF statement does nothing. (For complete syntax, see"IF Statement".)
InExample 4-4, when the value ofsales is larger than 50000, both the first and second conditions are true. However, because the first condition is true,bonus is assigned the value 1500, and the second condition is never tested. Afterbonus is assigned the value 1500, control passes to theDBMS_OUTPUT.PUT_LINE invocation.
A singleIFTHENELSIF statement is easier to understand than a logically equivalent nestedIFTHENELSE statement:
-- IF THEN ELSIF statementIFcondition_1 THENstatements_1; ELSIFcondition_2 THENstatements_2; ELSIFcondition_3 THENstatement_3;END IF;-- Logically equivalent nested IF THEN ELSE statementsIFcondition_1 THENstatements_1;ELSE IFcondition_2 THENstatements_2; ELSE IFcondition_3 THENstatements_3; END IF; END IF;END IF;
Example 4-5 uses anIFTHENELSIF statement with manyELSIF clauses to compare a single value to many possible values. For this purpose, a simpleCASE statement is clearer—seeExample 4-6.
Example 4-4 IF THEN ELSIF Statement
DECLARE PROCEDURE p (sales NUMBER) IS bonus NUMBER := 0; BEGINIF sales > 50000 THEN bonus := 1500;ELSIF sales > 35000 THEN bonus := 500;ELSE bonus := 100;END IF; DBMS_OUTPUT.PUT_LINE ( 'Sales = ' || sales || ', bonus = ' || bonus || '.' ); END p;BEGIN p(55000); p(40000); p(30000);END;/
Result:
Sales = 55000, bonus = 1500.Sales = 40000, bonus = 500.Sales = 30000, bonus = 100.
Example 4-5 IF THEN ELSIF Statement Simulates Simple CASE 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;/Result:
Very Good
The simpleCASE statement has this structure:
CASEselectorWHENselector_value_1 THENstatements_1WHENselector_value_2 THENstatements_2...WHENselector_value_n THENstatements_n[ ELSEelse_statements ]END CASE;]
Theselector is an expression (typically a single variable). Eachselector_value can be either a literal or an expression. (For complete syntax, see"CASE Statement".)
The simpleCASE statement runs the firststatements for whichselector_value equalsselector. Remaining conditions are not evaluated. If noselector_value equalsselector, theCASE statement runselse_statements if they exist and raises the predefined exceptionCASE_NOT_FOUND otherwise.
Example 4-6 uses a simpleCASE statement to compare a single value to many possible values. TheCASE statement inExample 4-6 is logically equivalent to theIFTHENELSIF statement inExample 4-5.
Note:
As in a simpleCASE expression, if the selector in a simpleCASE statement has the valueNULL, it cannot be matched byWHENNULL (seeExample 2-51). Instead, use a searchedCASE statement withWHENconditionISNULL (seeExample 2-53).
Example 4-6 Simple CASE Statement
DECLARE grade CHAR(1);BEGIN grade := 'B';CASE gradeWHEN '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;/
Result:
Very Good
The searchedCASE statement has this structure:
CASEWHENcondition_1 THENstatements_1WHENcondition_2 THENstatements_2...WHENcondition_n THENstatements_n[ ELSEelse_statements ]END CASE;]
The searchedCASE statement runs the firststatements for whichcondition is true. Remaining conditions are not evaluated. If nocondition is true, theCASE statement runselse_statements if they exist and raises the predefined exceptionCASE_NOT_FOUND otherwise. (For complete syntax, see"CASE Statement".)
The searchedCASE statement inExample 4-7 is logically equivalent to the simpleCASE statement inExample 4-6.
In bothExample 4-7 andExample 4-6, theELSE clause can be replaced by anEXCEPTION part.Example 4-8 is logically equivalent toExample 4-7.
Example 4-7 Searched CASE Statement
DECLARE grade CHAR(1);BEGIN grade := 'B';CASEWHEN 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;/
Result:
Very Good
Example 4-8 EXCEPTION Instead of ELSE Clause in 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'); END CASE;EXCEPTIONWHEN CASE_NOT_FOUND THENDBMS_OUTPUT.PUT_LINE('No such grade');END;/Result:
Very Good
Loop statements run the same statements with a series of different values. The loop statements are:
BasicLOOP
FORLOOP
CursorFORLOOP
WHILELOOP
The statements that exit a loop are:
EXIT
EXITWHEN
The statements that exit the current iteration of a loop are:
CONTINUE
CONTINUEWHEN
EXIT,EXITWHEN,CONTINUE, andCONTINUEWHEN and can appear anywhere inside a loop, but not outside a loop. Oracle recommends using these statements instead of the"GOTO Statement", which can exit a loop or the current iteration of a loop by transferring control to a statement outside the loop. (A raised exception also exits a loop. For information about exceptions, see"Overview of Exception Handling".)
LOOP statements can be labeled, andLOOP statements can be nested. Labels are recommended for nested loops to improve readability. You must ensure that the label in theENDLOOP statement matches the label at the beginning of the same loop statement (the compiler does not check).
Topics
The basicLOOP statement has this structure:
[label ] LOOPstatementsEND LOOP [label ];
With each iteration of the loop, thestatements run and control returns to the top of the loop. To prevent an infinite loop, a statement or raised exception must exit the loop.
See Also:
TheEXIT statement exits the current iteration of a loop unconditionally and transfers control to the end of either the current loop or an enclosing labeled loop.
InExample 4-9, theEXIT statement inside the basicLOOP statement transfers control unconditionally to the end of the current loop.
See Also:
Example 4-9 Basic LOOP Statement with EXIT Statement
DECLARE x NUMBER := 0;BEGINLOOP DBMS_OUTPUT.PUT_LINE ('Inside loop: x = ' || TO_CHAR(x)); x := x + 1; IF x > 3 THENEXIT; END IF;END LOOP;-- After EXIT, control resumes here DBMS_OUTPUT.PUT_LINE(' After loop: x = ' || TO_CHAR(x));END;/
Result:
Inside loop: x = 0Inside loop: x = 1Inside loop: x = 2Inside loop: x = 3After loop: x = 4
TheEXITWHEN statement exits the current iteration of a loop when the condition in itsWHEN clause is true, and transfers control to the end of either the current loop or an enclosing labeled loop.
Each time control reaches theEXITWHEN statement, the condition in itsWHEN clause is evaluated. If the condition is not true, theEXITWHEN statement does nothing. To prevent an infinite loop, a statement inside the loop must make the condition true, as inExample 4-10.
InExample 4-10, theEXITWHEN statement inside the basicLOOP statement transfers control to the end of the current loop whenx is greater than 3.Example 4-10 is logically equivalent toExample 4-9.
See Also:
InExample 4-11, one basicLOOP statement is nested inside the other, and both have labels. The inner loop has twoEXITWHEN statements; one that exits the inner loop and one that exits the outer loop.
AnEXITWHEN statement in an inner loop can transfer control to an outer loop only if the outer loop is labeled.
InExample 4-12, the outer loop is not labeled; therefore, the inner loop cannot transfer control to it.
Example 4-10 Basic LOOP Statement with EXIT WHEN Statement
DECLARE x NUMBER := 0;BEGINLOOP DBMS_OUTPUT.PUT_LINE('Inside loop: x = ' || TO_CHAR(x));x := x + 1; -- prevents infinite loopEXIT WHEN x > 3;END LOOP;-- After EXIT statement, control resumes here DBMS_OUTPUT.PUT_LINE('After loop: x = ' || TO_CHAR(x));END;/
Result:
Inside loop: x = 0Inside loop: x = 1Inside loop: x = 2Inside loop: x = 3After loop: x = 4
Example 4-11 Nested, Labeled Basic LOOP Statements with EXIT WHEN Statements
DECLARE s PLS_INTEGER := 0; i PLS_INTEGER := 0; j PLS_INTEGER;BEGIN<<outer_loop>> LOOP i := i + 1; j := 0;<<inner_loop>> LOOP j := j + 1; s := s + i * j; -- Sum several productsEXIT inner_loop WHEN (j > 5);EXIT outer_loop WHEN ((i * j) > 15); END LOOP inner_loop; END LOOP outer_loop; DBMS_OUTPUT.PUT_LINE ('The sum of products equals: ' || TO_CHAR(s));END;/
Result:
The sum of products equals: 166
Example 4-12 Nested, Unabeled Basic LOOP Statements with EXIT WHEN Statements
DECLARE i PLS_INTEGER := 0; j PLS_INTEGER := 0; BEGIN LOOP i := i + 1; DBMS_OUTPUT.PUT_LINE ('i = ' || i); LOOP j := j + 1; DBMS_OUTPUT.PUT_LINE ('j = ' || j);EXIT WHEN (j > 3); END LOOP; DBMS_OUTPUT.PUT_LINE ('Exited inner loop');EXIT WHEN (i > 2); END LOOP; DBMS_OUTPUT.PUT_LINE ('Exited outer loop');END;/Result:
i = 1j = 1j = 2j = 3j = 4Exited inner loopi = 2j = 5Exited inner loopi = 3j = 6Exited inner loopExited outer loop PL/SQL procedure successfully completed.
TheCONTINUE statement exits the current iteration of a loop unconditionally and transfers control to the next iteration of either the current loop or an enclosing labeled loop.
InExample 4-13, theCONTINUE statement inside the basicLOOP statement transfers control unconditionally to the next iteration of the current loop.
See Also:
Example 4-13 CONTINUE Statement in Basic LOOP Statement
DECLARE x NUMBER := 0;BEGINLOOP -- After CONTINUE statement, control resumes here DBMS_OUTPUT.PUT_LINE ('Inside loop: x = ' || TO_CHAR(x)); x := x + 1; IF x < 3 THENCONTINUE; END IF; DBMS_OUTPUT.PUT_LINE ('Inside loop, after CONTINUE: x = ' || TO_CHAR(x)); EXIT WHEN x = 5; END LOOP; DBMS_OUTPUT.PUT_LINE (' After loop: x = ' || TO_CHAR(x));END;/
Result:
Inside loop: x = 0Inside loop: x = 1Inside loop: x = 2Inside loop, after CONTINUE: x = 3Inside loop: x = 3Inside loop, after CONTINUE: x = 4Inside loop: x = 4Inside loop, after CONTINUE: x = 5After loop: x = 5
TheCONTINUEWHEN statement exits the current iteration of a loop when the condition in itsWHEN clause is true, and transfers control to the next iteration of either the current loop or an enclosing labeled loop.
Each time control reaches theCONTINUEWHEN statement, the condition in itsWHEN clause is evaluated. If the condition is not true, theCONTINUEWHEN statement does nothing.
InExample 4-14, theCONTINUEWHEN statement inside the basicLOOP statement transfers control to the next iteration of the current loop whenx is less than 3.Example 4-14 is logically equivalent toExample 4-13.
See Also:
Example 4-14 CONTINUE WHEN Statement in Basic LOOP Statement
DECLARE x NUMBER := 0;BEGINLOOP -- After CONTINUE statement, control resumes here DBMS_OUTPUT.PUT_LINE ('Inside loop: x = ' || TO_CHAR(x)); x := x + 1;CONTINUE WHEN x < 3; DBMS_OUTPUT.PUT_LINE ('Inside loop, after CONTINUE: x = ' || TO_CHAR(x)); EXIT WHEN x = 5; END LOOP; DBMS_OUTPUT.PUT_LINE (' After loop: x = ' || TO_CHAR(x));END;/
Result:
Inside loop: x = 0Inside loop: x = 1Inside loop: x = 2Inside loop, after CONTINUE: x = 3Inside loop: x = 3Inside loop, after CONTINUE: x = 4Inside loop: x = 4Inside loop, after CONTINUE: x = 5After loop: x = 5
TheFORLOOP statement runs one or more statements while the loop index is in a specified range. The statement has this structure:
[label ] FORindex IN [ REVERSE ]lower_bound..upper_bound LOOPstatementsEND LOOP [label ];
WithoutREVERSE, the value ofindex starts atlower_bound and increases by one with each iteration of the loop until it reachesupper_bound. Iflower_bound is greater thanupper_bound, then thestatements never run.
WithREVERSE, the value ofindex starts atupper_bound and decreases by one with each iteration of the loop until it reacheslower_bound. Ifupper_bound is less thanlower_bound, then thestatements never run.
AnEXIT,EXITWHEN,CONTINUE, orCONTINUEWHEN in thestatements can cause the loop or the current iteration of the loop to end early.
Tip:
To process the rows of a query result set, use a cursorFORLOOP, which has a query instead of a range of integers. For details, see"Processing Query Result Sets With Cursor FOR LOOP Statements".
See Also:
InExample 4-15,index isi,lower_bound is 1, andupper_bound is 3. The loop prints the numbers from 1 to 3.
TheFORLOOP statement inExample 4-16 is the reverse of the one inExample 4-15: It prints the numbers from 3 to 1.
In some languages, theFORLOOP has aSTEP clause that lets you specify a loop index increment other than 1. To simulate theSTEP clause in PL/SQL, multiply each reference to the loop index by the desired increment.
InExample 4-17, theFORLOOP effectively increments the index by five.
Topics
Example 4-15 FOR LOOP Statements
BEGIN DBMS_OUTPUT.PUT_LINE ('lower_bound < upper_bound'); FOR i IN 1..3 LOOP DBMS_OUTPUT.PUT_LINE (i); END LOOP; DBMS_OUTPUT.PUT_LINE ('lower_bound = upper_bound'); FOR i IN 2..2 LOOP DBMS_OUTPUT.PUT_LINE (i); END LOOP; DBMS_OUTPUT.PUT_LINE ('lower_bound > upper_bound'); FOR i IN 3..1 LOOP DBMS_OUTPUT.PUT_LINE (i); END LOOP;END;/Result:
lower_bound < upper_bound123lower_bound = upper_bound2lower_bound > upper_bound
Example 4-16 Reverse FOR LOOP Statements
BEGIN DBMS_OUTPUT.PUT_LINE ('upper_bound > lower_bound'); FOR i IN REVERSE 1..3 LOOP DBMS_OUTPUT.PUT_LINE (i); END LOOP; DBMS_OUTPUT.PUT_LINE ('upper_bound = lower_bound'); FOR i IN REVERSE 2..2 LOOP DBMS_OUTPUT.PUT_LINE (i); END LOOP; DBMS_OUTPUT.PUT_LINE ('upper_bound < lower_bound'); FOR i IN REVERSE 3..1 LOOP DBMS_OUTPUT.PUT_LINE (i); END LOOP;END;/Result:
upper_bound > lower_bound321upper_bound = lower_bound2upper_bound < lower_bound
Example 4-17 Simulating STEP Clause in FOR LOOP Statement
DECLAREstep PLS_INTEGER := 5;BEGIN FOR i IN 1..3 LOOP DBMS_OUTPUT.PUT_LINE (i*step); END LOOP;END;/
Result:
51015
The index of aFORLOOP statement is implicitly declared as a variable of typePLS_INTEGER that is local to the loop. The statements in the loop can read the value of the index, but cannot change it. Statements outside the loop cannot reference the index. After theFORLOOP statement runs, the index is undefined. (A loop index is sometimes called a loop counter.)
InExample 4-18, theFORLOOP statement tries to change the value of its index, causing an error.
InExample 4-19, a statement outside theFORLOOP statement references the loop index, causing an error.
If the index of aFORLOOP statement has the same name as a variable declared in an enclosing block, the local implicit declaration hides the other declaration, asExample 4-20 shows.
Example 4-21 shows how to changeExample 4-20 to allow the statement inside the loop to reference the variable declared in the enclosing block.
InExample 4-22, the indexes of the nestedFORLOOP statements have the same name. The inner loop references the index of the outer loop by qualifying the reference with the label of the outer loop. For clarity only, the inner loop also qualifies the reference to its own index with its own label.
Example 4-18 FOR LOOP Statement Tries to Change Index Value
BEGIN FORi IN 1..3 LOOP IF i < 3 THEN DBMS_OUTPUT.PUT_LINE (TO_CHAR(i)); ELSEi := 2; END IF; END LOOP;END;/
Result:
i := 2;*ERROR at line 6:ORA-06550: line 6, column 8:PLS-00363: expression 'I' cannot be used as an assignment targetORA-06550: line 6, column 8:PL/SQL: Statement ignored
Example 4-19 Outside Statement References FOR LOOP Statement Index
BEGIN FORi IN 1..3 LOOP DBMS_OUTPUT.PUT_LINE ('Inside loop, i is ' || TO_CHAR(i)); END LOOP; DBMS_OUTPUT.PUT_LINE ('Outside loop, i is ' || TO_CHAR(i));END;/
Result:
DBMS_OUTPUT.PUT_LINE ('Outside loop, i is ' || TO_CHAR(i)); *ERROR at line 6:ORA-06550: line 6, column 58:PLS-00201: identifier 'I' must be declaredORA-06550: line 6, column 3:PL/SQL: Statement ignoredExample 4-20 FOR LOOP Statement Index with Same Name as Variable
DECLAREi NUMBER := 5;BEGIN FORi IN 1..3 LOOP DBMS_OUTPUT.PUT_LINE ('Inside loop, i is ' || TO_CHAR(i)); END LOOP; DBMS_OUTPUT.PUT_LINE ('Outside loop, i is ' || TO_CHAR(i));END;/
Result:
Inside loop, i is 1Inside loop, i is 2Inside loop, i is 3Outside loop, i is 5
Example 4-21 FOR LOOP Statement References Variable with Same Name as Index
<<main>> -- Label block.DECLARE i NUMBER := 5;BEGIN FOR i IN 1..3 LOOP DBMS_OUTPUT.PUT_LINE ( 'local: ' || TO_CHAR(i) || ', global: ' || TO_CHAR(main.i)-- Qualify reference with block label. ); END LOOP;END main;/
Result:
local: 1, global: 5local: 2, global: 5local: 3, global: 5
Example 4-22 Nested FOR LOOP Statements with Same Index Name
BEGIN<<outer_loop>> FOR i IN 1..3 LOOP<<inner_loop>> FOR i IN 1..3 LOOP IFouter_loop.i = 2 THEN DBMS_OUTPUT.PUT_LINE ('outer: ' || TO_CHAR(outer_loop.i) || ' inner: ' || TO_CHAR(inner_loop.i)); END IF; END LOOP inner_loop; END LOOP outer_loop;END;/
Result:
outer: 2 inner: 1outer: 2 inner: 2outer: 2 inner: 3
The lower and upper bounds of aFORLOOP statement can be either numeric literals, numeric variables, or numeric expressions. If a bound does not have a numeric value, then PL/SQL raises the predefined exceptionVALUE_ERROR.
InExample 4-24, the upper bound of theFORLOOP statement is a variable whose value is determined at run time.
Example 4-23 FOR LOOP Statement Bounds
DECLARE first INTEGER := 1; last INTEGER := 10; high INTEGER := 100; low INTEGER := 12;BEGIN-- Bounds are numeric literals: FOR j IN -5..5 LOOP NULL; END LOOP;-- Bounds are numeric variables: FOR k IN REVERSE first..last LOOP NULL; END LOOP;-- Lower bound is numeric literal,-- Upper bound is numeric expression: FOR step IN 0..(TRUNC(high/low) * 2) LOOP NULL; END LOOP;END;/
Example 4-24 Specifying FOR LOOP Statement Bounds at Run Time
DROP TABLE temp;CREATE TABLE temp ( emp_no NUMBER, email_addr VARCHAR2(50)); DECLARE emp_count NUMBER;BEGINSELECT COUNT(employee_id) INTO emp_count FROM employees;FOR i IN 1..emp_count LOOP INSERT INTO temp (emp_no, email_addr) VALUES(i, 'to be added later'); END LOOP;END;/
Suppose that you must exit aFORLOOP statement immediately if a certain condition arises. You can put the condition in anEXITWHEN statement inside theFORLOOP statement.
InExample 4-25, theFORLOOP statement executes 10 times unless theFETCH statement inside it fails to return a row, in which case it ends immediately.
Now suppose that theFORLOOP statement that you must exit early is nested inside anotherFORLOOP statement. If, when you exit the inner loop early, you also want to exit the outer loop, then label the outer loop and specify its name in theEXITWHEN statement, as inExample 4-26.
If you want to exit the inner loop early but complete the current iteration of the outer loop, then label the outer loop and specify its name in theCONTINUEWHEN statement, as inExample 4-27.
See Also:
"Overview of Exception Handling" for information about exceptions, which can also cause a loop to end immediately if a certain condition arises
Example 4-25 EXIT WHEN Statement in FOR LOOP Statement
DECLARE v_employees employees%ROWTYPE; CURSOR c1 is SELECT * FROM employees;BEGIN OPEN c1; -- Fetch entire row into v_employees record: FOR i IN 1..10 LOOP FETCH c1 INTO v_employees;EXIT WHEN c1%NOTFOUND; -- Process data here END LOOP; CLOSE c1;END;/Example 4-26 EXIT WHEN Statement in Inner FOR LOOP Statement
DECLARE v_employees employees%ROWTYPE; CURSOR c1 is SELECT * FROM employees;BEGIN OPEN c1; -- Fetch entire row into v_employees record:<<outer_loop>> FOR i IN 1..10 LOOP -- Process data here FOR j IN 1..10 LOOP FETCH c1 INTO v_employees;EXIT outer_loop WHEN c1%NOTFOUND; -- Process data here END LOOP; END LOOP outer_loop; CLOSE c1;END;/
Example 4-27 CONTINUE WHEN Statement in Inner FOR LOOP Statement
DECLARE v_employees employees%ROWTYPE; CURSOR c1 is SELECT * FROM employees;BEGIN OPEN c1; -- Fetch entire row into v_employees record:<<outer_loop>> FOR i IN 1..10 LOOP -- Process data here FOR j IN 1..10 LOOP FETCH c1 INTO v_employees;CONTINUE outer_loop WHEN c1%NOTFOUND; -- Process data here END LOOP; END LOOP outer_loop; CLOSE c1;END;/
TheWHILELOOP statement runs one or more statements while a condition is true. It has this structure:
[label ] WHILEcondition LOOPstatementsEND LOOP [label ];
If thecondition is true, thestatements run and control returns to the top of the loop, wherecondition is evaluated again. If thecondition is not true, control transfers to the statement after theWHILELOOP statement. To prevent an infinite loop, a statement inside the loop must make the condition false or null. For complete syntax, see"WHILE LOOP Statement".
AnEXIT,EXITWHEN,CONTINUE, orCONTINUEWHEN in thestatements can cause the loop or the current iteration of the loop to end early.
Some languages have aLOOPUNTIL orREPEATUNTIL structure, which tests a condition at the bottom of the loop instead of at the top, so that the statements run at least once. To simulate this structure in PL/SQL, use a basicLOOP statement with anEXITWHEN statement:
LOOPstatements EXIT WHENcondition;END LOOP;
InExample 4-28, the statements in the firstWHILELOOP statement never run, and the statements in the secondWHILELOOP statement run once.
Example 4-28 WHILE LOOP Statements
DECLARE done BOOLEAN := FALSE;BEGIN WHILE done LOOP DBMS_OUTPUT.PUT_LINE ('This line does not print.'); done := TRUE; -- This assignment is not made. END LOOP; WHILE NOT done LOOP DBMS_OUTPUT.PUT_LINE ('Hello, world!'); done := TRUE; END LOOP;END;/Result:
Hello, world!
Unlike theIF andLOOP statements, thesequential control statementsGOTO andNULL are not crucial to PL/SQL programming.
TheGOTO statement, which goes to a specified statement, is seldom needed. Occasionally, it simplifies logic enough to warrant its use.
TheNULL statement, which does nothing, can improve readability by making the meaning and action of conditional statements clear.
Topics
TheGOTO statement transfers control to a label unconditionally. The label must be unique in its scope and must precede an executable statement or a PL/SQL block. When run, theGOTO statement transfers control to the labeled statement or block. ForGOTO statement restrictions, see"GOTO Statement".
UseGOTO statements sparingly—overusing them results in code that is hard to understand and maintain. Do not use aGOTO statement to transfer control from a deeply nested structure to an exception handler. Instead, raise an exception. For information about the PL/SQL exception-handling mechanism, seePL/SQL Error Handling.
A label can appear only before a block (as inExample 4-21) or before a statement (as inExample 4-29), not in a statement, as inExample 4-30.
To correctExample 4-30, add aNULL statement, as inExample 4-31.
AGOTO statement can transfer control to an enclosing block from the current block, as inExample 4-32.
TheGOTO statement transfers control to the first enclosing block in which the referenced label appears.
TheGOTO statement inExample 4-33 transfers control into anIF statement, causing an error.
Example 4-29 GOTO Statement
DECLARE p VARCHAR2(30); n PLS_INTEGER := 37;BEGIN FOR j in 2..ROUND(SQRT(n)) LOOP IF n MOD j = 0 THEN p := ' is not a prime number';GOTO print_now; END IF; END LOOP; p := ' is a prime number';<<print_now>> DBMS_OUTPUT.PUT_LINE(TO_CHAR(n) || p);END;/
Result:
37 is a prime number
Example 4-30 Incorrect Label Placement
DECLARE done BOOLEAN;BEGIN FOR i IN 1..50 LOOP IF done THEN GOTO end_loop; END IF;<<end_loop>> END LOOP;END;/Result:
END LOOP;*ERROR at line 9:ORA-06550: line 9, column 3:PLS-00103: Encountered the symbol "END" when expecting one of the following:( begin case declare exit for goto if loop mod null raisereturn select update while with <an identifier><a double-quoted delimited-identifier> <a bind variable> <<continue close current delete fetch lock insert open rollbacksavepoint set sql run commit forall merge pipe purge
Example 4-31 GOTO Statement Goes to Labeled NULL Statement
DECLARE done BOOLEAN;BEGIN FOR i IN 1..50 LOOP IF done THENGOTO end_loop; END IF;<<end_loop>>NULL; END LOOP;END;/
Example 4-32 GOTO Statement Transfers Control to Enclosing Block
DECLARE v_last_name VARCHAR2(25); v_emp_id NUMBER(6) := 120;BEGIN<<get_name>> SELECT last_name INTO v_last_name FROM employees WHERE employee_id = v_emp_id; BEGIN DBMS_OUTPUT.PUT_LINE (v_last_name); v_emp_id := v_emp_id + 5; IF v_emp_id < 120 THENGOTO get_name; END IF; END;END;/
Result:
Weiss
Example 4-33 GOTO Statement Cannot Transfer Control into IF Statement
DECLARE valid BOOLEAN := TRUE;BEGINGOTO update_row; IF valid THEN<<update_row>> NULL; END IF;END;/
Result:
GOTO update_row;*ERROR at line 4:ORA-06550: line 4, column 3:PLS-00375: illegal GOTO statement; this GOTO cannot transfer control to label'UPDATE_ROW'ORA-06550: line 6, column 12:PL/SQL: Statement ignored
TheNULL statement only passes control to the next statement. Some languages refer to such an instruction as a no-op (no operation).
Some uses for theNULL statement are:
To provide a target for aGOTO statement, as inExample 4-31.
To improve readability by making the meaning and action of conditional statements clear, as inExample 4-34
To create placeholders and stub subprograms, as inExample 4-35
To show that you are aware of a possibility, but that no action is necessary, as inExample 4-36
InExample 4-34, theNULL statement emphasizes that only salespersons receive commissions.
InExample 4-35, theNULL statement lets you compile this subprogram and fill in the real body later.
Note:
Using theNULL statement might raise anunreachablecode warning if warnings are enabled. For information about warnings, see"Compile-Time Warnings".
InExample 4-36, theNULL statement shows that you have chosen to take no action for grades other than A, B, C, D, and F.
Example 4-34 NULL Statement Showing No Action
DECLARE v_job_id VARCHAR2(10); v_emp_id NUMBER(6) := 110;BEGIN SELECT job_id INTO v_job_id FROM employees WHERE employee_id = v_emp_id; IF v_job_id = 'SA_REP' THEN UPDATE employees SET commission_pct = commission_pct * 1.2; ELSENULL; -- Employee is not a sales rep END IF;END;/Example 4-35 NULL Statement as Placeholder During Subprogram Creation
CREATE OR REPLACE PROCEDURE award_bonus ( emp_id NUMBER, bonus NUMBER) AUTHID DEFINER ASBEGIN -- Executable part starts hereNULL; -- Placeholder-- (raises "unreachable code" if warnings enabled)END award_bonus;/
Example 4-36 NULL Statement in ELSE Clause of Simple CASE Statement
CREATE OR REPLACE PROCEDURE print_grade ( grade CHAR) AUTHID DEFINER ASBEGIN 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 NULL; END CASE;END;/BEGIN print_grade('A'); print_grade('S');END;/Result:
Excellent