分享一下我老师大神的人工智能教程!零基础,通俗易懂!http://blog.csdn.net/jiangjunshow
也欢迎大家转载本篇文章。分享知识,造福人民,实现我们中华民族伟大复兴!
存储过程中使用动态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