不是第一

存储过程&存储函数

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

存储过程概述

理解

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

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

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

它和视图有着同样的优点，清晰、安全，还可以减少网络传输量。不过它和视图不同，视图是虚拟表，通常不对底层数据表直接操作，而存储过程是程序化的 SQL，可以直接操作底层数据表，相比于面向集合的操作方式，能够实现一些更复杂的数据处理。

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

分类

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

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

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

创建存储过程

语法分析

语法：

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

END

类似于Java中的方法：

修饰符 返回类型 方法名(参数类型 参数名,...){

	方法体;
}

说明：

  1. 参数前面的符号的意思

  • IN：当前参数为输入参数，也就表示入参；

    存储过程只是读取这个参数的值。如果没有定义参数种类，默认就是 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'

  • LANGUAGE SQL：说明存储过程执行体是由SQL语句组成的，当前系统支持的语言为SQL。

  • [NOT] DETERMINISTIC：指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为NOT DETERMINISTIC。

  • { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }：指明子程序使用SQL语句的限制.

    1. CONTAINS SQL表示当前存储过程的子程序包含SQL语句，但是并不包含读写数据的SQL语句；
    2. NO SQL表示当前存储过程的子程序中不包含任何SQL语句；
    3. READS SQL DATA表示当前存储过程的子程序中包含读数据的SQL语句；
    4. MODIFIES SQL DATA表示当前存储过程的子程序中包含写数据的SQL语句。
    5. 默认情况下，系统会指定为CONTAINS SQL。

  • SQL SECURITY { DEFINER | INVOKER }：执行当前存储过程的权限，即指明哪些用户能够执行当前存储过程

    1. DEFINER表示只有当前存储过程的创建者或者定义者才能执行当前存储过程；
    2. INVOKER表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。
    3. 如果没有设置相关的值，则MySQL默认指定值为DEFINER。

  • COMMENT 'string'：注释信息，可以用来描述存储过程。

  1. 存储过程体中可以有多条 SQL 语句，如果仅仅一条SQL 语句，则可以省略 BEGIN 和 END

编写存储过程并不是一件简单的事情，可能存储过程中需要复杂的 SQL 语句。

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

因为MySQL默认的语句结束符号为分号‘;’。为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER改变存储过程的结束符。

比如：“DELIMITER //”语句的作用是将MySQL的结束符设置为//，并以“END //”结束存储过程。存储过程定义完毕之后再使用“DELIMITER ;”恢复默认结束符。DELIMITER也可以指定其他符号作为结束符。

当使用DELIMITER命令时，应该避免使用反斜杠（‘\’）字符，因为反斜线是MySQL的转义字符。

示例：

DELIMITER $

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

END $
代码举例

举例1：创建存储过程select_all_data()，查看 emps 表的所有数据

DELIMITER $

CREATE PROCEDURE select_all_data()
BEGIN
	SELECT * FROM emps;
	
END $

DELIMITER ;

举例2：创建存储过程avg_employee_salary()，返回所有员工的平均工资

DELIMITER //

CREATE PROCEDURE avg_employee_salary ()
BEGIN
	SELECT AVG(salary) AS avg_salary FROM emps;
END //

DELIMITER ;

举例3：创建存储过程show_max_salary()，用来查看“emps”表的最高薪资值。

CREATE PROCEDURE show_max_salary()
	LANGUAGE SQL
	NOT DETERMINISTIC
	CONTAINS SQL
	SQL SECURITY DEFINER
	COMMENT '查看最高薪资'
	BEGIN
		SELECT MAX(salary) FROM emps;
	END //

DELIMITER ;

举例4：创建存储过程show_min_salary()，查看“emps”表的最低薪资值。并将最低薪资通过OUT参数“ms”输出

DELIMITER //

CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)
	BEGIN
		SELECT MIN(salary) INTO ms FROM emps;
	END //

DELIMITER ;

举例5：创建存储过程show_someone_salary()，查看“emps”表的某个员工的薪资，并用IN参数empname输入员工姓名。

DELIMITER //

CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))
	BEGIN
		SELECT salary FROM emps WHERE ename = empname;
	END //

DELIMITER ;

举例6：创建存储过程show_someone_salary2()，查看“emps”表的某个员工的薪资，并用IN参数empname输入员工姓名，用OUT参数empsalary输出员工薪资。

DELIMITER //

CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(20),OUT empsalary DOUBLE)
	BEGIN
		SELECT salary INTO empsalary FROM emps WHERE ename = empname;
	END //

DELIMITER ;

举例7：创建存储过程show_mgr_name()，查询某个员工领导的姓名，并用INOUT参数“empname”输入员工姓名，输出领导的姓名。

调用存储过程

调用格式

存储过程有多重调用方法。存储过程必须使用CALL语句调用，并且存储过程和数据库相关，如果要执行其他数据库中的存储过程，需要指定数据库名，例如CALL dbname.procname。

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

格式：

1、调用in模式的参数：

CALL sp1('值');

2、调用out模式的参数：

SET @name;
CALL sp1(@name);
SELECT @name;

3、调用inout模式的参数

SET @name=值;
CALL sp1(@name);
SELECT @name;

语法

  • 声明语句分隔符 使用DELIMITER关键字将分隔符设置为“$$”或“//”
  • 过程体的标识

//如果没有声明分隔符，编译器会将其视为普通SQL语句进行处理，编译过程会报错
//正确用法：首先用DELIMITER关键字声明当前段的分隔符，最后要将分隔符还原为默认字符

BEGIN
    # … 
END
DELIMITER ; 
#END后，必须使用DELIMITER语句中设置的分隔符为结束
  • demo
#如果存在则删除
drop procedure if exists proc_patient_countPatient; 
delimiter//      #声明分隔符
create procedure proc_patient_countPatient()
begin #过程体开始 
    select count(0) as totalCount from patient;
end// #过程体结束 
delimiter;       #恢复默认分割符
​
#调用
call proc_patient_countPatient();
  • 存储过程参数设置
[IN | OUT | INOUT] 参数名 数据类型
DELIMITER //      #声明分隔符
CREATE PROCEDURE proc_patient_countPatient2(OUT patientNum INT)
    #省略...
DELIMITER ;      #恢复默认分隔符
  • 存储过程中的变量
DECLARE 变量名[,变量名...] 数据类型 [DEFAULT 值];
-- 给变量进行赋值

SET 变量名 = 表达式值[,变量名=表达式...] ;
-- 声明交易时间变量trade_time，并设置默认值为2020-07-10

DECLARE trade_time date DEFAULT '2020-07-10';
-- 设置变量total的值为100

SET total=100;

注意事项：

  • 定义存储过程时，所有局部变量的声明一定要放在存储过程体的开始；否则，会提示语法错误
-- 创建、调用存储过程示例
-- 使用存储过程查询获取并输出病人总人数
delimiter //
create procedure proc_patient_countPatient2(out patientCount int)
begin
	select count(0) into patientCount from patient;
end //
delimiter ;

#调用并输出病人总数
call proc_patient_countPatient2(@patientCount);
select @patientCount as '病人总数'; 





delimiter //
create procedure proc_exam_GetLastExamDateByPatientNameAndDepID
(in patient_name varchar(50), in dep_id int,out last_exam_date datetime)
begin
  declare patient_id int;  #声明局部变量
	
  select patientid into patient_id from patient 
  where patientname= patient_name;
	
  select patient_id; #输出病人的id
  
  #使用SELECT INTO语句可以一次给多个变量赋值
  select max(examdate) into last_exam_date 
  from prescription 
  where patientid = patient_id and depid = dep_id;
end //
delimiter ;



-- 调用

#设置用户变量@patientName为'夏颖'
set @patientName='夏颖';
#设置用户变量@dep_id为1 
set @dep_id=1;
#调用存储过程
call proc_exam_GetLastExamDateByPatientNameAndDepID
(@patientName,@dep_id,@last_exam_date);
#输出病人在某科室最后一次检查时间
select @last_exam_date;

设置用户执行存储过程的权限

通过DEFINER和SQL SECURITY特性控制存储过程的执行权限

语法：

CREATE
    [DEFINER = { user | CURRENT_USER }]  #定义DEFINER,默认为当前用户
PROCEDURE 存储过程名
    [SQL SECURITY { DEFINER | INVOKER } | …]#指定DEFINER或INVOKER权限
BEGIN
    …
END
  • DEFINER

默认DEFINER = CURRENT_USER

检查 ‘user_name’@‘host_name’ 的权限

  • INVOKER

执行存储过程时，会检查调用者的权限

注意事项：

  1. 如果省略sql security特性，则使用definer属性指定调用者，且调用者必须具有EXECUTE权限，必须在mysql.user表中

  2. 如果将sql security特性指定为invoker，则definer属性无效

存储过程的控制语句

  1. 条件语句
    • IF-ELSEIF-ELSE条件语句
IF 条件 THEN 语句列表
   [ELSEIF 条件 THEN 语句列表]
   [ELSE 语句列表]
END IF;
  • CASE条件语句

CASE
   WHEN 条件 THEN 语句列表
   [WHEN 条件 THEN 语句列表]
   [ELSE 语句列表]
END CASE;
-- 语法2：

CASE 列名
   WHEN 条件值 THEN 语句列表
   [WHEN 条件值 THEN 语句列表]
   [ELSE 语句列表]
END CASE; 

#示例11：使用CASE实现计算病人获得的返还医疗费用
create definer=`root`@`localhost` procedure `proc_income_calsubsidy`
(in i_patientid int ,in i_year varchar(10), out o_subsidy float)
begin
	declare t_totalcost float;
	declare t_income float default -1;
	select sum(checkitemcost) into t_totalcost from prescription p1 
	inner join checkitem on p1.checkitemid = checkitem.checkitemid 
	where patientid = i_patientid 
	and examdate >= concat(i_year,'-01-01') 
	and examdate <= concat(i_year,'-12-31');
	select income into t_income from subsidy 
	where patientid = i_patientid;
#根据规则计算返还金额
	case 
		when t_income >=0 and t_income < 5000 then set o_subsidy = t_totalcost * 0.2;
		when t_income < 1000 then set o_subsidy = t_totalcost * 0.15;
		when t_income < 30000 then set o_subsidy = t_totalcost * 0.05;
		when t_income >= 30000 or t_income < 0 then set o_subsidy = 0;
	end case;
end
  1. 循环语句
    • WHILE循环
[label:] WHILE 条件 DO
   语句列表
END WHILE [label]

-- label为标号，用于区分不同的循环，可省略  用在begin、repeat、while 或者loop 语句前
  • LOOP循环 不需判断初始条件，直接执行循环体
[label:] LOOP
   语句列表
END LOOP [label] ;
  • REPEAT循环
#与Java的do-while循环语句类似
[label:] REPEAT 
   语句列表
UNTIL 条件
END REPEAT [label]

与LOOP循环语句相比较

相同点：不需要初始条件直接进入循环体

不同点：REPEAT语句可以设置退出条件

使用REPEAT循环语句编码实现

根据输入的行数要求，向测试表test中批量插入测试数据 迭代语句

查看数据库中已创建的存储过程

语法：

show procedure status;

修改存储过程

使用ALTER PROCEDURE语句修改创建存储过程时定义的特性

ALTER PROCEDURE 存储过程名 [特性……] ;

将存储过程proc_patient_countPatient的SQL SECURITY特性修改为INVOKER

alter procedure proc_patient_countPatient
sql security invoker;

使用ALTER 关键字只能修改存储过程的特性，如果想修改存储过程中过程体的内容，需先删除该存储过程，再进行重新创建 在Navicat中，修改存储过程的内容后，可以直接保存

删除存储过程

使用DROP PROCEDURE语句删除已创建的存储过程

DROP PROCEDURE 存储过程名;