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