Control Structures  «Prev  Next»

Identifying PL/SQL Control Structures

  1. IF-ELSIF-ELSE: This statement allows you to control structure to execute a sequence of statements conditionally.
  2. LOOP-END LOOP: This statement allows you to execute a sequence of statements multiple times.
  3. EXIT: This statement allows you to exit from a loop immediately.
  4. EXIT-WHEN: This statement allows you to exit a loop when the given condition is met.
  5. WHILE-LOOP: This statement allows you to execute a sequence of statements while the given condition is TRUE.
  6. FOR-LOOP: This statement allows you to execute a sequence of statements for the given number of times.

Searched CASE Expression

A searched CASE expression lets you test different conditions instead of comparing a single expression to various values. It has the form shown in Example 4-1. A searched CASE expression has no selector. Each WHEN clause contains a search condition that yields a BOOLEAN value, so you can test different variables or multiple conditions in a single WHEN clause.

Example 4 - 1 Using a Search Condition with a CASE Statement

SQL> DECLARE
2 grade CHAR(1) := 'B';
3 appraisal VARCHAR2(120);						
4 id NUMBER := 8429862;
5 attendance NUMBER := 150;
6 min_days CONSTANT NUMBER := 200;
7
8 FUNCTION attends_this_school (id NUMBER)
9 RETURN BOOLEAN IS
10 BEGIN
11 RETURN TRUE;
12 END;
13
14 BEGIN
15 appraisal :=
16 CASE
17 WHEN attends_this_school(id) = FALSE
18 THEN 'Student not enrolled'
19 WHEN grade = 'F' OR attendance < min_days
20 THEN 'Poor (poor performance or bad attendance)'
21 WHEN grade = 'A' THEN 'Excellent'
22 WHEN grade = 'B' THEN 'Very Good'
23 WHEN grade = 'C' THEN 'Good'
24 WHEN grade = 'D' THEN 'Fair'
25 ELSE 'No such grade'
26 END;
27 DBMS_OUTPUT.PUT_LINE
28 ('Result for student ' || id || ' is ' || appraisal);
29 END;
30 /
Result for student 8429862 is Poor (poor performance or bad attendance)
PL/SQL procedure successfully completed.
SQL>