Home Mysql Mysql5 Flow Control Constructs

Questions:Ask|Latest

Flow Control Constructs
Posted on: March 13, 2008 By Deepak Kumar

Flow Control Constructs include the IF, CASE, LOOP, WHILE, ITERATE, REPEAT and LEAVE constructs. They are fully implemented.

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)

«Previous Index Next»

Recommend the tutorial

Ask Questions? Discuss: Flow Control Constructs

Post your Comment

Comments

young
June 8, 2012
c++

i like it

Latest
Related
Frameworks
Category