DELIMITER $$CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `mangos`.`diy`()
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
BEGIN
DECLARE sqlstr VARCHAR (2000) default '';
set sqlstr = 'select * from item_template';
execute(sqlstr);
set sqlstr='';
declare i int ;
set i=1;
while (i<11) do
set sqlstr='select stat_value'+i+' from item_template where stat_type'+i+' in(4,5,6)';
execute(sqlstr);
set i=i+1;
END WHILE; END$$DELIMITER ;错误码: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(sqlstr);
set sqlstr='';
declare i int ;
set i=1;
while (i<11) do
set sqlstr=' at line 12请问是什么原因
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `mangos`.`diy`()
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
BEGIN
DECLARE sqlstr VARCHAR (2000) default '';
set sqlstr = 'select * from item_template';
execute(sqlstr);
set sqlstr='';
declare i int ;
set i=1;
while (i<11) do
set sqlstr='select stat_value'+i+' from item_template where stat_type'+i+' in(4,5,6)';
execute(sqlstr);
set i=i+1;
END WHILE; END$$DELIMITER ;错误码: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(sqlstr);
set sqlstr='';
declare i int ;
set i=1;
while (i<11) do
set sqlstr=' at line 12请问是什么原因
mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
mysql> SET @a = 3;
mysql> SET @b = 4;
mysql> EXECUTE stmt1 USING @a, @b;
+------------+
| hypotenuse |
+------------+
| 5 |
+------------+
mysql> DEALLOCATE PREPARE stmt1;
BEGIN
declare i int ; set @sqlstr = 'select * from item_template';
PREPARE stmt1 FROM @sqlstr;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
set @sqlstr='';
set i=1;
while (i<11) do
set @sqlstr='select stat_value'+i+' from item_template where stat_type'+i+' in(4,5,6)';
PREPARE stmt1 FROM @sqlstr;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
set i=i+1;
END WHILE;
END$$DELIMITER ;
PREPARE stmt1 FROM @sqlstr;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
你只要将动态语句的查询字符串放进用户变量@sqlstr里即可了,不用像mssql那样定义一个sqlstr;另外,你的定义语句部分要放在最前面,不能在中间定义变量(如你的“declare i int ”)。