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

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

这里对代码进行一些解释。
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