PL/SQL Control Statements

最新推荐文章于 2024-09-27 00:45:01 发布

DB架构

最新推荐文章于 2024-09-27 00:45:01 发布

阅读量543

点赞数

分类专栏： PL/SQL 文章标签： java 数据库 sql

本文链接：https://blog.csdn.net/u011868279/article/details/129615322

版权

PL/SQL 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

PL/SQL Control Statements

PL/SQL has three categories of control statements:

Conditional selection statements, which run different statements for different data values.

The conditional selection statements are IF and and CASE.

Loop statements, which run the same statements with a series of different data values.

The loop statements are the basic LOOP, FOR LOOP, and WHILE LOOP.

The EXIT statement transfers control to the end of a loop. The CONTINUE statement exits the current iteration of a loop and transfers control to the next iteration. Both EXIT and CONTINUE have an optional WHEN clause, where you can specify a condition.

Sequential control statements, which are not crucial to PL/SQL programming.

The sequential control statements are GOTO, which goes to a specified statement, and NULL, which does nothing.

Conditional Selection Statements

The conditional selection statements, IF and CASE, run different statements for different data values.

The IF statement either runs or skips a sequence of one or more statements, depending on a condition. The IF statement has these forms:

IF THEN

IF THEN ELSE

IF THEN ELSIF

The CASE statement chooses from a sequence of conditions, and runs the corresponding statement. The CASE 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.

The CASE statement is appropriate when a different action is to be taken for each alternative.

IF THEN Statement

The IF THEN statement has this structure:

IF condition THEN
  statements
END IF;

If the condition is true, the statements run; otherwise, the IF statement does nothing.

Example 4-1 IF THEN Statement

SQL> 
SQL> conn hr/hr@PDB1;
Connected.
SQL> show user;
USER is "HR"
SQL> 
SQL> set pagesize 200 linesize 200
SQL> 
SQL> set serveroutput on
SQL> 
SQL> DECLARE
  2    PROCEDURE p (
  3      sales  NUMBER,
  4      quota  NUMBER,
  5      emp_id NUMBER
  6    )
  7    IS
  8      bonus    NUMBER := 0;
  9      updated  VARCHAR2(3) := 'No';
 10    BEGIN
 11      IF sales > (quota + 200) THEN
 12        bonus := (sales - quota)/4;
 13   
 14        UPDATE employees
 15        SET salary = salary + bonus 
 16        WHERE employee_id = emp_id;
 17   
 18        updated := 'Yes';
 19      END IF;
 20   
 21      DBMS_OUTPUT.PUT_LINE (
 22        'Table updated?  ' || updated || ', ' || 
 23        'bonus = ' || bonus || '.'
 24      );
 25    END p;
 26  BEGIN
 27    p(10100, 10000, 120);
 28    p(10500, 10000, 121);
 29  END;
 30  /
Table updated?  No, bonus = 0.
Table updated?  Yes, bonus = 125.

PL/SQL procedure successfully completed.

SQL>

Tips:

========================================================================

Avoid clumsy IF statements such as:

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

Instead, assign the value of the BOOLEAN expression directly to a BOOLEAN variable:

overdrawn := new_balance < minimum_balance;

A BOOLEAN variable is either TRUE, FALSE, or NULL. Do not write:

IF overdrawn = TRUE THEN
  RAISE insufficient_funds;
END IF;

Instead, write:

IF overdrawn THEN
  RAISE insufficient_funds;
END IF;

=================================================================

IF THEN ELSE Statement

The IF THEN ELSE statement has this structure:

IF condition THEN
  statements
ELSE
  else_statements
END IF;

If the value of condition is true, the statements run; otherwise, the else_statements run.

Example 4-2 IF THEN ELSE Statement

SQL> 
SQL> 
SQL> DECLARE
  2    PROCEDURE p (
  3      sales  NUMBER,
  4      quota  NUMBER,
  5      emp_id NUMBER
  6    )
  7    IS
  8      bonus  NUMBER := 0;
  9    BEGIN
 10      IF sales > (quota + 200) THEN
 11        bonus := (sales - quota)/4;
 12      ELSE
 13        bonus := 50;
 14      END IF;
 15   
 16      DBMS_OUTPUT.PUT_LINE('bonus = ' || bonus);
 17   
 18      UPDATE employees
 19      SET salary = salary + bonus 
 20      WHERE employee_id = emp_id;
 21    END p;
 22  BEGIN
 23    p(10100, 10000, 120);
 24    p(10500, 10000, 121);
 25  END;
 26  /
bonus = 50
bonus = 125

PL/SQL procedure successfully completed.

SQL>

Example 4-3 Nested IF THEN ELSE Statements

SQL> 
SQL> DECLARE
  2    PROCEDURE p (
  3      sales  NUMBER,
  4      quota  NUMBER,
  5      emp_id NUMBER
  6    )
  7    IS
  8      bonus  NUMBER := 0;
  9    BEGIN
 10      IF sales > (quota + 200) THEN
 11        bonus := (sales - quota)/4;
 12      ELSE
 13        IF sales > quota THEN
 14          bonus := 50;
 15        ELSE
 16          bonus := 0;
 17        END IF;
 18      END IF;
 19   
 20      DBMS_OUTPUT.PUT_LINE('bonus = ' || bonus);
 21   
 22      UPDATE employees
 23      SET salary = salary + bonus 
 24      WHERE employee_id = emp_id;
 25    END p;
 26  BEGIN
 27    p(10100, 10000, 120);
 28    p(10500, 10000, 121);
 29    p(9500, 10000, 122);
 30  END;
 31  /
bonus = 50
bonus = 125
bonus = 0

PL/SQL procedure successfully completed.

SQL>

IF THEN ELSIF Statement

The IF THEN ELSIF statement has this structure:

IF condition_1 THEN
  statements_1
ELSIF condition_2 THEN
  statements_2
[ ELSIF condition_3 THEN
    statements_3
]...
[ ELSE
    else_statements
]
END IF;

The IF THEN ELSIF statement runs the first statements for which condition is true. Remaining conditions are not evaluated. If no condition is true, the else_statements run, if they exist; otherwise, the IF THEN ELSIF statement does nothing.

Example 4-4 IF THEN ELSIF Statement

SQL> 
SQL> DECLARE
  2    PROCEDURE p (sales NUMBER)
  3    IS
  4      bonus  NUMBER := 0;
  5    BEGIN 
  6      IF sales > 50000 THEN
  7        bonus := 1500;
  8      ELSIF sales > 35000 THEN
  9        bonus := 500;
 10      ELSE
 11        bonus := 100;
 12      END IF;
 13   
 14      DBMS_OUTPUT.PUT_LINE (
 15        'Sales = ' || sales || ', bonus = ' || bonus || '.'
 16      );
 17    END p;
 18  BEGIN
 19    p(55000);
 20    p(40000);
 21    p(30000);
 22  END;
 23  /
Sales = 55000, bonus = 1500.
Sales = 40000, bonus = 500.
Sales = 30000, bonus = 100.

PL/SQL procedure successfully completed.

SQL>

A single IF THEN ELSIF statement is easier to understand than a logically equivalent nested IF THEN ELSE statement:

-- IF THEN ELSIF statement

IF condition_1 THEN statements_1;
  ELSIF condition_2 THEN statements_2;
  ELSIF condition_3 THEN statement_3;
END IF;

-- Logically equivalent nested IF THEN ELSE statements

IF condition_1 THEN
  statements_1;
ELSE
  IF condition_2 THEN
    statements_2;
  ELSE
    IF condition_3 THEN
      statements_3;
    END IF;
  END IF;
END IF;

Example 4-5 IF THEN ELSIF Statement Simulates Simple CASE Statement

SQL> 
SQL> DECLARE
  2    grade CHAR(1);
  3  BEGIN
  4    grade := 'B';
  5    
  6    IF grade = 'A' THEN
  7      DBMS_OUTPUT.PUT_LINE('Excellent');
  8    ELSIF grade = 'B' THEN
  9      DBMS_OUTPUT.PUT_LINE('Very Good');
 10    ELSIF grade = 'C' THEN
 11      DBMS_OUTPUT.PUT_LINE('Good');
 12    ELSIF grade = 'D' THEN
 13      DBMS_OUTPUT. PUT_LINE('Fair');
 14    ELSIF grade = 'F' THEN
 15      DBMS_OUTPUT.PUT_LINE('Poor');
 16    ELSE
 17      DBMS_OUTPUT.PUT_LINE('No such grade');
 18    END IF;
 19  END;
 20  /
Very Good

PL/SQL procedure successfully completed.

SQL>

Simple CASE Statement

The simple CASE statement has this structure:

CASE selector
WHEN selector_value_1 THEN statements_1
WHEN selector_value_2 THEN statements_2
...
WHEN selector_value_n THEN statements_n
[ ELSE
  else_statements ]
END CASE;]

Example 4-6 Simple CASE Statement

SQL> 
SQL> 
SQL> DECLARE
  2    grade CHAR(1);
  3  BEGIN
  4    grade := 'B';
  5  
  6    CASE grade
  7      WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
  8      WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
  9      WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
 10      WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
 11      WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
 12      ELSE DBMS_OUTPUT.PUT_LINE('No such grade');
 13    END CASE;
 14  END;
 15  /
Very Good

PL/SQL procedure successfully completed.

SQL>

Searched CASE Statement

The searched CASE statement has this structure:

CASE
WHEN condition_1 THEN statements_1
WHEN condition_2 THEN statements_2
...
WHEN condition_n THEN statements_n
[ ELSE
  else_statements ]
END CASE;]

The searched CASE statement runs the first statements for which condition is true. Remaining conditions are not evaluated. If no condition is true, the CASE statement runs else_statements if they exist and raises the predefined exception CASE_NOT_FOUND otherwise.

Example 4-7 Searched CASE Statement

SQL> 
SQL> DECLARE
  2    grade CHAR(1);
  3  BEGIN
  4    grade := 'B';
  5    
  6    CASE
  7      WHEN grade = 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
  8      WHEN grade = 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
  9      WHEN grade = 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
 10      WHEN grade = 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
 11      WHEN grade = 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
 12      ELSE DBMS_OUTPUT.PUT_LINE('No such grade');
 13    END CASE;
 14  END;
 15  /
Very Good

PL/SQL procedure successfully completed.

SQL>

Example 4-8 EXCEPTION Instead of ELSE Clause in CASE Statement

SQL> DECLARE
  2    grade CHAR(1);
  3  BEGIN
  4    grade := 'B';
  5    
  6    CASE
  7      WHEN grade = 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
  8      WHEN grade = 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
  9      WHEN grade = 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
 10      WHEN grade = 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
 11      WHEN grade = 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
 12    END CASE;
 13  EXCEPTION
 14    WHEN CASE_NOT_FOUND THEN
 15      DBMS_OUTPUT.PUT_LINE('No such grade');
 16  END;
 17  /
Very Good

PL/SQL procedure successfully completed.

SQL>

LOOP Statements

Loop statements run the same statements with a series of different values. The loop statements are:

Basic LOOP

FOR LOOP

Cursor FOR LOOP

WHILE LOOP

The statements that exit a loop are:

EXIT

EXIT WHEN

The statements that exit the current iteration of a loop are:

CONTINUE

CONTINUE WHEN

EXIT, EXIT WHEN, CONTINUE, and CONTINUE WHEN 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

LOOP statements can be labeled, and LOOP statements can be nested. Labels are recommended for nested loops to improve readability. You must ensure that the label in the END LOOP statement matches the label at the beginning of the same loop statement (the compiler does not check).

Basic LOOP Statement

The basic LOOP statement has this structure:

[ label ] LOOP
  statements
END LOOP [ label ];

EXIT Statement

The EXIT 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.

Example 4-9 Basic LOOP Statement with EXIT Statement

SQL> 
SQL> DECLARE
  2    x NUMBER := 0;
  3  BEGIN
  4    LOOP
  5      DBMS_OUTPUT.PUT_LINE ('Inside loop:  x = ' || TO_CHAR(x));
  6      x := x + 1;
  7      IF x > 3 THEN
  8        EXIT;
  9      END IF;
 10    END LOOP;
 11    -- After EXIT, control resumes here
 12    DBMS_OUTPUT.PUT_LINE(' After loop:  x = ' || TO_CHAR(x));
 13  END;
 14  /
Inside loop:  x = 0
Inside loop:  x = 1
Inside loop:  x = 2
Inside loop:  x = 3
After loop:  x = 4

PL/SQL procedure successfully completed.

SQL>

EXIT WHEN Statement

The EXIT WHEN statement exits the current iteration of a loop when the condition in its WHEN clause is true, and transfers control to the end of either the current loop or an enclosing labeled loop.

Each time control reaches the EXIT WHEN statement, the condition in its WHEN clause is evaluated. If the condition is not true, the EXIT WHEN statement does nothing. To prevent an infinite loop, a statement inside the loop must make the condition true

Example 4-10 Basic LOOP Statement with EXIT WHEN Statement

SQL> 
SQL> DECLARE
  2    x NUMBER := 0;
  3  BEGIN
  4    LOOP
  5      DBMS_OUTPUT.PUT_LINE('Inside loop:  x = ' || TO_CHAR(x));
  6      x := x + 1;  -- prevents infinite loop
  7      EXIT WHEN x > 3;
  8    END LOOP;
  9    -- After EXIT statement, control resumes here
 10    DBMS_OUTPUT.PUT_LINE('After loop:  x = ' || TO_CHAR(x));
 11  END;
 12  /
Inside loop:  x = 0
Inside loop:  x = 1
Inside loop:  x = 2
Inside loop:  x = 3
After loop:  x = 4

PL/SQL procedure successfully completed.

SQL>

one basic LOOP statement is nested inside the other, and both have labels. The inner loop has two EXIT WHEN statements; one that exits the inner loop and one that exits the outer loop.

Example 4-11 Nested, Labeled Basic LOOP Statements with EXIT WHEN Statements

SQL> 
SQL> DECLARE
  2    s  PLS_INTEGER := 0;
  3    i  PLS_INTEGER := 0;
  4    j  PLS_INTEGER;
  5  BEGIN
  6    <<outer_loop>>
  7    LOOP
  8      i := i + 1;
  9      j := 0;
 10      <<inner_loop>>
 11      LOOP
 12        j := j + 1;
 13        s := s + i * j; -- Sum several products
 14        EXIT inner_loop WHEN (j > 5);
 15        EXIT outer_loop WHEN ((i * j) > 15);
 16      END LOOP inner_loop;
 17    END LOOP outer_loop;
 18    DBMS_OUTPUT.PUT_LINE
 19      ('The sum of products equals: ' || TO_CHAR(s));
 20  END;
 21  /
The sum of products equals: 166

PL/SQL procedure successfully completed.

SQL>

An EXIT WHEN statement in an inner loop can transfer control to an outer loop only if the outer loop is labeled.

Example 4-12 Nested, Unabeled Basic LOOP Statements with EXIT WHEN Statements

SQL> 
SQL> 
SQL> DECLARE
  2    i PLS_INTEGER := 0;
  3    j PLS_INTEGER := 0;
  4   
  5  BEGIN
  6    LOOP
  7      i := i + 1;
  8      DBMS_OUTPUT.PUT_LINE ('i = ' || i);
  9      
 10      LOOP
 11        j := j + 1;
 12        DBMS_OUTPUT.PUT_LINE ('j = ' || j);
 13        EXIT WHEN (j > 3);
 14      END LOOP;
 15   
 16      DBMS_OUTPUT.PUT_LINE ('Exited inner loop');
 17   
 18      EXIT WHEN (i > 2);
 19    END LOOP;
 20   
 21    DBMS_OUTPUT.PUT_LINE ('Exited outer loop');
 22  END;
 23  /
i = 1
j = 1
j = 2
j = 3
j = 4
Exited inner loop
i = 2
j = 5
Exited inner loop
i = 3
j = 6
Exited inner loop
Exited outer loop

PL/SQL procedure successfully completed.

SQL>

CONTINUE Statement

The CONTINUE 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.

Example 4-13 CONTINUE Statement in Basic LOOP Statement

SQL> 
SQL> DECLARE
  2    x NUMBER := 0;
  3  BEGIN
  4    LOOP -- After CONTINUE statement, control resumes here
  5      DBMS_OUTPUT.PUT_LINE ('Inside loop:  x = ' || TO_CHAR(x));
  6      x := x + 1;
  7      IF x < 3 THEN
  8        CONTINUE;
  9      END IF;
 10      DBMS_OUTPUT.PUT_LINE
 11        ('Inside loop, after CONTINUE:  x = ' || TO_CHAR(x));
 12      EXIT WHEN x = 5;
 13    END LOOP;
 14   
 15    DBMS_OUTPUT.PUT_LINE (' After loop:  x = ' || TO_CHAR(x));
 16  END;
 17  /
Inside loop:  x = 0
Inside loop:  x = 1
Inside loop:  x = 2
Inside loop, after CONTINUE:  x = 3
Inside loop:  x = 3
Inside loop, after CONTINUE:  x = 4
Inside loop:  x = 4
Inside loop, after CONTINUE:  x = 5
After loop:  x = 5

PL/SQL procedure successfully completed.

SQL>

CONTINUE WHEN Statement

The CONTINUE WHEN statement exits the current iteration of a loop when the condition in its WHEN clause is true, and transfers control to the next iteration of either the current loop or an enclosing labeled loop.

Each time control reaches the CONTINUE WHEN statement, the condition in its WHEN clause is evaluated. If the condition is not true, the CONTINUE WHEN statement does nothing.

Example 4-14 CONTINUE WHEN Statement in Basic LOOP Statement

SQL> 
SQL> DECLARE
  2    x NUMBER := 0;
  3  BEGIN
  4    LOOP -- After CONTINUE statement, control resumes here
  5      DBMS_OUTPUT.PUT_LINE ('Inside loop:  x = ' || TO_CHAR(x));
  6      x := x + 1;
  7      CONTINUE WHEN x < 3;
  8      DBMS_OUTPUT.PUT_LINE
  9        ('Inside loop, after CONTINUE:  x = ' || TO_CHAR(x));
 10      EXIT WHEN x = 5;
 11    END LOOP;
 12    DBMS_OUTPUT.PUT_LINE (' After loop:  x = ' || TO_CHAR(x));
 13  END;
 14  /
Inside loop:  x = 0
Inside loop:  x = 1
Inside loop:  x = 2
Inside loop, after CONTINUE:  x = 3
Inside loop:  x = 3
Inside loop, after CONTINUE:  x = 4
Inside loop:  x = 4
Inside loop, after CONTINUE:  x = 5
After loop:  x = 5

PL/SQL procedure successfully completed.

SQL>

Example 4-15 FOR LOOP Statements

SQL> 
SQL> BEGIN
  2    DBMS_OUTPUT.PUT_LINE ('lower_bound < upper_bound');
  3   
  4    FOR i IN 1..3 LOOP
  5      DBMS_OUTPUT.PUT_LINE (i);
  6    END LOOP;
  7   
  8    DBMS_OUTPUT.PUT_LINE ('lower_bound = upper_bound');
  9   
 10    FOR i IN 2..2 LOOP
 11      DBMS_OUTPUT.PUT_LINE (i);
 12    END LOOP;
 13   
 14    DBMS_OUTPUT.PUT_LINE ('lower_bound > upper_bound');
 15   
 16    FOR i IN 3..1 LOOP
 17      DBMS_OUTPUT.PUT_LINE (i);
 18    END LOOP;
 19  END;
 20  /
lower_bound < upper_bound
1
2
3
lower_bound = upper_bound
2
lower_bound > upper_bound

PL/SQL procedure successfully completed.

SQL>

Example 4-16 Reverse FOR LOOP Statements

SQL> 
SQL> BEGIN
  2    DBMS_OUTPUT.PUT_LINE ('upper_bound > lower_bound');
  3   
  4    FOR i IN REVERSE 1..3 LOOP
  5      DBMS_OUTPUT.PUT_LINE (i);
  6    END LOOP;
  7   
  8    DBMS_OUTPUT.PUT_LINE ('upper_bound = lower_bound');
  9   
 10    FOR i IN REVERSE 2..2 LOOP
 11      DBMS_OUTPUT.PUT_LINE (i);
 12    END LOOP;
 13   
 14    DBMS_OUTPUT.PUT_LINE ('upper_bound < lower_bound');
 15   
 16    FOR i IN REVERSE 3..1 LOOP
 17      DBMS_OUTPUT.PUT_LINE (i);
 18    END LOOP;
 19  END;
 20  /
upper_bound > lower_bound
3
2
1
upper_bound = lower_bound
2
upper_bound < lower_bound

PL/SQL procedure successfully completed.

SQL>

In some languages, the FOR LOOP has a STEP clause that lets you specify a loop index increment other than 1. To simulate the STEP clause in PL/SQL, multiply each reference to the loop index by the desired increment.

Example 4-17 Simulating STEP Clause in FOR LOOP Statement

SQL> 
SQL> DECLARE
  2    step  PLS_INTEGER := 5;
  3  BEGIN
  4    FOR i IN 1..3 LOOP
  5      DBMS_OUTPUT.PUT_LINE (i*step);
  6    END LOOP;
  7  END;
  8  /
5
10
15

PL/SQL procedure successfully completed.

SQL>

FOR LOOP Statement

FOR LOOP Index

The index of a FOR LOOP statement is implicitly declared as a variable of type PLS_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 the FOR LOOP statement runs, the index is undefined. (A loop index is sometimes called a loop counter.)

Example 4-18 FOR LOOP Statement Tries to Change Index Value

SQL> 
SQL> BEGIN
  2    FOR i IN 1..3 LOOP
  3      IF i < 3 THEN
  4        DBMS_OUTPUT.PUT_LINE (TO_CHAR(i));
  5      ELSE
  6        i := 2;
  7      END IF;
  8    END LOOP;
  9  END;
 10  /
      i := 2;
      *
ERROR at line 6:
ORA-06550: line 6, column 7:
PLS-00363: expression 'I' cannot be used as an assignment target
ORA-06550: line 6, column 7:
PL/SQL: Statement ignored


SQL>

Example 4-19 Outside Statement References FOR LOOP Statement Index

SQL> BEGIN
  2    FOR i IN 1..3 LOOP
  3      DBMS_OUTPUT.PUT_LINE ('Inside loop, i is ' || TO_CHAR(i));
  4    END LOOP;
  5    
  6    DBMS_OUTPUT.PUT_LINE ('Outside loop, i is ' || TO_CHAR(i));
  7  END;
  8  /
  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 declared
ORA-06550: line 6, column 3:
PL/SQL: Statement ignored


SQL>

If the index of a FOR LOOP statement has the same name as a variable declared in an enclosing block, the local implicit declaration hides the other declaration

Example 4-20 FOR LOOP Statement Index with Same Name as Variable

SQL> 
SQL> DECLARE
  2    i NUMBER := 5;
  3  BEGIN
  4    FOR i IN 1..3 LOOP
  5      DBMS_OUTPUT.PUT_LINE ('Inside loop, i is ' || TO_CHAR(i));
  6    END LOOP;
  7    
  8    DBMS_OUTPUT.PUT_LINE ('Outside loop, i is ' || TO_CHAR(i));
  9  END;
 10  /
Inside loop, i is 1
Inside loop, i is 2
Inside loop, i is 3
Outside loop, i is 5

PL/SQL procedure successfully completed.

SQL>

Example 4-21 FOR LOOP Statement References Variable with Same Name as Index

SQL> 
SQL> <<main>>  -- Label block.
  2  DECLARE
  3    i NUMBER := 5;
  4  BEGIN
  5    FOR i IN 1..3 LOOP
  6      DBMS_OUTPUT.PUT_LINE (
  7        'local: ' || TO_CHAR(i) || ', global: ' ||
  8        TO_CHAR(main.i)  -- Qualify reference with block label.
  9      );
 10    END LOOP;
 11  END main;
 12  /
local: 1, global: 5
local: 2, global: 5
local: 3, global: 5

PL/SQL procedure successfully completed.

SQL>

Example 4-22 Nested FOR LOOP Statements with Same Index Name

SQL> 
SQL> BEGIN
  2    <<outer_loop>>
  3    FOR i IN 1..3 LOOP
  4      <<inner_loop>>
  5      FOR i IN 1..3 LOOP
  6        IF outer_loop.i = 2 THEN
  7          DBMS_OUTPUT.PUT_LINE
  8            ('outer: ' || TO_CHAR(outer_loop.i) || ' inner: '
  9             || TO_CHAR(inner_loop.i));
 10        END IF;
 11      END LOOP inner_loop;
 12    END LOOP outer_loop;
 13  END;
 14  /
outer: 2 inner: 1
outer: 2 inner: 2
outer: 2 inner: 3

PL/SQL procedure successfully completed.

SQL>

Lower Bound and Upper Bound

The lower and upper bounds of a FOR LOOP 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 exception VALUE_ERROR.

Example 4-23 FOR LOOP Statement Bounds

SQL> DECLARE
  2    first  INTEGER := 1;
  3    last   INTEGER := 10;
  4    high   INTEGER := 100;
  5    low    INTEGER := 12;
  6  BEGIN
  7    -- Bounds are numeric literals:
  8    FOR j IN -5..5 LOOP
  9      NULL;
 10    END LOOP;
 11   
 12    -- Bounds are numeric variables:
 13    FOR k IN REVERSE first..last LOOP
 14      NULL;
 15    END LOOP;
 16   
 17   -- Lower bound is numeric literal,
 18   -- Upper bound is numeric expression:
 19    FOR step IN 0..(TRUNC(high/low) * 2) LOOP
 20      NULL;
 21    END LOOP;
 22  END;
 23  /

PL/SQL procedure successfully completed.

SQL>

Example 4-24 Specifying FOR LOOP Statement Bounds at Run Time

EXIT WHEN or CONTINUE WHEN Statement in FOR LOOP Statement

Suppose that you must exit a FOR LOOP statement immediately if a certain condition arises. You can put the condition in an EXIT WHEN statement inside the FOR LOOP statement.

Example 4-25 EXIT WHEN Statement in FOR LOOP Statement

SQL> 
SQL> DECLARE
  2    v_employees employees%ROWTYPE;
  3    CURSOR c1 is SELECT * FROM employees;
  4  BEGIN
  5    OPEN c1;
  6    -- Fetch entire row into v_employees record:
  7    FOR i IN 1..10 LOOP
  8      FETCH c1 INTO v_employees;
  9      EXIT WHEN c1%NOTFOUND;
 10      -- Process data here
 11    END LOOP;
 12    CLOSE c1;
 13  END;
 14  /

PL/SQL procedure successfully completed.

SQL>

Example 4-26 EXIT WHEN Statement in Inner FOR LOOP Statement

SQL> 
SQL> DECLARE
  2    v_employees employees%ROWTYPE;
  3    CURSOR c1 is SELECT * FROM employees;
  4  BEGIN
  5    OPEN c1;
  6    
  7    -- Fetch entire row into v_employees record:
  8    <<outer_loop>>
  9    FOR i IN 1..10 LOOP
 10      -- Process data here
 11      FOR j IN 1..10 LOOP
 12        FETCH c1 INTO v_employees;
 13        EXIT outer_loop WHEN c1%NOTFOUND;
 14        -- Process data here
 15      END LOOP;
 16    END LOOP outer_loop;
 17   
 18    CLOSE c1;
 19  END;
 20  /

PL/SQL procedure successfully completed.

SQL>

Example 4-27 CONTINUE WHEN Statement in Inner FOR LOOP Statement

SQL> 
SQL> DECLARE
  2    v_employees employees%ROWTYPE;
  3    CURSOR c1 is SELECT * FROM employees;
  4  BEGIN
  5    OPEN c1;
  6    
  7    -- Fetch entire row into v_employees record:
  8    <<outer_loop>>
  9    FOR i IN 1..10 LOOP
 10      -- Process data here
 11      FOR j IN 1..10 LOOP
 12        FETCH c1 INTO v_employees;
 13        CONTINUE outer_loop WHEN c1%NOTFOUND;
 14        -- Process data here
 15      END LOOP;
 16    END LOOP outer_loop;
 17   
 18    CLOSE c1;
 19  END;
 20  /

PL/SQL procedure successfully completed.

SQL>

WHILE LOOP Statement

The WHILE LOOP statement runs one or more statements while a condition is true. It has this structure:

[ label ] WHILE condition LOOP
  statements
END LOOP [ label ];

If the condition is true, the statements run and control returns to the top of the loop, where condition is evaluated again. If the condition is not true, control transfers to the statement after the WHILE LOOP statement. To prevent an infinite loop, a statement inside the loop must make the condition false or null

An EXIT, EXIT WHEN, CONTINUE, or CONTINUE WHEN in the statements can cause the loop or the current iteration of the loop to end early.

Example 4-28 WHILE LOOP Statements

SQL> 
SQL> DECLARE
  2    done  BOOLEAN := FALSE;
  3  BEGIN
  4    WHILE done LOOP
  5      DBMS_OUTPUT.PUT_LINE ('This line does not print.');
  6      done := TRUE;  -- This assignment is not made.
  7    END LOOP;
  8  
  9    WHILE NOT done LOOP
 10      DBMS_OUTPUT.PUT_LINE ('Hello, world!');
 11      done := TRUE;
 12    END LOOP;
 13  END;
 14  /
Hello, world!

PL/SQL procedure successfully completed.

SQL>

Sequential Control Statements

Unlike the IF and LOOP statements, the sequential control statements GOTO and NULL are not crucial to PL/SQL programming.

The GOTO statement, which goes to a specified statement, is seldom needed. Occasionally, it simplifies logic enough to warrant its use.

The NULL statement, which does nothing, can improve readability by making the meaning and action of conditional statements clear.

GOTO Statement

The GOTO 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, the GOTO statement transfers control to the labeled statement or block.

Use GOTO statements sparingly—overusing them results in code that is hard to understand and maintain. Do not use a GOTO statement to transfer control from a deeply nested structure to an exception handler. Instead, raise an exception.

Example 4-29 GOTO Statement

SQL> 
SQL> DECLARE
  2    p  VARCHAR2(30);
  3    n  PLS_INTEGER := 37;
  4  BEGIN
  5    FOR j in 2..ROUND(SQRT(n)) LOOP
  6      IF n MOD j = 0 THEN
  7        p := ' is not a prime number';
  8        GOTO print_now;
  9      END IF;
 10    END LOOP;
 11  
 12    p := ' is a prime number';
 13   
 14    <<print_now>>
 15    DBMS_OUTPUT.PUT_LINE(TO_CHAR(n) || p);
 16  END;
 17  /
37 is a prime number

PL/SQL procedure successfully completed.

SQL>

Example 4-30 Incorrect Label Placement

SQL> DECLARE
  2    done  BOOLEAN;
  3  BEGIN
  4    FOR i IN 1..50 LOOP
  5      IF done THEN
  6         GOTO end_loop;
  7      END IF;
  8      <<end_loop>>
  9    END LOOP;
 10  END;
 11  /
  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 raise
return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
continue close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe purge
json_exists json_value json_query json_object json_array


SQL>

Example 4-31 GOTO Statement Goes to Labeled NULL Statement

SQL>  DECLARE
  2    done  BOOLEAN;
  3  BEGIN
  4    FOR i IN 1..50 LOOP
  5      IF done THEN
  6        GOTO end_loop;
  7      END IF;
  8      <<end_loop>>
  9      NULL;
 10    END LOOP;
 11  END;
 12  /

PL/SQL procedure successfully completed.

SQL>

Example 4-32 GOTO Statement Transfers Control to Enclosing Block

SQL> 
SQL> DECLARE
  2    v_last_name  VARCHAR2(25);
  3    v_emp_id     NUMBER(6) := 120;
  4  BEGIN
  5    <<get_name>>
  6    SELECT last_name INTO v_last_name
  7    FROM employees
  8    WHERE employee_id = v_emp_id;
  9    
 10    BEGIN
 11      DBMS_OUTPUT.PUT_LINE (v_last_name);
 12      v_emp_id := v_emp_id + 5;
 13   
 14      IF v_emp_id < 120 THEN
 15        GOTO get_name;
 16      END IF;
 17    END;
 18  END;
 19  /
Weiss

PL/SQL procedure successfully completed.

SQL>

Example 4-33 GOTO Statement Cannot Transfer Control into IF Statement

SQL> 
SQL> DECLARE
  2    valid BOOLEAN := TRUE;
  3  BEGIN
  4    GOTO update_row;
  5    
  6    IF valid THEN
  7    <<update_row>>
  8      NULL;
  9    END IF;
 10  END;
 11  /
  GOTO update_row;
  *
ERROR at line 4:
ORA-06550: line 4, column 3:
PLS-00375: illegal GOTO statement; this GOTO cannot branch to label 'UPDATE_ROW'
ORA-06550: line 6, column 12:
PL/SQL: Statement ignored


SQL>

NULL Statement

The NULL statement only passes control to the next statement. Some languages refer to such an instruction as a no-op (no operation).

Some uses for the NULL statement are:

To provide a target for a GOTO statement

To improve readability by making the meaning and action of conditional statements clear.

To create placeholders and stub subprograms,

To show that you are aware of a possibility, but that no action is necessary,

Example 4-34 NULL Statement Showing No Action

SQL> 
SQL> DECLARE
  2    v_job_id  VARCHAR2(10);
  3     v_emp_id  NUMBER(6) := 110;
  4  BEGIN
  5    SELECT job_id INTO v_job_id
  6    FROM employees
  7    WHERE employee_id = v_emp_id;
  8    
  9    IF v_job_id = 'SA_REP' THEN
 10      UPDATE employees
 11      SET commission_pct = commission_pct * 1.2;
 12    ELSE
 13      NULL;  -- Employee is not a sales rep
 14    END IF;
 15  END;
 16  /

PL/SQL procedure successfully completed.

SQL>

Example 4-35 NULL Statement as Placeholder During Subprogram Creation

SQL> CREATE OR REPLACE PROCEDURE award_bonus (
  2    emp_id NUMBER,
  3    bonus NUMBER
  4  ) AS
  5  BEGIN    -- Executable part starts here
  6    NULL;  -- Placeholder
  7    -- (raises "unreachable code" if warnings enabled)
  8  END award_bonus;
  9  /

Procedure created.

SQL>

Example 4-36 NULL Statement in ELSE Clause of Simple CASE Statement

SQL> 
SQL> CREATE OR REPLACE PROCEDURE print_grade (
  2    grade CHAR
  3  ) AUTHID DEFINER AS
  4  BEGIN
  5    CASE grade
  6      WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
  7      WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
  8      WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
  9      WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
 10      WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
 11      ELSE NULL;
 12    END CASE;
 13  END;
 14  /

Procedure created.

SQL>