簡單的存儲過程各個關鍵字的用法:
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;
