-- drop PROCEDURE temp_sys_getOranizationName_001;
create PROCEDURE temp_sys_getOranizationName_001()
BEGIN
DECLARE parentDeptId varchar(36) default '' ; -- 父部门id
declare parentDeptName varchar(100) default '' ; -- 父部门名称
declare currentDeptName varchar(100) default '' ; -- 当前部门名称
declare retStr varchar(1000) default '' ; -- 返回字符串(部门1/部门1的子部门1/部门1的子部门2)
declare deptName varchar(2000) default '' ;
declare stopflag int default 0 ;
declare tempUuid varchar(50) ;
declare tempData varchar(100);
declare oranizationData cursor for select uuid from sys_organization ; -- 创建一个游标变量,declare变量名cursor...
declare continue handler for not found set stopflag = 1; -- 当变量中保存的结果都查询一遍(遍历),到达结尾,将变量stopflag设置为1,用于循环中判断是否结束
open oranizationData ;-- 打开游标
fetch oranizationData into tempUuid ;-- 游标向前走一步,取出一条记录放在变量 username 中
while (stopflag = 0) do -- 如果游标还没有结尾就继续
begin
if tempUuid is not null and length(tempUuid) > 0 then
set currentDeptName = (select ifnull(organization_name, null) from sys_organization where uuid = tempUuid);
else
set retStr='';
end if;
set tempData=tempUuid;
WHILE tempUuid is not null
do
SET parentDeptId = (SELECT parent_id FROM sys_organization WHERE uuid = tempUuid);
IF parentDeptId is not null THEN
set parentDeptName = (select organization_name from sys_organization td where td.uuid = parentDeptId);
if parentDeptName is not null then
set deptName = concat(parentDeptName,'/', deptName);
end if;
SET tempUuid = parentDeptId;
ELSE
SET tempUuid = parentDeptId;
END IF;
END WHILE;
update sys_organization set ancestors_names = concat(deptName,currentDeptName) where uuid = tempData ;
fetch oranizationData into tempUuid;
set deptName='';
set parentDeptName='';
set currentDeptName='';
set parentDeptId='';
end;
end while; -- 结束循环
close oranizationData ; -- 关闭游标
END
call temp_sys_getOranizationName_001
mysql事务批量处理祖籍列表名称
最新推荐文章于 2024-04-09 21:28:18 发布
![](https://img-home.csdnimg.cn/images/20240711042549.png)