带判断条件的MySQL存储过程
[cc lang='sql']
delimiter $$
create procedure sp_xf_update_book()
begin
declare v_done bit default false;
declare v_commit int;
declare $v_id bigint;
declare $existsFlag int default 0;
declare $bookId bigint;
declare $bookName varchar(50);
declare cur cursor for
select id,book_id,book_name from xf_book;
declare continue handler for not found set v_done = true;
set autocommit =0;
open cur;
repeat
fetch cur into $v_id,$bookId,$bookName;
IF not v_done then
select count(*) as cnt into $existsFlag from xf_book_bak where id = $v_id ;
if $existsFlag = 0 then
insert into xf_book_bak
select * from xf_book where id=$v_id;
else
update xf_book_bak set book_name = $bookName where id=$v_id;
end if;
end if;
set v_commit=v_commit+1;
IF (v_commit MOD 100 = 0) THEN
COMMIT;
END IF;
until v_done end repeat;
close cur;
commit;
end$$
delimiter ;[/cc]
This work is licensed under a CC A-S 4.0 International License.