mysql存储过程之动态sql

蔚落 2022-05-11 02:44 275阅读 0赞

** Mysql 5.0** **以后，支持了动态****sql****语句，我们可以通过传递不同的参数得到我们想要的值**

这里介绍两种在存储过程中的动态sql：

set sql = （预处理的sql语句，可以是用concat拼接的语句）

set @sql = sql

PREPARE **stmt\_name **FROM @sql;

**EXECUTE stmt\_name**;

\{DEALLOCATE | DROP\} PREPARE **stmt\_name**;

![70][]

CREATE DEFINER = \`root\`@\`%\` PROCEDURE \`NewProc\`(IN \`USER\_ID\` varchar(**36**),IN \`USER\_NAME\` varchar(**36**))

BEGIN

declare SQL\_FOR\_SELECT varchar(**500**);                                                           　　　　　　　　　　　　　　　　　　　　　　      \-- 定义预处理sql语句

set SQL\_FOR\_SELECT = CONCAT("select \* from  user  where user\_id = '",USER\_ID,"' and user\_name = '",USER\_NAME,"'");   \-- 拼接查询sql语句

set @sql = SQL\_FOR\_SELECT;

PREPARE stmt FROM @sql;         \-- 预处理动态sql语句

EXECUTE stmt ;                        \-- 执行sql语句

deallocate prepare stmt;      \-- 释放prepare

END;

![70 1][]

上述是一个简单的查询用户表的存储过程，当我们调用此存储过程，可以根据传入不同的参数获得不同的值

但是：上述存储过程中，我们必须在拼接sql语句之前把USER\_ID,USER\_NAME定义好，而且在拼接sql语句之后，我们无法改变USER\_ID,USER\_NAME的值，如下

![70 2][]

1 CREATE DEFINER = \`root\`@\`%\` PROCEDURE \`NewProc\`(IN \`USER\_ID\` varchar(**36**),IN \`USER\_NAME\` varchar(**36**))

2 BEGIN

4         declare SQL\_FOR\_SELECT varchar(**500**);                                                            　　　　　　　　　　 　　　　　　　　　　　　  \-- 定义预处理sql语句

6         set SQL\_FOR\_SELECT = CONCAT("select \* from user where user\_id = '",USER\_ID,"' and user\_name = '",USER\_NAME,"'");   \-- 拼接查询sql语句

8         set @sql = SQL\_FOR\_SELECT;

9       　PREPARE stmt FROM @sql;        \-- 预处理动态sql语句

10       　EXECUTE stmt ;                       \-- 执行sql语句

11         deallocate prepare stmt;       \-- 释放prepare

14         set USER\_ID = '2';

15         set USER\_NAME = 'lisi';

17         set @sql = SQL\_FOR\_SELECT;

18       　PREPARE stmt FROM @sql;        \-- 预处理动态sql语句

19       　EXECUTE stmt ;                       \-- 执行sql语句

20         deallocate prepare stmt;      \-- 释放prepare

21 END;

![70 3][]

我们用call aa('1','zhangsan');来调用该存储过程，第一次动态执行，我们得到了‘张三’的信息，然后我们在第14,15行将USER\_ID，USER\_NAME改为lisi，我们希望得到李四的相关信息，可查出来的结果依旧是张三的信息，说明我们在拼接sql语句后，不能再改变参数了。为了解决这种问题，下面介绍第二中方式

set sql = （预处理的sql语句，可以是用concat拼接的语句，参数用 ？代替）

set @sql = sql

PREPARE **stmt\_name **FROM @sql;

set @**var\_name = xxx;**

**EXECUTE stmt\_name USING \[USING @****var\_name**** \[, @****var\_name****\] ...\]**;

\{DEALLOCATE | DROP\} PREPARE **stmt\_name**;

上述的代码我们就可以改成

![70 4][]

1 CREATE DEFINER = \`root\`@\`%\` PROCEDURE \`NewProc\`(IN \`USER\_ID\` varchar(**36**),IN \`USER\_NAME\` varchar(**36**))

2 BEGIN

4         declare SQL\_FOR\_SELECT varchar(**500**);                                                                                                                                           \-- 定义预处理sql语句

6         set SQL\_FOR\_SELECT = "select \* from user where user\_id = ? and user\_name = ? ";   　　　　　　　　　　　　　　　　　　　　　　　 \-- 拼接查询sql语句

8         set @sql = SQL\_FOR\_SELECT;

9         PREPARE stmt FROM @sql;                                                　\-- 预处理动态sql语句

11         set @parm1 = USER\_ID;                                                      \-- 传递sql动态参数

12         set @parm2 = USER\_NAME;

14         EXECUTE stmt USING @parm1 , @parm2;                        　　\-- 执行sql语句

15         deallocate prepare stmt;                                                \-- 释放prepare

18         set @sql = SQL\_FOR\_SELECT;

19         PREPARE stmt FROM @sql;                                               　 \-- 预处理动态sql语句

21         set @parm1 = '2';                                                       　　 \-- 传递sql动态参数

22         set @parm2 = 'lisi';

24         EXECUTE stmt USING @parm1 , @parm2;                       　　  \-- 执行sql语句

25         deallocate prepare stmt;                                                 \-- 释放prepare

26 END;

![70 5][]

题外案例：

DROP PROCEDURE IF EXISTS \`procedureMonth\`;

CREATE DEFINER = \`root\`@\`%\` PROCEDURE \`procedureMonth\`(IN a varchar(2000))  
BEGIN  
  DECLARE result int DEFAULT 0;  
   DECLARE tableName VARCHAR(2000);  
   declare done int DEFAULT 0;  
   DECLARE cur\_tmp CURSOR FOR select name from business where is\_publish=1;  
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;  
        OPEN cur\_tmp;  
            cursor\_loop:loop  
                FETCH NEXT FROM cur\_tmp INTO tableName;        
                 if done=1 then  
                    leave cursor\_loop;  
                 end if;  
        set @ax=a;  
        set @sqlstr=concat("(select count(\*) into @m from ", tableName," where DATE\_FORMAT(create\_date,'%Y%m')=DATE\_FORMAT(CURDATE(),'%Y%m')","and status!='",@ax,"')");    
                prepare stmt from @sqlstr;    
                EXECUTE stmt;             
                deallocate prepare stmt;  
              set result=result+@m;   
            end loop cursor\_loop;  
               
        CLOSE cur\_tmp;

select "月单",result;

END;

这样，我们就可以真正的使用不同的参数（当然也可以在存储过程中通过逻辑生成不同的参数）来使用动态sql了。

几个注意：

*  　存储动态SQL的值的变量不能是自定义变量，必须是用户变量或者全局变量   如：set sql = 'xxx';  prepare stmt from sql;是错的，正确为： set @sql = 'xxx';  prepare stmt from @sql;
 *     即使 **preparable\_stmt **语句中的 ? 所代表的是一个字符串，你也不需要将 ? 用引号包含起来。
 *    如果动态语句中用到了 in 则sql语句应该这样写：set @sql = "select \* from user where user\_id in (?,?,?) "

[70]: /images/20220511/b14c4ab184c54f2da18ad3e86e6c1ecd.png
[70 1]: /images/20220511/c616043b2f4548ea8a709be1d29b0ed2.png
[70 2]: /images/20220511/9c4abd3f463a40baaf31c583285911dc.png
[70 3]: /images/20220511/f98b4a410a2342e8b77ade3d1adb86d2.png
[70 4]: /images/20220511/c701fbd01de24c48bc838f335c26cc80.png
[70 5]: /images/20220511/e6467153ea9f41279cb3925f593cdd36.png