1. 存储过程的概念

存储过程可称为过程化SQL语言，是在普通SQL语句的基础上增加了编程语言的特点，把数据操作语句(DML)和查询语句(DQL)组织在过程化代码中，通过逻辑判断、循环等操作实现复杂计算的程序语言。

换句话说，存储过程其实就是数据库内置的一种编程语言，这种编程语言也有自己的变量、if语句、循环语句等。在一个存储过程中可以将多条SQL语句以逻辑代码的方式将其串联起来，执行这个存储过程就是将这些SQL语句按照一定的逻辑去执行，所以一个存储过程也可以看做是一组为了完成特定功能的SQL 语句集。

每一个存储过程都是一个数据库对象，就像table和view一样，存储在数据库当中，一次编译永久有效。并且每一个存储过程都有自己的名字。客户端程序通过存储过程的名字来调用存储过程。

在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升。

2. 存储过程的优点和缺点

优点：速度快。

  • 降低了应用服务器和数据库服务器之间网络通讯的开销。尤其在数据量庞大的情况下效果显著。

缺点：移植性差。编写难度大。维护性差。

  • 每一个数据库都有自己的存储过程的语法规则，这种语法规则不是通用的。一旦使用了存储过程，则数据库产品很难更换，例如：编写了mysql的存储过程，这段代码只能在mysql中运行，无法在oracle数据库中运行。
  • 对于数据库存储过程这种语法来说，没有专业的IDE工具（集成开发环境），所以编码速度较低。自然维护的成本也会较高。

在实际开发中，存储过程还是很少使用的。只有在系统遇到了性能瓶颈，在进行优化的时候，对于大数量的应用来说，可以考虑使用一些。

3. 第一个存储过程

3.1 存储过程的创建

1
2
3
4
create procedure p1()
begin
	select empno,ename from emp;
end;

3.2 存储过程的调用

1
call p1();

3.3 存储过程的查看

查看创建存储过程的语句：

1
show create procedure p1;

查看创建存储过程

通过系统表information_schema.ROUTINES查看存储过程的详细信息：

information_schema.ROUTINES 是 MySQL 数据库中一个系统表，存储了所有存储过程、函数、触发器的详细信息，包括名称、返回值类型、参数、创建时间、修改时间等。

1
select * from information_schema.routines where routine_name = 'p1';

系统表information_schema.ROUTINES

information_schema.ROUTINES 表中的一些重要的列包括：

  • SPECIFIC_NAME：存储过程的具体名称，包括该存储过程的名字，参数列表。
  • ROUTINE_SCHEMA：存储过程所在的数据库名称。
  • ROUTINE_NAME：存储过程的名称。
  • ROUTINE_TYPE：PROCEDURE表示是一个存储过程，FUNCTION表示是一个函数。
  • ROUTINE_DEFINITION：存储过程的定义语句。
  • CREATED：存储过程的创建时间。
  • LAST_ALTERED：存储过程的最后修改时间。
  • DATA_TYPE：存储过程的返回值类型、参数类型等。

3.4 存储过程的删除

1
drop procedure if exists p1;

3.5 delimiter命令

在 MySQL 中，delimiter 命令用于改变 MySQL 解释语句的定界符。MySQL 默认使用分号 ; 作为语句的定界符。而使用 delimiter 命令可以将分号 ; 更改为其他字符，从而可以在 SQL 语句中使用分号 ;

例如，假设需要创建一个存储过程。在存储过程中通常会包括多条 SQL 语句，而这些语句都需要以分号 ; 结尾。但默认情况下，执行到第一条语句的分号 ; 后，MySQL 就会停止解释，导致后续的语句无法执行。解决方式就是使用 delimiter 命令将分号改为其他字符，使分号 ; 不再是语句定界符。

1
2
3
4
5
6
7
8
9
delimiter //

CREATE PROCEDURE my_proc ()
BEGIN
SELECT * FROM my_table;
INSERT INTO my_table (col1, col2) VALUES ('value1', 'value2');
END //

delimiter ;

在这个例子中，我们使用 delimiter // 命令将定界符改为两个斜线 //。在存储过程中，以分号 ; 结尾的语句不再被解释为语句的结束。而使用 delimiter ; 可以将分号恢复为语句定界符。

总之，delimiter 命令可以改变 MySQL 数据库系统中 SQL 查询语句的分隔符，从而可使一条 SQL 查询语句包含多个 SQL 语句。这样的话，就方便了我们在一个语句里面加入多个语句，而且不会被错

4. MySQL的变量

mysql中的变量包括：系统变量、用户变量、局部变量。

4.1 系统变量

MySQL 系统变量是指在 MySQL 服务器运行时控制其行为的参数。这些变量可以被设置为特定的值来改变服务器的默认设置，以满足不同的需求。

MySQL 系统变量可以具有全局（global）或会话（session）作用域。

  • 全局作用域是指对所有连接和所有数据库都适用；
  • 会话作用域是指只对当前连接和当前数据库适用。

查看系统变量：

1
2
3
4
5
show [global|session] variables;

show [global|session] variables like '';

select @@[global|session.]系统变量名;

PS：没有指定session或global时，默认是session

设置系统变量：

1
2
3
set [global | session] 系统变量名 = 值;

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

PS：无论是全局设置还是会话设置，当mysql服务重启之后，之前配置都会失效。可以通过修改MySQL根目录下的my.ini配置文件达到永久修改的效果。（my.ini是MySQL数据库默认的系统级配置文件，默认是不存在的，需要新建，并参考一些资料进行配置。）

windows系统是my.ini，linux系统是my.cnf，my.ini文件通常放在mysql安装的根目录下。

这个文件通常是不存在的，可以新建，新建后例如提供以下配置：

1
2
[mysqld]
autocommit=0

这种配置就表示永久性关闭自动提交机制。（不建议这样做。）

4.2 用户变量

用户自定义的变量。只在当前会话有效。所有的用户变量@开始。

给用户变量赋值：

1
2
3
4
5
set @name = 'jackson';
set @age := 30;
set @gender := '男', @addr := '北京大兴区';
select @email := 'jackson@123.com';
select sal into @sal from emp where ename ='SMITH';

读取用户变量的值：

1
select @name, @age, @gender, @addr, @email, @sal;

PS：mysql中变量不需要声明。直接赋值就行。如果没有声明变量，直接读取该变量，返回null

4.3 局部变量

在存储过程中可以使用局部变量。使用declare声明。在begin和end之间有效。

变量的声明：

1
declare 变量名 数据类型 [default ...];

变量的数据类型就是表字段的数据类型，例如：int、bigint、char、varchar、date、time、datetime等。

PS：declare通常出现在begin end之间的开始部分。

变量的赋值：

1
2
3
set 变量名 = 值;
set 变量名 := 值;
select 字段名 into 变量名 from 表名 ...;

例如：以下程序演示局部变量的声明、赋值、读取：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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;
1
call p2();

5. if语句

语法格式：

1
2
3
4
5
6
7
8
9
if 条件 then
......
elseif 条件 then
......
elseif 条件 then
......
else
......
end if;

案例：员工月薪sal，超过10000的属于“高收入”，6000到10000的属于“中收入”，少于6000的属于“低收入”

1
2
3
4
5
6
7
8
9
10
11
12
13
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;
1
call p3();

6. 参数

存储过程的参数包括三种形式：

  • in：入参（未指定时，默认是in）
  • out：出参
  • inout：既是入参，又是出参

案例：员工月薪sal，超过10000的属于“高收入”，6000到10000的属于“中收入”，少于6000的属于“低收入”

1
2
3
4
5
6
7
8
9
10
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;
1
2
call p4(5000, @grade);
select @grade;

案例：将传入的工资sal上调10%

1
2
3
4
create procedure p5(inout sal int)
begin
	set sal := sal * 1.1;
end;
1
2
3
set @sal := 10000;
call p5(@sal);
select @sal;

7. case语句

语法格式：

1
2
3
4
5
6
7
8
9
10
case 值
	when 值1 then
	......
	when 值2 then
	......
	when 值3 then
	......
	else
	......
end case;
1
2
3
4
5
6
7
8
9
10
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 冬季。其他非法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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;
1
2
call mypro(9, @season);
select @season;

8. while循环

语法格式：

1
2
3
while 条件 do
	循环体;
end while;

案例：传入一个数字n，计算1~n中所有偶数的和

1
2
3
4
5
6
7
8
9
10
11
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;
1
call mypro(10);

9. repeat循环

语法格式：

1
2
3
4
repeat
	循环体;
	until 条件
end repeat;

PS：条件成立时结束循环

案例：传入一个数字n，计算1~n中所有偶数的和

1
2
3
4
5
6
7
8
9
10
11
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 /* 注意：这里不能添加 ; 结尾 */
	end repeat;
end;
1
2
call mypro(10, @sum);
select @sum;

10. loop循环

语法格式：

1
2
3
4
5
6
7
8
9
10
11
create procedure mypro()
begin 
	declare i int default 0;
  mylp:loop 
		set i := i + 1;
		if i = 5 then 
			leave mylp;
		end if;
		select i;
	end loop;
end;

案例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create procedure mypro()
begin 
	declare i int default 0;
  mylp:loop 
		set i := i + 1;
		if i = 5 then 
			iterate mylp;
		end if;
		if i = 10 then 
		  leave mylp;
		end if;
		select i;
	end loop;
end;

11. 游标cursor

游标（cursor）可以理解为一个指向结果集中某条记录的指针，允许程序逐一访问结果集中的每条记录，并对其进行逐行操作和处理。

使用游标时，需要在存储过程或函数中定义一个游标变量，并通过 DECLARE 语句进行声明和初始化。然后，使用 OPEN 语句打开游标，使用 FETCH 语句逐行获取游标指向的记录，并进行处理。最后，使用 CLOSE 语句关闭游标，释放相关资源。游标可以大大地提高数据库查询的灵活性和效率。

声明游标的语法：

1
declare 游标名称 cursor for 查询语句;

打开游标的语法：

1
open 游标名称;

通过游标取数据的语法：

1
fetch 游标名称 into 变量[,变量,变量......]

关闭游标的语法：

1
close 游标名称;

案例：从dept表查询部门编号和部门名，创建一张新表dept2，将查询结果插入到新表中

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
drop procedure if exists mypro;

create procedure mypro()
begin 
	
	/* 声明变量 */
	declare dept_no int;
	declare dept_name varchar(255);

	/* 声明游标，注意：声明游标的语句必须放在声明普通变量的后面。 */
	declare dept_cursor cursor for select deptno,dname from dept;
	
	/* 删除表dept2 */
	drop table if exists dept2;
	
	/* 创建表 dept2 */
	create table dept2(
		deptno int,
		dname varchar(255)
	);
	
	/* 打开游标 */
	open dept_cursor;
	
	/* 通过游标取数据 */
	/*fetch dept_cursor into dept_no, dept_name;*/
	
	/* 查看以下变量的值 */
	/*select dept_no,dept_name;*/
	
	/* 使用循环从游标中取数据 */
	while true do 
		fetch dept_cursor into dept_no, dept_name;
		/*select dept_no,dept_name;*/
		insert into dept2(deptno,dname) values(dept_no, dept_name);
	end while;
	
	/* 关闭游标 */
	close dept_cursor;
end;

call mypro();

执行结果：

游标cursor执行结果出现异常

出现了异常：异常信息中显示没有数据了。这是因为while true循环导致的。

不过虽然出现了异常，但是表创建成功了，数据也插入成功了：

使用游标cursor创建表成功

注意：声明局部变量和声明游标有顺序要求，局部变量的声明需要在游标声明之前完成。

12. 捕捉异常并处理

语法格式：

1
DECLARE handler_name HANDLER FOR condition_value action_statement
  • handler_name 表示异常处理程序的名称，重要取值包括：
    • CONTINUE：发生异常后，程序不会终止，会正常执行后续的过程。(捕捉)
    • EXIT：发生异常后，终止存储过程的执行。（上抛）
  • condition_value 是指捕获的异常，重要取值包括：
    • SQLSTATE sqlstate_value，例如：SQLSTATE ‘02000’
    • SQLWARNING，代表所有01开头的SQLSTATE
    • NOT FOUND，代表所有02开头的SQLSTATE
    • SQLEXCEPTION，代表除了01和02开头的所有SQLSTATE
  • action_statement 是指异常发生时执行的语句，例如：CLOSE cursor_name

给之前的游标添加异常处理机制：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
drop procedure if exists mypro;

create procedure mypro()
begin 

	declare no int;
	declare name varchar(100);
	declare dept_cursor cursor for select deptno,dname from dept;
	
	/* 通常在这个位置进行异常的处理 */
	declare exit handler for not found close dept_cursor;

	drop table if exists dept2;
	create table dept2(
		no int primary key,
		name varchar(100)
	);
	
	open dept_cursor;
	
	while true do
		fetch dept_cursor into no, name;
		insert into dept2(no,name) values(no,name);
	end while;
	
	close dept_cursor;
end;

call mypro();

13. 存储函数

存储函数：带返回值的存储过程。参数只允许是in（但不能写显示的写in）。没有out，也没有inout。

语法格式：

1
2
3
4
5
CREATE FUNCTION 存储函数名称(参数列表) RETURNS 数据类型 [特征]
BEGIN
	--函数体
	RETURN ...;
END;

“特征”的可取重要值如下：

  • deterministic：用该特征标记该函数为确定性函数（什么是确定性函数？每次调用函数时传同一个参数的时候，返回值都是固定的）。这是一种优化策略，这种情况下整个函数体的执行就会省略了，直接返回之前缓存的结果，来提高函数的执行效率。
  • no sql：用该特征标记该函数执行过程中不会查询数据库，如果确实没有查询语句建议使用。告诉 MySQL 优化器不需要考虑使用查询缓存和优化器缓存来优化这个函数，这样就可以避免不必要的查询消耗产生，从而提高性能。
  • reads sql data：用该特征标记该函数会进行查询操作，告诉 MySQL 优化器这个函数需要查询数据库的数据，可以使用查询缓存来缓存结果，从而提高查询性能；同时 MySQL 还会针对该函数的查询进行优化器缓存处理。

案例：计算1~n的所有偶数之和

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 删除函数
drop function if exists sum_fun;

-- 创建函数
create function sum_fun(n int)
returns int deterministic 
begin 
	declare result int default 0;
	while 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;

14. 触发器

MySQL 触发器是一种数据库对象，它是与表相关联的特殊程序。它可以在特定的数据操作（例如插入（INSERT）、更新（UPDATE）或删除（DELETE））触发时自动执行。MySQL 触发器使数据库开发人员能够在数据的不同状态之间维护一致性和完整性，并且可以为特定的数据库表自动执行操作。

触发器的作用主要有以下几个方面：

  • 强制实施业务规则：触发器可以帮助确保数据表中的业务规则得到强制执行，例如检查插入或更新的数据是否符合某些规则。
  • 数据审计：触发器可以声明在执行数据修改时自动记日志或审计数据变化的操作，使数据对数据库管理员和 SQL 审计人员更易于追踪和审计。
  • 执行特定业务操作：触发器可以自动执行特定的业务操作，例如计算数据行的总数、计算平均值或总和等。

MySQL 触发器分为两种类型: BEFORE 和 AFTER。BEFORE 触发器在执行 INSERT、UPDATE、DELETE 语句之前执行，而 AFTER 触发器在执行 INSERT、UPDATE、DELETE 语句之后执行。

创建触发器的语法如下：

1
2
3
4
5
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 中：

1
2
3
4
5
6
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 值将设置为旧值加上新值。

需要注意的是，在使用 NEW 和 OLD 时，需要根据 DML 操作的类型进行判断，以确定哪个关键字表示新值，哪个关键字则表示旧值。

案例：当我们对dept表中的数据进行insert、delete、update的时候，请将这些操作记录到日志表当中

日志表如下：

1
2
3
4
5
6
7
8
9
10
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表中插入数据时，记录日志

1
2
3
4
5
6
7
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;

查看触发器：

1
show triggers;

删除触发器：

1
drop trigger if exists dept_trigger_insert;

向dept表中插入一条记录：

向dept表中插入一条记录

日志表中多了一条记录：

日志表中多了一条新增记录

触发器2：修改dept表中数据时，记录日志

1
2
3
4
5
6
7
8
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;

更新一条记录：

1
update dept set loc = '北京' where deptno = 60;

日志表中多了一条记录：

日志表中多了一条修改记录

PS：更新一条记录则对应一条日志。如果一次更新3条记录，那么日志表中插入3条记录。

触发器3：删除dept表中数据时，记录日志

1
2
3
4
5
6
7
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;

删除一条记录：

1
delete from dept where deptno = 60;

日志表中多了一条记录：

日志表中多了一条删除记录