DROP PROCEDURE `p7`//
CREATE DEFINER=`root`@`localhost` PROCEDURE `p7`()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE aa,bb VARCHAR(64);
DECLARE cur1 CURSOR FOR SELECT tablename,columnname FROM `temp_table`;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; OPEN cur1;
REPEAT
FETCH cur1 INTO aa, bb;
IF NOT done THEN
set @sql_text:=concat("update ",aa," set ",bb,"='abc'");
prepare stmt from @sql_text;
execute stmt;
deallocate prepare stmt;
END IF;
UNTIL done END REPEAT; CLOSE cur1;
select * from mdl_block;
END***********************
以上代码可以通过,也就是建立存储过程没有问题
调用时使用call p7()
在Mysql administrator中可以执行成功,但是在Phpmyadmin中就有问题,错误代码如下:
#1312 - PROCEDURE moodle.p7 can't return a result set in the given context
请问问题出在哪里?
查阅资料,有解决方法说在用PHP连接数据库的时候,用下面的方法解决:
define('CLIENT_MULTI_RESULTS', 131072);
$link = mysql_connect("127.0.0.1", "root", "",1,CLIENT_MULTI_RESULTS) or die("Could not connect: ".mysql_error());但是我在PHPmyadmini中没有找到 mysql_connect这个函数,该怎么处理啊?????
多谢高手指点
CREATE DEFINER=`root`@`localhost` PROCEDURE `p7`()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE aa,bb VARCHAR(64);
DECLARE cur1 CURSOR FOR SELECT tablename,columnname FROM `temp_table`;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; OPEN cur1;
REPEAT
FETCH cur1 INTO aa, bb;
IF NOT done THEN
set @sql_text:=concat("update ",aa," set ",bb,"='abc'");
prepare stmt from @sql_text;
execute stmt;
deallocate prepare stmt;
END IF;
UNTIL done END REPEAT; CLOSE cur1;
select * from mdl_block;
END***********************
以上代码可以通过,也就是建立存储过程没有问题
调用时使用call p7()
在Mysql administrator中可以执行成功,但是在Phpmyadmin中就有问题,错误代码如下:
#1312 - PROCEDURE moodle.p7 can't return a result set in the given context
请问问题出在哪里?
查阅资料,有解决方法说在用PHP连接数据库的时候,用下面的方法解决:
define('CLIENT_MULTI_RESULTS', 131072);
$link = mysql_connect("127.0.0.1", "root", "",1,CLIENT_MULTI_RESULTS) or die("Could not connect: ".mysql_error());但是我在PHPmyadmini中没有找到 mysql_connect这个函数,该怎么处理啊?????
多谢高手指点
所以你需要行重新定义一下;delimiter //CREATE DEFINER=`root`@`localhost` PROCEDURE `p7`()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE aa,bb VARCHAR(64);
DECLARE cur1 CURSOR FOR SELECT tablename,columnname FROM `temp_table`;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; OPEN cur1;
REPEAT
FETCH cur1 INTO aa, bb;
IF NOT done THEN
set @sql_text:=concat("update ",aa," set ",bb,"='abc'");
prepare stmt from @sql_text;
execute stmt;
deallocate prepare stmt;
END IF;
UNTIL done END REPEAT; CLOSE cur1;
select * from mdl_block;
END
//delimiter ;
begin
select "hello world!" as A;
end //改变 delimiter 值;创建执行成功了,可是在调用执行的时候报错:#1312 - PROCEDURE moodle.p3 an't return a result set in the given context语句:call p3();也试过了 call p3()//
改变 delimiter 值;
也同样报错,那位高手能帮解决一下?我的phpmyadmin版本号为:5.0.45-community-nt
http://blog.csdn.net/chenlong12580/article/details/7514960