记一次MySQL存储过程调试实例

第一次接触MySQL存储过程,资料看的也是一头雾水

需求为有两张表,需要根据A表的查询结果对B表进行逻辑处理

A表:

B表:

写的存储过程如下

查找错误1328:

错误：1328 SQLSTATE: HY000 (ER_SP_WRONG_NO_OF_FETCH_ARGS) 消息：不正确的FETCH变量数目。

于是便更改存储过程代码如下:

delimiter $$
create procedure test012(aid int)
begin
	declare types_3rd int; -- type类型 值为int类型
	#declare types_4rd int;
	declare done int default 0;
	#声明游标
	declare cur1 cursor  for
		select stype from s_type;
	#当游标打到尾部时,mysql自动设置done = 1
	declare continue handler for sqlstate '02000' set done = 1;

	open cur1;
	repeat
	#移动游标并赋值
	fetch cur1 into types_3rd;
	if not done then
			set types_3rd = (select stype from s_type where stype = 4);
			if types_3rd = 3 then update u_user set u_is_delete = 0 where uid = aid;
			elseif types_3rd = 4 then update u_user set u_sex = 0 where uid = aid;
			else
			update u_user set u_is_delete = 0 where uid = aid;
			end if;
	end if;
	until done end repeat;
	close cur1;
end $$
delimiter ;

call test012(2);

运行存储过程OK,调用也OK,最后查看表中数据也得到了更新

不得不说SQL的存储过程对于我来说还是挺难的,任重道远,还得继续努力,不能只停留在SQL基础层面