我有一个存储过程如下: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;my_test是我自定义的一个函数,每次执行了select这句后就进入下一次循环了,这句set tmpname = '000000000000';根本执行不到,所以我无法重置tmpname的值;
这个游标赋值也有点奇怪,比如游标指向3个字符串的集合(aaaaa,bb,ccc);
tmpname得到的三次值分别是aaaaa, bbaaa, cccaa;也就是说游标的字符串貌似没有结束字符???
顺便问下mysql中 varchar字符串的结束字符是什么?
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;my_test是我自定义的一个函数,每次执行了select这句后就进入下一次循环了,这句set tmpname = '000000000000';根本执行不到,所以我无法重置tmpname的值;
这个游标赋值也有点奇怪,比如游标指向3个字符串的集合(aaaaa,bb,ccc);
tmpname得到的三次值分别是aaaaa, bbaaa, cccaa;也就是说游标的字符串貌似没有结束字符???
顺便问下mysql中 varchar字符串的结束字符是什么?
解决方案 »
- 求sql语句,简单的
- show master status \G 显示为空集
- Can't create a new thread (errno 12)问题
- [求助] Mysql 时间戳 的问题`` 求助~~~
- 急!execute的问题,请高手帮忙
- 我从www.mysql.com上down了mysql-4.1.1-alpha-win.zip,如何安装并启动服务呢?
- win2000上如何连接mysql
- win7安装mysql,无法连接远程服务器
- 如果在windows下用C建立多个与MySQL的连接
- 我想在sql中的所有表中查询哪些表存在co1这个字段怎么弄,
- INSERT语句
- mysql workbench报错
set done=0;
....my_test代码是什么
open cur1;LOOP语句到底是怎么执行的?游标指向的字符串有结束符吗?
myloop: LOOPset 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;
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;
需要有一个中间变量,现在我还不知道这是为什么???
为什么直接引用会导致变量的缓存混乱呢?
只要再加一句如set name1 = substring(tmpname,1);
然后使用name1即可;
select count(*) into retvalue from issues where my_test(name1, title);