Control Structure: This chapter shows you how to structure the flow of control through a PL/SQL program. There are three types of Control structure available in PL/SQL, These are Conditional constructs with the IF statements, CASE expression and LOOP control structure.
IF Statements: In Oracle, the IF-THEN-ELSE statement is used to execute code when a condition is TRUE, or execute different code if the condition evaluates to FALSE. It allows PL/SQL to perform actions selectively based on conditions.
Syntax:
IF condition THEN statements; [ELSIF condition THEN statements;] [ELSE statements;] END IF;
Example:
DECLARE
age number := 40;
BEGIN
IF age < 11
THEN DBMS_OUTPUT.PUT_LINE ('I am a child ');
ELSIF age < 20
THEN DBMS_OUTPUT.PUT_LINE ('I am young ');
ELSE
THEN DBMS_OUTPUT.PUT_LINE ('I always young');
END IF;
END;
Note: ELSIF and ELSE are optional in an IF statements. You can have any number of ELSIF keyword but only one ELSE keyword in your IF statements. END IF makes the end of an IF statements and must be terminated by a semicolon.
CASE Expression: A CASE expression a results based on one or more alternatives. To return the result, the CASE expression uses a selector. The selector is followed by one or more WHEN clause that are checked sequentially. If the value of the selector equals the value of WHEN clause expression, that WHEN clause is executed & that results is returned.
Syntax:
CASE selector WHEN expression1 THEN result1 WHEN expression2 THEN result2 .... WHEN expressionN THEN resultN [ELSE resultN+1] END;
Example:
DECLARE
grade VARCHAR2(1) := UPPER('&Grade');
appraisal VARCHAR2(20);
BEGIN
appraisal :=
CASE
WHEN grade = 'A' THEN 'Excellent'
WHEN grade = 'B' THEN 'Good'
WHEN grade IN ('C', 'D') THEN 'Fair'
ELSE 'No such relusts'
END;
DBMS_OUTPUT.PUT_LINE ('Grade: '|| grade ||' appraisal '||appraisal);
END;
CASE Expression & CASE Statement: A case expression evaluates the condition and returns a value. On the other hand, a CASE statements evaluates the condition and perform an actions. A CASE statements can be complete PL/SQL block. CASE statements end with END CASE; but CASE expression end with END.
Example of CASE Statements:
DECLARE
did NUMBER;
dname VARCHAR2(20);
emp NUMBER;
mid NUMBER := 108;
BEGIN
CASE mid
WHEN 108 THEN
SELECT department_id, department_name
INTO did, dname FROM departments
WHERE manager_id = 108;
SELECT count(*) INTO emp FROM employees
WHERE department_id = did;
WHEN 200 THEN
.........
END CASE;
DBMS_OUTPUT.PUT_LINE ('You are working in the ' || dname ||
' department. There are '|| emp || ' employees in this department');
END;
LOOP Statements: Loops are mainly used to execute statements repeatedly until an exit condition are reached. It is mandatory to have an exit condition in a loop, otherwise the loop is infinite. There are three loop types…
- BASIC Loop
- FOR Loop
- WHILE Loop
Basic Loop: A basic loop allows execution of its statements at least once, even if the condition has been met upon entering the loop. Example are…
DECLARE
star VARCHAR2(20);
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE (star);
star := star||'*';
EXIT WHEN LENGTH(star) > 5;
END LOOP;
END;
WHILE Loop: Use the WHILE loop to repeat statements while a condition is TRUE. Example are…
DECLARE
star VARCHAR2(20);
BEGIN
star := '*';
WHILE LENGTH(star) < 6 LOOP
DBMS_OUTPUT.PUT_LINE (star);
star := star||'*';
END LOOP;
END;
FOR Loop: Use FOR loop to shortcut the test for the number of iteration. Do not declare the counter, it is declared implicitly by lower_bound ..upper_bound syntax. Example are…
DECLARE
star VARCHAR2(20);
BEGIN
FOR i IN 0..5 LOOP
DBMS_OUTPUT.PUT_LINE (star);
star := star||'*';
END LOOP;
END;
continue…..