mysql 存储过程 动态sql语句

mysql存储过程，触发器变量作表名
 
最近写一些mysql的存储过程和函数，发现网上比较有价值的文档很少，大都是照着手册上抄来的，有些实际问题解决不了，比如用变量作表名。
 经过反复调试，总算找到解决办法，一下是一些简单的记录，比较零碎。
 
部分内容转自1.用变量做表名：
 简单的用set或者declare语句定义变量，然后直接作为sql的表名是不行的，mysql会把变量名当作表名。在其他的sql数据库中也是如此，mssql的解决方法是将整条sql语句作为变量，其中穿插变量作为表名，然后用sp_executesql调用该语句。
 
这在mysql5.0之前是不行的，5.0之后引入了一个全新的语句，可以达到类似sp_executesql的功能(仅对procedure有效，function不支持动态查询)：
 PREPARE stmt_name FROM preparable_stmt；EXECUTE stmt_name[USING@var_name[,@var_name].]；{DEALLOCATE|DROP}PREPARE stmt_name；为了有一个感性的认识，下面先给几个小例子：
 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；
 mysql SET@s='SELECT SQRT(POW(?,2) POW(?,2))AS hypotenuse'；mysql PREPARE stmt2 FROM@s；mysql SET@a=6；mysql SET@b=8；mysql EXECUTE stmt2 USING@a,@b； -- |hypotenuse| -- |10| -- mysql DEALLOCATE PREPARE stmt2；
 
如果你的MySQL版本是5.0.7或者更高的，你还可以在LIMIT子句中使用它，示例如下：mysql SET@a=1；mysql PREPARE STMT FROM"SELECT*FROM tbl LIMIT?"；mysql EXECUTE STMT USING@a；mysql SET@skip=1； SET@numrows=5；mysql PREPARE STMT FROM"SELECT*FROM tbl LIMIT?,?"；mysql EXECUTE STMT USING@skip,@numrows；
 使用PREPARE的几个注意点：
 A：PREPARE stmt_name FROM preparable_stmt；预定义一个语句，并将它赋给stmt_name，tmt_name是不区分大小写的。
 B：即使preparable_stmt语句中的?所代表的是一个字符串，你也不需要将?用引号包含起来。
 C：如果新的PREPARE语句使用了一个已存在的stmt_name，那么原有的将被立即释放！即使这个新的PREPARE语句因为错误而不能被正确执行。
 D：PREPARE stmt_name的作用域是当前客户端连接会话可见。
 E：要释放一个预定义语句的资源，可以使用DEALLOCATE PREPARE句法。F：EXECUTE stmt_name句法中，如果stmt_name不存在，将会引发一个错误。
 G：如果在终止客户端连接会话时，没有显式地调用DEALLOCATE PREPARE句法释放资源，服务器端会自己动释放它。
 H：在预定义语句中，CREATE TABLE,DELETE,DO,INSERT,REPLACE,SELECT,SET,UPDATE,和大部分的SHOW句法被支持。
 I：PREPARE语句不可以用于存储过程，自定义函数！但从MySQL 5.0.13开始，它可以被用于存储过程，仍不支持在函数中使用！
 
下面给个示例：
 CREATE PROCEDURE`p1`(IN id INT UNSIGNED,IN name VARCHAR(11))BEGIN lable_exit：BEGIN SET@SqlCmd='SELECT*FROM tA'；IF id IS NOT NULL THEN SET@SqlCmd=CONCAT(@SqlCmd,'WHERE id=?')；PREPARE stmt FROM@SqlCmd； SET@a=id；EXECUTE stmt USING@a；LEAVE lable_exit；END IF；IF name IS NOT NULL THEN SET@SqlCmd=CONCAT(@SqlCmd,'WHERE name LIKE?')；PREPARE stmt FROM@SqlCmd； SET@a=CONCAT(name,'；EXECUTE stmt USING@a；LEAVE lable_exit；END IF；END lable_exit；END；CALL`p1`(1,NULL)；CALL`p1`(NULL,'QQ')；DROP PROCEDURE`p1`；
 
了解了PREPARE的用法，再用变量做表名就很容易了。不过在实际操作过程中还发现其他一些问题，比如变量定义，declare变量和 set@var=value变量的用法以及参数传入的变量。
 测试后发现， set@var=value这样定义的变量直接写在字符串中就会被当作变量转换，declare的变量和参数传入的变量则必须用CONCAT来连接。具体的原理没有研究。
 
EXECUTE stmt USING@a；这样的语句USING后面的变量也只能用 set@var=value这种，declare和参数传入的变量不行。
 另外php调用mysql存储过程的时候也碰到很多问题，总是出现PROCEDURE pcan't return aresult set in the given context这样的问题。
 关于php调用mysql存储过程的问题，转载另外一篇文章说明：php调用mysql存储过程和函数的方法
本文来自: 高校自动化网( Www.zdh1909.com) 详细出处参考(转载请保留本链接)： http://www.zdh1909.com/html/sql/12155.html