MySQL 存储过程应注意的问题

原创
2016/07/20 18:46
阅读数 160

1. select count(1) from tableA where 1<>1 返回的值为0 ,而不是空(NULL)

2.定义变量cnt 为INT 类型如: DECLARE cnt INT ; 后使用 SET cnt = NULL ; 是可以将cnt的值设置为NULL的.

3.定义的变量中如果含有中文,请指定其字符编码,否则不能保证计算的正确性.

4.如果使用了游标和循环,要记得循环终止条件,示例如下:

 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    OPEN curUsrData;
    labelLoop : LOOP
      FETCH curUsrData INTO uId,intrId,regTime,authTime,bindTime;
      -- 处理未找到记录异常,循环终止
      IF done = 1 THEN
        LEAVE labelLoop;
      END IF ;

      -- 输出调试信息
      SELECT uId,intrId,regTime,authTime,bindTime;

5. 内部变量赋值引起的 HANDLER FRO NOT FOUND 状态改变

Note:此问题非常隐蔽

示例如下:

DROP TABLE IF EXISTS test_NotFoundData;
CREATE TABLE `test_NotFoundData` (
  `id` int(11) NOT NULL,
  `name` varchar(32) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO test_NotFoundData
VALUES(1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e'),(6,'f');

DROP TABLE IF EXISTS test_NotFound;
CREATE TABLE `test_NotFound` (
  `id` int(11) NOT NULL,
  `name` varchar(32) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP PROCEDURE IF EXISTS proc_testNotFound;

CREATE PROCEDURE proc_testNotFound()
  BEGIN
    DECLARE done INT DEFAULT FALSE ;

    DECLARE did INT ;
    DECLARE dnm VARCHAR(32);

    DECLARE tid INT;
    DECLARE tnm VARCHAR(32);


    DECLARE cur CURSOR FOR SELECT id,name FROM test_NotFoundData;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE ;

    OPEN cur;
    labelLoop : LOOP
      FETCH cur INTO did,dnm;
      IF done THEN
        LEAVE labelLoop;
      END IF ;

      SELECT id, name INTO tid,tnm
      FROM test_NotFound t
      WHERE t.id = did;

      SELECT did,dnm;

    END LOOP;
    CLOSE cur;
  END;

CALL proc_testNotFound();

你会发现循环只执行了一次就终止了,原因在于使用了Select into @变量 ,并且数据表中没有数据,此时HANDLER FRO NOT FOUND 状态会改变,致使循环退出.

 SELECT id, name INTO tid,tnm
      FROM test_NotFound t
      WHERE t.id = did;

除去 into @变量后正常,在实际开发中确实需要对这类变量赋值,解决的办法是先查询当前的总记录数,若大于1,再执行查询并写入到变量,代码如下:

 -- 先执行查询总数量,
 SELECT count(1) INTO rowCnt 
 FROM test_NotFound t
 WHERE t.id = did;

 IF rowCnt < 1 THEN
   ITERATE labelLoop;
 END IF ;
 -- 再执行查询并INTO 到变量,这样可以解决此问题
 SELECT id, name INTO tid,tnm
 FROM test_NotFound t
 WHERE t.id = did;

也可以使用定义多个handler来完成

BEGIN
 -- define another handler to handle the exception which occurred in the BEGIN END internal.
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET donex = TRUE ;
 SELECT id, name INTO tid,tnm
 FROM test_NotFound t
 WHERE t.id = did;
END;

 

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
1 收藏
1
分享
返回顶部
顶部