PL/SQL has three categories of control statements:
(1) Conditional selection statements, which run different statements for different data values.(IF & CASE).
(2) Loop statements,which run the same statements with a series of different data values.(the basic LOOP,FOR LOOP, and WHILE LOOP).
(3) Sequential control statements,which are not crucial to PL/SQL programming. (GOTO)
Conditional Selection Statements
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
IF THEN Statement
The IF THEN statement has this structure:
IF condition THEN
statements
END IF;
In Example 4–1, the statements between THEN and END IF run if and only if the value of sales is greater than quota+200.
Example 4-1 IF THEN Statement
DECLARE
PROCEDURE p
(sales NUMBER,
quota NUMBER,
emp_id NUMBER
)
IS
bonus NUMBER := 0;
updated VARCHAR2(3) := 'No';
BEGIN
IF 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;
/
IF THEN ELSE Statement
The IF THEN ELSE statement has this structure:
IF condition THEN
statements
ELSE
else_statements
END IF;
In Example 4–2, the statement between THEN and ELSE runs if and only if the value of sales is greater than quota+200; otherwise, the statement between ELSE and END IF runs.
Example 4-2 IF THEN ELSE Statement
DECLARE
PROCEDURE P(SALES NUMBER, QUOTA NUMBER, EMP_ID NUMBER) IS
BONUS NUMBER := 0;
BEGIN
IF 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;
/
Example 4-3 Nested IF THEN ELSE Statements
DECLARE
PROCEDURE p (
sales NUMBER,
quota NUMBER,
emp_id NUMBER)
IS
bonus NUMBER := 0;
BEGIN
IF sales > (quota + 200) THEN
bonus := (sales - quota)/4;
ELSE
IF 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;
/
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;
Example 4-4 IF THEN ELSIF Statement
DECLARE
PROCEDURE P(sales NUMBER) IS
bonus NUMBER := 0;
BEGIN
IF 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;
/
Example 4–5 IF THEN ELSIF Statement Simulates Simple CASE Statement
Simple CASE Statement
Example 4–6 Simple CASE Statement
Searched CASE Statement
Example 4–7 Searched CASE Statement
Example 4–8 EXCEPTION Instead of ELSE Clause in CASE Statement
LOOP Statements
Loop statements run the same statements with a series of different values. The loop statement 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
Basic LOOP Statement
The basic LOOP statement has this structure:
[ label ] LOOP
statements
END LOOP [ label ];
EXIT Statement
Example 4-9 Basic LOOP Statement with EXIT Statement
EXIT WHEN Statement
Example 4–10 Basic LOOP Statement with EXIT WHEN Statement
Example 4–11 Nested, Labeled Basic LOOP Statements with EXIT WHEN Statements
Example 4–12 Nested, Unabeled Basic LOOP Statements with EXIT WHEN Statements
CONTINUE Statement
Example 4–13 CONTINUE Statement in Basic LOOP Statement
CONTINUE WHEN Statement
Example 4–14 CONTINUE WHEN Statement in Basic LOOP Statement
FOR LOOP Statement
The FOR LOOP statement runs one or more statements while the loop index is in a specified range. The statement has this structrure:
[label] FOR index IN [ REVERSE ] lower_bound..upper_bound LOOP
statements
END LOOP [label];
Example 4-15 FOR LOOP Statements
Example 4-16 Reverse FOR LOOP Statements
Example 4 - 17 Simulating STEP Clause in 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
Example 4–19 Outside Statement References FOR LOOP Statement Index
Example 4–20 FOR LOOP Statement Index with Same Name as Variable
Example 4–22 Nested FOR LOOP Statements with Same Index Name
WHILE LOOP Statement
The WHILE LOOP statement runs one or more statements while condition is true.It has this structure:
[ label ] WHILE condition LOOP
statements
END LOOP [ label ];
SequentiaL Control Statements
Unlike the IF and LOOP statements , the sequential control statements GOTO and NULL
are not crucial to PL/SQL programming.
GOTO Statement
The GOTO statement transfers control to a label unconditionally. The label must be unqiue 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.
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;
/
Example 4–30 Incorrect Label Placement
Example 4–31 GOTO Statement Goes to Labeled NULL Statement
Example 4–32 GOTO Statement Transfers Control to Enclosing Block
Example 4–33 GOTO Statement Cannot Transfer Control into IF Statement
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).
Example 4–34 NULL Statement Showing No Action
Example 4–35 NULL Statement as Placeholder During Subprogram Creation
Example 4–36 NULL Statement in ELSE Clause of Simple CASE Statement