MySQL存储过程、递归调用

MySQL存储过程、递归调用

实现字典数据的预处理，维护类别表、数据表、tree表，数据库在jwzh_manager库

1、先将excel导入到system_dict表，按表字段注释对应匹配。

2、编写存储过程处理数据

CREATE DEFINER=`root`@`%` PROCEDURE `handle_system_dict`()
BEGIN
	#Routine body goes here...
	-- 声明变量
	declare done int default false;
	declare dict_type_variable varchar(100);
	
	-- 创建一个 name 为cur的游标
	declare cur cursor for select dict_type from system_dict group by dict_name, dict_type having count(dict_parent_code) >0;
	
	-- 当游标走到末尾 也就是遍历完成之后 将标志位设置成true
	declare continue HANDLER for not found set done = true;
	
	-- 删除处理表数据
	truncate table system_dict_type_bak;
	truncate table system_dict_data_bak;
	truncate table system_dict_tree_bak;
	
	-- 插入type表
	insert into system_dict_type_bak (dict_name, dict_type, dict_view)
	select dict_name, dict_type, '1' from system_dict group by dict_name, dict_type having count(dict_parent_code) =0
	union all
	select dict_name, dict_type, '2' from system_dict group by dict_name, dict_type having count(dict_parent_code) >0;
	commit;
	
	-- 插入data表
	insert into system_dict_data_bak (dict_sort, dict_label, dict_code, dict_type)
	select dict_sort, dict_label, dict_code, dict_type from system_dict where dict_type in ( select dict_type from system_dict group by dict_name, dict_type having count(dict_parent_code) =0 );
	commit;

	-- 插入tree表
	insert into system_dict_tree_bak (dict_code, dict_parent_code, dict_sort, dict_label_full, dict_type)
	select dict_code, dict_parent_code, dict_sort, dict_label, dict_type from system_dict where dict_type in ( select dict_type from system_dict group by dict_name, dict_type having count(dict_parent_code) >0 );
	commit;
	
	-- 开启游标
	open cur;
	
		-- 进行循环
		read_loop:loop
		
			-- 将游标中是数据填充到 dict_type 当中
			fetch cur into dict_type_variable;
			
			if done then -- 如果到达末尾
				leave read_loop; -- 跳出循环相当于 break;
			end if;
						
			-- 逻辑操作
			CALL handle_system_dict_tree_first(dict_type_variable); 
		
		-- 结束循环
		end loop;
	
	-- 关闭游标
	close cur;
	
	update system_dict_tree_bak set ancestors = substring(ancestors, 2);
	commit;
	
END

CREATE DEFINER=`root`@`%` PROCEDURE `handle_system_dict_tree_first`(IN `dictType` varchar(100))
BEGIN
	#Routine body goes here...
	
	-- 声明变量
	declare done int default false;
	declare ancestors_variable varchar(500); 
	declare dict_type_variable varchar(100);  
	declare dict_level int;
	declare dict_code_variable varchar(32);
	
	-- 如果type为1获取第一级数据
	declare cur cursor for select ancestors, dict_type, dict_code from system_dict_tree_bak where dict_parent_code is null and dict_type = dictType;
	
	-- 当游标走到末尾 也就是遍历完成之后 将标志位设置成true
	declare continue HANDLER for not found set done = true;
	
	-- ancestors, dict_level参数赋值
	set dict_level = 0;
	
	-- 修改第一级 dict_level
	update system_dict_tree_bak set dict_level = CONCAT(dict_level, '') where dict_parent_code is null and dict_type = dictType;
	commit;
	
	-- 开启游标
	open cur;

		-- 进行循环
		read_loop:loop

			-- 将游标中是数据填充到 dict_type 当中
			fetch cur into ancestors_variable, dict_type_variable, dict_code_variable;

			if done then -- 如果到达末尾
				leave read_loop; -- 跳出循环相当于 break;
			end if;
			
-- 			下面的select是输出断点
-- 			select dict_level, ancestors_variable, dict_type_variable, dict_code_variable;

			-- 逻辑操作
			CALL handle_system_dict_tree_other(dict_type_variable, dict_code_variable, ancestors_variable, dict_level);

		-- 结束循环
		end loop;

	-- 关闭游标
	close cur;

END

CREATE DEFINER=`root`@`%` PROCEDURE `handle_system_dict_tree_other`(IN `dictType` varchar(100),IN `dictCode` varchar(32),IN `dictAncestors` varchar(500), IN `dictLevel` int)
BEGIN
	#Routine body goes here...
	
	-- 声明变量
	declare done int default false;
	declare ancestors_variable varchar(500); 
	declare dict_type_variable varchar(100);  
	declare dict_level int;
	declare dict_code_variable varchar(32);
	

	-- 获取下一级
	declare cur cursor for select ancestors, dict_type, dict_code from system_dict_tree_bak where dict_parent_code = dictCode and dict_type = dictType;

	-- 当游标走到末尾 也就是遍历完成之后 将标志位设置成true
	declare continue HANDLER for not found set done = true;
	
	-- ancestors, dict_level参数赋值
	set ancestors_variable = CONCAT(dictAncestors, ',', dictCode);
	set dict_level = dictLevel + 1;
	
-- 	设置递归层级
	SET max_sp_recursion_depth=10;
	-- 修改下一级 ancestors, dict_level
	update system_dict_tree_bak set ancestors = ancestors_variable, dict_level = CONCAT(dict_level, '') where dict_parent_code = dictCode and dict_type = dictType;
	commit;
	
-- 			下面的select是输出断点
-- 			select dict_level, ancestors_variable, dict_type_variable, dict_code_variable;

	-- 开启游标
	open cur;

		-- 进行循环
		read_loop:loop

			-- 将游标中是数据填充到 dict_type 当中
			fetch cur into ancestors_variable, dict_type_variable, dict_code_variable;

			if done then -- 如果到达末尾
				leave read_loop; -- 跳出循环相当于 break;
			end if;

			-- 逻辑操作
			CALL handle_system_dict_tree_other(dict_type_variable, dict_code_variable, ancestors_variable, dict_level);

		-- 结束循环
		end loop;

	-- 关闭游标
	close cur;

END

3、执行存储过程，结束后自行验证数据准确性，没有异议后分别插入正式表