mysql中存储过程、函数的一些问题

　　最近写一些mysql的存储过程和函数，发现网上比较有价值的文档很少，大都是照着手册上抄来的，有些实际问题解决不了，比如用变量作表名。

经过反复调试，总算找到解决办法，一下是一些简单的记录，比较零碎。部分内容转自


1.用变量做表名：

简单的用set或者declare语句定义变量，然后直接作为sql的表名是不行的，mysql会把变量名当作表名。在其他的sql数据库中也是如此，mssql的解决方法是将整条sql语句作为变量，其中穿插变量作为表名，然后用sp_executesql调用该语句。

这在mysql5.0之前是不行的，5.0之后引入了一个全新的语句，可以达到类似sp_executesql的功能（仅对procedure有效，function不支持动态查询）：

PREPAREstmt_nameFROMpreparable_stmt;
EXECUTEstmt_name[USING@var_name[,@var_name]...];
{DEALLOCATEDROP}PREPAREstmt_name;

为了有一个感性的认识，
下面先给几个小例子：

mysql>PREPAREstmt1FROMSELECTSQRT(POW(?,2)+POW(?,2))AShypotenuse;
mysql>SET@a=3;
mysql>SET@b=4;
mysql>EXECUTEstmt1USING@a,@b;
+------------+
hypotenuse
+------------+
5
+------------+
mysql>DEALLOCATEPREPAREstmt1;

mysql>SET@s=SELECTSQRT(POW(?,2)+POW(?,2))AShypotenuse;
mysql>PREPAREstmt2FROM@s;
mysql>SET@a=6;
mysql>SET@b=8;
mysql>EXECUTEstmt2USING@a,@b;
+------------+
hypotenuse
+------------+
10
+------------+
mysql>DEALLOCATEPREPAREstmt2;

如果你的MySQL版本是5.0.7或者更高的，你还可以在LIMIT子句中使用它，示例如下：
mysql>SET@a=1;mysql>PREPARESTMTFROMSELECT*FROMtblLIMIT?;
mysql>EXECUTESTMTUSING@a;
mysql>SET@skip=1;SET@numrows=5;
mysql>PREPARESTMTFROMSELECT*FROMtblLIMIT?,?;
mysql>EXECUTESTMTUSING@skip,@numrows;

使用PREPARE的几个注意点：
A：PREPAREstmt_nameFROMpreparable_stmt;预定义一个语句，并将它赋给stmt_name，tmt_name是不区分大小写的。
B：即使preparable_stmt语句中的?所代表的是一个字符串，你也不需要将?用引号包含起来。
C：如果新的PREPARE语句使用了一个已存在的stmt_name，那么原有的将被立即释放！即使这个新的PREPARE语句因为错误而不能被正确执行。
D：PREPAREstmt_name的作用域是当前客户端连接会话可见。
E：要释放一个预定义语句的资源，可以使用DEALLOCATEPREPARE句法。
F：EXECUTEstmt_name句法中，如果stmt_name不存在，将会引发一个错误。
G：如果在终止客户端连接会话时，没有显式地调用DEALLOCATEPREPARE句法释放资源，服务器端会自己动释放它。
H：在预定义语句中，CREATETABLE,DELETE,DO,INSERT,REPLACE,SELECT,SET,UPDATE,和大部分的SHOW句法被支持。
I：PREPARE语句不可以用于存储过程，自定义函数！但从MySQL5.0.13开始，它可以被用于存储过程，仍不支持在函数中使用！

下面给个示例：
CREATEPROCEDURE`p1`(INidINTUNSIGNED,INnameVARCHAR(11))
BEGINlable_exit:
BEGIN
SET@SqlCmd=SELECT*FROMtA;
IFidISNOTNULLTHEN
SET@SqlCmd=CONCAT(@SqlCmd,WHEREid=?);
PREPAREstmtFROM@SqlCmd;
SET@a=id;
EXECUTEstmtUSING@a;
LEAVElable_exit;
ENDIF;
IFnameISNOTNULLTHEN
SET@SqlCmd=CONCAT(@SqlCmd,WHEREnameLIKE?);
PREPAREstmtFROM@SqlCmd;
SET@a=CONCAT(name,%);
EXECUTEstmtUSING@a;
LEAVElable_exit;
ENDIF;
ENDlable_exit;
END;
CALL`p1`(1,NULL);
CALL`p1`(NULL,QQ);
DROPPROCEDURE`p1`;

了解了PREPARE的用法，再用变量做表名就很容易了。不过在实际操作过程中还发现其他一些问题，比如变量定义，declare变量和set@var=value变量的用法以及参数传入的变量。

测试后发现，set@var=value这样定义的变量直接写在字符串中就会被当作变量转换，declare的变量和参数传入的变量则必须用CONCAT来连接。具体的原理没有研究。

EXECUTEstmtUSING@a;这样的语句USING后面的变量也只能用set@var=value这种，declare和参数传入的变量不行。

另外php调用mysql存储过程的时候也碰到很多问题，总是出现PROCEDUREpcantreturnaresultsetinthegivencontext这样的问题。