MySQL数据库-存储过程-游标、条件处理程序

爱丽思 25天前 ⋅ 96 阅读

游标（CURSOR）是用来存储查询结果集的数据类型，再储存过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH、和CLOSE，其语法如下：

声明游标

DECLARE 游标名称 CURSOR FOR 查询语句;

打开游标

OPEN 游标名称;

获取游标记录

FETCH 游标名称 INTO 变量[,变量];

关闭游标

CLOSE 游标名称;

例子：根据传入的参数uage来查询tb_user中所有用户年龄小于uage的用户名（name）和专业（profession），并将用户名和专业插入所创建的一张新表中。

-- 如果存在相同的存储过程，删除存储过程
DROP PROCEDURE IF EXISTS copydate_to_new_table;
-- 定义存储过程
create procedure copydate_to_new_table(in age int)
begin
	-- 定义变量存储姓名和专业,注意变量定义需要在游标之前进行定义
	declare new_uname varchar(255) default ''; 
	declare new_profession varchar(255) default ''; 
	-- 定义游标存储查询出来的数据
	declare u_cursor cursor for select name,profession from tb_user where uage < age;
	-- 创建表存储游标数据
	drop table if EXISTS new_tb_user;
	create table if not exists new_tb_user(
		id int PRIMARY KEY auto_increment,
		name VARCHAR(100),
		profession VARCHAR(100)
	);
	-- 开启游标
	OPEN u_cursor;
	-- 循环游标数据，这里使用while会导致报错，但是不影响遍历完游标数据
	while true do
		-- 游标取值
		FETCH u_cursor INTO new_uname,new_profession;
		-- 插入数据
		insert into new_tb_user values(null,new_uname,new_profession);
	end while;
	-- 关闭游标
	CLOSE u_cursor;
end;

-- 调用存储过程
 call copydate_to_new_table(30);

条件处理程序

条件处理程序（handler）可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。具体语法为：

DECLARE handler_action HANDLER FOR condition_value [,condition_value]... statement;

handler_action 
    CONTINUE:继续执行当前程序
    EXIT:终止执行当前程序
condition_value 
    SQLSTATE sqlstate_value: 状态码，如 02000
    SQLWARNING:所有以01开头的SQLSTATE代码的简写
    NOT FOUND:所有有以02开头的SQLSTATE代码的简写
    SQLEXCEPTION:所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码简写

针对上述例子可以可通过添加条件处理程序解决while死循环导致的报错，通过添加以下SQL结局

在定义完游标后再定义以下条件处理程序即可

-- 定义条件处理程序 当满足条件时 exit（退出）当前程序（存储过程），close u_cursor 关闭游标
declare exit handler for SQLSTATE '02000' close u_cursor;

或定义以下条件处理程序

-- NOT FOUND 处理所有有以02开头的SQLSTATE，满足条件推出程序（存储过程），close u_cursor 关闭游标
declare exit handler for NOT FOUND close u_cursor;

上述例子的完整过程如下

-- 删除存储过程
DROP PROCEDURE IF EXISTS copydate_to_new_table;
-- 定义存储过程
create procedure copydate_to_new_table(in age int)
begin
	-- 定义变量存储姓名和专业,注意变量定义需要在游标之前进行定义
	declare new_uname varchar(255) default ''; 
	declare new_profession varchar(255) default ''; 
	-- 定义游标存储查询出来的数据
	declare u_cursor cursor for select name,profession from tb_user where uage < age;
	-- 定义条件处理程序，处理游标循环结束问题
	-- 当满足条件时 exit（退出）当前程序（存储过程），close u_cursor 关闭游标
  -- declare exit handler for SQLSTATE '02000' close u_cursor;
	-- 或定义以下条件处理程序
	declare exit handler for NOT FOUND close u_cursor;
	-- 创建表存储游标数据
	drop table if EXISTS new_tb_user;
	create table if not exists new_tb_user(
		id int PRIMARY KEY auto_increment,
		name VARCHAR(100),
		profession VARCHAR(100)
	);
	-- 开启游标
	OPEN u_cursor;
	-- 循环游标数据
	while true do
		-- 游标取值
		FETCH u_cursor INTO new_uname,new_profession;
		-- 插入数据
		insert into new_tb_user values(null,new_uname,new_profession);
	end while;
	-- 关闭游标
	CLOSE u_cursor;
end;

-- 调用存储过程
call copydate_to_new_table(30);

注意：转载请携带文章源地址

#数据库 #MySQL #存储过程