MySQL存储过程中使用动态SQL与静态SQL的区别

zhwz

　　存储过程中使用动态SQL 实例

　　

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`t1`$$
CREATE
/** [DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `test`.`t1`()
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

*/
BEGIN
SET @i = 3;
SET @j = 5;
-- 设立动态SQL
SET @SQL = 'select * from `t1` limit ?,?';
-- 加载动态SQL
PREPARE STMT FROM @SQL;
-- 执行动态SQL
EXECUTE STMT USING @i,@j;
END$$

DELIMITER ;

　　
　　
　　存储过程中未使用动态SQL实例，静态sql 的limit 的变量必须是用decare 声明的才行

　　

DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`t2`$$
CREATE
/** [DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `test`.`t2`()
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
*/
BEGIN
-- 如果不使用动态SQL,那么变量必须通过declare进行定义，才能在select语句中使用。
DECLARE i INT DEFAULT 0;
DECLARE j INT DEFAULT 0;
SET i = 3;
SET j = 5;
SELECT * FROM t1 LIMIT i,j;
END$$
DELIMITER ;

如果不定义，会报如下错误：　　

　　

DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`t3`$$
CREATE
/** [DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `test`.`t3`()
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

SET @i = 3;
SET @j = 5;
SET @sql = 'select * from `t1` limit ?,?';
PREPARE STMT FROM @sql;
EXECUTE STMT USING @i,@j;
*/
BEGIN
SET @i = 3;
SET @j = 5;
SELECT * FROM t1 LIMIT @i,@j;
END$$
DELIMITER ;

执行后，报错如下：　　
　　(0 row(s) affected, 1 warning(s))

Execution Time : 00:00:00:281

Transfer Time : 00:00:01:045

Total Time   : 00:00:01:326



Error Code : 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 '@i,@j;

END' at line 18



Execution Time : 00:00:00:000

Transfer Time : 00:00:00:000
Total Time   : 00:00:00:000