轉載自：http://www.open-open.com/lib/view/open1342179295542.html
最近在做一個移動設備多類型登錄的統一用戶系統。其中記錄用戶資料的部分，因為涉及到更換設備的相同用戶、同一個用戶多類型同時具備的情況，所以想分辨出盡量少的用戶去合理記錄，就需要多次查詢。于是決定研究一下 MySQL 存儲程序。
MySQL 現在是 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 ，其首頁有內容表格，里邊有“視圖和存儲程序”這一項，也就是第 19 章。
MySQL 中，會出現 Stored Programs 這個詞。但實際上它是存儲的程序之意思，包括存儲程序和觸發程序。存儲程序是 Stored Routines ，跟 Oracle 一樣，包括過程體（Procedures）和函數體（Functions）。過程體通過指定輸出類型參數將過程值帶出，用 CALL 語句加過程名和參數進行調用；函數體具有返回值，直接用函數名和參數調用。
聲明過程體請先參看我的例子。首先我為了記錄用戶登錄數據，制作了這個表（涉及公司機密的有關名稱已經更改）：
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 作為用戶的唯一標識。然后編寫了如下的存儲過程（涉及公司機密的有關名稱已經更改）：
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 用來聲明語句終止符的關鍵字。由于存儲程序之中會包含很多默認的終止符分號，所以在聲明存儲程序之前，需要將終止符改變成其它的。我使用的是 // ，這也是 MySQL 官方文檔示例中使用的。
2、參數的輸入輸出類型在參數名前邊。這和 Oracle 不同。
3、MySQL 存儲程序中變量類型的 VARCHAR 必須指定長度，這和 Oracle 有所不同。
4、程序內部的本地變量用 DECLARE 關鍵字聲明。
5、SQL SECURITY INVOKER 的意思是，由執行者進行執行權限確認。執行者需要對這個存儲程序所在的庫具有 EXECUTE 權限。
這意味著，GRANT 權限時候，如果想使用存儲程序，就不能再只賦予 SELECT, INSERT, UPDATE, DELETE 了，還需要增加 EXECUTE 。
6、注釋有二種方式，分別是 -- 的單行注釋，和 /* */ 的多行注釋。這和 Oracle 一樣。
所有的這些聲明語句內容，都可以參看 http://dev.mysql.com/doc/refman/5.5/en/create-procedure.html 。
可以通過 mysql.proc 表來查詢已有存儲過程的信息，常用字段為 db 和 name ，表示存儲過程的數據庫和名稱。
這里需要注意的是，不但程序員需要查詢 mysql.proc 表，執行存儲過程的時候，數據庫執行用戶也需要能夠查詢 mysql.proc 表。如果執行者沒有對 mysql.proc 的 SELECT 權限，則存儲過程執行時會產生錯誤：
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.
提供一個增加權限的語句參考：
GRANT SELECT ON mysql.proc TO username@'192.168.0%';
FLUSH PRIVILEGES;
接下來說一說通過 Java 程序調用 MySQL 存儲程序的方法。
MySQL 存儲程序基本遵循了 SQL 標準，于是只要不涉及 MySQL 特性的存儲程序，我們也就可以使用標準的 java.sql 包里邊關于存儲程序的各種類來實現調用。
1、獲取 Connection 對象
2、通過 Connection 的 prepareCall() 方法，生成 CallableStatement 對象。
3、通過 setInt(), setString() 一類的方法注冊輸入參數；通過 registerOutParameter() 注冊輸出參數。
4、用 execute() 方法執行語句。
5、通過 getInt(), getString() 一類的方法獲取輸出參數的值。
以下是我調用 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 調用存儲程序會有一定時間的延時。所以如果存儲程序內容密度不是很大，請考慮在實際環境中測試耗時，以決定使用存儲程序還是多次執行 SQL 語句。
轉自：http://blog.csdn.net/shanelooli/article/details/7743268