存储过程和函数

存储过程和函数能够将复杂的SQL逻辑封装在一起，应用程序无须关注存储过程和函数内部复杂的SQL逻辑，而只需要简单地调用存储过程和函数即可。

存储过程

概述

含义：存储过程的英文是 Stored Procedure 。它的思想很简单，就是一组经过 预先编译 的 SQL 语句的封装。

执行过程：存储过程预先存储在 MySQL 服务器上，需要执行的时候，客户端只需要向服务器端发出调用存储过程的命令，服务器端就可以把预先存储好的这一系列 SQL 语句全部执行。

好处：

1. 简化操作，提高了sql语句的重用性，减少了开发程序员的压力
2. 减少操作过程中的失误，提高效率
3. 减少网络传输量（客户端不需要把所有的 SQL 语句通过网络发给服务器）
4. 减少了 SQL 语句暴露在网上的风险，也提高了数据查询的安全性

与视图，函数的对比：

存储过程和视图有着同样的优点，清晰、安全，还可以减少网络传输量。

不过它和视图不同，视图是虚拟表 ，通常不对底层数据表直接操作，而存储过程是程序化的 SQL，可以直接操作底层数据表 ，相比于面向集合的操作方式，能够实现一些更复杂的数据处理。

一旦存储过程被创建出来，使用它就像使用函数一样简单，我们直接通过调用存储过程名即可。相较于函数，存储过程没有返回值。

参数分类

存储过程的参数有以下几种

参数	作用
IN	输入参数
OUT	输出参数
INOUT	该参数既可以用作输入也可以用作输出

创建

创建存储过程的语法：

CREATE PROCEDURE 存储过程名
(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]
BEGIN
存储过程体
END

语法说明：

1. 形参列表

(IN|OUT|INOUT 参数名 参数类型,...)

参数前面的符号的意思 ： 表示该参数是输入参数还是输出参数，或者是输入和输出参数

1. 形参类型可以是 MySQL数据库中的任意类型。
2. characteristics 表示创建存储过程时指定的对存储过程的约束条件，其取值信息如下：

• LANGUAGE SQL
• [NOT] DETERMINISTIC
• { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
• SQL SECURITY { DEFINER | INVOKER }
• COMMENT 'string'

约束条件参数	说明
LANGUAGESQL	存储过程执行体由sql语句组成，当前系统支持的语言为 SQL
[NOT] DETERMINISTIC	当前存储过程的结果是否确定，DETERMINISTIC 表示存储过程的结果是确定的，也就是说在执行存储过程的时候；相同的输入都会得到相同的结果 ，NOT DETERNINISTIC 表示当前存储过程执行的结果是不确定的，相同的输入也可能有不同的输出
{CONTAINS SQL	NOSQL	READS SQL DATA	MODIFIES SQL DATA}	当前存储过程中受到的子程序限制：CONTAINS SQL :当前存储农户过程子程序中包含 SQL 语句，但并不包含读写数据的 sql 语句； NO SQL ：当前存储过程中给不包含任何 SQL 子句 READS SQL DATA 当前存储过程中包含读取 sql 的子句
SQL SECURITY{DEFINER	INVOKER}	表示当前用户对于该存储过程的权限：DEFINER : 当前用户可以定义该存储过程；INVOKER :当前用户可以调用该存储过程
COMMENT 'String'	注释信息用来描述对应的存储过程

1. 存储过程体中可以有多条 SQL 语句，如果仅仅一条SQL 语句，则可以省略 BEGIN 和 END；编写存储过程并不是一件简单的事情，可能存储过程中需要复杂的 SQL 语句。

注意事项：

• • BEGIN…END：BEGIN…END 中间包含了多个语句，每个语句都以（;）号为结束符。
  • DECLARE：DECLARE 用来声明变量，使用的位置在于 BEGIN…END 语句中间，而且需要在其他语句使用之前进行变量的声明
  • SET：赋值语句，用于对变量进行赋值。
  • SELECT… INTO：把从数据表中查询的结果存放到变量中，也就是为变量赋值。、

在 console 环境中 用 delimeter 定义新的结束符号 （重新定义 结束符 解决存储过程中大量 ; 导致冲突的问题）

DELIMITER $
CREATE PROCEDURE 存储过程名
(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]
BEGIN
sql语句1;
sql语句2;
END $
DELIMITER ;

创建并调用一个单表查询的存储过程：

create procedure select_all_student()
begin
    select id, name, sex, birth, department, address from student;
end;
call select_all_student();

创建一个带有输出参数的存储过程

通过存储过程输出考试的最低分

# 创建存储过程输出考试成绩的最低分
create procedure print_min_score(out minScore int)
begin
    select min(grade) into minScore from score;
end;
# 调用存储过程的时候定义这个变量用于接收
call print_min_score(@minScore);
# 查询结果
select @minScore;

创建带有入参的存储过程

带输入参数的存储过程输出参数：学员id 查询当前学生的总成绩

# 带输入参数的存储过程输出参数：学员id 查询当前学生的总成绩
create procedure print_sum_grade(in stu_id int,out sum_grade int)
begin
    select sum(grade) into sum_grade from score where score.stu_id = stu_id;
end;
# 调用这个存储过程
call print_sum_grade(904,@sum_score);
select @sum_score;

创建带有输入输出 inout 参数的存储过程

# 创建存储过程show_student_department()，查询某个学员的院系，并用INOUT参数“stu_x”输
# 入学员姓名，输出院系名称。
create procedure print_dept_student(inout stu_info varchar(20))
begin
    select student.department into stu_info from student where student.name = stu_info;
end;
# 定义变量
set @stu_info = '王二麻子';
call print_dept_student(@stu_info);
select @stu_info;

代码举例：创建存储过程，实现累加运算，计算 1+2+…+n 等于多少

create procedure add_all(in maxN int)
begin
#     声明局部变量 i 用于比较和变量自增
    declare i int default 1;
#       声明局部变量 sum 用于统计累加和
    declare sum int default 0;
    while i <= maxN do
        set sum = sum + i;
        set i = i + 1;
    end while;
    select sum;
end;
​
call add_all(100);

存储函数

函数的语法结构：

CREATE FUNCTION 函数名(参数名 参数类型,...)
RETURNS 返回值类型
[characteristics ...]
BEGIN
函数体 #函数体中肯定有 RETURN 语句
END

说明：

1. 参数列表：指定参数为IN、OUT或INOUT只对PROCEDURE是合法的，FUNCTION中总是默认为IN 参数。
2. RETURNS type 语句表示函数返回数据的类型；RETURNS子句只能对FUNCTION做指定，对函数而言这是强制 的。它用来指定函数的返回类型，而且函数体必须包含一个RETURN value 语句。
3. characteristic 创建函数时指定的对函数的约束。取值与创建存储过程时相同，这里不再赘述。
4. 函数体也可以用BEGIN…END来表示SQL代码的开始和结束。如果函数体只有一条语句，也可以省略BEGIN…END。

调用存储函数：

SELECT 函数名(参数列表) ;

创建

有参数的 存储函数

# 创建存储函数 用于查询对应学生的部门名称
create function get_department_by_name(name varchar(30))
    returns varchar(30)
#     函数有确定的返回结果
    deterministic
begin
    return (select student.department from student where student.name = name);
end;
# 直接调用
select get_department_by_name('王二麻子');
# 定义变量，调用函数
set @stu_name = '张老二';
select get_department_by_name(@stu_name);

存储过程与存储函数对比

	关键字	调用语句	返回值	应用场景
过程	PROCEDURE	CALL 存储过程()	理解为有0个或 多个	一般用于更新
函数	FUNCTION	SELECT 函数 ()	只能是一个	一般用于查询结果为一个值并返回时

存储函数可以放在查询语句中使用，存储过程不行。反之，存储过程的功能更加强大，包括 能够执行对表的操作（比如创建表，删除表等）和事务操作，这些功能是存储函数不具备的。

存储过程与函数的操作

查看

在创建存储过程或者函数之后：

用户可以使用SHOW STATUS语句或SHOW CREATE语句来 查看，也可直接从系统的information_schema数据库中查询。这里介绍3种方法。

1. 使用SHOW CREATE语句查看存储过程和函数的创建信息；

语法结构：

SHOW CREATE {PROCEDURE | FUNCTION} 存储过程名或函数名

展示存储过程的创建信息

show create procedure add_all;

1. 使用 SHOW STATUS 语句查看存储过程和函数的状态信息

语法结构：

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

这个语句返回子程序的特征，如数据库、名字、类型、创建者及创建和修改日期。

同时支持 like 模糊匹配查询

show function status like 'get_department_by_name';
show function status like '%get%';

1. 从infomation_schema.Routines表中查看存储过程和函数的信息

MySQL中存储过程和函数的信息存储在information_schema数据库下的Routines表中。可以通过 查询该表的记录来查询存储过程和函数的信息。其基本语法形式如下：

SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME='存储过程或函数的
名' [AND ROUTINE_TYPE = {'PROCEDURE|FUNCTION'}]

select *
from information_schema.ROUTINES
where ROUTINE_NAME = 'add_count'
and ROUTINE_TYPE = 'PROCEDURE';

说明：如果在MySQL数据库中存在存储过程和函数 名称相同 的情况，最好指定 ROUTINE_TYPE 查询条件来指明查询的是存储过程还是函数

修改

修改存储过程和存储函数，指的是修改存储过程或者被存储函数的约束 存储过程和存储函数本身的功能（内容） 保持不变

ALTER {PROCEDURE | FUNCTION} 存储过程或函数的名 [characteristic ...]

其中 ： characteristic 指的是存储过程或函数的特性，通常用于做权限约束，存储过程主体描述，声明主体函数是否使用 sql ，添加注释等

修改案例

查询修改前的存储过程状态（数据权限）

show procedure status like 'addStudent';

发现当前默认的 SQL SECURITY 级别是 DEFINER

现在修改这个级别并且添加注释

alter procedure addStudent modifies sql data sql security definer comment '修改存储过程执行权限';

删除

语法与删除其他内容基本一致

DROP {PROCEDURE | FUNCTION} [IF EXISTS] 存储过程或函数的名

存储过程优缺点

优点：

1. 存储过程可以一次编译多次使用。存储过程只在创建时进行编译，之后的使用都不需要重新编译， 这就提升了 SQL 的执行效率。
2. 可以减少开发工作量。将代码封装成模块，实际上是编程的核心思想之一，这样可以把复杂的问题拆解成不同的模块，然后模块之间可以重复使用 ，在减少开发工作量的同时，还能保证代码的结构清晰。
3. 存储过程的安全性强。我们在设定存储过程的时候可以设置对用户的使用权限 ，这样就和视图一样具有较强的安全性。
4. 可以减少网络传输量。因为代码封装到存储过程中，每次使用只需要调用存储过程即可，这样就减少了网络传输量。
5. 良好的封装性。在进行相对复杂的数据库操作时，原本需要使用一条一条的 SQL 语句，可能要连接多次数据库才能完成的操作，现在变成了一次存储过程，只需要连接一次即可 。

缺点：

存储过程虽然有诸如上面的好处，但缺点也是很明显的。

1. 可移植性差。存储过程不能跨数据库移植，比如在 MySQL、Oracle 和 SQL Server 里编写的存储过程，在换成其他数据库时都需要重新编写。
2. 调试困难。只有少数 DBMS 支持存储过程的调试。对于复杂的存储过程来说，开发和维护都不容易。虽然也有一些第三方工具可以对存储过程进行调试，但要收费。
3. 存储过程的版本管理很困难。比如数据表索引发生变化了，可能会导致存储过程失效。我们在开发软件的时候往往需要进行版本管理，但是存储过程本身没有版本控制，版本迭代更新的时候很麻烦。
4. 它不适合高并发的场景。高并发的场景需要减少数据库的压力，有时数据库会采用分库分表的方式，而且对可扩展性要求很高，在这种情况下，存储过程会变得难以维护， 增加数据库的压力 ， 显然就不适用了。