mysql 存储过程 事务 游标_MySQL存储过程的游标，事务使用

CREATE DEFINER=`root`@`%` PROCEDURE `insertData`()

BEGIN

DECLARE Done INT DEFAULT 0;

declare p_name varchar(100);

DECLARE IMP_CUR CURSOR FOR SELECT name FROM studen;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;

#定义事务正确类型

DECLARE t_error INTEGER DEFAULT 0;

#捕捉sql错误

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;

#开始事务

START TRANSACTION;

open IMP_CUR ;

FETCH next from IMP_CUR INTO p_name;

repeat

IF NOT Done THEN

insert into class (name) values(p_name);

END IF;

FETCH next from IMP_CUR INTO imp_name;

until done end repeat;

CLOSE IMP_CUR ;

#发生错误

IF t_error = 1 THEN

#回滚事务

ROLLBACK;

select '操作成功';

ELSE

COMMIT;

sleect '操作成功';

END IF;

END