• 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
  • 相关阅读:
    【Oracle】实体化视图
    安装Linux Centos系统硬盘分区方法
    .NET基础一
    【MySQL】无法启动mysql服务(位于本地计算机上)错误1067,进程意外中止
    Linux基础一
    SQL Server中生成100万行8位纯数字的随机数(转)
    SQL Server配置数据库邮件
    SQL点点滴滴_聪明的小写法(持续更新中)
    过去的2017和已经到来的2018
    【Oracle】PL/SQL Developer使用技巧(持续更新中)
  • 原文地址:https://www.cnblogs.com/njcxwz/p/9183141.html
Copyright © 2020-2023  润新知