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;

 

   这样,我们就可以真正的使用不同的参数(当然也可以在存储过程中通过逻辑生成不同的参数)来使用动态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 (?,?,?) "   

 这里我也有个问题,因为有可能我不确定in语句里有几个参数,所以我试过这么写

  set @sql = "select * from user where user_id in (?) "  

然后参数我传的是  "'1','2','3'"  我以为程序会将我的动态sql解析出来(select * from user where user_id in ('1','2','3')) 但是并没有解析出来,各位大侠们帮帮忙,有什么好方法解决这个问题么?

  

posted @ 2013-02-18 19:19 分享黑夜 阅读(...) 评论(...) 编辑 收藏