mysql 游标多值_MySQL游标多一次循环

触发器代码片段如下：

declare c_name cursor for select b.id from asset_db_priv a,mc$priv_database b where a.node_name=b.priv_name and a.p_node_name=new.name;

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

set pnid=new.id;

open c_name;

REPEAT

fetch c_name into p_id;

insert into db_operation_priv(op_id,priv_db_id,status) values (pnid,p_id,'1');

UNTIL done END REPEAT;

close c_name;

发现db_operation_priv表格会被多插一条记录(最后一条重复)。查看触发器代码很明显done = 1时还会执行一次insert，那么在insert之前加一层判断即可。我先加了if not done then或者if done<>1 then，发现insert 一次都没有执行，将done值插入测试表发现done在变为1之前都是null,改为if done is null then即可。

改好的代码片段如下：

declare c_name cursor for select b.id from asset_db_priv a,mc$priv_database b where a.node_name=b.priv_name and a.p_node_name=new.name;

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

set pnid=new.id;

open c_name;

REPEAT

fetch c_name into p_id;

if done is null then

insert into db_operation_priv(op_id,priv_db_id,status) values (pnid,p_id,'1');

end if;

UNTIL done END REPEAT;

close c_name;