MySql 存储过程和函数

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

1.存储过程概述

• 含义：存储过程的英文是 Stored Procedure 。它的思想很简单，就是一组经过 预先编译 的 SQL 语句的封装
• 执行过程：存储过程预先存储在 MySQL 服务器上，需要执行的时候，客户端只需要向服务器端发出调用存储过程的命令，服务器端就可以把预先存储好的这一系列 SQL 语句全部执行。

1.1 分类

存储过程的参数类型可以是IN、OUT和INOUT。根据这点分类如下：

1. 没有参数（无参数无返回）
2. 仅仅带 IN 类型（有参数无返回）
3. 仅仅带 OUT 类型（无参数有返回）
4. 既带 IN 又带 OUT（有参数有返回）
5. 带 INOUT（有参数有返回）

注意：IN、OUT、INOUT 都可以在一个存储过程中带多个。

2.创建存储过程

2.1 语法结构

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

这个结构就类似于Java中的方法定义了

修饰符 返回类型 方法名(参数类型 参数名,...)
{
	方法体;
}
1
2
3
4

语法结构说明：

• 1. 参数前面的符号的意思
     
• 2. 形参类型可以是 MySQL数据库中的任意类型
• 3. characteristics 表示创建存储过程时指定的对存储过程的约束条件，其取值信息如下：
• 4. 存储过程体中可以有多条 SQL 语句，如果仅仅一条SQL 语句，则可以省略 BEGIN 和 END编写存储过程并不是一件简单的事情，可能存储过程中需要复杂的 SQL 语句
• 5. 需要设置新的结束标记, DELIMITER 新的结束标记

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

2.2 案例代码

案例1：创建存储过程 get_max_grade(),查看考试成绩的最高分

DELIMITER //
CREATE PROCEDURE get_max_grade()
LANGUAGE SQl # 存储过程由SQL组成
NOT DETERMINISTIC # 结果不确定
CONTAINS SQL # 当前存储过程子程序包含SQL语句
SQL SECURITY DEFINER # 当前创建的用户才能执行
COMMENT '查看最高成绩' # 注释
BEGIN
	select max(grade) from score;
END //
DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11

案例2:创建存储过程get_min_grade()，查看“score”表的最低薪资值。并将最低薪资通过OUT参数“ms”输出

DELIMITER //
CREATE PROCEDURE get_min_grade(OUT ms int)
BEGIN
select min(grade) into ms from score;
END //
DELIMITER ;
1
2
3
4
5
6

调用过程

CALL get_min_grade(@ms);
1

查看变量

SELECT @ms ;
1

案例3：创建存储过程get_someone_sumgrade()，查看“score”表的某个学员的总成绩，并用IN参数stuid输入员工姓名

DELIMITER //
CREATE PROCEDURE get_someone_sumgrade(IN stuid int)
BEGIN
	select sum(grade) from score where stu_id = stuid;
END //
DELIMITER ;
1
2
3
4
5
6

调用过程

CALL get_someone_sumgrade(906) ;
1

案例4：创建存储过程get_someone_sumgrade2()，查看“score”表的某个学员的总成绩，并用IN参数stuid输入员工姓名.并用OUT参数sumgrade来输出总成绩

DELIMITER //
CREATE PROCEDURE get_someone_sumgrade2(IN stuid int,OUT sumgrade int)
BEGIN
	select sum(grade) into sumgrade from score where stu_id = stuid;
END //
DELIMITER ;
1
2
3
4
5
6

调用过程

CALL get_someone_sumgrade2(901,@sumgrade);
1

查看变量

SELECT @sumgrade ;
1

案例5：创建存储过程show_student_department()，查询某个学员的院系，并用INOUT参数“stu_x”输入学员姓名，输出院系名称

DELIMITER //
CREATE PROCEDURE show_student_department(INOUT stu_x VARCHAR(20))
BEGIN
	select department into stu_x from student where name = stu_x;
END //
DELIMITER ;
1
2
3
4
5
6

调用过程

SET @stu_x := '张三';
CALL show_student_department(@stu_x);
1
2

查看变量

SELECT @stu_x ;
1

3.调用存储过程

3.1 调用的格式

CALL 存储过程名称(实参列表);
1

针对存储过程不同类型的参数，调用的方式也有区别：

• 调用in模式的参数

  CALL sp1('值') ;
  1

• 调用out模式的参数

  SET @paras ;
  CALL sp1(@paras) ;
  SELECT @paras ;
  1
  2
  3

• 调用INOUT模式的参数

  SET @paras = 值;
  CALL sp1(@paras) ;
  SELECT @paras ;
  1
  2
  3

如果你用的是 Navicat 工具，那么在编写存储过程的时候，Navicat 会自动设置 DELIMITER 为其他
符号，我们不需要再进行 DELIMITER 的操作。

4.存储函数

4.1 语法结构

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

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

4.2 调用存储过程

SELECT 函数名(参数列表) ;
1

4.3 对比过程和函数