提示:本文是利用业余时间写的,SQL代码是我直接敲的,没有实际执行过,我将会进行修改。谢谢各位。本人也是初学mysql存储过程,把自己在学习过程中遇到的问题总结下来,希望对各位同学提供帮助。
1、MySql存储过程创建
通过以下简单的例子,来简单学习并创建一个简单的存储过程,从执行结果中可以得到table_name的最大id。
DROP PROCEDURE IF EXISTS proc_name;
DELIMITER //
CREATE PROCEDURE proc_name() -- 创建存储过程 参数([in|out|inout] 参数 datatype) 本例子不涉及in 和 out 参数
BEGIN
DECLARE maxId int(10) DEFAULT 0; -- 声明变量 maxId
SELECT MAX(id) INTO maxId FROM table_name; -- 从表中查询最大的id 放到变量maxId中
SELECT maxId; -- 输入maxId的值
END //
DELIMITER ;
2、游标的使用
我们从t_user表中查询status为CANCEL的,删除所有t_user_history对应的数据
DROP PROCEDURE IF EXISTS proc_del_userhistory;
DELIMITER //
CREATE PROCEDURE proc_del_userhistory() -- 创建存储过程数
BEGIN
DECLARE userId int(10) DEFAULT 0; -- 声明变量 userId
DECLARE userStatus char(10) DEFAULT ''; -- 声明变量 userStatus
DECLARE deleteStatus char(10) DEFAULT 'CANCEL'; -- 声明要删除状态的值
DECLARE stopFlag int(1) DEFAULT 0; -- 循环停止标志,可以不声明
DECLARE user_cursor CURSOR FOR SELECT id,status FROM t_user; -- 声明游标user_cursor
SET deleteStatus = 'CANCEL'; -- 如果default没设值,这里也可以设置哦。
OPEN user_cursor; -- 打开游标
REPEAT -- 这里采用REPEAT循环游标
FETCH user_cursor INTO userId,userStatus ; -- fetch取值 把当前的值放到userId中
IF stopFlag <> 1 THEN -- 判断是否还有数据 防止多循环一次
-- 这里为了学习,把状态的判断放到IF 语句中,同学们自己写代码时,一定要放到where 语句中哦。。
IF userStatus = deleteStatus THEN
DELETE FROM t_user_history WHERE user_id = userId;
END IF;
END IF;
UNTIL stopFlag=1 END REPEAT; -- 循环结束条件
CLOSE user_cursor; -- 关闭光标
END //
DELIMITER ;
我想经过这个例子,大家一般的存储过程都可以写了。接下来,我分享一下自己在存储过程中遇到的比较难以解决的问题
3、游标循环次数多一次
主要是循环的时候,条件判断的问题,可参考上面例子的处理方式(IF stopFlag <> 1 THEN -- 判断是否还有数据 防止多循环一次)
4、游标循环次数比预期少(针对次数少的情况)
这种情况出现一般出现的循环光标中,存在SELECT操作,由于概念不清导致的。参考http://hi.baidu.com/kankin/blog/item/1e0eac036338cff109fa93d0.html
部分代码:
declare flag boolean;
declare continue handler for NOT FOUND set flag = false; -- 每次fetch操作,mysql都判断根据 NOT FOUND 异常,如果发送则设置为flag = false
set flag = true;
open cur;
fetch cur into userId;
while flag do
select id into accountId from t_user_account where id = userId; -- 这里如果没有取到数据,亦会报NOT FOUND 异常把 flag 设为false,导致循环提前退出
if accountId is not null then
do coding....
end if;
fetch cur into userId;
select fetchOk;
end while;
解决方法,再循环体内部,保留flag值
declare flag boolean;
declare falg_temp boolean;
declare continue handler for NOT FOUND set flag = false; -- 每次fetch操作,mysql都判断根据 NOT FOUND 异常,如果发送则设置为flag = false
set flag = true;
open cur;
fetch cur into userId;
while flag do
set flag_temp = flag; -- 设置flag_temp 为当前flag值
select id into accountId from t_user_account where id = userId; -- 这里如果没有取到数据,亦会报NOT FOUND 异常把 flag 设为false,导致循环提前退出
if accountId is not null then
do coding....
end if;
set flag = flag_temp; -- 还原flag值
fetch cur into userId;
select fetchOk;
end while;