欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

递归获取课程体系树 博客分类: mysql  

程序员文章站 2024-03-15 17:07:36
...
-- 功能:课程体系树
-- 参数: 租户ID,权限类型 (T代表租户,G 代表管理员)
-- 作者: xxxxx
CREATE PROCEDURE `PRO_COURSE_STRUCTURE` (IN vTenantId VARCHAR(36), IN vPROTYPE CHAR(1))
BEGIN
	-- 数量
     DECLARE i int;
	-- 统计多少条记录
	 DECLARE vCount INT;
     -- 岗位Id
     DECLARE vPositionId VARCHAR(36);
      -- 技能Id
     DECLARE vSkillId VARCHAR(36);
     -- 中间转换值
     DECLARE MedianId VARCHAR(36);
     
     -- 创建临时表
     CREATE TEMPORARY TABLE tmp_CourseStructure
     (
		ID VARCHAR(36) NOT NULL, NAME VARCHAR(50) NOT NULL,
        PARENTID VARCHAR(36) 
     );
	
    -- 获取岗位信息树 
    BEGIN
    
		SET vCount=(SELECT COUNT(1) FROM oep.position);
		-- 创建岗位临时表
		CREATE TEMPORARY TABLE IF NOT EXISTS TmpPosition ENGINE = MEMORY 
		SELECT p.PositionId,p.PositionName,
					   p.PositionParentId,p.TenantId 
					   ,@curRow := @curRow + 1 AS row_number
		FROM oep.position p
		JOIN    (SELECT @curRow := 0) r;
		
		-- 循环获取岗位 
		SET i=1;
		
		loop1: WHILE i<=vCount DO
				
			   -- 获取某条记录信息
			   SET vPositionId=	(SELECT PositionId FROM TmpPosition WHERE  row_number =i);
			   -- 当岗位ID不为NULL
			   IF vPositionId IS NOT NULL
			   THEN 
					 SET MedianId= vPositionId;
					 SET vPositionId= (SELECT GetPositionInfo(vPositionId));
					 -- 当为最末级时
					 IF vPositionId IS NOT NULL AND vPositionId = '00000000-0000-0000-0000-000000000000'
					 THEN 
						INSERT INTO tmp_CourseStructure (ID,NAME,PARENTID) 
						SELECT PositionId AS ID, PositionName AS NAME,PositionParentId AS PARENTID FROM TmpPosition
						WHERE PositionId =vPositionId;
					 ELSE 
						INSERT INTO tmp_CourseStructure (ID,NAME,PARENTID) 
						SELECT PositionId AS ID, PositionName AS NAME,PositionParentId AS PARENTID FROM TmpPosition
						WHERE PositionId =MedianId;
					 END IF;
			   END IF;
			  
			   SET i=i+1;
		END WHILE loop1;
		-- 删除临时岗位表 	
		DROP TEMPORARY TABLE IF EXISTS TmpPosition;
    END;
	
    -- 获取技能信息树 
    BEGIN
    
		SET vCount=(SELECT COUNT(1) FROM oep.position);
		-- 创建技能临时表
		CREATE TEMPORARY TABLE IF NOT EXISTS Tmpskill ENGINE = MEMORY 
		SELECT  l.TenantId,l.SkillId,l.ParentId,l.SkillCode,l.SkillName,
				@curRow := @curRow + 1 AS row_number
		FROM    oep.skill l
		JOIN    (SELECT @curRow := 0) r;
		
		-- 循环获取技能 
        
		SET i=1;
		
		loop1: WHILE i<=vCount DO
				
			   -- 获取某条记录信息
			   SET vPositionId=	(SELECT PositionId FROM TmpPosition WHERE  row_number =i);
			   -- 当技能ID不为NULL
			   IF vPositionId IS NOT NULL
			   THEN 
					 SET MedianId= vPositionId;
					 SET vPositionId= (SELECT GetPositionInfo(vPositionId));
					 -- 当为最末级时
					 IF vPositionId IS NOT NULL AND vPositionId = '00000000-0000-0000-0000-000000000000'
					 THEN 
						INSERT INTO tmp_CourseStructure (ID,NAME,PARENTID) 
						SELECT PositionId AS ID, PositionName AS NAME,PositionParentId AS PARENTID FROM TmpPosition
						WHERE PositionId =vPositionId;
					 ELSE 
						INSERT INTO tmp_CourseStructure (ID,NAME,PARENTID) 
						SELECT PositionId AS ID, PositionName AS NAME,PositionParentId AS PARENTID FROM TmpPosition
						WHERE PositionId =MedianId;
					 END IF;
			   END IF;
			  
			   SET i=i+1;
		END WHILE loop1;
		-- 删除临时技能表 	
		DROP TEMPORARY TABLE IF EXISTS TmpPosition;
    END;
	
     
END