mysql存储过程整理

mysql存储过程梳理

0.前言-为什么要使用存储过程

​ 在做一些复杂业务逻辑时，如果程序与数据库进行多次交互，会增加连接资源的消耗，增加整个业务逻辑处理的时间，存储过程能够减少程序与数据库的交互次数，节省连接资源，加快处理速度。

以下示例均在8.0.21版本下测试成功。

1.优缺点

1.1 优点

• 存储过程能够减少程序与数据库的交互次数，节省连接资源，加快处理速度

• 创建时会先编译，后续的调用都不需要再次编译

• 生产环境中，可以通过直接修改存储过程的方式修改业务逻辑，而不用重启服务器

1.2 缺点

• 过程化编译，维护成本高，尤其是在一些复杂的业务逻辑中
• 测试不方便，无法debug
• 不同数据库之间可移植性差，语法不一样

2.基本语法

2.1 基本关键字

2.1.1 delimiter

声明结束符为:

delimiter

告诉mysql解释器，该段命令是否已经结束了，mysql是否可以执行了。
默认情况下，delimiter是分号;。在命令行客户端中，如果有一行命令以分号结束，
那么回车后，mysql将会执行该命令。如输入下面的语句
mysql> select * from test_table;
然后回车，那么MySQL将立即执行该语句。

但有时候，不希望MySQL这么做。在为可能输入较多的语句，且语句中包含有分号

例如下面的意思为，以$$作为结束符号,表示这个存储过程的结尾

delimiter $$

2.1.2 create

创建一个存储过程

CREATE PROCEDURE

2.1.3 drop

删除一个存储过程

drop procedure pro_name

2.1.4 begin|end

逻辑代码块

-- 开始
BEGIN
...
-- 结束
END

2.2 语法结构

2.2.1 示例

DELIMITER $$
CREATE
	PROCEDURE demo_procedure()
BEGIN
	select 'hello procedure';
END $$
DELIMITER ;
call demo_procedure();

2.2.2 总体结构

[]内的参数为可选参数

CREATE
	[DEFINER = user]
	PROCEDURE sp_name ([proc_parameter[,...]])
	[characteristic ...] routine_body
-- proc_parameter 参数部分，可以如下书写:
	[ IN | OUT | INOUT ] param_name type
	-- type类型可以是MYSQL支持的所有类型
-- toutine_body(程序体)部分，可以书写合法的SQL语句 ---- BEGIN ... END

2.2.3 routine_body(代码体)结构

BEGIN
...
END $$

2.3 变量

2.3.1 声明与赋值

2.3.1.1 declare 声明

声明变量 declare var_name type[default var_value]
举例: declare test_name varchar(32);

2.3.1.2 set 赋值

set test_name = '张三';

2.3.1.3 into赋值

select '张三' into  test_name

完整示例

DELIMITER $$

CREATE PROCEDURE test_var_procedure()
BEGIN
    declare test_name varchar(32) default '李四';
    set test_name = '张三';
    select test_name;
END $$
DELIMITER ;

call test_var_procedure();

2.3.2 局部变量与用户变量

2.3.2.1 局部变量

用户自定义，在begin-end块中使用

语法:
声明变量 declare var_name type[default var_value]
举例: declare test_name varchar(32);

-- set赋值
DELIMITER $$
CREATE PROCEDURE test_procedure()
BEGIN
	declare test_name varchar(32);
	set test_name = '张三';
	select test_name;
END $$
DELIMITER ;

2.3.2.2 用户变量

用户自定义，当前会话(连接)有效

语法:
@test_name
不需要提前声明，使用即声明

DELIMITER $$
CREATE PROCEDURE test_procedure()
BEGIN
	set @test_name = '张三';
	select @test_name;
END $$
DELIMITER ;

call test_procedure();
-- 同一会话中直接查询也能查询出来
select @test_name

2.4 入参、出参

2.4.1 入参

IN表示入参，需要传入到存储过程中的参数

DELIMITER $$
CREATE PROCEDURE  test_procedure(IN age int,IN name varchar(32))
BEGIN
	set @age = age;
	set @name = name;
	select @age,@name;
END $$
DELIMITER ;

call test_procedure(18,'张三');

2.4.2 出参

OUT表示出参

DELIMITER $$
CREATE PROCEDURE test_procedure(IN p_name varchar(32),OUT age int)
BEGIN
	
	SELECT 18 INTO age;
 -- set age = 19;
	
END $$
DELIMITER ;

call test_procedure('张三',@age);
SELECT @age;

2.4.3 出入参

INOUT

DELIMITER $$
CREATE PROCEDURE test_procedure(INOUT age int)
BEGIN
	
	SELECT 18 INTO age;
 -- set age = 19;
	
END $$
DELIMITER ;

-- 通过变量传参进，再通过同一个变量出
set @age = 20;
call test_procedure(@age);
SELECT @age;

2.5 流程控制

写流行控制的时候，优先把结构写好，再补齐内部逻辑，比如写IF后紧跟着应该先把END IF写好，逻辑再补在中间，防止遗漏END。

2.5.1 判断IF

-- 语法
IF condition THEN statement_list
	[ELSEIF condition THEN statement_list] ...
	[ELSE statement_list]
END IF

示例:

DELIMITER $$
CREATE PROCEDURE test_procedure(IN p_name VARCHAR(32),OUT o_age int)
BEGIN

	IF p_name = '张三'
		THEN set o_age = 18;
	ELSE
		set o_age = 19;
	END IF;
END $$
DELIMITER ;

call test_procedure('李四',@age);
SELECT @age;

2.5.2 CASE

语义类似于java中的switch..case，CASE也在sql语句中常用到

语法：

-- 语法一
CASE case_value
	WHEN when_value THEN statement_list
	[WHEN when_value THEN statement_list]
	...
	[ELSE statement_list]
END CASE
-- 语法二
CASE
	WHEN search_condition THEN statement_list
	[WHEN search_condition THEN statement_list]
	...
	[ELSE statement_list]
END CASE

示例:

DELIMITER $$
CREATE PROCEDURE demo_procedure(IN name varchar(32),OUT age INT)
BEGIN
	CASE name
		WHEN '张三' 
		THEN set age = 12;
        ELSE set age = 18;
	END CASE
	CASE
		WHEN name = '李四'
		THEN set age = 20;
		ELSE set age = 18;
	END CASE;
END $$
DELIMITER ;
call demo_procedure('张三',@age);
select @age;

2.5.3 循环LOOP

-- 语法
[begin_label:] LOOP
	statement_list
END LOOP [end_label]

begin_label:给循环声明一个变量，这个变量指向这个循环体

举例

需要说明，loop是死循环，需要手动退出循环，我们可以使用leave来退出。

可以把leave看成我们java中的break；与之对应的，就有iterate--类比java的continue

-- 循环打印1-10
DELIMITER $$
CREATE PROCEDURE demo_procedure()
BEGIN
	-- 声明一个下标变量，初始值为1
	DECLARE demo_index INT DEFAULT 1;
	demo_loop:LOOP
		-- 若大于10，则跳出循环
		IF demo_index >10
		THEN LEAVE demo_loop;
		END IF;
		select demo_index;
		set demo_index = demo_index + 1;
	END LOOP demo_loop;

END $$
DELIMITER ;
call demo_procedure();

2.5.4 跳出LEAVE

Leave语句表明退出指定标签的流程控制语句块，通常会用在begin…end，以及loop, repeat, while的循环语句

-- 循环打印1-10
DELIMITER $$
CREATE PROCEDURE demo_procedure()
BEGIN
	-- 声明一个下标变量，初始值为1
	DECLARE demo_index INT DEFAULT 1;
	demo_loop:LOOP
		-- 若大于10，则跳出循环
		IF demo_index >10
		THEN LEAVE demo_loop;
		END IF;
		select demo_index;
		set demo_index = demo_index + 1;
	END LOOP demo_loop;

END $$
DELIMITER ;
call demo_procedure();

2.5.5 ITERATE

类比于java中的continue

DELIMITER $$
CREATE PROCEDURE demo_procedure()
BEGIN
-- 打印到10的时候才跳出，拦截打印到2的时候跳出
	DECLARE num INT DEFAULT 1;
	demo_loop: LOOP
		SELECT num;
		set num = num +1;
		IF num <10
			THEN ITERATE demo_loop;
		END IF;
		IF num =3 or num = 11
			THEN LEAVE demo_loop;
		END IF;
	END LOOP demo_loop;
END $$
DELIMITER ;

call demo_procedure();

2.5.6 REPEAT

类似于java的do..while

-- 语法
[demo_label:] REPEAT
	statement_list
UNTIL search_condition -- 直到...为止
END REPEAT [demo_label]

start=>start: start
statement=>operation: statement
expressions=>condition: expressions
stop=>end: stop
start->statement->expressions
expressions(false)->statement
expressions(true)->stop

示例:

DELIMITER $$
CREATE PROCEDURE demo_procedure()
BEGIN
-- 循环打印1-10
	DECLARE num INT DEFAULT 1;

	demo_repeat: REPEAT
		SELECT num;
		set num = num + 1;
		UNTIL num>10
	END REPEAT demo_repeat;
END $$
DELIMITER ;
call demo_procedure();

2.5.7 WHILE

类似于java中的while

-- 语法
[demo_label:] WHILE demo_condition DO
	statement_list
END WHILE [demo_lable]

示例:

-- 循环打印1-10
DELIMITER $$
CREATE PROCEDURE demo_procedure()
BEGIN
-- 循环打印1-10
	DECLARE num INT DEFAULT 1;
	demo_while: WHILE num <=10 DO
		select num;
		set num = num+1;
	END WHILE demo_while;

END $$
DELIMITER ;
call demo_procedure();

2.6 游标

游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标充当指针的作用。尽管游标能遍历结果中的所有行，但他一次只指向一行。游标的作用就是用于对查询数据库所返回的记录进行遍历，以便进行相应的操作。

2.6.1 用法

-- 1.声明一个游标，这里的demo_table可以是任意集合
DECLARE cur_name CURSOR FOR demo_table
-- 2.打开定义的游标
OPEN cur_name
-- 3.获得下一行数据
FETCH cur_name INTO field_one,field_two,...
-- 4.释放游标
CLOSE cur_name

2.6.2 示例

-- 建表，造数据
-- name,age
-- 张三,18
-- 李四,19
-- 需求：将表中所有人的姓名和年龄拼接成一个字符串：张三,18;李四,19;
CREATE TABLE `user_information` (
  `name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `age` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO user_information(name,age) VALUES ('张三',18);
INSERT INTO user_information(name,age) VALUES ('李四',19);

-- -------------------- 创建存储过程----------------------
DELIMITER $$
-- 将用户信息拼接成一个字符串 张三,18;李四,19；
CREATE PROCEDURE demo_procedure()
BEGIN
-- 临时存储姓名
DECLARE c_name VARCHAR(255);
-- 临时存储年龄
DECLARE c_age INT;
-- 存放拼接字符串
DECLARE res VARCHAR(255) DEFAULT '';
DECLARE done INT DEFAULT 0;
-- 1.定义游标
DECLARE demo_cursor CURSOR FOR select name,age from user_information;
-- 2.捕获系统抛出的 not found 错误，如果捕获到，将 done 设置为 1  相当于try异常
DECLARE CONTINUE HANDLER FOR NOT found SET done=1;
-- 3.打开游标
OPEN demo_cursor;
demo_loop: LOOP
	-- 注意FETCH的位置，FETCH是触发not found的触发点，但是done的判断和java的catch异常有区别，
	-- FETCH触发异常的时候done已经为1了，但是done的IF判断还没有执行，要在下一次loop循环执行，所以一定要注意FETCH
	-- 和done判断的位置。
	FETCH demo_cursor INTO c_name,c_age;
	-- 若done=1，即发生not found异常，则结束循环
	IF done=1
		THEN LEAVE demo_loop;
	END IF;
	SET res = CONCAT(res,c_name,',',c_age,';');
END LOOP demo_loop;
SELECT res;
-- 释放游标(ps:网上很多文章说游标未释放会产生死锁，但是我没有复现-_-)
CLOSE demo_cursor;
END $$
DELIMITER ;

call demo_procedure();

2.7 存储过程中的HANDLER

HANDLER的声明必须要放在变量声明、游标声明的后面

声明顺序依次为变量、游标、HANDLER

异常抓取

-- 语法
-- 执行顺序为，发生异常->抓取到condition_value的异常->执行statement语句->执行handler_action相应的动作
DECLARE handler_action HANDLER
FOR condition_value[,condition_value]...
statement

-- 常见handler_aciton
handler_action:{
	CONTINUE|
	EXIT|
	UNDO
}
-- 常见的condition_value,condition_value也可以是指定的错误码
{
	mysql_error_code|
	SQLSTATE [VALUE] sqlstate_value|
	condition_name|
	SQLWARNING|
	NOT FOUND|
	SQLEXCEPTION
}
-- CONTINUE 继续往下执行程序
CONTINUE: Execution of the current program continues.
-- EXIT 退出，后面语句不执行
EXIT:Execution terminates for the BEGIN ... END compound statement in which
the handler is declared. This is true even if the condition occurs in an inner block.
-- UNDO 后面语句不执行，前面执行过的语句撤销(据说mysql还不支持，暂未验证)