Mac安装使用Mysql教程(从零开始)
- 第一章 Mac安装MySQL
- 第二章 安装数据库管理软件DBeaver
- 第三章 DBeaver创建MySQL数据库
- 第四章 终端管理MySQL
- 第五章 MySQL基本操作之查询
- 第六章 MySQL基本操作之插入
- 第七章 MySQL基本操作之更新与删除
- 第八章 创建和操纵表
- 第九章 使用视图
- 第十章 使用存储过程
- 第十一章 管理事务处理
- 第十二章 游标
- 第十三章 MySQL学习问题及解决记录
第十章 使用存储过程
10.1 存储过程简介
存储过程就是为以后使用而保存的一条 或多条 SQL 语句。可将其视为批文件,虽然它们的作用不仅限于批处理。
不同数据库中的存储过程使用方式差别很大,且支持度也不太相同。Access和SQLite不支持存储过程,且MySQL 5之后才支持存储过程。要了解各数据库中存储过程的使用方式需看相关参考文档,本文着重对MySQL中的存储过程进行介绍。
10.2 存储过程优缺点
-
通过把处理封装在一个易用的单元中,可以简化复杂的操作(如前面 例子所述)。
-
由于不要求反复建立一系列处理步骤,因而保证了数据的一致性。如 果所有开发人员和应用程序都使用同一存储过程,则所使用的代码都是相同的。这一点的延伸就是防止错误。需要执行的步骤越多,出错的可能性就越大。防止错误保证了数据的一致性。
-
简化对变动的管理。如果表名、列名或业务逻辑(或别的内容)有变化,那么只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。这一点的延伸就是安全性。通过存储过程限制对基础数据的访问,减少了数据讹误(无意识的或别的原因所导致的数据讹误)的机会。
-
因为存储过程通常以编译过的形式存储,所以 DBMS 处理命令所需的 工作量少,提高了性能。
-
存在一些只能用在单个请求中的 SQL 元素和特性,存储过程可以使用 它们来编写功能更强更灵活的代码。
简而言之,存储过程具有简单,安全,高效的优点。但SQL代码的存储过程依然存在如下两个缺陷: -
不同 DBMS 中的存储过程语法有所不同。事实上,编写真正的可移植 存储过程几乎是不可能的。不过,存储过程的自我调用(名字以及数 据如何传递)可以相对保持可移植。因此,如果需要移植到别的 DBMS, 至少客户端应用代码不需要变动。
-
一般来说,编写存储过程比编写基本 SQL 语句复杂,需要更高的技能, 更丰富的经验。因此,许多数据库管理员把限制存储过程的创建作为 安全措施(主要受上一条缺陷的影响)。受DBMS限制,即使不可以编写自己的存储过程,但可以使用别的存储过程。
文中提到的优点也可以解释为什么要使用存储过程。
10.3 执行存储过程
尽管不同DBMS执行存储过程的具体语法有所不同,但结构大体相同,可总结为如下形式:
声明(var,DECLARE)参数;
执行(EXE或EXECUTE) OUT参数=存储过程名(‘IN参数’/:OUT参数);
SELECT 参数(检索输出存储过程中的数据);
以下通过一个具体例子介绍Oracle,SQL Server,MySQL三种数据库执行存储过程的具体操作。其中RetrunValue是从存储过程返回的值,在创建存储过程时用OUT标示。其中MySQL的命令是在终端输入并验证成功,其它的命令参考自SQL必知必会-中文-第4版,未经亲自验证。
Oracle
var ReturnValue NUMBER
EXEC MailingListCount(:ReturnValue);
SELECT ReturnValue;
SQL Server
DECLARE @ReturnValue INT
EXECUTE @ReturnValue=MailingListCount;
SELECT @ReturnValue;
MySQL
mysql> CALL MailingListCount(@ReturnValue);
mysql> SELECT @ReturnValue;
10.4 创建存储过程
还是通过具体例子分别介绍三种数据库创建存储过程的具体方式。其中MySQL的命令依然是在终端中执行并且经过亲自验证正确。其它的命令均摘抄自书本SQL必知必会-中文-第4版,我还未亲自实践检验过。
Oracle
CREATE PROCEDURE MailingListCount (
ListCount OUT INTEGER
)
IS
v_rows INTEGER;
BEGIN
SELECT COUNT(*) INTO v_rows
FROM Customers
WHERE NOT cust_email IS NULL;
ListCount := v_rows;
END;
SQL Server
CREATE PROCEDURE MailingListCount
AS
DECLARE @cnt INTEGER
SELECT @cnt = COUNT(*)
FROM Customers
WHERE NOT cust_email IS NULL;
RETURN @cnt;
MySQL
mysql> DELIMITER //
mysql> CREATE PROCEDURE MailingListCount (OUT v_rows INT)
BEGIN
SELECT COUNT(*) INTO v_rows
FROM Customers
WHERE NOT cust_email IS NULL;
END//
mysql> DELIMITER ;
mysql> CALL MailingListCount(@v_rows);
mysql> SELECT @v_rows;
关于MySQL创建存储过程的详细讲解请参见MySQL创建存储过程。
10.5 参考
[1] Ben Forta.SQL必知必会-中文-第4版
[2] .MySQL 8.0参考手册(pdf)
[3] .MySQL 8.0参考手册(web)
[4] 风亡小窝.mysql存储过程详细教程
[5] 量变决定质变.MySQL调用存储过程