DELIMITER // create procedure test1(out value int) begin declare x tinytext; declare cursor_1 cursor for SELECT name FROM test GROUP BY name; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; open cursor_1; repeat fetch cursor_1 into x; until done end repeat; close cursor_1; set value=x; end;// DELIMITER ; call pro19(@return_value); select @return_value; 不对啊。
不好意思上面调用的错了DELIMITER // create procedure test1(out value int) begin declare x tinytext; declare cursor_1 cursor for SELECT name FROM test GROUP BY name; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; open cursor_1; repeat fetch cursor_1 into x; until done end repeat; close cursor_1; set value=x; end;// DELIMITER ; call test1(@return_value); select @return_value;这样的效果一样,也是无法得到结果!
你的中间哪有update 语句啊?你的这个存储过程是想实现什么功能?举例说明一下。
DELIMITER // create procedure test1(out value int) begin declare x tinytext; declare cursor_1 cursor for SELECT name FROM test GROUP BY name; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; open cursor_1; repeat fetch cursor_1 into x; select x; -- 加上这个你就知道你取到值了。 until done end repeat; close cursor_1; set value=x; end;// DELIMITER ; call test1(@return_value); select @return_value;
整个存储要实现的功能是,根据select取到的值送到update语句,作为update判断的一个条件。 比如有一个表test 下面三个字段 id name scorce a b c d e f t g kscorce的值来自name的一部分。先select scorce from test group by scorce这样是不是能取到scorce的值! 第二步 update test set id=a where id=b and name like '%上面取到的值%' and socrce='上面取到的值'
加一个select倒是查出结果了。但是我不明白最后set value=x这不到底有没有执行到。
写了一个SQL update好像不行 没执行到如下 DELIMITER $$ DROP PROCEDURE IF EXISTS `pro19`$$ BEGIN DECLARE X TINYTEXT; DECLARE Y INT; DECLARE cursor_1 CURSOR FOR SELECT key FROM test GROUP BY key; DECLARE CONTINUE HANDLER FOR NOT FOUND SET Y=1; OPEN cursor_1; REPEAT FETCH cursor_1 INTO X; UPDATE test SET ack=2 WHERE ack=0 AND allargs LIKE '%X%' AND key='X'; UNTIL Y=1 END REPEAT; CLOSE cursor_1; END$$ DELIMITER ;
create procedure test1(out value int)
begin
declare x tinytext;
declare cursor_1 cursor for SELECT name FROM test GROUP BY name;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
open cursor_1;
repeat
fetch cursor_1 into x;
until done end repeat;
close cursor_1;
set value=x;
end;//
DELIMITER ;
call pro19(@return_value);
select @return_value;
不对啊。
create procedure test1(out value int)
begin
declare x tinytext;
declare cursor_1 cursor for SELECT name FROM test GROUP BY name;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
open cursor_1;
repeat
fetch cursor_1 into x;
until done end repeat;
close cursor_1;
set value=x;
end;//
DELIMITER ;
call test1(@return_value);
select @return_value;这样的效果一样,也是无法得到结果!
create procedure test1(out value int)
begin
declare x tinytext;
declare cursor_1 cursor for SELECT name FROM test GROUP BY name;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
open cursor_1;
repeat
fetch cursor_1 into x;
select x; -- 加上这个你就知道你取到值了。
until done end repeat;
close cursor_1;
set value=x;
end;//
DELIMITER ;
call test1(@return_value);
select @return_value;
比如有一个表test
下面三个字段
id name scorce
a b c
d e f
t g kscorce的值来自name的一部分。先select scorce from test group by scorce这样是不是能取到scorce的值!
第二步
update test set id=a where id=b and name like '%上面取到的值%' and socrce='上面取到的值'
加一个select倒是查出结果了。但是我不明白最后set value=x这不到底有没有执行到。
没执行到如下
DELIMITER $$
DROP PROCEDURE IF EXISTS `pro19`$$
BEGIN
DECLARE X TINYTEXT;
DECLARE Y INT;
DECLARE cursor_1 CURSOR FOR SELECT key FROM test GROUP BY key;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET Y=1;
OPEN cursor_1;
REPEAT
FETCH cursor_1 INTO X;
UPDATE test SET ack=2 WHERE ack=0 AND allargs LIKE '%X%' AND key='X';
UNTIL Y=1 END REPEAT;
CLOSE cursor_1;
END$$
DELIMITER ;