1、定义

存储过程是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字和参数来执行，并获得相应的结果。

应用程序通过存储过程操作表。

2、特点

（1）封装性：将业务逻辑封装在数据库内部，减少应用程序的复杂性

（2）可维护性：集中管理数据库操作，便于维护和更新。若表结构发生修改，不需要修改应用程序，而是修改存储过程。

（3）可重用性：可以多次被调用，提高代码的重用性。

3、优缺点

3.1优点

（1）性能优化：存储过程在创建时编译编译并存在数据库中，执行速度比单个SQL语句快。

（2）代码重用：存储过程可以重复调用，减少重复代码，提高代码的可维护性。

（3）安全性：可以限制用户直接访问数据库，通过存储过程间接访问，保证系统安全性。

（4）事务管理：可以在存储过程中实现复杂的事务逻辑。

（5）降低耦合：表结构发生变化，只需要修改存储过程，应用程序的修改较小。

3.2缺点

（1）可移植性差：不同数据库的实现可能有所不同，存储过程不能实现跨数据库移植，更换数据库需要重新编写。

（2）调试困难：只有少数数据库管理系统支持存储过程的调试和可视化界面，开发和维护困难。

（3）不适合高并发场景：在高并发场景下，存储过程可能会增加数据库的压力，难以维护。

4、环境准备

5、语法

5.1创建

-- 修改SQL语句的结束标识符为//
delimiter //

create procedure 存储过程名 (参数列表)
begin
--sql 语句
end

-- 修改SQL语句的结束标识符为; (使用之后还原
delimiter ;

5.2调用

call 存储过程名 (参数列表);

5.3查看

-- 查看指定数据库中创建的存储过程
SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA='表名';

-- 查看创建存储过程的sql
SHOW CREATE PROCEDURE 存储过程名;

-- 查看创建表的SQL
SHOW CREATE TABLE 表名;

5.4删除

-- 删除存储过程名
DROP PROCEDURE IF EXISTS 存储过程名;

5.5示例

计算学生总分

6、变量

6.1系统变量

MySQL的配置变量，控制服务器的行为和性能，分为全局变量、会话变量

（1）查看系统变量

-- 查看所有系统变量，不指定则默认是session
show [global|session] variables;

-- 查看指定的系统变量，like模糊查询
show [global|session] variables like 'xxx';

-- 查看指定的系统变量，精确查询
show @@[global|session].系统变量名;

（2）设置系统变量

修改session级别的值不影响全局变量的值，开启下一个会话后还是读取全局变量的值

set [global|session] 系统变量名 = 值;
set @@session.系统变量名 = 值;

6.2用户定义变量

在SQL中会话中定义的变量，作用域为当前会话。

（1）赋值

-- 方式1：推荐
set @var_name:=expr [,@var_name]...;

-- 方式2
select 列名 into @var_name from 表名 where ...;

（2）使用

SET @age=18;
SELECT @age;

SET @age:=19;
SELECT @19;

select @age:=20;

SELECT count(*) into @stu_count from student;
select @stu_count;

SET @age:=18;
SELECT * FROM student WHERE age=@age;

6.3局部变量

只在存储过程、函数或触发器的范围内有效，需要使用declare声明，作用域的范围在声明的begin...end代码块内

（1）声明

declare 变量名 变量类型 [default 默认值] ...;

（2）赋值

-- 方式1
set var_name=值;

-- 方式2
set var_name:=值;

-- 方式3
select 列名 into var_name from 表名 where ...;

（3）使用

delimiter //
-- 创建存储过程
CREATE PROCEDURE p1()
BEGIN
  -- 定义局部变量
  DECLARE stu_count INT DEFAULT 0;
  -- 把查询结果赋值到局部变量
  SELECT count(*) INTO stu_count from student;
  -- 使用（查看）局部变量
  SELECT stu_count;
END//

delimiter ;

CALL p1();

7、SQL编程

7.1条件判断

if 条件1 then
    ......
[elseif 条件2 then
    ......
else
    ......]
end if;

使用

delimiter //

CREATE PROCEDURE p2()
BEGIN
  -- 定义初始分数变量
  DECLARE score int DEFAULT 0;
  -- 定义结果变量
  DECLARE result VARCHAR(10);
  
  if score>=90 THEN
    set result:='优秀';
  ELSEIF score>=80 and score<90 THEN
    set result:='良好';
  ELSEIF score>=60 and score<80 THEN
    set result:='及格';
  ELSE
    set result:='不及格';
  END IF;
  
  -- 查询结果
  SELECT result;
  
END//

delimiter ;

CALL p2();

7.2参数

（1）分类

类型	描述
IN	输入类型，调用存储过程时要传入的值，默认参数类型（外部不生效，可近似看为值类型）
OUT	输出类型，可作为存储过程的返回值
INOUT	输入输出类型（外部生效，可近似看为引用类型）

（2）语法

delimiter //

create procedure 存储过程名 (IN/OUT/INOUT 参数名 参数类型 [,...]) 
begin
    -- sql
end//

delimiter ;

（3）实例

成绩分类

delimiter //

CREATE PROCEDURE p3(IN score INT,OUT result VARCHAR(10))
BEGIN
  IF score>=90 THEN
    SET result:='优秀';
  ELSEIF score>=80 AND score<90 THEN
    set result:='良好';
  ELSEIF score>=60 AND score<80 THEN
    SET result:='及格';
  ELSE
    SET result:='不及格';
  end if ;
END//

delimiter;

-- 调用存储过程
CALL p3(90,@result);
-- 查询结果
SELECT @result;

分数+10

delimiter //

CREATE PROCEDURE p4(INOUT score INT)
BEGIN
  set score:=score+10;

END//

delimiter;

set @score:=66;
CALL p4(@score);
SELECT @score;

7.3CASE

注：SQL语句不具备向下穿透的功能（如Java中if a>10;else if a>5,zai SQL中需要写为if a>0;elseif a<=10 and a>5）

（1）语法1，类似Java的switch-case语句——case是固定值的时候可以使用

case case_value
    when when_value then statement_list
    [when when_value then statement_list]...
    [else statement_list]
end case

（2）语法2，计算每个when子句的search_condition，直到某个表达式为真，才执行相应的statement_list——case会变化的时候可以使用

（statement_list不能为空！）

case 
    when search_condition then statement_list
    [when search_condition then statement_list]...
    [else statement_list]
end case

（3）实例：①传入状态码，输出该状态码对应的含义

delimiter//

CREATE PROCEDURE p5(IN code INT,OUT result VARCHAR(50))
BEGIN
  CASE code
	WHEN 0 THEN
		SET result:='成功';
  WHEN 10001 THEN
		SET result:='用户名或密码错误';
  WHEN 10002 THEN
		SET result:='没有对应的权限，请联系管理员';
  WHEN 20001 THEN
		SET result:='传入的参数有误';
  WHEN 20002 THEN
		SET result:='没有找到相应的结果';
	ELSE
		set result:='服务器错误，联系管理员';
END CASE;

END//

delimiter;

CALL p5(100401,@result);
SELECT @result;

②输入月份，输出对应的季度

delimiter //
CREATE PROCEDURE p6(IN month int,OUT result VARCHAR(50))
BEGIN
  CASE
	WHEN month>=1 and month<=3 THEN
		SET result:='第1季度';
  WHEN month>=4 and month<=6 THEN
		SET result:='第2季度';
  WHEN month>=7 and month<=9 THEN
		SET result:='第3季度';
  WHEN month>=10 and month<=12 THEN
		SET result:='第4季度';
	ELSE
		SET result:='非法输入';
END CASE;


END//

delimiter;

CALL p6(-11,@result);
SELECT @result;

7.4循环

7.4.1WHILE

（1）语法

while search_condition do
    statement_list
end while;

（2）练习：传入一个数n，计算从1累加到n的值

delimiter//
CREATE PROCEDURE p7(IN n INT,OUT result int)
BEGIN
  set result=0;
  WHILE n>0 DO
    set result:=result+n;
    set n:=n-1;
  END WHILE;

END//

delimiter;

CALL p7(10,@result);
select @result;

7.4.2REPEAT

（1）语法——类似Java中的do-while

repeat
    statement_list
    until search_condition
end repeat;

（2）练习：传入一个数n，计算从1累加到n的值

delimiter //

CREATE PROCEDURE p8(IN n INT)
BEGIN
  DECLARE total int DEFAULT 0;
  REPEAT
    -- 查看n
    select n;
    SET total:=total+n;
    set n:=n-1;
  UNTIL n<=0 END REPEAT;
  SELECT total;
END//

delimiter ;

CALL p8(5);

7.4.3LOOP

（1）语法

本身是死循环，需要配合leave label（退出整个循环，类似于Java中的break）和iterate label（终止当前循环，类似Java中的continue）使用

[begin_label:]loop
    statement_list
end loop[end_label]

（2）练习：①传入一个数n，计算从1累加到n的值

delimiter//

CREATE PROCEDURE p9(IN n INT)
BEGIN
  -- 保存结果
  DECLARE result INT DEFAULT 0;
  
  sum_label: LOOP
    -- 退出条件
    IF n<=0 THEN
      LEAVE sum_label; 
    END IF;
    
    -- 执行
    SET result:=result+n;
    SET n:=n-1;
    
  END LOOP sum_label;
  
  SELECT result;
END//

delimiter;

CALL p9(5);

②传入一个数n，计算偶数的累加值

delimiter //

CREATE PROCEDURE p10(IN n INT)
BEGIN
  DECLARE total INT DEFAULT 0;

  sum_label: LOOP
    IF n<=0 THEN
      LEAVE sum_label; 
    END IF; 
    IF n%2=1 THEN
      set n:=n-1;
      ITERATE sum_label;
    END IF;
    SET total:=total+n;
    set n:=n-1;
  END LOOP sum_label;
  select total;
END//

delimiter ;

CALL p10(5);

7.5游标

只读，不能进行更新操作。

是一种数据库对象，允许在存储过程和函数中对查询到的结果进行逐行检索。

（1）语法

游标必须在处理程序之前被声明，变量必须在游标或处理程序之前被声明！

-- 声明
declare 游标名 cursor for 查询语句;

-- 打开
open 游标名;

-- 获取游标记录
fetch 游标名 into 变量[,变量] ...;

-- 关闭
close 游标名;

（2）练习：传入班级编号，查询学生表中属于该班级学生的学生信息，并将符合条件的学生信息写入到一张新表中t_student_class(id,student_name,class_name)

delimiter //

CREATE PROCEDURE p11(IN class_id INT)
BEGIN
  -- 定义变量
  DECLARE student_name VARCHAR(20);
  DECLARE class_name VARCHAR(20);
  
  -- 定义游标
  DECLARE s_cursor CURSOR FOR
    select s.name student_name,c.name class_name from student s,class c 
    where s.class_id=c.id and s.class_id=1;
    
  -- 创建表
  DROP table if EXISTS t_student_class;
  CREATE TABLE t_student_class(
    id BIGINT PRIMARY KEY auto_increment,
    student_name VARCHAR(20) not null,
    class_name VARCHAR(20) not null
  );
  
  -- 开启游标
  OPEN s_cursor;
  
  -- 遍历结果集
  WHILE TRUE DO
    -- 获取游标记录
    FETCH s_cursor into student_name,class_name;
    -- 写入到新表
    INSERT INTO t_student_class VALUES(NULL,student_name,class_name);
  END WHILE;
  
  -- 关闭游标
  CLOSE s_cursor;

END//

delimiter ;

CALL p11(1);

错误原因：游标遍历到末尾

7.6条件处理程序

定义条件是事先定义程序执行过程中可能出现的问题，处理程序定义在遇到问题室应当采取的措施。使用条件处理程序保证存储过程或函数在遇到警告或错误时能继续进行，增强程序处理问题的能力，避免程序异常停止。

（1）语法

declare handler_action handler
    for condition_value [,condition_value]...
    statement

handler_action:{
    continue     -- 继续执行当前程序
    |exit    -- 终止当前程序
}

condition_value:{
    mysql_error_code
    |sqlstate [value] sqlstate_value
    |sqlwarning    -- 01开头的sqlstate代码
    |not found    -- 02开头的sqlstate代码
    |sqlexception
}

（2）练习：加入条件处理程序，解决游标越界问题

delimiter //

CREATE PROCEDURE p12(IN class_id INT)

BEGIN
  -- 定义变量
  DECLARE student_name VARCHAR(20);
  DECLARE class_name VARCHAR(20);
  
  -- 定义游标结束标识
  DECLARE is_done bool DEFAULT false;
  
  -- 定义游标
  DECLARE s_cursor CURSOR FOR
    select s.name student_name,c.name class_name from student s,class c 
    where s.class_id=c.id and s.class_id=1;
    
  -- 定义条件处理程序
  DECLARE CONTINUE HANDLER 
    FOR NOT FOUND 
    SET is_done:=true;
  
  -- 创建表
  DROP table if EXISTS t_student_class;
  CREATE TABLE t_student_class(
    id BIGINT PRIMARY KEY auto_increment,
    student_name VARCHAR(20) not null,
    class_name VARCHAR(20) not null
  );
  
  -- 开启游标
  OPEN s_cursor;
  
  -- 遍历结果集
  WHILE NOT is_done DO
    -- 获取游标记录
    FETCH s_cursor into student_name,class_name;
    -- 写入到新表
    INSERT INTO t_student_class VALUES(NULL,student_name,class_name);
  END WHILE;
  
  -- 关闭游标
  CLOSE s_cursor;

END//

delimiter ;

CALL p12(1);

写入两次沙悟净：条件处理程序处理异常后，重置成上一次获取到的记录；为解决问题，需要在处理异常之后直接退出程序，需要将while循环改成loop循环

  -- 遍历结果集
  read_loop: LOOP
    -- 获取游标记录
    FETCH s_cursor into student_name,class_name;
    -- 判断退出条件
    IF is_done THEN
      LEAVE read_loop; 
    END IF; 
    
    -- 写入到新表
    INSERT INTO t_student_class VALUES(NULL,student_name,class_name);
  END LOOP read_loop;

7.7存储函数

有返回值的存储过程，参数类型只能是IN，类似于内置函数。存储函数和存储过程的主要区别在于存储函数必须有返回值！存储过程不一定。

（1）语法

create function 存储函数名称 ([参数列表])
returns type [characteristic ...]
begin
    -- sql
    return ...;
end;

characteristic:    -- 返回值的类型
    [not] deterministic    -- 想通的输入参数总是产生[不同]相同的结果
    |no sql    -- 不包含SQL语句
    |reads sql data    -- 包含读取数据的语句，如select
    |modifies sql data    -- 包含修改数据的语句，如update，delete

select 存储函数名称 ([参数列表]);
    

（2）练习：计算从1到n的累计值

1418 - This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

解决方案：指定characteristic特性

delimiter //

CREATE FUNCTION fun1(n INT) RETURNS INT DETERMINISTIC
BEGIN
  DECLARE total INT DEFAULT 0;
  WHILE n > 0 DO
    SET total:= total + n;
    SET n:= n - 1;
  END WHILE;
  
  RETURN total;

END//

delimiter ;

SELECT fun1(100);

7.8触发器

（1）什么是触发器

是一个与表关联的数据库对象，在对表进行insert、update、delete操作时，触发并执行指定触发器定义的SQL语句（类似于去餐馆吃饭时点单后会触发老板记账这一行为）触发器可以在对表操作之前或之后执行

支持三种类型的触发器：insert触发器、update触发器、delete触发器

触发器类型	OLD和NEW
insert触发器	new表示将要或已经新增的数据
update触发器	old表示修改之前的数据，new表示将要或已经新增的数据
delete触发器	old表示将要或已经删除的数据

MySQL只支持行级触发器，不支持语句级触发器。

（2）语法

-- 创建
create trigger [if not exists] trigger_name
    trigger_name trigger_event
    on tbl_name for each row
begin
    trigger_stmt;
end;

trigger_time:{before|after}
trigger_event:{insert|update|delete}

-- 查看
show triggers;

-- 删除
drop trigger [if exists] [schema_name.]trigger_name;

（3）练习

插入触发器

CREATE TABLE student_log(
  id bigint primary key auto_increment,
  operation_type varchar(10) not null comment '操作类型:insert/update/delete',
  operation_time datetime not null comment '操作时间',
  operation_id bigint  not null comment '操作记录的id',
  operation_data varchar(500) comment '操作数据'
);

delimiter //

-- 插入数据的触发器
CREATE TRIGGER IF NOT EXISTS trg_student_insert
  AFTER INSERT ON student FOR EACH ROW
BEGIN
  -- 插入日志到student_log表
  INSERT INTO student_log(
    operation_type,
    operation_time,
    operation_id,
    operation_data
  ) VALUES (
    'insert',
    now(),
    new.id,
    concat(new.id,',',
           new.name,',',
           new.sno,',',
           new.age,',',
           new.gender,',',
           new.enroll_date,',',
           new.class_id)
  );
END//

delimiter ;

更新触发器

-- 插入数据的触发器
delimiter //
CREATE TRIGGER if NOT EXISTS trg_student_update
  AFTER UPDATE ON student FOR EACH ROW
BEGIN
  -- 插入日志到student_log表
  INSERT INTO student_log(
    operation_type,
    operation_time,
    operation_id,
    operation_data
  )VALUES(
    'update',
    now(),
    new.id,
    CONCAT(old.id,',',old.name,',',old.sno,',',old.age,',',old.gender,',',old.enroll_date,',',old.class_id,'|',
           new.id,',',new.name,',',new.sno,',',new.age,',',new.gender,',',new.enroll_date,',',new.class_id)
  );
END//
delimiter ;

SELECT * from student;
UPDATE student set age = 20,class_id=2 where name = '曹操';
select * from student_log;

删除触发器

-- 删除数据的触发器
delimiter //

CREATE TRIGGER trg_student_delete
  after DELETE ON student for EACH ROW
BEGIN
  -- 插入日志到student_log表
  INSERT INTO student_log(
    operation_type,
    operation_time,
    operation_id,
    operation_data
  )VALUES(
    'delete',
    now(),
    old.id,
    CONCAT(old.id,',',old.name,',',old.sno,',',old.age,',',old.gender,',',old.enroll_date,',',old.class_id)
  );
END//

delimiter ;

SELECT * from student;
DELETE from student where name = '曹操';
select * from student_log;