MySQL 存储过程（超详细）
一、什么是存储过程？
- 存储过程可称为过程化SQL语言，是在普通SQL语句的基础上增加了编程语言的特点，把数据操作语句(DML)和查询语句(DQL)组织在过程化代码中，通过逻辑判断、循环等操作实现复杂计算的程序语言。
- 换句话说，存储过程其实就是数据库内置的一种编程语言，这种编程语言也有自己的变量、if语句、循环语句等。在一个存储过程中可以将多条SQL语句以逻辑代码的方式将其串联起来，执行这个存储过程就是将这些SQL语句按照一定的逻辑去执行，所以一个存储过程也可以看做是一组为了完成特定功能的SQL 语句集。
- 每一个存储过程都是一个数据库对象，就像table和view一样，存储在数据库当中，一次编译永久有效。并且每一个存储过程都有自己的名字。客户端程序（Java程序）通过存储过程的名字来调用存储过程。
- 在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升
二、存储过程的优点和缺点？
优点：速度快。
- 降低了应用服务器(Java程序)和数据库服务器之间网络通讯的开销。尤其在数据量庞大的情况下效果显著。
缺点：移植性差。编写难度大。维护性差。
- 每一个数据库都有自己的存储过程的语法规则，这种语法规则不是通用的。一旦使用了存储过程，则数据库产品很难更换，例如：编写了mysql的存储过程，这段代码只能在mysql中运行，无法在oracle数据库中运行。
- 对于数据库存储过程这种语法来说，没有专业的IDE工具（集成开发环境），所以编码速度较低。自然维护的成本也会较高。
在实际开发中，存储过程还是很少使用的。只有在系统遇到了性能瓶颈，在进行优化的时候，对于大数量的应用来说，可以考虑使用一些。
三、第一个存储过程
3.1 存储过程的创建
create procedure p1() begin select empno,ename from emp; end;
3.2 存储过程的调用
call p1();
3.3 存储过程的查看
查看创建存储过程的语句：
show create procedure p1;
通过系统表information_schema.ROUTINES查看存储过程的详细信：information_schema.ROUTINES 是 MySQL 数据库中一个系统表，存储了所有存储过程、函数、触发器的详细信息，包括名称、返回值类型、参数、创建时间、修改时间等。
select * from information_schema.routines where routine_name = 'p1';
information_schema.ROUTINES 表中的一些重要的列包括：
- SPECIFIC_NAME：存储过程的具体名称，包括该存储过程的名字，参数列表。
- ROUTINE_SCHEMA：存储过程所在的数据库名称。
- ROUTINE_NAME：存储过程的名称。
- ROUTINE_TYPE：PROCEDURE表示是一个存储过程，FUNCTION表示是一个函数。
- ROUTINE_DEFINITION：存储过程的定义语句。
- CREATED：存储过程的创建时间。
- LAST_ALTERED：存储过程的最后修改时间。
- DATA_TYPE：存储过程的返回值类型、参数类型等。
3.4 存储过程的删除
drop procedure if exists p1;
四、MySQL的变量
mysql中的变量包括：系统变量、用户变量、局部变量。
4.1 系统变量
- MySQL 系统变量是指在 MySQL 服务器运行时控制其行为的参数。这些变量可以被设置为特定的值来改变服务器的默认设置，以满足不同的需求。
- MySQL 系统变量可以具有全局（global）或会话（session）作用域。
- 全局作用域是指对所有连接和所有数据库都适用；
- 会话作用域是指只对当前连接和当前数据库适用。
查看系统变量
show [global|session] variables; show [global|session] variables like ''; select @@[global|session.]系统变量名;
注意：没有指定session或global时，默认是session
设置系统变量
set [global | session] 系统变量名 = 值; set @@[global | session.]系统变量名 = 值;
注意：无论是全局设置还是会话设置，当mysql服务重启之后，之前配置都会失效。可以通过修改MySQL根目录下的my.ini配置文件达到永久修改的效果。（my.ini是MySQL数据库默认的系统级配置文件，默认是不存在的，需要新建，并参考一些资料进行配置。）
windows系统是my.ini、 linux系统是my.cnf， my.ini文件通常放在mysql安装的根目录下，如下图：
这个文件通常是不存在的，可以新建，新建后例如提供以下配置：
[mysqld] autocommit=0
这种配置就表示永久性关闭自动提交机制。（不建议这样做。）
4.2 用户变量
用户自定义的变量。只在当前会话有效。所有的用户变量'@'开始。
给用户变量赋值
set @name = 'jackson';// 静态赋值 set @age := 30; set @gender := '男', @addr := '北京大兴区'; select @email := 'jackson@123.com'; select sal into @sal from emp where ename ='SMITH'; // 动态赋值
读取用户变量的值
select @name, @age, @gender, @addr, @email, @sal;
注意：mysql中变量不需要声明。直接赋值就行。如果没有声明变量，直接读取该变量，返回null
4.3 局部变量
在存储过程中可以使用局部变量。使用declare声明。在begin和end之间有效。
变量的声明
declare 变量名 数据类型 [default ...];
变量的数据类型就是表字段的数据类型，例如：int、bigint、char、varchar、date、time、datetime等。
注意：declare通常出现在begin end之间的开始部分。
变量的赋值
set 变量名 = 值; set 变量名 := 值; select 字段名 into 变量名 from 表名 ...;
案例：以下程序演示局部变量的声明、赋值、读取：
create PROCEDURE p2() begin /*声明变量*/ declare emp_count int default 0; /*声明变量*/ declare sal double(10,2) default 0.0; /*给变量赋值*/ select count(*) into emp_count from emp; /*给变量赋值*/ set sal := 5000.0; /*读取变量的值*/ select emp_count; /*读取变量的值*/ select sal; end;
call p2();
五、if语句
5.1 语法格式：
if 条件 then ...... elseif 条件 then ...... elseif 条件 then ...... else ...... end if;
案例：员工月薪sal，超过10000的属于“高收入”，6000到10000的属于“中收入”，少于6000的属于“低收入”。
create procedure p3( ) begin declare sal int default 5000; declare grade varchar(20); if sal > 10000 then set grade := '高收入'; elseif sal >= 6000 then set grade := '中收入'; else set grade := '低收入'; end if; select grade; end;
call p3();
六、参数
存储过程的参数包括三种形式：
- in：入参（未指定时，默认是in）
- out：出参
- inout：既是入参，又是出参
案例：员工月薪sal，超过10000的属于“高收入”，6000到10000的属于“中收入”，少于6000的属于“低收入”。
create procedure p4(in sal int, out grade varchar(20)) begin if sal > 10000 then set grade := '高收入'; elseif sal >= 6000 then set grade := '中收入'; else set grade := '低收入'; end if; end;
call p4(5000, @grade); select @grade;
案例：将传入的工资sal上调10%
create procedure p5(inout sal int) begin set sal := sal * 1.1; end;
set @sal := 10000; call p5(@sal); select @sal;
七、case语句
7.1 语法格式：
case 值 when 值1 then ...... when 值2 then ...... when 值3 then ...... else ...... end case;
case when 条件1 then ...... when 条件2 then ...... when 条件3 then ...... else ...... end case;
案例：根据不同月份，输出不同的季节。3 4 5月份春季。6 7 8月份夏季。9 10 11月份秋季。12 1 2 冬季。其他非法。
create procedure mypro(in month int, out result varchar(100)) begin case month when 3 then set result := '春季'; when 4 then set result := '春季'; when 5 then set result := '春季'; when 6 then set result := '夏季'; when 7 then set result := '夏季'; when 8 then set result := '夏季'; when 9 then set result := '秋季'; when 10 then set result := '秋季'; when 11 then set result := '秋季'; when 12 then set result := '冬季'; when 1 then set result := '冬季'; when 2 then set result := '冬季'; else set result := '非法月份'; end case; end;
create procedure mypro(in month int, out result varchar(100)) begin case when month = 3 or month = 4 or month = 5 then set result := '春季'; when month = 6 or month = 7 or month = 8 then set result := '夏季'; when month = 9 or month = 10 or month = 11 then set result := '秋季'; when month = 12 or month = 1 or month = 2 then set result := '冬季'; else set result := '非法月份'; end case; end;
call mypro(9, @season); select @season;
八、while循环
8.1 语法格式：
while 条件 do 循环体; end while;
案例：传入一个数字n，计算1~n中所有偶数的和。
create procedure mypro(in n int) begin declare sum int default 0; while n > 0 do if n % 2 = 0 then set sum := sum + n; end if; set n := n - 1; end while; select sum; end;call mypro(10);
九、repeat循环
9.1 语法格式：
repeat 循环体; until 条件 end repeat;
注意：条件成立时结束循环
案例：传入一个数字n，计算1~n中所有偶数的和。
create procedure mypro(in n int, out sum int) begin set sum := 0; repeat if n % 2 = 0 then set sum := sum + n; end if; set n := n - 1; until n 0 do if n % 2 = 0 then set result := result + n; end if; set n := n - 1; end while; return result; end; -- 调用函数 set @result = sum_fun(100); select @result;
十四、触发器
MySQL 触发器是一种数据库对象，它是与表相关联的特殊程序。它可以在特定的数据操作（例如插入（INSERT）、更新（UPDATE）或删除（DELETE））触发时自动执行。MySQL 触发器使数据库开发人员能够在数据的不同状态之间维护一致性和完整性，并且可以为特定的数据库表自动执行操作。
触发器的作用主要有以下几个方面：
- 强制实施业务规则：触发器可以帮助确保数据表中的业务规则得到强制执行，例如检查插入或更新的数据是否符合某些规则。
- 数据审计：触发器可以声明在执行数据修改时自动记日志或审计数据变化的操作，使数据对数据库管理员和 SQL 审计人员更易于追踪和审计。
- 执行特定业务操作：触发器可以自动执行特定的业务操作，例如计算数据行的总数、计算平均值或总和等。
MySQL 触发器分为两种类型: BEFORE 和 AFTER
- BEFORE 触发器在执行 INSERT、UPDATE、DELETE 语句之前执行
- AFTER 触发器在执行 INSERT、UPDATE、DELETE 语句之后执行。
创建触发器的语法如下：
CREATE TRIGGER trigger_name BEFORE/AFTER INSERT/UPDATE/DELETE ON table_name FOR EACH ROW BEGIN -- 触发器执行的 SQL 语句 END;
其中：
- trigger_name：触发器的名称
- BEFORE/AFTER：触发器的类型，可以是 BEFORE 或者 AFTER
- INSERT/UPDATE/DELETE：触发器所监控的 DML 调用类型
- table_name：触发器所绑定的表名
- FOR EACH ROW：表示触发器在每行受到 DML 的影响之后都会执行
- 触发器执行的 SQL 语句：该语句会在触发器被触发时执行
需要注意的是，触发器是一种高级的数据库功能，只有在必要的情况下才应该使用，例如在需要实施强制性业务规则时。过多的触发器和复杂的触发器逻辑可能会影响查询性能和扩展性。
关于触发器的NEW和OLD关键字：
在 MySQL 触发器中，NEW 和OLD 是两个特殊的关键字，用于引用在触发器中受到修改的行的新值和旧值。具体而言：
- NEW：在触发 INSERT 或 UPDATE 操作期间，NEW 用于引用将要插入或更新到表中的新行的值。
- OLD：在触发 UPDATE 或 DELETE 操作期间，OLD 用于引用更新或删除之前在表中的旧行的值。
通俗的讲，NEW 是指触发器执行的操作所要插入或更新到当前行中的新数据；而 OLD 则是指当前行在触发器执行前原本的数据。
在MySQL 触发器中，NEW 和 OLD 使用方法是相似的。在触发器中，可以像引用表的其他列一样引用 NEW 和 OLD。例如，可以使用 OLD.column_name 从旧行中引用列值，也可以使用 NEW.column_name 从新行中引用列值。
示例：
假设有一个名为 my_table 的表，其中包含一个名为 quantity 的列。当在该表上执行 UPDATE 操作时，以下触发器会将旧值 OLD.quantity 累加到新值 NEW.quantity 中：
CREATE TRIGGER my_trigger BEFORE UPDATE ON my_table FOR EACH ROW BEGIN SET NEW.quantity = NEW.quantity + OLD.quantity; END;
在此触发器中，OLD.quantity 引用原始行的 quantity 值（旧值），而 NEW.quantity 引用更新行的 quantity 值（新值）。在触发器执行期间，数据行的 quantity 值将设置为旧值加上新值。
案例：当我们对dept表中的数据进行insert delete update的时候，请将这些操作记录到日志表当中，日志表如下：
drop table if exists oper_log; create table oper_log( id bigint primary key auto_increment, table_name varchar(100) not null comment '操作的哪张表', oper_type varchar(100) not null comment '操作类型包括insert delete update', oper_time datetime not null comment '操作时间', oper_id bigint not null comment '操作的那行记录的id', oper_desc text comment '操作描述' );
触发器1：向dept表中插入数据时，记录日志
create trigger dept_trigger_insert after insert on dept for each row begin insert into oper_log(id,table_name,oper_type,oper_time,oper_id,oper_desc) values (null,'dept','insert',now(),new.deptno,concat('插入数据：deptno=', new.deptno, ',dname=', new.dname,',loc=', new.loc)); end;查看触发器：
show triggers;
删除触发器：
drop trigger if exists dept_trigger_insert;
向dept表中插入一条记录：
日志表中多了一条记录：
触发器2：修改dept表中数据时，记录日志
create trigger dept_trigger_update after update on dept for each row begin insert into oper_log(id,table_name,oper_type,oper_time,oper_id,oper_desc) values (null,'dept','update',now(),new.deptno,concat('更新前：deptno=', old.deptno, ',dname=', old.dname,',loc=', old.loc, ',更新后：deptno=', new.deptno, ',dname=', new.dname,',loc=', new.loc)); end;更新一条记录：
update dept set loc = '北京' where deptno = 60;
日志表中多了一条记录：
注意：更新一条记录则对应一条日志。如果一次更新3条记录，那么日志表中插入3条记录。
触发器3：删除dept表中数据时，记录日志
create trigger dept_trigger_delete after delete on dept for each row begin insert into oper_log(id,table_name,oper_type,oper_time,oper_id,oper_desc) values (null,'dept','delete',now(),old.deptno,concat('删除了数据：deptno=', old.deptno, ',dname=', old.dname,',loc=', old.loc)); end;删除一条记录：
delete from dept where deptno = 60;
日志表中多了一条记录：
- 降低了应用服务器(Java程序)和数据库服务器之间网络通讯的开销。尤其在数据量庞大的情况下效果显著。
