MySQL 存储过程初研究

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 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;

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) { }
}