记录一个存储过程使用

delimiter $$
create procedure up()
begin
declare no_more_record integer default 0;
declare sid bigint;
declare uid bigint;
declare bankCode varchar(255) default ‘’;
declare accountBankCode varchar(255);
declare tempVar cursor for select id, user_id, bank_code from a_table where is_delete = 0 and states != 3 and char_length(user_id) < 10; // 声明游标
declare continue handler for not found set no_more_record = 1; // 当数据循环结束时，设置no_more_record = 1
set no_more_record = 0;
open tempVar; // 开启游标
posLoop:loop // 开始loop循环，posLoop为标签
if no_more_record = 1 then
leave posLoop; // 跳出整体的循环
end if;
fetch tempVar into sid, uid, bankCode; // 取游标中的值并赋予对应的变量
set @var = (select account_type from b_table where user_id = uid and bank_card_num = bankCode and is_delete = 0 limit 1); // 给变量赋值
set accountBankCode = @var; // 给变量赋值
if accountBankCode is null then
iterate posLoop; // 跳过本次循环
elseif accountBankCode = ‘’ then
iterate posLoop;
else
update a_table set account_bank_code = accountBankCode where id = sid;
end if;
insert into temp_logs (sid, uid, bank_code, account_bank_code, result) values (sid, uid, bankCode, accountBankCode, “success”); // 自己设计日志表，插入记录处理日志
end loop posLoop;
close tempVar; // 关闭游标
end $$
delimiter ;
call up; // 调用存储过程
drop procedure up; // 删除存储过程