MYSQL存储过程学习

1、游标的使用

BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE userid INT;
    DECLARE rs CURSOR FOR SELECT `uid` FROM sinbegin_user WHERE service = 0;/*定义游标*/
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;/*异常处理*/
    OPEN rs;/*打开游标*/
        FETCH NEXT FROM rs INTO userid;
        REPEAT/*遍历数据表*/
            IF NOT done THEN
                UPDATE sinbegin_user SET service = 1 WHERE uid = userid;
            END IF;
        FETCH NEXT FROM rs INTO userid;
        UNTIL done END REPEAT;
    CLOSE rs;/*关闭游标*/
END

DELIMITER $$
 
CREATE DEFINER=`root`@`%` PROCEDURE `sp_pament_TrainFee`()
BEGIN
/*局部变量的定义 declare*/
declare strYear int;
declare strEnrollID int;
declare feesum int;
declare stop int default 0;
declare cur cursor for(
select  year ,EnrollID ,sum(Fee) from trainmanage  where ReturnStatus !=1 group by Year,EnrollID );
 /*这把 游标 异常后 捕捉
         *        并设置 循环使用 变量 stop 为 null 跳出循环。
*/
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stop = null;
/*开游标*/
OPEN cur;
/*游标向下走一步，将查询出来的值付给定义的变量*/
    FETCH cur INTO strYear,strEnrollID,feesum;
    WHILE ( stop is not null) DO
    update payment  set  ExpectTrain=feesum   where EnrollID = strEnrollID and right(ID,2) =right(strYear,2) ;
    FETCH cur INTO strYear,strEnrollID,feesum;
    END WHILE;
/*游标向下走一步*/
CLOSE cur;
END

2、IF条件判断的使用

语法

IF search_condition THEN statement_list

    [ELSEIF search_condition THEN statement_list] ...

    [ELSE statement_list]

END IF

示例代码

IF CurrentLingQi >= 1800 THEN
    UPDATE socialrelation SET LingQi = 1800 WHERE NodeName = ShizuName;
ELSE
     UPDATE socialrelation SET LingQi = LingQi + 60 WHERE NodeName = ShizuName;
END IF;

 3、局部变量的定义

DECLARE var_name[,...] type [DEFAULT value]  
例如：

DECLARE done INT DEFAULT 0;

4、调用存储过程语法

CALL sp_name([parameter[,...]]);
示例：

CALL test();--无参数
CALL test(1,2)--带参数

5、变量的两种赋值方法

SET var_name = expr [, var_name = expr] ...

SELECT col_name[,...] INTO var_name[,...] table_expr
例如：

set no='101010',title='存储过程中定义变量与赋值'; 

select id into @id from tbl_currentWeather where cityid = _cityid;

select  @id:=id,@cityid:=cityid  from tbl_currentWeather where cityid = _cityid;

 6、CASE语句

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
例如;

CASE
    WHEN (sale_value>200) THEN
        CALL free_shipping(sale_id);
        CASE customer_status
            WHEN 'PLATINUM' THEN
                CALL apply_discount(sale_id,20);
            WHEN 'GOLD' THEN
                CALL apply_discount(sale_id,15);
            WHEN 'SILVER' THEN
                CALL apply_discount(sale_id,10);
            WHEN 'BRONZE' THEN
                CALL apply_discount(sale_id,5);
        END CASE;
END CASE;

SELECT CASE 1 WHEN 1 THEN 'one'  WHEN 2 THEN 'two' ELSE 'more' END; 

7、循环语句　　

loop leave iterate 实例

CREATE PROCEDURE ABC()
   BEGIN
      DECLARE a INT Default 0 ;
      simple_loop: LOOP
         SET a=a+1;
         select a;
         IF a=5 THEN
            LEAVE simple_loop;
         END IF;
   END LOOP simple_loop;
END

REPEAT 实例

create procedure pro
begin
  declare a int default 3;
  repeat
   select a;
   set a=a+1;
  untile a>5 end repeat;
end

while实例

create procedure pro
begin
 declare a int default 4;
 while a<10 do
   select a;
   set a=a+1;
 end while;
end

更多可以参考:
http://www.cnblogs.com/lyhabc/p/3793524.html