MySQL 存儲過程初研究
最近在做一個移動設(shè)備多類型登錄的統(tǒng)一用戶系統(tǒng)。其中記錄用戶資料的部分,因為涉及到更換設(shè)備的相同用戶、同一個用戶多類型同時具備的情況,所以想分辨出盡量少的用戶去合理記錄,就需要多次查詢。于是決定研究一下 MySQL 存儲程序。
MySQL 現(xiàn)在是 5.5 或者 5.6 。因為存儲程序是 5.x 才具備的特性,所以放棄了具有中文文檔的 5.1 ,選擇可能會修改了很多問題的 5.5 。可惜這就造成我不得不去看在線英文文檔,因為我實在找不到 MySQL 5.5 的 PDF 版中文文檔…… 在線文檔地址是:http://dev.mysql.com/doc/refman/5.5/en/index.html ,其首頁有內(nèi)容表格,里邊有“視圖和存儲程序”這一項,也就是第 19 章。
MySQL 中,會出現(xiàn) Stored Programs 這個詞。但實際上它是存儲的程序之意思,包括存儲程序和觸發(fā)程序。存儲程序是 Stored Routines ,跟 Oracle 一樣,包括過程體(Procedures)和函數(shù)體(Functions)。過程體通過指定輸出類型參數(shù)將過程值帶出,用 CALL 語句加過程名和參數(shù)進行調(diào)用;函數(shù)體具有返回值,直接用函數(shù)名和參數(shù)調(diào)用。
聲明過程體請先參看我的例子。首先我為了記錄用戶登錄數(shù)據(jù),制作了這個表(涉及公司機密的有關(guān)名稱已經(jīng)更改):
DROP TABLE IF EXISTS test.MyTable; CREATE TABLE test.MyTable ( id INTEGER, type VARCHAR(16), name VARCHAR(16), passwd VARCHAR(16), updateTime DATETIME, deviceMacs VARCHAR(255), CONSTRAINT test_MyTable_pk PRIMARY KEY (id, type) );
通過 id 作為用戶的唯一標識。然后編寫了如下的存儲過程(涉及公司機密的有關(guān)名稱已經(jīng)更改):
DROP PROCEDURE IF EXISTS test.myProcedure; DELIMITER // CREATE PROCEDURE test.myProcedure ( IN vType VARCHAR(16), IN vName VARCHAR(16), IN vPasswd VARCHAR(16), IN vDeviceMac VARCHAR(12), OUT iId INTEGER ) SQL SECURITY INVOKER /* ********** ********** ********** ********** This is a database procedure for user login process. author:Shane Loo Li version:1.1.0, 2012-7-6 FridayNew history:1.1.0, 2012-7-6 FridayShane Loo LiNew ********** ********** ********** ********** */ BEGIN DECLARE iCount INTEGER; DECLARE vDeviceMacs VARCHAR(255); SELECT COUNT(1) INTO iCount FROM test.MyTable WHERE type = vType AND name = vName; -- 如果不存在傳入的用戶,則插入新登錄信息。 IF iCount = 0 THEN SELECT COUNT(1) INTO iCount FROM test.MyTable WHERE deviceMac LIKE CONCAT('%', vDeviceMac, '%'); IF iCount = 0 THEN INSERT INTO test.MyTable VALUES ( (SELECT MAX(id) + 1 FROM test.MyTable), vType, vName, vPasswd, NOW(), vDeviceMac); ELSE SELECT COUNT(1) INTO iCount FROM test.MyTable WHERE deviceMac LIKE CONCAT('%', vDeviceMac, '%') AND type = vType; IF iCount = 0 THEN SELECT id INTO iId FROM test.MyTable WHERE deviceMac LIKE CONCAT('%', vDeviceMac, '%') AND type = vType LIMIT 1; INSERT INTO test.MyTable VALUES ( iId, vType, vName, vPasswd, NOW(), vDeviceMac); ELSE INSERT INTO test.MyTable VALUES ( (SELECT MAX(id) + 1 FROM test.MyTable), vType, vName, vPasswd, NOW(), vDeviceMac); END IF; END IF; -- 如果存在傳入的用戶,則更新其記錄 ELSE SELECT id, deviceMacs INTO iId, vDeviceMacs FROM test.MyTable WHERE type = vType AND name = vName LIMIT 1; IF vDeviceMacs LIKE CONCAT('%', vDeviceMac, '%') THEN UPDATE test.MyTable SET passwd=vPasswd, updateTime=NOW() WHERE id = iId AND type = vType AND name = vName; ELSE UPDATE test.MyTable SET passwd=vPasswd, updateTime=NOW(), deviceMacs=CONCAT(vDeviceMacs, ',', vDeviceMac) WHERE id = iId AND type = vType AND name = vName; END IF; END IF; END // DELIMITER ;
這里對代碼進行一些解釋。
1、DELIMITER 是 MySQL 用來聲明語句終止符的關(guān)鍵字。由于存儲程序之中會包含很多默認的終止符分號,所以在聲明存儲程序之前,需要將終止符改變成其它的。我使用的是 // ,這也是 MySQL 官方文檔示例中使用的。
2、參數(shù)的輸入輸出類型在參數(shù)名前邊。這和 Oracle 不同。
3、MySQL 存儲程序中變量類型的 VARCHAR 必須指定長度,這和 Oracle 有所不同。
4、程序內(nèi)部的本地變量用 DECLARE 關(guān)鍵字聲明。
5、SQL SECURITY INVOKER 的意思是,由執(zhí)行者進行執(zhí)行權(quán)限確認。執(zhí)行者需要對這個存儲程序所在的庫具有 EXECUTE 權(quán)限。
這意味著,GRANT 權(quán)限時候,如果想使用存儲程序,就不能再只賦予 SELECT, INSERT, UPDATE, DELETE 了,還需要增加 EXECUTE 。
6、注釋有二種方式,分別是 -- 的單行注釋,和 /* */ 的多行注釋。這和 Oracle 一樣。
所有的這些聲明語句內(nèi)容,都可以參看 http://dev.mysql.com/doc/refman/5.5/en/create-procedure.html 。
可以通過 mysql.proc 表來查詢已有存儲過程的信息,常用字段為 db 和 name ,表示存儲過程的數(shù)據(jù)庫和名稱。
這里需要注意的是,不但程序員需要查詢 mysql.proc 表,執(zhí)行存儲過程的時候,數(shù)據(jù)庫執(zhí)行用戶也需要能夠查詢 mysql.proc 表。如果執(zhí)行者沒有對 mysql.proc 的 SELECT 權(quán)限,則存儲過程執(zhí)行時會產(chǎn)生錯誤:
java.sql.SQLException: User does not have access to metadata required to determine stored procedure parameter types. If rights can not be granted, configure connection with "noAccessToProcedureBodies=true" to have driver generate parameters that represent INOUT strings irregardless of actual parameter types.
提供一個增加權(quán)限的語句參考:
GRANT SELECT ON mysql.proc TO username@'192.168.0%'; FLUSH PRIVILEGES;
接下來說一說通過 Java 程序調(diào)用 MySQL 存儲程序的方法。
MySQL 存儲程序基本遵循了 SQL 標準,于是只要不涉及 MySQL 特性的存儲程序,我們也就可以使用標準的 java.sql 包里邊關(guān)于存儲程序的各種類來實現(xiàn)調(diào)用。
1、獲取 Connection 對象
2、通過 Connection 的 prepareCall() 方法,生成 CallableStatement 對象。
3、通過 setInt(), setString() 一類的方法注冊輸入?yún)?shù);通過 registerOutParameter() 注冊輸出參數(shù)。
4、用 execute() 方法執(zhí)行語句。
5、通過 getInt(), getString() 一類的方法獲取輸出參數(shù)的值。
以下是我調(diào)用 MySQL 存儲過程的一段示例程序。其中獲取 Connection 對象的方法,是來自于自己做的連接池。
Connection conn = (Connection) line.use(); CallableStatement cs = null; int result = -1; try { cs = conn.prepareCall("{call amdream.testProcedure(?, ?)}"); cs.setInt(1, 1099); cs.registerOutParameter(2, Types.INTEGER); cs.execute(); result = cs.getInt(2); } catch (Exception ex) { ex.printStackTrace(); } finally { try { conn.close(); } catch (Exception ex) { } }
用 Java 調(diào)用存儲程序會有一定時間的延時。所以如果存儲程序內(nèi)容密度不是很大,請考慮在實際環(huán)境中測試耗時,以決定使用存儲程序還是多次執(zhí)行 SQL 語句。
轉(zhuǎn)自:http://blog.csdn.net/shanelooli/article/details/7743268