关于Mysql存储过程的一点心得

     最近因为项目需要，写了一个Mysql数据库的存储过程，第一次写Mysql的存储过程还是碰到了不少坑，再此总结一下。

用DECLARE 定义变量或游标时必须放在过程的最前面，在其他执行的sql语句之前，否则会报语法错误

BEGIN
    #局部变量定义 必须放在最前端
    DECLARE i_tn INT DEFAULT 0; #TOTAL_NUMBER
    #定义游标
    DECLARE typeCur CURSOR FOR SELECT types,num FROM certification_type WHERE scbz='0' AND DOMAIN=in_domain AND LEVEL=in_level;

存储过程没有bool或boolean类型，必须使用int类型替代 但可以用true或false来赋值和判断

DECLARE f_done INT DEFAULT FALSE; #CURSOR FLAG
IF NOT f_done THEN 

对于游标可以设置一个handle，在到遍历结果集末尾时触发

#定义游标状态	
DECLARE CONTINUE HANDLER FOR NOT FOUND SET f_done=TRUE;
FETCH sectionCur INTO i_sc,i_rt;
IF NOT f_done THEN 

存储过程中出现异常如何处理，可以做如下定义在，异常时跳出存储过程，并记录错误信息

#定义SQL异常退出 写错误日志
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    #先记录错误信息 再ROLLBACK 否则将清空错误信息
    GET DIAGNOSTICS CONDITION 1 v_ex_rs=RETURNED_SQLSTATE,v_ex_mt=MESSAGE_TEXT;  
    ROLLBACK;
    INSERT INTO e_log (e_code,e_msg) VALUES (v_ex_rs,v_ex_mt);
END;

使用case语句时，最好都要加上最后的else条件，即使是什么也不做的语句DO NULL，否则在所有的when条件都判断为否时，会报20000, Case not found for CASE statement错误

CASE 
WHEN(SUBSTR(v_sc,1,1)='1' AND i_sn1>0)THEN SET i_sn1=i_sn1-1;IF(i_sn1=0)THEN SET f_change=TRUE;END IF;
WHEN(SUBSTR(v_sc,2,1)='1' AND i_sn2>0)THEN SET i_sn2=i_sn2-1;IF(i_sn2=0)THEN SET f_change=TRUE;END IF;
WHEN(SUBSTR(v_sc,3,1)='1' AND i_sn3>0)THEN SET i_sn3=i_sn3-1;IF(i_sn3=0)THEN SET f_change=TRUE;END IF;
WHEN(SUBSTR(v_sc,4,1)='1' AND i_sn4>0)THEN SET i_sn4=i_sn4-1;IF(i_sn4=0)THEN SET f_change=TRUE;END IF;
WHEN(SUBSTR(v_sc,5,1)='1' AND i_sn5>0)THEN SET i_sn5=i_sn5-1;IF(i_sn5=0)THEN SET f_change=TRUE;END IF;
WHEN(SUBSTR(v_sc,6,1)='1' AND i_sn6>0)THEN SET i_sn6=i_sn6-1;IF(i_sn6=0)THEN SET f_change=TRUE;END IF;
WHEN(SUBSTR(v_sc,7,1)='1' AND i_sn7>0)THEN SET i_sn7=i_sn7-1;IF(i_sn7=0)THEN SET f_change=TRUE;END IF;
WHEN(SUBSTR(v_sc,8,1)='1' AND i_sn8>0)THEN SET i_sn8=i_sn8-1;IF(i_sn8=0)THEN SET f_change=TRUE;END IF;
WHEN(SUBSTR(v_sc,9,1)='1' AND i_sn9>0)THEN SET i_sn9=i_sn9-1;IF(i_sn9=0)THEN SET f_change=TRUE;END IF;
WHEN(SUBSTR(v_sc,10,1)='1' AND i_sn10>0)THEN SET i_sn10=i_sn10-1;IF(i_sn10=0)THEN SET f_change=TRUE;END IF;
ELSE DO NULL;
END CASE;

如何判断查询结果集是否为空

IF(FOUND_ROWS()=0)THEN

如何主动跳出存储过程

leave_procedure: 
BEGIN
    XXXX;
    LEAVE leave_procedure; 

如何操作游标

    DECLARE sectionCur CURSOR FOR SELECT section_code,ratio FROM certification_section;	
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET f_done=TRUE;
    OPEN sectionCur;
	SET f_done=FALSE;
	REPEAT
		FETCH sectionCur INTO i_sc,i_rt;
		IF NOT f_done THEN 
            XXX;
        END IF;
        UNTIL f_done END REPEAT;
	CLOSE sectionCur; 

如何做事务控制

START TRANSACTION;
    XXX;
COMMIT;或ROLLBACK; 结束事务
    

如何动态拼接执行sql语句，其中需要拼接“'”单引号时，需要用"\"做转义，动态执行的语句必须赋值给用户变量@XXX，才能预处理执行

SET v_sql=CONCAT('INSERT INTO user_exam_topic(openid,exam_id,serial_num,topic_id,answer,is_correct) ','SELECT \'',in_openid,'\',',out_id,',0,id,answer,\'1\' FROM topic_library ','WHERE scbz=\'0\' AND types=\'',i_ty,'\'',v_sql_domain,v_sql_level,' ORDER BY RAND() LIMIT ',i_nm);
SET @do_sql=v_sql;
PREPARE stmt FROM @do_sql;  
EXECUTE stmt;   
DEALLOCATE PREPARE stmt; 

如何从动态执行的sql中获取结果 先select into到用户变量@xxx,再从@xxx中读取

SET v_sql=CONCAT('SELECT id,answer,section into @i_id,@v_an,@v_sc FROM topic_library ','WHERE scbz=\'0\' AND types=\'',i_ty,'\'',v_sql_domain,v_sql_level,v_sql_id,v_sql_section,' ORDER BY RAND() LIMIT 1');
SET @do_sql=v_sql;
PREPARE stmt FROM @do_sql;  
EXECUTE stmt;   
DEALLOCATE PREPARE stmt; 
SET [email protected]_id,[email protected]_an,[email protected]_sc;

如何实现rownum序号列

select (@rowNO := @rowNo+1) AS 序号,a.* from  t_user a,(select @rowNO :=0) b

写在最后，很多东西还有不了解的，需要多查官方文档

https://dev.mysql.com/doc/refman/5.6/en/