MYSQL 存储过程一直执行不结束 mysql存储过程cursor

这里使用的是MySQL 8

• 关键字
• 存储过程相关操作

• 声明语句结束符
• 创建存储过程
• 存储过程开始和结束
• 变量的使用

• 变量赋值
• 用户变量
• 局部变量
• 查看变量值

• 调用存储过程
• 删除存储过程
• 查询数据库有哪些存储过程
• 查看存储过程详情
• 存储过程注释

• 存储过程参数类型

• 参数类型：IN
• 参数类型：OUT
• 参数类型：INOUT

• 存储过程控制语句

• 变量的作用域
• 条件语句
• case语句
• while循环语句
• repeat循环语句
• loop循环语句
• LABELS标签
• ITERATE迭代
• 游标遍历查询结果集

这里使用的是MySQL 8)

关键字

括号中是小写，看起来方便，但关键字还是建议写成大写形式的。不过学习时可以先写成小写，毕竟自己看着舒服！

关键字	作用
DELIMITER （delimiter）	声明存储过程体语句结束符
CREATE PROCEDURE (create procedure)	创建存储过程（其实应该分开，两个关键字，但这样记好记），可以传参
参数类型： IN (in)	表示参数是输入参数（传入值可以是字面量或变量）
参数类型：OUT (out)	表示参数是输出参数，向调用者返回数据(可以返回多个值)（传出值只能是变量）
参数类型： INOUT (inout)	表示参数即是输入参数又是输出参数（值只能是变量）
BEGIN .... END （begin…end)	存储过程开始和结束标签
@	用在用户变量名前，表示该变量是用户变量
DECLARE （declare）	声明局部变量，只能在 BEGIN .... END 内使用
SET （set）	给变量赋值
call	调用存储过程

存储过程相关操作

声明语句结束符

语句结束符用在END关键字后面，用来表示存储体过程结束。如：END $$。

• 命令：

DELIMITER 符号（可以自定义）

• 示例

DELIMITER $$

或者

DELIMITER //

• 注意
  在存储过程结束后，要将语句结束符修改回;，即：

DELIMITER ;

创建存储过程

• 命令

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数数据类型 )

或：

CREATE PROCEDURE 数据库名.存储过程名(IN|OUT|INOUT 参数名 参数数据类型 )

默认情况下，存储过程和默认数据库相关联，如果想指定存储过程创建在某个特定的数据库下，那么在过程名前面加数据库名做前缀。

• 示例

CREATE PROCEDURE myprocedure(IN test_in int)

存储过程开始和结束

• 命令

BEGIN
	存储过程体
END $$

• 示例

CREATE PROCEDURE myprocedure(IN test_in int)
BEGIN
	存储过程体
END $$

这组关键字还可以嵌套使用、贴标签等，示例：

CREATE PROCEDURE myprocedure(IN test_in int)
label1: BEGIN
	label2: BEGIN
		存储过程体
	END label2 ; -- 注意这里的封号
END label1 $$

变量的使用

变量赋值

• 命令

set 变量名 = 值

用户变量

用户变量可以在存储过程体外声明，使用@符号表明该变量是用户变量。

• 形式

@变量名

• 声明并赋值

set @u_variable= 1；

• 注意

• @只是表明变量是用户变量，可以在存储过程体外声明，并不意味着用户变量是用@来创建，如：@u_variable;是不正确的。
• 声明和使用用户变量时，都需要加上@，如：select @u_variable。
• 滥用用户变量会导致程序难以理解及管理。

局部变量

局部变量只能存在与BEGIN ... END之内，使用DECLARE声明

• 命令

DECLARE 变量名 数据类型 [default value]

• 数据类型
  MySQL数据类型：int, float, date,varchar(length)等
• 声明示例

BEGIN
	DECLARE test_int int unsigned default 1;  
	DECLARE test_numeric number(8,2) DEFAULT 9.95;  
	DECLARE test_date date DEFAULT '2020-7-18';  
	DECLARE test_datetime datetime DEFAULT '2020-7-18 15:55:59';  
	DECLARE test_varchar varchar(255) DEFAULT 'hello world';
END $$

• unsigned : 无符号
• signed ：有符号

• 引用示例

CREATE PROCEFURE myprocedure(OUT t_int int)
BEGIN
	DECLARE test_int int unsigned default 1;
	set t_int = test_int;
END $$

查看变量值

• 命令
  用在BEGIN .. END内

select 局部变量名；

• 或者：
  用在存储过程体外

select @用户变量名；

调用存储过程

• 命令

call 存储过程名

• 示例：@t_int是返回参数

call myprocedure(@t_int);
select @t_int;

删除存储过程

• 命令

DROP PROCEDURE 存储过程名

查询数据库有哪些存储过程

• 命令

show procedure status where db='数据库名';

或者：

select routine_name from information_schema.routines where routine_schema='数据库名';

查看存储过程详情

• 命令

show create procedure 数据库名.存储过程名;

存储过程注释

使用双横杠代表注释。

• 命令

-- 这里是注释内容

存储过程参数类型

• 首先，创建一个数据库，并插入数据

create table t_user(
id int primary key auto_increment,
username varchar(30),
sex varchar(10)
)engine = innodb;

insert into t_user(id, username, sex) values(default, "吕布", "男");
insert into t_user(id, username, sex) values(default, "貂蝉", "女");

以下写的是比较完整的存储过程模板，可以借鉴以下！！！

参数类型：IN

表示参数是输入类型参数，由调用者传递给存储过程（传入值可以是字面量或变量）。下面代码：从用户表中查询用户id为1的用户。

• 代码示例

DELIMITER $$
DROP PROCEDURE IF EXISTS test_in;
CREATE PROCEDURE test_in(IN t_id int)
BEGIN
	SELECT * FROM t_user WHERE id = t_id;
END $$
DELIMITER ;

set @t_id = 1;
call test_in(@t_id);

参数类型：OUT

表示参数是输出参数，由存储过程返回给调用者，(可以返回多个值，传出值只能是变量）。下面代码：从user表中查出用户id为2的用户的性别。

• 代码示例

DELIMITER $$
DROP PROCEDURE IF EXISTS test_out;
CREATE PROCEDURE test_out(OUT t_sex varchar(10))
BEGIN
	DECLARE u_sex varchar(10) DEFAULT '男';
	set u_sex = (select sex from t_user where id=2);
	set t_sex = u_sex;
END $$
DELIMITER ;
call test_out(@t_sex);
select @t_sex;

参数类型：INOUT

表示这个参数即是输入参数又是输出参数，以下代码功能：传递用户名称，获取用户性别。

• 代码示例

DELIMITER $$
DROP PROCEDURE IF EXISTS test_inout;
CREATE PROCEDURE test_inout(INOUT t_msg varchar(30))
BEGIN
	DECLARE t_msg_tmp varchar(10) DEFAULT '男';
	set t_msg_tmp = (select sex from t_user where username=t_msg);
	set t_msg = t_msg_tmp;
END $$
DELIMITER ;
set @t_msg = '貂蝉';
call test_inout(@t_msg);
select @t_msg;

存储过程控制语句

变量的作用域

在BEGIN ... END内声明的局部变量，在嵌套使用时，外层声明的变量可以在内层进行访问，但是内层的访问在遇到自己的END时，就会消失，无法在外层访问到。可以通过OUT参数等方式保存下来。

• 代码示例

DELIMITER $$
DROP PROCEDURE IF EXISTS proc;
CREATE PROCEDURE proc()
BEGIN
		DECLARE x1 int unsigned default 1;
		BEGIN
			DECLARE x1 int unsigned default 2; -- 内层的x1只在本层有效
			select x1; 
		END; -- 注意这里的封号
		select x1; -- 这里是外层的x1，内层的x1已经失效
END $$
DELIMITER ;
call proc();

条件语句

• 命令

if 条件 then
	执行语句;
elseif 条件 then
	执行语句;
else
	执行语句;
end if;

• 代码示例

DELIMITER $$
DROP PROCEDURE IF EXISTS proc;
CREATE PROCEDURE proc(INOUT t_num int)
BEGIN
	DECLARE x1 int unsigned default 3;
	if x1=1 then
		set t_num=1;
	elseif x1=2 then
		set t_num=2;
	else
		set t_num=3;
	end if;
END $$
DELIMITER ;

set @t_num = 0;
call proc(@t_num);
select @t_num;

case语句

• 命令

case 条件变量
when 条件值 then
	执行语句;
when 条件值 then
	执行语句;
else
	执行语句;
end case;

• 代码示例

DELIMITER $$
DROP PROCEDURE IF EXISTS proc;
CREATE PROCEDURE proc(INOUT t_num int)
BEGIN
	DECLARE x1 int unsigned default 3;
	case x1
	when 1 then
		set t_num = 1;
	when 2 then
		set t_num = 2;
	else 
		set t_num =3;
	end case;
END $$
DELIMITER ;

set @t_num = 0;
call proc(@t_num);
select @t_num;

while循环语句

先判断条件是否符合，符合才执行语句。只有符合条件，才执行循环体。也就是只有符合条件才进行循环

• 命令

while 条件 do
	执行语句；
end while;

• 代码示例

DELIMITER $$
DROP PROCEDURE IF EXISTS proc;
CREATE PROCEDURE proc(INOUT t_num int)
BEGIN
	while t_num <3 do
		set t_num = t_num + 1;
	end while;
END $$
DELIMITER ;

set @t_num = 0;
call proc(@t_num);
select @t_num;

repeat循环语句

先执行依次语句，在判断是否符合条件，不符合则继续执行语句。直到符合条件，才不执行循环体，否则一直执行，也就是直到符合条件才退出循环。

• 命令

repeat
	执行语句
until 循环条件
end repeat;

• 代码示例

DELIMITER $$
DROP PROCEDURE IF EXISTS proc;
CREATE PROCEDURE proc(INOUT t_num int)
BEGIN
	repeat
		set t_num = t_num + 1;
	until t_num >3
	end repeat;
END $$
DELIMITER ;

set @t_num = 0;
call proc(@t_num);
select @t_num;

loop循环语句

loop不需要给定循环条件，使用标签和leave关键字退出循环。我们可以使用if条件语句去控制loop循环次数。

• 命令

自定义标签:loop
	执行语句；
	leave 自定义标签;
end loop;

• 代码示例

DELIMITER $$
DROP PROCEDURE IF EXISTS proc;
CREATE PROCEDURE proc(INOUT t_num int)
BEGIN
	loop_label:loop
		set t_num = t_num + 1;
		if t_num > 3 then
			leave loop_label;
		end if;
	end loop;
END $$
DELIMITER ;

set @t_num = 0;
call proc(@t_num);
select @t_num;

LABELS标签

标签，也叫标号，可以用在 begin、repeat、while 或者 loop 语句前，语句标号只能在合法的语句前面使用。可以跳出循环，使运行指令达到复合语句的最后一步。不需要加引号，直接写即可！如上loop_label所示那样！

ITERATE迭代

ITERATE 通过引用复合语句的标签，来重新开始复合语句。

• 命令

iterate 标签;

• 代码示例

DELIMITER $$
DROP PROCEDURE IF EXISTS proc;
CREATE PROCEDURE proc(INOUT t_num int)
BEGIN
	loop_label:loop
		set t_num = t_num + 1;
		if t_num = 3 then
			set t_num = t_num+1;
			iterate loop_label;
		end if;
		
		if t_num > 6 then
			leave loop_label;
		end if;
	end loop;
END $$
DELIMITER ;

set @t_num = 0;
call proc(@t_num);
select @t_num;

游标遍历查询结果集

当我们需要对查询的结果集循环进行处理时，就可以使用游标来逐一获取结果集中的值，再逐一进行处理即可。

• 命令

-- 定义while循环标志，不同循环语句，判定条件不同，注意了
-- 在MySQL中，int类型0代表false，1代表true，done可以自定义，是比变量名而已
DECLARE done int default true;
-- 定义游标
DECLARE cur CURSOR FOR (具体的查询语句);
-- 设定循环标志为结束标志，当游标中没有数据时，设定标志位为false，结束while循环
DECLARE CONTINUE HANDLER FOR NOT FOUND set done = false;
-- 打开游标
OPEN cur;
-- 使用while循环语句
while done do
	-- 使用FETCH关键字从游标中取值，使用INTO关键字将值注入局部变量
	FETCH cur INTO 局部变量1, 局部变量2;
	-- 具体操作
	执行语句;
end while;
-- 关闭游标
CLOSE cur;

• 数据库表
• 代码示例

DELIMITER $$
DROP PROCEDURE IF EXISTS proc;
CREATE PROCEDURE proc()
BEGIN
	-- 为数据库表的每个字段设置局部变量，用户保存
	DECLARE tmp_id int unsigned default 1;
	DECLARE tmp_username varchar(30) default '';
	DECLARE tmp_sex varchar(10) default '男';

	-- 定义循环标志
	DECLARE done int default true;
	-- 定义游标
	DECLARE cur CURSOR FOR (select * from t_user);
	-- 设定循环标志为结束标志
	DECLARE CONTINUE HANDLER FOR NOT FOUND set done = false;
	-- 打开游标
	OPEN cur;
	-- 使用while循环语句
	while done do
		FETCH cur INTO tmp_id, tmp_username, tmp_sex;
		-- 具体操作
		select tmp_id,tmp_username,tmp_sex;
	end while;
	-- 关闭游标
	CLOSE cur;
END $$
DELIMITER ;
call proc();