MySQL 存储过程初研究

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:
最近在做一个移动设备多类型登录的统一用户系统。其中记录用户资料的部分,因为涉及到更换设备的相同用户、同一个用户多类型同时具备的情况,所以想分辨出尽量少的用户去合理记录,就需要多次查询。于是决定研究一下 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 语句。
相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
1月前
|
存储 SQL NoSQL
|
2月前
|
存储 SQL 关系型数据库
MySql数据库---存储过程
MySql数据库---存储过程
41 5
|
2月前
|
存储 关系型数据库 MySQL
MySQL 存储过程返回更新前记录
MySQL 存储过程返回更新前记录
59 3
|
2月前
|
存储 SQL 关系型数据库
MySQL 存储过程错误信息不打印在控制台
MySQL 存储过程错误信息不打印在控制台
77 1
|
4月前
|
存储 关系型数据库 MySQL
Mysql表结构同步存储过程(适用于模版表)
Mysql表结构同步存储过程(适用于模版表)
52 0
|
4月前
|
存储 SQL 关系型数据库
MySQL 创建存储过程注意项
MySQL 创建存储过程注意项
45 0
|
5月前
|
存储 SQL 关系型数据库
(十四)全解MySQL之各方位事无巨细的剖析存储过程与触发器!
前面的MySQL系列章节中,一直在反复讲述MySQL一些偏理论、底层的知识,很少有涉及到实用技巧的分享,而在本章中则会阐述MySQL一个特别实用的功能,即MySQL的存储过程和触发器。
103 0
|
3天前
|
关系型数据库 MySQL 数据库
Python处理数据库:MySQL与SQLite详解 | python小知识
本文详细介绍了如何使用Python操作MySQL和SQLite数据库,包括安装必要的库、连接数据库、执行增删改查等基本操作,适合初学者快速上手。
45 15
|
3天前
|
关系型数据库 MySQL 数据库
数据库数据恢复—MYSQL数据库文件损坏的数据恢复案例
mysql数据库文件ibdata1、MYI、MYD损坏。 故障表现:1、数据库无法进行查询等操作;2、使用mysqlcheck和myisamchk无法修复数据库。
|
7天前
|
SQL 关系型数据库 MySQL
MySQL导入.sql文件后数据库乱码问题
本文分析了导入.sql文件后数据库备注出现乱码的原因,包括字符集不匹配、备注内容编码问题及MySQL版本或配置问题,并提供了详细的解决步骤,如检查和统一字符集设置、修改客户端连接方式、检查MySQL配置等,确保导入过程顺利。