- Compound Statements
- Using Labels
- Conditional Statements
- Looping Statements
- Transfer of Control Statements
- Chapter Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
3.3 Conditional Statements
Conditional statements allow stored procedures to make decisions. They are used to define multiple branches of execution based on whether or not a condition was met.
A commonly used conditional statement is the IF statement, where a branch of execution can be taken if a specific condition is satisfied. IF statements can also define a branch of execution for when a condition is not met.
Another conditional statement in SQL PL is the CASE statement, which is similar to an IF statement, but the branching decision can be based on the value of a single variable.
3.3.1 The IF Statement
The most commonly used approach for conditional execution is the IF statement. There are essentially three different types of IF statements.
The simplest form of the IF statement does something if a condition is true, and nothing otherwise.
But what happens if you want to do one thing if a condition is true and something else if it is false? This is where the ELSE clause comes in handy. When used in conjunction with an IF statement, you can do something IF a condition is true and something ELSE if the condition is false.
Thirdly, ELSEIF is used to branch to multiple code paths based on mutually exclusive conditions in the same manner as an IF statement. You can make use of an ELSEIF statement to rewrite a ladder of nested IF ... ELSE statements for readability. Your procedure can specify an unlimited number of ELSEIF statements.
The syntax of an IF statement is depicted in Figure 3.7.
){kind=link}
Figure 3.7 IF Syntax Diagram
>>-IF--search-condition--THEN----------------------------------->
.-------------------------------.
V |
>--------SQL-procedure-statement--;---+------------------------->
.--------------------------------------------------------------------------.
V |
>--------+--------------------------------------------------------------------+--+>
| .-------------------------------. |
| V | |
'-ELSEIF--search-condition--THEN-----SQL-procedure-statement--;---+--'
>-----+------------------------------------------+--END IF-----><
| .-------------------------------. |
| V | |
'-ELSE-----SQL-procedure-statement--;---+--'
The search-condition specifies the condition for which an SQL statement should be invoked. If the condition is false, processing continues to the next search-condition, until either a condition is true or processing reaches the ELSE clause.
SQL-procedure-statement specifies the statements to be invoked if the preceding search-condition is true. If no search-condition evaluates to true, then the SQL-procedure-statement following the ELSE keyword is invoked.
The snippet of an SQL procedure shown in Figure 3.8 demonstrates how the rating of an employee determines the raise in salary and bonus that he or she will receive.
){kind=link}
Figure 3.8 IF Statement Example
IF rating = 1 THEN -- (1)
UPDATE employee
SET salary = salary * 1.10, bonus = 1000
WHERE empno = employee_number;
ELSEIF rating = 2 THEN -- (2)
UPDATE employee
SET salary = salary * 1.05, bonus = 500
WHERE empno = employee_number; ELSE
UPDATE employee -- (3)
SET salary = salary * 1.03, bonus = 0
WHERE empno = employee_number; END IF;
SQL PL does not require the use of a compound statement to execute more than one statement in a branch of a conditional statement.
Indent statements within the body of IF, ELSEIF, and ELSE statements in order to improve readability. If there are several levels of nesting, indent code at each level to reflect their level of nesting.
At (1), an employee with a 1 rating can expect a raise of 10% and a bonus of $1000. At (2), an employee with a 2 rating earns a 5% raise with a $500 bonus. At (3), all other employees can expect a 3% pay hike with no bonus.
You are not simply limited to mathematical operators such as equals (=) and greater than (>). You can also use the SQL keywords NOT, AND, and OR to build conditions in your IF statements.
NOTE
An IF or ELSEIF condition must involve an operator. It is not sufficient to merely specify a variable (as can be done in some other programming languages), as SQL PL does not support the notion of a negative value meaning false and a positive value meaning true.
Your stored procedure can also make use of nested IF statements. There is no limit imposed by DB2 on the number of nested levels, though it is best not to get too carried away as it takes away from the readability of your code. Now if that's not enough, you can also nest IF statements inside of loops and loops inside of IF statements. When nesting IFs, a common problem is inadvertently matching an ELSE with the wrong IF. Beware.
3.3.2 The CASE Statement
The CASE statement provides the ability to evaluate a list of options based on the value of a single variable. You would most likely choose to use a CASE statement if you have a large decision tree and all branches depend on the value of the same variable. Otherwise you would be better off using a series of IF, ELSEIF, and ELSE statements. The syntax diagram for the CASE statement is shown in Figure 3.9.
){kind=link}
Figure 3.9 CASE Statement Syntax Diagram
>>-CASE----+-| searched-case-statement-when-clause |-+---------->
'-| simple-case-statement-when-claus |---'
>----END CASE--------------------------------------------------><
simple-case-statement-when-clause
|---expression-------------------------------------------------->
.-------------------------------------------------------------.
| .-------------------------------. |
V V | |
>--------WHEN--expression--THEN-----SQL-procedure-statement--;---+--+>
>-----+------------------------------------------+--------------|
| .-------------------------------. |
| V | |
'-ELSE-----SQL-procedure-statement--;---+--'
searched-case-statement-when-clause
.-------------------------------------------------------------------.
| .-------------------------------. |
V V | |
|------WHEN--search-condition--THEN-----SQL-procedure-statement--;---+--+->
>----+------------------------------------------+---------------|
| .-------------------------------. |
| V | |
'-ELSE-----SQL-procedure-statement--;---+--'
The CASE statement has two general forms: one that uses a simple-case-statement-when-clause, and one that uses a searched-case-statement-when-clause.
In the simple-case-statement-when-clause, the expression prior to the first WHEN keyword is tested for equality with the value of each expression that follows the WHEN keyword. If the expressions result in the same value, the SQL procedure-statement following the THEN keyword is executed. Otherwise, comparisons are continued between the first expression and the expression following the next WHEN clause. If the result does not match any of the search conditions, and an ELSE clause is present, the statements in the ELSE clause are processed.
In a searched-case-statement-when-clause, the search-condition following each WHEN keyword is evaluated. If search-condition evaluates to true, the statements in the associated THEN clause are processed. If it evaluates to false, the next search-condition is evaluated. If no search-condition evaluates to true and an ELSE clause is present, the statements in the ELSE clause are processed.
Both forms of the CASE statement require END CASE to denote the end of the statement. It is possible to use a CASE statement without an ELSE clause. However, if none of the conditions specified in the WHEN clause are true at run-time, an error will result(SQLSTATE 20000).
The example that you have already seen in Figure 3.8 could be rewritten as shown in Figure 3.10 using the simple-case-statement-when-clause.
){kind=link}
Figure 3.10 Simple CASE Example
CASE rating
WHEN 1 THEN -- (1) Note: WHEN argument is a value
UPDATE EMPLOYEE
SET SALARY = SALARY *1.10, BONUS = 1000
WHERE EMPNO = employee_num;
WHEN 2 THEN -- (2)
UPDATE EMPLOYEE
SET SALARY = SALARY *1.05, BONUS = 500
WHERE EMPNO = employee_num;
ELSE
UPDATE EMPLOYEE -- (3)
SET SALARY = SALARY *1.03, BONUS = 0
WHERE EMPNO = employee_num;
END CASE;
Once again, at (1), an employee with a rating of 1 can expect a raise of 10% and a bonus of $1000. At (2), an employee with a rating of 2 earns a 5% raise and a bonus of $500, while at (3), all other employees can simply expect a raise of 3% and no bonus.
Perhaps there have been some recent changes to the rating system, and there is now a wider range of ratings that employees can receive. Now, two employees with slightly different ratings can earn the same raise and bonus. Obviously, our code needs to be updated.
Figure 3.11 reflects the changes to the rating system and shows how to handle this using a searched-case-statement-when-clause. Note that the WHEN clause now contains a condition.
){kind=link}
Figure 3.11 Searched CASE Example
CASE
WHEN rating >= 1 AND rating < 4 THEN -- (1)
UPDATE EMPLOYEE
SET SALARY = SALARY *1.10, BONUS = 1000
WHERE EMPNO = employee_num;
WHEN rating >= 4 AND rating < 8 THEN
UPDATE EMPLOYEE
SET SALARY = SALARY *1.05, BONUS = 500
WHERE EMPNO = employee_num;
ELSE
UPDATE EMPLOYEE
SET SALARY = SALARY *1.03, BONUS = 0
WHERE EMPNO = employee_num;
END CASE;
As you can see, our code now handles a range of ratings for each condition of the CASE statement. For example, at (1), an employee with a rating that falls between 1 and 3 inclusive will receive a raise of 10% and a bonus of $1000.