Flow Control Constructs

Flow Control Membangun meliputi IF, CASE, LOOP, WHILE, ITERATE, REPEAT and LEAVE konstruksi. Mereka adalah sepenuhnya dilaksanakan.
Konstruksi ini dapat berisi pernyataan tunggal atau suatu blok statemen menggunakan dengan BEGIN ... .. pernyataan END. Dan konstruksi ini dapat diulang juga.

IF Pernyataan

Sintaks umum IF Pernyataan:
 IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list] ...[ELSE statement_list] END IF

IF pernyataan bersyarat menerapkan dasar membangun. Ketika search_condition ini benar maka hanya terkait SQL statement_list dijalankan tetapi jika salah maka statement_list klausa ELSE dijalankan. statement_list bisa terdiri satu atau lebih pernyataan. Contoh:

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)

Contonh penggunaan  IF Statement dalam 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)

Pernyataan CASE :

        CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list] ... [ELSE statement_list] END CASE
Atau
        CASE WHEN search_condition THEN statement_list [WHEN search_condition THEN statement_list] ... [ELSE statement_list] END CASE

Sintaks pertama melaksanakan statement_list ketika case_value yang = when_value. Jika tidak ada case_value sesuai dengan when_value maka jalankan statement_list klausa ELSE. Dan dalam sintaks kedua, jika search_condition benar maka hanya sesuai statement_list SQL mengeksekusi tetapi jika salah maka statement_list klausa ELSE dijalankan. Contoh:

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)

 Pernyataan LOOP

 Sintaks umum LOOP Pernyataan:
        [begin_label:] LOOP statement_list END LOOP [end_label]
Menerapkan Pernyataan LOOP loop sederhana membangun. 

Pernyataan ini dipakai untuk mengulang pelaksanaan statement_list tersebut,  

statement_list dapat berisi satu atau lebih dari satu laporan. 

Laporan bisa mengulang eksekusi loop sampai keluar dan biasanya yang 

dapat dilakukan dengan Pernyataan LEAVE. Pernyataan LOOP dapat diberi label juga.

 

 Pernyataan LEAVE

 Sintaks umum LEAVE Pernyataan:
         LEAVE label

 Pernyataan LEAVE digunakan untuk keluar dari kontrol aliran konstruksi.

 Pada contoh berikut ini kita menjelaskan Anda baik LOOP dan LEAVE Pernyataan.

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)   SUMBER : http://www.roseindia.net/mysql/mysql5/flow-control-constructs.shtml