大家都知道只有的了mysql 5后才出现了存储过程，这些用法，那么我们要在sql 中用if else while这些，就得用存过程或函数来实例了。 mysql delimiter // mysql create function myfunction (quantity int(10)) returns int(10) - begin - - while quantity mo
大家都知道只有的了mysql 5后才出现了存储过程，，这些用法，那么我们要在sql 中用if else while这些，就得用存过程或函数来实例了。
mysql> delimiter //
mysql> create function myfunction (quantity int(10)) returns int(10)
    -> begin
    ->
    ->     while quantity mod 12 > 0 do
    ->     set quantity = quantity + 1;
    ->     end while;
    ->
    ->     return quantity;
    ->
    -> end
    -> //
query ok, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql>
mysql> select myfunction(10);
+----------------+
| myfunction(10) |
+----------------+
|             12 |
+----------------+
1 row in set (0.00 sec)
mysql>
mysql> select myfunction(24);
+----------------+
| myfunction(24) |
+----------------+
|             24 |
+----------------+
1 row in set (0.00 sec)
实例二
mysql> delimiter //
mysql> create procedure test_while (in in_count int)
    -> begin
    ->     declare count int default 0;
    ->
    ->     while count     ->         set count = count + 1;
    ->     end while;
    ->
    ->     select count;
    -> end
    -> //
query ok, 0 rows affected (0.00 sec)
mysql>
mysql> delimiter ;
mysql>
mysql> call test_while(10);
+-------+
| count |
+-------+
|    10 |
+-------+
1 row in set (0.00 sec)
query ok, 0 rows affected (0.00 sec)
带有条件判断的
mysql> delimiter $$
mysql>
mysql> create procedure myproc()
    -> begin
    ->
    ->     declare i int;
    ->     set i=1;
    ->     loop1: while i    ->          if mod(i,2)0 then /*even number - try again*/
    ->             select concat(i, is an odd number);
    ->          end if;
    ->          set i=i+1;
    ->     end while loop1;
    -> end$$
query ok, 0 rows affected (0.00 sec)
mysql>
mysql> delimiter ;
mysql> call myproc();
+-------------------------------+
| concat(i, is an odd number) |
+-------------------------------+
| 1 is an odd number            |
+-------------------------------+
1 row in set (0.02 sec)
+-------------------------------+
| concat(i, is an odd number) |
+-------------------------------+
| 3 is an odd number            |
+-------------------------------+
1 row in set (0.02 sec)
+-------------------------------+
| concat(i, is an odd number) |
+-------------------------------+
| 5 is an odd number            |
+-------------------------------+
1 row in set (0.02 sec)
+-------------------------------+
| concat(i, is an odd number) |
+-------------------------------+
| 7 is an odd number            |
+-------------------------------+
1 row in set (0.02 sec)
+-------------------------------+
| concat(i, is an odd number) |
+-------------------------------+
| 9 is an odd number            |
+-------------------------------+
1 row in set (0.02 sec)
query ok, 0 rows affected (0.38 sec)