Flow Control Constructs

Flow Control Constructs include the IF, CASE, LOOP, WHILE, ITERATE, REPEAT and LEAVE constructs. They are fully implemented.
These constructs can contain single statement or a block of statements using with BEGIN?..END statement. And these constructs can be nested also.

IF Statement

The general syntax of IF Statement is :
IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list] ...[ELSE statement_list] END IF

IF statement implements a basic conditional construct. When search_condition is true then only corresponding SQL statement_list is executed but if it is false then the ELSE clause statement_list is executed. statement_list can consists one or more statements. Example :

mysql> delimiter //
mysql> CREATE PROCEDURE IFProc(IN p INT)
    -> IF(p<7)
    -> THEN
    -> SELECT * FROM Client;
    -> ELSE
    -> SELECT * FROM Products;
    -> END IF
    -> //
Query OK, 0 rows affected (0.31 sec)

mysql> delimiter ;
mysql> CALL IFProc(5);
+------+---------------+----------+
| C_ID | Name          | City     |
+------+---------------+----------+
| 1    | A K Ltd       | Delhi    |
| 2    | V K Associate | Mumbai   |
| 3    | R K India     | Banglore |
| 4    | R S P Ltd     | Kolkata  |
| 5    | A T Ltd       | Delhi    |
| 6    | D T Info      | Delhi    |
+------+---------------+----------+
6 rows in set (0.09 sec)
Query OK, 0 rows affected (0.11 sec)

mysql> CALL IFProc(8);
+---------+-------------+------+----------+
| Prod_ID | Prod_Detail | C_ID | price    |
+---------+-------------+------+----------+
| 111     | Monitor     | 1    | 7000.00  |
| 112     | Processor   | 2    | 11000.00 |
| 113     | Keyboard    | 2    | 1200.00  |
| 114     | Mouse       | 3    | 500.00   |
| 115     | CPU         | 5    | 15500.00 |
+---------+-------------+------+----------+
5 rows in set (0.09 sec)
Query OK, 0 rows affected (0.11 sec)

Example for Using IF Statement in Select Clause.

mysql> SELECT Name AS NAME, City AS CITY,
    -> IF(City<>'Mumbai',"Software","Bollywood") AS Profession
    -> FROM Client;
+---------------+----------+------------+
| NAME          | CITY     | Profession |
+---------------+----------+------------+
| A K Ltd       | Delhi    | Software   |
| V K Associate | Mumbai   | Bollywood  |
| R K India     | Banglore | Software   |
| R S P Ltd     | Kolkata  | Software   |
| A T Ltd       | Delhi    | Software   |
| D T Info      | Delhi    | Software   |
+---------------+----------+------------+
6 rows in set (0.02 sec)

CASE Statement

The general syntax of CASE Statement is :
CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list] ... [ELSE statement_list] END CASE
Or
CASE WHEN search_condition THEN statement_list [WHEN search_condition THEN statement_list] ... [ELSE statement_list] END CASE

The first syntax execute the statement_list when the case_value=when_value. If there is no matching case_value with when_value then it execute the ELSE clause statement_list. And in the second syntax, if search_condition is true then only corresponding SQL statement_list is execute but if it is false then the ELSE clause statement_list is executed. Example :

mysql> delimiter //
mysql> CREATE PROCEDURE WHENProc(IN p INT)
    -> CASE p*10
    -> WHEN 20 THEN SELECT * FROM Products WHERE Price<=7000;
    -> WHEN 30 THEN SELECT * FROM Products WHERE Price>=7000;
    -> ELSE
    -> SELECT * FROM Products;
    -> END CASE
    -> //
Query OK, 0 rows affected (0.03 sec)

mysql> delimiter ;
mysql> CALL WHENProc(2);
+---------+-------------+------+---------+
| Prod_ID | Prod_Detail | C_ID | price   |
+---------+-------------+------+---------+
| 111     | Monitor     | 1    | 7000.00 |
| 113     | Keyboard    | 2    | 1200.00 |
| 114     | Mouse       | 3    | 500.00  |
+---------+-------------+------+---------+
3 rows in set (0.03 sec)
Query OK, 0 rows affected (0.05 sec)

mysql> CALL WHENProc(3);
+---------+-------------+------+----------+
| Prod_ID | Prod_Detail | C_ID | price    |
+---------+-------------+------+----------+
| 111     | Monitor     | 1    | 7000.00  |
| 112     | Processor   | 2    | 11000.00 |
| 115     | CPU         | 5    | 15500.00 |
+---------+-------------+------+----------+
3 rows in set (0.01 sec)
Query OK, 0 rows affected (0.02 sec)

mysql> CALL WHENProc(1);
+---------+-------------+------+----------+
| Prod_ID | Prod_Detail | C_ID | price    |
+---------+-------------+------+----------+
| 111     | Monitor     | 1    | 7000.00  |
| 112     | Processor   | 2    | 11000.00 |
| 113     | Keyboard    | 2    | 1200.00  |
| 114     | Mouse       | 3    | 500.00   |
| 115     | CPU         | 5    | 15500.00 |
+---------+-------------+------+----------+
5 rows in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)

LOOP Statement

The general syntax of LOOP Statement is :
[begin_label:] LOOP statement_list END LOOP [end_label]

LOOP Statement implements a simple loop construct. This statement is used to repeat execution of the statement_list, statement_list can contain one or more than one statements. These statements can repeat the execution until the loop is exited and usually that can be done with a LEAVE Statement. The LOOP Statement can be labeled also.

LEAVE Statement

The general syntax of LEAVE Statement is :
LEAVE label

The LEAVE Statement is used to exit from any flow control constructs.

In the following example we are describing you a both LOOP and LEAVE Statement.

mysql> delimiter //
mysql> CREATE PROCEDURE LProc()
    -> BEGIN
    -> DECLARE p INT;
    -> SET p=1;
    -> lbl: LOOP
    -> SELECT * FROM Client WHERE C_ID=p;
    -> SET p=p+1;
    -> IF p > 5
    -> THEN LEAVE lbl;
    -> END IF;
    -> END LOOP;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> CALL LProc();
+------+---------+-------+
| C_ID | Name    | City  |
+------+---------+-------+
| 1    | A K Ltd | Delhi |
+------+---------+-------+
1 row in set (0.00 sec)

+------+---------------+--------+
| C_ID | Name          | City   |
+------+---------------+--------+
| 2    | V K Associate | Mumbai |
+------+---------------+--------+
1 row in set (0.01 sec)

+------+-----------+----------+
| C_ID | Name      | City     |
+------+-----------+----------+
| 3    | R K India | Banglore |
+------+-----------+----------+
1 row in set (0.02 sec)

+------+-----------+---------+
| C_ID | Name      | City    |
+------+-----------+---------+
| 4    | R S P Ltd | Kolkata |
+------+-----------+---------+
1 row in set (0.03 sec)

+------+---------+-------+
| C_ID | Name    | City  |
+------+---------+-------+
| 5    | A T Ltd | Delhi |
+------+---------+-------+
1 row in set (0.04 sec)

Query OK, 0 rows affected (0.04 sec)

ITERATE Statement

The general syntax of ITERATE Statement is:
ITERATE label

ITERATE Statement can appear only within REPEAT, LOOP and WHILE Statements. ITERATE is used to iterate (Continue) the loop again. Example :

mysql> delimiter //
mysql> CREATE PROCEDURE ITERProc()
    -> BEGIN
    -> DECLARE p INT;
    -> SET p=1;
    -> lbl: LOOP
    -> SET p=p+1;
    -> IF p<5
    -> THEN ITERATE lbl;
    -> END IF;
    -> SELECT * FROM Client;
    -> LEAVE lbl;
    -> END LOOP lbl;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> CALL ITERProc();
+------+---------------+----------+
| C_ID | Name          | City     |
+------+---------------+----------+
| 1    | A K Ltd       | Delhi    |
| 2    | V K Associate | Mumbai   |
| 3    | R K India     | Banglore |
| 4    | R S P Ltd     | Kolkata  |
| 5    | A T Ltd       | Delhi    |
| 6    | D T Info      | Delhi    |
+------+---------------+----------+
6 rows in set (0.01 sec)
Query OK, 0 rows affected (0.02 sec)

REPEAT Statement

The general syntax of REPEAT Statement is:
[begin_label:] REPEAT statement_list UNTIL search_condition END REPEAT [end_label]

Statement_list contains the one or more statements. REPEAT Statement is used to repeat the statement_list until the search_condition evaluates true. The REPEAT Statement can be labeled also. Example :

mysql> delimiter //
mysql> CREATE PROCEDURE REProc()
    -> BEGIN
    -> DECLARE p INT;
    -> SET p=1;
    -> REPEAT
    -> SELECT * FROM Products WHERE C_ID=p;
    -> SET p=p+1;
    -> UNTIL p > 5
    -> END REPEAT;
    -> END
    -> //
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> CALL REProc();
+---------+-------------+------+---------+
| Prod_ID | Prod_Detail | C_ID | price   |
+---------+-------------+------+---------+
| 111     | Monitor     | 1    | 7000.00 |
+---------+-------------+------+---------+
1 row in set (0.01 sec)

+---------+-------------+------+----------+
| Prod_ID | Prod_Detail | C_ID | price    |
+---------+-------------+------+----------+
| 112     | Processor   | 2    | 11000.00 |
| 113     | Keyboard    | 2    | 1200.00  |
+---------+-------------+------+----------+
2 rows in set (0.02 sec)

+---------+-------------+------+--------+
| Prod_ID | Prod_Detail | C_ID | price  |
+---------+-------------+------+--------+
| 114     | Mouse       | 3    | 500.00 |
+---------+-------------+------+--------+
1 row in set (0.03 sec)

Empty set (0.04 sec)

+---------+-------------+------+----------+
| Prod_ID | Prod_Detail | C_ID | price    |
+---------+-------------+------+----------+
| 115     | CPU         | 5    | 15500.00 |
+---------+-------------+------+----------+
1 row in set (0.04 sec)
Query OK, 0 rows affected (0.05 sec)

WHILE Statement

The general syntax of WHILE Statement is:
[begin_label:] WHILE search_condition DO statement_list END WHILE [end_label]

The WHILE Statement repeats the statement_list until the search_condition evaluates true. The WHILE Statement can be labeled also. Example :

mysql> delimiter //
mysql> CREATE PROCEDURE WHILProc()
    -> BEGIN
    -> DECLARE p INT;
    -> SET p=1;
    -> WHILE p <= 5 DO
    -> UPDATE Products
    -> SET Price=Price*1.03 WHERE C_ID=p;
    -> SET p=p+1;
    -> IF p=4
    -> THEN
    -> SET p=p+1;
    -> END IF;
    -> END WHILE;
    -> SELECT * FROM Products;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> CALL WHILProc();
+---------+-------------+------+----------+
| Prod_ID | Prod_Detail | C_ID | price    |
+---------+-------------+------+----------+
| 111     | Monitor     | 1    | 7426.30  |
| 112     | Processor   | 2    | 11669.90 |
| 113     | Keyboard    | 2    | 1273.08  |
| 114     | Mouse       | 3    | 530.45   |
| 115     | CPU         | 5    | 16443.95 |
+---------+-------------+------+----------+
5 rows in set (0.10 sec)
Query OK, 0 rows affected (0.12 sec)

Flow Control Constructs

Flow Control Constructs

Flow Control Constructs

Tutorials