面向MySQL存储过程编程（详细）

所有知识体系文章，GitHub已收录，欢迎老板们前来Star！

GitHub地址： https://github.com/Ziphtracks/JavaLearningmanual

搜索关注微信公众号“码出Offer”，送你学习福利资源！

MySQL存储过程

---

一、存储过程

1.1 什么是存储过程

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升

1.2 数据库存储过程程序

当我们了了解存储过程是什么之后，就需要了解数据库中存在的这三种类型的数据库存储类型程序，如下：

• 存储过程： 存储过程是最常见的存储程序，存储过程是能够接受输入和输出参数并且能够在请求时被执行的程序单元。
• 存储函数： 存储函数和存储过程很相像，但是它的执行结果会返回一个值。最重要的是存储函数可以被用来充当标准的 SQL 语句，允许程序员有效的扩展 SQL 语言的能力。
• 触发器： 触发器是用来响应激活或者触发数据库行为事件的存储程序。通常，触发器用来作为数据库操作语言的响应而被调用，触发器可以被用来作为数据校验和自动反向格式化。

注意： 其他的数据库提供了别的数据存储程序，包括包和类。目前MySQL不提供这种结构。

1.3 为什么要使用存储程序

虽然目前的开发中存储程序我们使用的并不是很多，但是不一定就否认它。其实存储程序会为我们使用和管理数据库带来了很多优势：

• 使用存储程序更加安全。
• 存储程序提供了一种数据访问的抽象机制，它能够极大的改善你的代码在底层数据结构演化过程中的易维护性。
• 存储程序可以降低网络拥阻，因为属于数据库服务器的内部数据，这相比在网上传输数据要快的多。
• 存储程序可以替多种使用不同构架的外围应用实现共享的访问例程，无论这些构架是基于数据库服务器外部还是内部。
• 以数据为中心的逻辑可以被独立的放置于存储程序中，这样可以为程序员带来更高、更为独特的数据库编程体验。
• 在某些情况下，使用存储程序可以改善应用程序的可移植性。（在另外某些情况下，可移植性也会很差！）

这里我大致解释一下上述几种使用存储程序的优势：

我们要知道在Java语言中，我们使用数据库与Java代码结合持久化存储需要引入JDBC来完成。会想到JDBC，我们是否还能想起SQL注入问题呢？虽然使用PreparedStatement解决SQL注入问题，那就真的是绝对安全吗？不，它不是绝对安全的。

这时候分析一下数据库与Java代码的连接操作流程。在BS结构中，一般都是浏览器访问服务器的，再由服务器发送SQL语句到数据库，在数据库中对SQL语句进行编译运行，最后把结果通过服务器处理再返回浏览器。在此操作过程中，浏览器对服务器每发送一次对数据库操作的请求就会调用对应的SQL语句编译和执行，这是一件十分浪费性能的事情，性能下降 了就说明对数据库的操作效率低 了。

还有一种可能是，在这个过程中进行发送传输的SQL语句是对真实的库表进行操作的SQL语句，如果在发送传输的过程中被拦截了，一些不法分子会根据他所拦截的SQL语句推断出我们数据库中的库表结构，这是一个很大的安全隐患 。

关于可维护性的提高，这里模拟一个场景。通常数据库在公司中是由DBA来管理的，如果管理数据库多年的DBA辞职了，此时数据库会被下一任DBA来管理。这里时候问题来了，数据库中这么多的数据和SQL语句显然对下一任管理者不太友好。就算管理多年的DBA长时间不操作查看数据库也会忘记点什么东西。所以，我们在需要引入存储程序来进行SQL语句的统一编写和编译，为维护提供了便利 。（其实我觉得这个例子并不生动合理，但是为了大家能理解，请体谅！）

讲了很多存储程序的优势演变过程，其核心就是： 需要将编译好的一段或多段SQL语句放置在数据库端的存储程序中，以便解决以上问题并方便开发者直接调用。

二、存储过程的使用步骤

2.1 存储过程的开发思想

存储过程时数据库的一个重要的对象，可以封装SQL语句集，可以用来完成一些较复杂的业务逻辑，并且可以入参（传参）、出参（返回参数），这里与Java中封装方式十分相似。

而且创建时会预先编译后保存，开发者后续的调用都不需要再次编译。

2.2 存储过程的优缺点

存储过程使用的优缺点其实在1.3中的优势中说到了。这里我简单罗列一下存储过程的优点与缺点。

• 优点：
  • 在生产环境下，可以通过直接修改存储过程的方式修改业务逻辑或bug，而不用重启服务器。
  • 执行速度快，存储过程经过编译之后会比单独一条一条编译执行要快很多。
  • 减少网络传输流量。
  • 便于开发者或DBA使用和维护。
  • 在相同数据库语法的情况下，改善了可移植性。
• 缺点：
  • 过程化编程，复杂业务处理的维护成本高。
  • 调试不便。
  • 因为不同数据库语法不一致，不同数据库之间可移植性差。

2.3 MySQL存储过程的官方文档

英语好或者有能力的小伙伴可以去参考一下官方文档。如果不参考官方文档，没关系，我在下面也会详细讲述MySQL存储过程的各个知识点。

https://dev.mysql.com/doc/refman/5.6/en/preface.html

2.3 存储过程的使用语法

create PROCEDURE 过程名( in|out|inout 参数名 数据类型 , ...)
begin
	sql语句;
end;
call 过程名(参数值);

in是定义传入参数的关键字。out是定义出参的关键字。inout是定义一个出入参数都可以的参数。如果括号内什么都不定义，就说明该存储过程时一个无参的函数。在后面会有详细的案例分析。

注意： SQL语句默认的结束符为;，所以在使用以上存储过程时，会报1064的语法错误。我们可以使用DELIMITER关键字临时声明修改SQL语句的结束符为//，如下：

-- 临时定义结束符为"//"
DELIMITER //
create PROCEDURE 过程名( in|out 参数名 数据类型 , ...)
begin
	sql语句;
end//
-- 将结束符重新定义回结束符为";"
DELIMITER ;

例如： 使用存储过程来查询员工的工资（无参）

注意： 如果在特殊的必要情况下，我们还可以通过delimiter关键字将;结束符声明回来使用，在以下案例中我并没有这样将结束符声明回原来的;，在此请大家注意~

为什么我在这里提供了drop（删除）呢？

是因为我们在使用的时候如果需要修改存储过程中的内容，我们需要先删除现有的存储过程后，再creat重新创建。

# 声明结束符为//
delimiter //

# 创建存储过程(函数)
create procedure se()
begin
    select salary from employee;
end //

# 调用函数
call se() //

# 删除已存在存储过程——se()函数
drop procedure if exists se //

三、存储过程的变量和赋值

3.1 局部变量

声明局部变量语法： declare var_name type [default var_value];

赋值语法：

注意： 局部变量的定义，在begin/end块中有效。

使用set为参数赋值

# set赋值

# 声明结束符为//
delimiter //
    
# 创建存储过程
create procedure val_set()
begin
    # 声明一个默认值为unknown的val_name局部变量
    declare val_name varchar(32) default 'unknown';
	# 为局部变量赋值
    set val_name = 'Centi';
	# 查询局部变量
    select val_name;
end //

# 调用函数
call val_set() //

使用into接收参数

delimiter //
create procedure val_into()
begin
    # 定义两个变量存放name和age
    declare val_name varchar(32) default 'unknown';
    declare val_age int;
    # 查询表中id为1的name和age并放在定义的两个变量中
    select name,age into val_name,val_age from employee where id = 1;
    # 查询两个变量
    select val_name,val_age;
end //

call val_into() //

3.2 用户变量

用户自定义用户变量，当前会话（连接）有效。与Java中的成员变量相似。

• 语法： @val_name
• 注意： 该用户变量不需要提前声明，使用即为声明。

delimiter //
create procedure val_user()
begin
    # 为用户变量赋值
    set @val_name = 'Lacy';
end //

# 调用函数
call val_user() //

# 查询该用户变量
select @val_name //

3.3 会话变量

会话变量是由系统提供的，只在当前会话（连接）中有效。

语法： @@session.val_name

# 查看所有会话变量
show session variables;
# 查看指定的会话变量
select @@session.val_name;
# 修改指定的会话变量
set @@session.val_name = 0;

这里我获取了一下所有的会话变量，大概有500条会话变量的记录。等我们深入学习MySQL后，了解了各个会话变量值的作用，可以根据需求和场景来修改会话变量值。

delimiter //
create procedure val_session()
begin
    # 查看会话变量
    show session variables;
end //

call val_session() //

3.4 全局变量

全局变量由系统提供，整个MySQL服务器内有效。

语法： @@global.val_name

# 查看全局变量中变量名有char的记录
show global variables like '%char%' //
# 查看全局变量character_set_client的值
select @@global.character_set_client //

3.5 入参出参

入参出参的语法我们在文章开头已经提过了，但是没有演示，在这里我将演示一下入参出参的使用。

语法： in|out|inout 参数名 数据类型 , ...

in定义出参；out定义入参；inout定义出参和入参。

出参in

使用出参in时，就是需要我们传入参数，在这里可以对参入的参数加以改变。简单来说in只负责传入参数到存储过程中，类似Java中的形参。

delimiter //
create procedure val_in(in val_name varchar(32))
begin
    # 使用用户变量出参(为用户变量赋参数值)
    set @val_name1 = val_name;
end //

# 调用函数
call val_in('DK') //

# 查询该用户变量
select @val_name1 //

入参out

在使用out时，需要传入一个参数。而这个参数相当于是返回值，可以通过调用、接收来获取这个参数的内容。简单来说out只负责作返回值。

delimiter //
# 创建一个入参和出参的存储过程
create procedure val_out(in val_id int