RelationalDBDesign 





Programming PL/SQL  «Prev  Next»
Lesson 1

Introduction to Writing Control Structures in PL/SQL

Now that you know how to build your first PL/SQL block, let us learn how to write control structures.
Control structures allow you to program your logic based on a certain condition or while a particular condition is satisfied.
In this module, you will learn about conditional control statements such as IF-THEN, as well as iterative control statements such as LOOP and WHILE-LOOP.

Module objectives

By the end of this module, you will know how to:
  1. Build logic by using the IF-THEN-ELSE and ELSIF statements
  2. Determine how to begin and end a simple loop in a PL/SQL block
  3. Observe the structure of FOR and WHILE loops
  4. Define uses for labels and GOTO statements
In the next lesson, we will begin by building logic using IF-THEN-ELSE statements.


Simple CASE Expression

A simple CASE expression selects a result from one or more alternatives, and returns the result. Although it contains a block that might stretch over several lines, it really is an expression that forms part of a larger statement, such as an assignment or a subprogram call. The CASE expression uses a selector, an expression whose value determines which alternative to return.
A CASE expression has the form illustrated in Example 4 show below. The selector (grade) is followed by one or more WHEN clauses, which are checked sequentially. The value of the selector determines which clause is evaluated. The first WHEN clause that matches the value of the selector determines the result value, and subsequent WHEN clauses are not evaluated. If there are no matches, then the optional ELSE clause is performed.

Example 4 : Using the WHEN Clause with a CASE Statement

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