MySQL 存儲過程初研究

最近在做一個移動設備多類型登錄的統一用戶系統。其中記錄用戶資料的部分，因為涉及到更換設備的相同用戶、同一個用戶多類型同時具備的情況，所以想分辨出盡量少的用戶去合理記錄，就需要多次查詢。于是決定研究一下 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)
);

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 Friday  New
history:    1.1.0, 2012-7-6 Friday  Shane Loo Li    New
** ** ** ** /
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 ;</pre> 
這里對代碼進行一些解釋。

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