MySQL存储过程中使用临时表、游标以及调试存储过程

最新推荐文章于 2024-08-29 10:43:19 发布

张三李四王五赵六

最新推荐文章于 2024-08-29 10:43:19 发布

阅读量800

点赞数 3

文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/weixin_41343836/article/details/108384886

版权

1、使用临时表和嵌套游标的存储过程

最近因为业务需求原因，需要用存储过程来实现一些数据库操作，在网上看了一些大佬的相关内容

，最后写出了这个嵌套了三重游标的存储过程，不容易啊，在这里记录一下，存储过程如下:

CREATE DEFINER = 'root'@'%'
PROCEDURE ddent.QUERY_FAULT(IN class_name VARCHAR(255), IN fault VARCHAR(255))
BEGIN
-- 定义结束标识 由于嵌套了多层游标所有定义了多个标识
  DECLARE done INT DEFAULT FALSE;
	DECLARE edone INT DEFAULT FALSE;
	DECLARE fdone INT DEFAULT FALSE;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;-- 绑定当前游标的结束标识,这里需要注意的是，多个游标的绑定不能写在一起，需要在每个游标的begin  end之间绑定
-- 创建临时表
  create temporary table if not exists tb1 (id integer, name varchar(100), type varchar(10),f integer); 
-- 创建好先清空临时表
  truncate TABLE tb1;  
	BEGIN
-- 定义游标遍历行的变量
	declare c1_id int;
	declare c1_name varchar(100);
	declare c1_type varchar(10);
	declare c1_f int;

	declare out_id int;
	declare out_title VARCHAR(300);
	declare out_f int;
-- 定义游标1以及结束标识
-- 查询知识库分类，故障分类，故障子类,取相似度最高的前十条
  DECLARE cur1 CURSOR FOR (
			select id,name,type,similarity(class_name,name) f from (
      select id,name,'t1' type from t_know where deleted=0 and isopen=3200 and similarity(class_name,name)>=10
       union all
      select id,name,'t2' type from t_ify where deleted=0 and similarity(class_name,name)>=10
       union all
      select id,name,'t3' type from t_item where deleted=0 and similarity(class_name,name)>=10
       ) t order by f desc limit 10 );
-- 打开游标
	OPEN cur1;
-- 开始循环
	loop_1: LOOP # 为当循环取个别名
-- 游标每一行对应的变量,需要注意的是不能与sql中的列名一样
		FETCH cur1 INTO c1_id,c1_name,c1_type,c1_f; # 使用定义好的变量对应查询语句的列
		IF done THEN
      LEAVE loop_1;#跳出循环
		ELSE
			set edone = false; -- 重置标识
-- 通过分类递归查询他的所有子类
				if c1_type='t1' THEN #分支内容
					begin
						declare c2_id int; # 使用定义好的变量对应查询语句的列
						declare cur2 cursor for select id from t_know where FIND_IN_SET(id,fn_getKnowtypeChildids(c1_id));#通过第一条语句的结果c1_id当做条件查询，这里递归单独写了一个函数fn_getKnowtypeChildids
						DECLARE CONTINUE HANDLER FOR NOT FOUND SET edone = true;-- 绑定当前游标的结束标识
						OPEN cur2; 
						loop_2: LOOP 
						fetch cur2 INTO c2_id; 
						if edone then 
							LEAVE loop_2; 
						else
							set fdone = FALSE; 
							begin
-- 通过第二条语句返回的结果c2_id当做条件查询
-- 通过分类查询内容,将知识标题相似度大于等于10的插入到临时表中
								declare cur3 cursor for select id,title,similarity(fault,title) f from t_content where deleted=0 and status=201001 and similarity(fault,title)>=10 and typeid=c2_id;
								DECLARE CONTINUE HANDLER FOR NOT FOUND SET fdone = true;-- 结束标识
								open cur3;
								loop_3: loop
								fetch cur3 into out_id,out_title,out_f; 
								if fdone then 
									leave loop_3;
								else 
									 insert into tb1(id,name,type,f) values(out_id,out_title,'t1',out_f); #将最终需要的结果放入临时表中
								end if;
								end LOOP loop_3; #第三层循环结束
								close cur3;
							end;
						end if;
						end LOOP loop_2; #第二层循环结束
						CLOSE cur2; #关闭第二个游标
					END;
				elseif c1_type='t2' THEN 
					begin
						-- 略
					END;
				elseif c1_type='t3' then
					begin
						-- 略
					end;
				end if;
    END IF;
	END LOOP loop_1; #整个大循环结束
	CLOSE cur1; #关闭游标
  select id, name, type, f from tb1 order by f DESC; # 返回临时表的内容
	END;
END