4 PL/SQL Control Statements Part 1
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