56,678
社区成员
发帖
与我相关
我的任务
分享
create trigger t_trigger AFTER insert on issues for each ROW
BEGIN
DECLARE retValue INTEGER;
DECLARE tmpname varchar(255);
DECLARE title varchar(255) character set utf8;
DECLARE tmpproject_id INTEGER;
declare done tinyint(1) default 0;
DECLARE cur1 CURSOR for
select login from users where id in (select user_id from members where members.project_id = new.project_id);
DECLARE CONTINUE HANDLER FOR NOT FOUND set done = 1;
set tmpproject_id = new.project_id;
set title = new.subject;
open cur1;
myloop: LOOP
FETCH cur1 into tmpname;
IF done = 1 THEN
LEAVE myloop;
ELSE
//每次执行完这句就进入下一个循环了
select count(*) into retvalue from issues where my_test(tmpname, title);
set tmpname = '000000000000';//这句根本就执行不到???
END IF;
END LOOP myloop;
close cur1;
END;
open cur1;
LOOP语句到底是怎么执行的?游标指向的字符串有结束符吗?
myloop: LOOP
set tmpname = '000000000000';//这句还是不执行???
FETCH cur1 into tmpname;
IF done = 1 THEN
LEAVE myloop;
ELSE
//这句每次循环都执行
select count(*) into retvalue from issues where my_test(tmpname, title);
END IF;
END LOOP myloop;
close cur1;
open cur1;
set sql='select count(*) into retvalue from issues where my_test('
PREPARE stmt2 FROM sql;
myloop: LOOP
set tmpname = '000000000000';
set sql=concat(sql,tmpname,', title)');
EXECUTE stmt2;
FETCH cur1 into tmpname;
IF done = 1 THEN
LEAVE myloop;
ELSE
set sql=concat(sql,tmpname,', title)');
EXECUTE stmt2;
END IF;
END LOOP myloop;
DEALLOCATE PREPARE stmt2;
close cur1;