MySQL存储过程中使用动态SQL与静态SQL的区别
存储过程中使用动态SQL 实例DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`t1`$$
CREATE
/** */
PROCEDURE `test`.`t1`()
/*LANGUAGE SQL
| 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
/** */
PROCEDURE `test`.`t2`()
/*LANGUAGE SQL
| 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
/** */
PROCEDURE `test`.`t3`()
/*LANGUAGE SQL
| 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
页:
[1]