mysql存儲過程中各種動態(tài)sql語句的用法

本文主要給大家簡單講講mysql存儲過程中各種動態(tài)sql語句的用法，相關專業(yè)術(shù)語大家可以上網(wǎng)查查或者找一些相關書籍補充一下，這里就不涉獵了，我們就直奔主題吧，希望mysql存儲過程中各種動態(tài)sql語句的用法這篇文章可以給大家?guī)硪恍嶋H幫助。                                                           

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

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的各個參數(shù)中間以","號分割
	SET @strsql = CONCAT('SELECT SUM(',_xnb,') INTO @tnum FROM btc_user_coin');
	## 預處理需要執(zhí)行的動態(tài)SQL，其中stmt是一個變量
	PREPARE stmt FROM @strsql;  
	## 執(zhí)行SQL語句
	EXECUTE stmt;  
	## 釋放掉預處理段
	deallocate prepare stmt;
	## 賦值給定義的變量
	SET _num = @tnum;
	SELECT _num
END;;

mysql 存儲過程中使用動態(tài)sql語句

Mysql 5.0 以后，支持了動態(tài)sql語句，我們可以通過傳遞不同的參數(shù)得到我們想要的值

這里介紹兩種在存儲過程中的動態(tài)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;       -- 預處理動態(tài)sql語句
      EXECUTE stmt ;                -- 執(zhí)行sql語句
      deallocate prepare stmt;      -- 釋放prepareEND;

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

但是：上述存儲過程中，我們必須在拼接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;        -- 預處理動態(tài)sql語句
       EXECUTE stmt ;                 -- 執(zhí)行sql語句
       deallocate prepare stmt;       -- 釋放prepare


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

　我們用call aa('1','zhangsan');來調(diào)用該存儲過程，第一次動態(tài)執(zhí)行，我們得到了‘張三’的信息，然后我們在第14,15行將USER_ID，USER_NAME改為lisi，我們希望得到李四的相關信息，可查出來的結(jié)果依舊是張三的信息，說明我們在拼接sql語句后，不能再改變參數(shù)了。

為了解決這種問題，下面介紹第二中方式：

2.set sql = （預處理的sql語句，可以是用concat拼接的語句，參數(shù)用 ？代替）

　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;     -- 預處理動態(tài)sql語句

       set @parm1 = USER_ID;        -- 傳遞sql動態(tài)參數(shù)
       set @parm2 = USER_NAME;

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

       set @sql = SQL_FOR_SELECT;
       PREPARE stmt FROM @sql;                 -- 預處理動態(tài)sql語句

       set @parm1 = '2';                       -- 傳遞sql動態(tài)參數(shù)
       set @parm2 = 'lisi';

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

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

幾個注意：

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

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

• 如果動態(tài)語句中用到了 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語句里有幾個參數(shù)，所以我試過這么寫

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

然后參數(shù)我傳的是  "'1','2','3'"  我以為程序會將我的動態(tài)sql解析出來（select * from user where user_id in ('1','2','3')） 但是并沒有解析出來，在寫存儲過程in里面的列表用個傳入?yún)?shù)代入的時候，就需要用到如下方式：

1.使用find_in_set函數(shù)

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

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

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); -- 連接字符串生成要執(zhí)行的SQL語句
prepare stmt from @sentence; -- 預編釋一下。 “stmt”預編釋變量的名稱，
execute stmt; -- 執(zhí)行SQL語句
deallocate prepare stmt; -- 釋放資源
END;

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