Mysql 存储过程2

4,if 多条件分支

  select * from customer;

  mysql> select * from customer

+----+--------+----------+

| id | name   | password |

+----+--------+----------+

|  2 | lijuan | lijuan   |

|  3 | 陈超阳 | 陈超阳   |

|  4 | 张三   | 张三     |

|  5 | 张三   | 张三     |

|  6 | 李四   | 李四     |

+----+--------+----------+

5 rows in set (0.04 sec)

  create procedure sp_search_customer6(in mycondition int)

  begin

     if mycondition > 0 then

         select * from customer where name = '陈超阳';

     elseif mycondition > 1    then

         select * from customer where name = '张三';

     elseif mycondition > 2  then

         select * from customer where name = '李四';

     else

         select * from customer;

     end if;

  end

  call sp_search_customer(2);

  结果：

    mysql> call sp_search_customer6(3);

+----+--------+----------+

| id | name   | password |

+----+--------+----------+

|  3 | 陈超阳 | 陈超阳   |

+----+--------+----------+

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

  注意点：1，在多个条件下，如果给出的条件满足多个分支，那么只执行最前面

          的那个条件。上述存储过程只要给出大于0的数结果总是陈超阳。

 说明了那个问题。

 2，elseif 是合体，不能分开。

 3，end if是分体，并且需要跟上分号。

2013/8/19 2258  131

 

 

5,mysql中case分支语句。

  create procedure sp_search_cusomer7(in mycondition int)

begin

  case mycondition

      when 1 then

          select * from customer where name = '陈超阳';

      when 2 then 

          select * from customer where name = '张三';

      when 3 then 

          select * from customer where name = '李四';

      when 4 then

          select * from customer where name = '王五';

      else

          select * from customer;

  end case;

end

call sp_search_cusomer7(7);

 

6，本地变量（也叫局部变量

   在上一个例子中我们可以看到有很多相似的代码，我们可以吧相似的代码

   放在判断条件之外，让可变的地方以变量来表示。对上个存储过程我们

   修改为如下结构。

   create procedure sp_search_customer8(in mycondition int)

begin 

    DECLARE tmp varchar(20);

    case mycondition

       when  1 then

         set  tmp = '陈超阳';

       when  2 then

         set  tmp = '张三';

       when    3     then

         set  tmp = '李四';

       when  4       then

         set  tmp = '王五';

       else

         set  tmp = '';    

     end case;

     select * from customer where name = tmp;

end

call sp_search_customer8(9);

注意：1，定义变量  declare tmp varchar(20) '陈超阳';

         上面定义的时候直接赋予变量初始值了。

      2，该变量赋值：set tmp = '陈超阳';

      3，本地变量也叫做局部变量，只能使用在存储过程中用于保存存储过程中

         的临时值。

      4，变量一定要事先声明才能使用，否则不能直接直接使用。

 

7，使用循环语句。

   两种循环语句，while 和 repeat

   实例1：编写存储过程计算n的阶乘。

    create procedure sp_factorial(in p_num int,out p_result int)

    begin

        set p_result = 1;

        while p_num > 1 do

           set p_result = p_result * p_num;

           set p_num = p_num-1;

        end while;     

    end

    call sp_factorial(2,@jiecheng);

    select @jiecheng;

 

    实例2，用repear实现阶乘功能

    create procedure sp_factorial2(in p_num int,p_result int)

    begin

       set p_num = 1;

       repeat

          set p_result = p_result * p_num;

          set p_num = p_num-1;

       until p_num <=1 

       end repeat;

    end

    注意：repeat结果至少要执行一次循环，而while至少可以不执行。

 

 

实例：

      有两张表一张是收入表，一张是开支表，每个人的收入和开支分别存储上这两张表中，分别编写存储过程和函数实现功能：输入一个人的名字，得到该人的余额。

收入表：

  | income | CREATE TABLE `income` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `username` varchar(6) DEFAULT NULL,

  `income` int(11) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 |

支出表：

| outcome | CREATE TABLE `outcome` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `username` varchar(6) DEFAULT NULL,

  `outcome` int(11) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 |

 

存储过程：

create procedure balance(in myname varchar(20),out balance int)

begin

  declare sumincome int;

  declare sumoutcome int;

  select sum(income) into sumincome  from income where username = myname;

  select sum(outcome) into sumcome from outcome where username = myname;

  set balance = sumincome - sumoutcome;

end

测试：

  call balance('supan',@supanBalance);

  select @supanBalance;

 

函数：

  create function balance(myname varchar(20)) returns int

begin

  declare sumincome int;

  declare sumoutcome int;

  select sum(income) into sumincome from income where username = myname;

  select sum(outcome) into sumoutcome from outcome where username = myname;

  return sumincome - sumoutcome;

end

测试：

     select balance('supan');

 

 Over

NOte:上面程序没有测试，可能有误，不过大致思想就是那样。