id="ifstmt">IF statement

The IF statement executes different sets of SQL statements based on the result of search conditions.

 

Syntax

>>-+--------+--IF--search-condition--THEN----------------------->
   '-label:-'
 
   .-----------------------------.
   V                             |
>----SQL-procedure-statement-- ;-+------------------------------>
 
   .---------------------------------------------------------------------.
   V                                                                     |
>----+-----------------------------------------------------------------+-+-->
     |                                 .-----------------------------. |
     |                                 V                             | |
     '-ELSEIF--search-condition--THEN----SQL-procedure-statement-- ;-+-'
 
>--+---------------------------------------+--END IF-----------><
   |       .-----------------------------. |
   |       V                             | |
   '-ELSE----SQL-procedure-statement-- ;-+-'
 

 

Description

label

Specifies the label for the IF statement. The label name cannot be the same as the routine name or another label within the same scope. For more information, see Labels.

search-condition

Specifies the search-condition for which an SQL statement should be executed. If the condition is unknown or false, processing continues to the next search condition, until either a condition is true or processing reaches the ELSE clause.

SQL-procedure-statement

Specifies an SQL statement that should be executed if the preceding search-condition is true.

 

Example

The following SQL procedure accepts two IN parameters: an employee number and an employee rating. Depending on the value of rating, the employee table is updated with new values in the salary and bonus columns.

CREATE PROCEDURE UPDATE_SALARY_IF    (IN employee_number CHAR(6), INOUT rating SMALLINT)
   LANGUAGE SQL
   MODIFIES SQL DATA
   BEGIN
      DECLARE not_found CONDITION FOR SQLSTATE '02000';
      DECLARE EXIT HANDLER FOR not_found          SET rating = -1;
      IF rating = 1
         THEN UPDATE employee          SET salary = salary * 1.10, bonus = 1000
         WHERE empno = employee_number;
      ELSEIF rating = 2
         THEN UPDATE employee          SET salary = salary * 1.05, bonus = 500
         WHERE empno = employee_number;
      ELSE UPDATE employee          SET salary = salary * 1.03, bonus = 0
         WHERE empno = employee_number;
      END IF;
   END 

---

[ Top of Page | Previous Page | Next Page | Contents |
Index ]