mysql 存储过程 编写注意事项

mysql的存储过程有很多需要注意的地方,一不留神就会出错,可能调试了老半天才发现原因

1  没有return 语句

可以采用leave代替,返回直接使用select语句 比如select 1;

2.mysql的语句要严格加上分号,非执行语句的不需要加,比如begin、end、loop等

3.游标的获取状态 通常采用定义一个继续执行的处理,CONTINUE的意思就是遇到Not FOUND的异常时,会设置一个变量标志,但是程序继续执行,然后我们根据这个标志来跳出循环或者返回等等

    declare csrTransId cursor For select trans_id from tbl_fkcmd_trans where device_id=dev_id AND status='RUN';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

4.事务 异常处理 mysql没有try catch 我们只能根据错误的标志自行回滚,这里有个要注意的事项,CONTINUE HANDLER FOR SQLEXCEPTION一般放在了CONTINUE HANDLER FOR NOT FOUND后面如果放在前面就会出问题了,想想为什么?

因为CONTINUE HANDLER FOR NOT FOUND通常用在游标的循环场景下,我们希望在这个场景下捕获这个“异常”然后跳出循环,mysql先捕获了这个,程序继续执行,不会将其为一个事务的异常捕获,所以CONTINUE HANDLER 具有先后顺序,有点类似try catch中的异常捕获,先捕获的异常在后面的catch语句中不在捕获。

  DECLARE t_error INTEGER DEFAULT 0;    
        declare csrTransId cursor For select trans_id from tbl_fkcmd_trans where device_id=dev_id AND status='RUN';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;   

5.输出参数不可以和字段名同名,否则为null

下面贴上 一段完整的存储过程,仅作为示例

rt:BEGIN
 -- 遍历数据结束标志
  DECLARE done INT DEFAULT 0;

declare trans_id_tmp varchar(16);
  DECLARE t_error INTEGER DEFAULT 0;    
        declare csrTransId cursor For select trans_id from tbl_fkcmd_trans where device_id=dev_id AND status='RUN';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;   
  -- 将结束标志绑定到游标

    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    -- SET NOCOUNT ON;

    -- trans_id
     set trans_id_p='';
    if (dev_id is null || LENGTH(dev_id) = 0) THEN
        select -1;
LEAVE rt;
end if;
    -- Insert statements for procedure here
    SELECT trans_id into trans_id_p FROM tbl_fkcmd_trans where device_id=dev_id AND cmd_code='RESET_FK' AND status='WAIT';
    if(FOUND_ROWS() = 0) then 
        select -2;
LEAVE rt;
end if;
    start transaction;
        Open csrTransId;
  read_loop: LOOP
        Fetch Next From csrTransId    Into trans_id_tmp;
    IF done=1 THEN
      LEAVE read_loop;
    END IF;
            DELETE FROM tbl_fkcmd_trans_cmd_param WHERE trans_id=trans_id_tmp;
            DELETE FROM tbl_fkcmd_trans_cmd_result WHERE trans_id=trans_id_tmp;
  END LOOP;
        close csrTransId;
        UPDATE tbl_fkcmd_trans SET status='CANCELLED', update_time = NOW() WHERE device_id=dev_id AND status='RUN';
        UPDATE tbl_fkcmd_trans SET status='RESULT', update_time = now() WHERE device_id=dev_id AND cmd_code='RESET_FK';
  IF t_error = 1 THEN    
            ROLLBACK; 
        set trans_id_p='';
        select -2;
leave rt;
   else
    commit;
    select 0;
end if;
END

 

转载于:https://www.cnblogs.com/njcxwz/p/9183141.html

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值