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

-- 功能:课程体系树
-- 参数: 租户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