mysql实例：If statement with ELSEIF and ELSE

mysql>
mysql> delimiter $$
mysql> CREATE PROCEDURE myProc(normal_price NUMERIC(8,2),OUT discount_price NUMERIC(8,2)) //创建带参的存储过程
    -> NO SQL
    -> BEGIN
    ->     IF (normal_price>500) THEN
    ->        SET discount_price=normal_price*.8;
    ->     ELSEIF (normal_price>100) THEN
    ->        SET discount_price=normal_price*.9;
    ->
    ->     ELSE
    ->        SET discount_price=normal_price;
    ->     END IF;
    ->     select discount_price;
    -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql>
mysql> set @p = 600; //设置变量值
Query OK, 0 rows affected (0.00 sec)

mysql> set @dp = 0; //设置变量值
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> call myProc(@p, @dp); //调用存储过程
+----------------+
| discount_price |
+----------------+
|         480.00 |
+----------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select @dp; //输出返回值
+--------+
| @dp    |
+--------+
| 480.00 |
+--------+
1 row in set (0.00 sec)

mysql> drop procedure myProc; //删除存储过程
Query OK, 0 rows affected (0.00 sec)

mysql>