欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2024-03-14 17:18:22
...

     最近因为项目需要,写了一个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/