mysql存储过程之动态sql

mysql存储过程之动态sql

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

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

1.  

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

　　set @sql = sql

　　PREPARE stmt_name FROM @sql;

　　EXECUTE stmt_name;

　　{DEALLOCATE | DROP} PREPARE stmt_name;

 

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;

 

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

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

 1 CREATE DEFINER = `root`@`%` PROCEDURE `NewProc`(IN `USER_ID` varchar(36),IN `USER_NAME` varchar(36))

 2 BEGIN

 3    

 4         declare SQL_FOR_SELECT varchar(500);                                                            　　　　　　　　　　 　　　　　　　　　　　　  -- 定义预处理sql语句

 5

 6         set SQL_FOR_SELECT = CONCAT("select * from user where user_id = '",USER_ID,"' and user_name = '",USER_NAME,"'");   -- 拼接查询sql语句

 7

 8         set @sql = SQL_FOR_SELECT;

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

10       　EXECUTE stmt ;                       -- 执行sql语句

11         deallocate prepare stmt;       -- 释放prepare

12

13

14         set USER_ID = '2';

15         set USER_NAME = 'lisi';

16

17         set @sql = SQL_FOR_SELECT;

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

19       　EXECUTE stmt ;                       -- 执行sql语句

20         deallocate prepare stmt;      -- 释放prepare

21 END;

 

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

 　　2.

　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;

 

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

 1 CREATE DEFINER = `root`@`%` PROCEDURE `NewProc`(IN `USER_ID` varchar(36),IN `USER_NAME` varchar(36))

 2 BEGIN

 3    

 4         declare SQL_FOR_SELECT varchar(500);                                                                                                                                           -- 定义预处理sql语句

 5

 6         set SQL_FOR_SELECT = "select * from user where user_id = ? and user_name = ? ";   　　　　　　　　　　　　　　　　　　　　　　　 -- 拼接查询sql语句

 7

 8         set @sql = SQL_FOR_SELECT;

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

10

11         set @parm1 = USER_ID;                                                      -- 传递sql动态参数

12         set @parm2 = USER_NAME;

13

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

15         deallocate prepare stmt;                                                -- 释放prepare

16

17

18         set @sql = SQL_FOR_SELECT;

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

20

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

22         set @parm2 = 'lisi';

23

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

25         deallocate prepare stmt;                                                 -- 释放prepare

26 END;

题外案例：

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 (?,?,?) "