mysql存儲過程中各種動態sql語句的用法

本文主要給大家簡單講講mysql存儲過程中各種動態sql語句的用法，相關專業術語大家可以上網查查或者找一些相關書籍補充一下，這里就不涉獵了，我們就直奔主題吧，希望mysql存儲過程中各種動態sql語句的用法這篇文章可以給大家帶來一些實際幫助。                                                           

簡單的存儲過程各個關鍵字的用法:

CREATE DEFINER = CURRENT_USER PROCEDURE `NewProc`(in _xnb varchar(50))
BEGIN
	## 定義變量
	DECLARE _num FLOAT(14,6) DEFAULT 0;
	## @表示全局變量 相當于php $
	## 拼接賦值 INTO 必須要用全局變量不然語句會報錯
    ## //CONCAT會把'SELECT SUM('和_xnb和') INTO @tnum FROM btc_user_coin'拼接起來,CONCAT的各個參數中間以","號分割
	SET @strsql = CONCAT('SELECT SUM(',_xnb,') INTO @tnum FROM btc_user_coin');
	## 預處理需要執行的動態SQL，其中stmt是一個變量
	PREPARE stmt FROM @strsql;  
	## 執行SQL語句
	EXECUTE stmt;  
	## 釋放掉預處理段
	deallocate prepare stmt;
	## 賦值給定義的變量
	SET _num = @tnum;
	SELECT _num
END;;

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;      -- 釋放prepareEND;

上述是一個簡單的查詢用戶表的存儲過程，當我們調用此存儲過程，可以根據傳入不同的參數獲得不同的值。

但是：上述存儲過程中，我們必須在拼接sql語句之前把USER_ID,USER_NAME定義好，而且在拼接sql語句之后，我們無法改變USER_ID,USER_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


       set USER_ID = '2'; -- 主動指定參數USER_ID的值
       set USER_NAME = 'lisi';       set @sql = SQL_FOR_SELECT;       PREPARE stmt FROM @sql;       -- 預處理動態sql語句
       EXECUTE stmt ;                -- 執行sql語句
       deallocate prepare stmt;      -- 釋放prepareEND;

　我們用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;

上述的代碼我們就可以改成 ：

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 = "select * from user where user_id = ? and user_name = ? ";  
       -- 拼接查詢sql語句

       set @sql = SQL_FOR_SELECT;
       PREPARE stmt FROM @sql;     -- 預處理動態sql語句

       set @parm1 = USER_ID;        -- 傳遞sql動態參數
       set @parm2 = USER_NAME;

       EXECUTE stmt USING @parm1 , @parm2;     -- 執行sql語句
       deallocate prepare stmt;                -- 釋放prepare

       set @sql = SQL_FOR_SELECT;
       PREPARE stmt FROM @sql;                 -- 預處理動態sql語句

       set @parm1 = '2';                       -- 傳遞sql動態參數
       set @parm2 = 'lisi';

       EXECUTE stmt USING @parm1 , @parm2;     -- 執行sql語句
       deallocate prepare stmt;                -- 釋放prepare
END;

這樣，我們就可以真正的使用不同的參數（當然也可以在存儲過程中通過邏輯生成不同的參數）來使用動態sql了。

幾個注意：

• 　存儲動態SQL的值的變量不能是自定義變量，必須是用戶變量或者全局變量   如：set sql = 'xxx';  prepare stmt from sql;是錯的，正確為： set @sql = 'xxx';  prepare stmt from @sql;

• 即使 preparable_stmt 語句中的 ? 所代表的是一個字符串，你也不需要將 ? 用引號包含起來。

• 如果動態語句中用到了 in ,正常寫法應該這樣：select * from table_name t where t.field1 in (1,2,3,4,...);

• 則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')） 但是并沒有解析出來，在寫存儲過程in里面的列表用個傳入參數代入的時候，就需要用到如下方式：

1.使用find_in_set函數

select * from table_name t where find_in_set(t.field1,'1,2,3,4');

2.還可以比較笨實的方法，就是組裝字符串，然后執行

DROP PROCEDURE IF EXISTS photography.Proc_Test;
CREATE PROCEDURE photography.`Proc_Test`(param1 varchar(1000))
BEGIN
set @id = param1;
set @sel = 'select * from access_record t where t.ID in (';
set @sel_2 = ')';
set @sentence = concat(@sel,@id,@sel_2); -- 連接字符串生成要執行的SQL語句
prepare stmt from @sentence; -- 預編釋一下。 “stmt”預編釋變量的名稱，
execute stmt; -- 執行SQL語句
deallocate prepare stmt; -- 釋放資源
END;

mysql存儲過程中各種動態sql語句的用法就先給大家講到這里，對于其它相關問題大家想要了解的可以持續關注我們的行業資訊。我們的板塊內容每天都會捕捉一些行業新聞及專業知識分享給大家的。