mysql 存储过程实现 oracle START WITH CONNECT BY

[color=green]oracle树查询[/color]

SELECT R.RESOURCE_ID,R.RESOURCE_NAME,R.RESOURCE_URL,R.RESOURCE_TYPE,R.CREATE_TIME,R.STATUS
		,RS.RESOURCE_STRUCTURE_ID,RS.RESOURCE_LEVEL,RS.PARENT_RESOURCE_ID,RS.RESOURCE_SORTNUMBER
		 FROM  T_SYS_RESOURCE_STRUCTURE RS   JOIN   T_SYS_RESOURCE R ON  R.RESOURCE_ID = RS.RESOURCE_ID 
			START WITH 
			<if test="resourceId != null and resourceId != ''">
			RS.PARENT_RESOURCE_ID = #{resourceId}
			</if>
			<if test="resourceId == null or resourceId == ''">
			RS.PARENT_RESOURCE_ID = '0'
			</if>
			   CONNECT BY  PRIOR  RS.RESOURCE_ID=RS.PARENT_RESOURCE_ID
		ORDER BY RS.RESOURCE_SORTNUMBER

[color=green]mysql 存储过程实现[/color]

DROP PROCEDURE IF EXISTS `ResourcesTreeQuery`;
CREATE PROCEDURE `ResourcesTreeQuery`(paramId varchar(30))
BEGIN
    create temporary  table if not exists tmp_table(
        RESOURCE_ID varchar(30),RESOURCE_NAME varchar(50),RESOURCE_URL varchar(200),RESOURCE_TYPE varchar(2),CREATE_TIME timestamp,STATUS varchar(2),RESOURCE_STRUCTURE_ID varchar(30),RESOURCE_LEVEL int,
        PARENT_RESOURCE_ID varchar(30),RESOURCE_SORTNUMBER int
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    SET @@max_sp_recursion_depth = 99 ;
    call ResourcesIterative(paramId);
    select * from tmp_table 
	union SELECT R.RESOURCE_ID,R.RESOURCE_NAME,R.RESOURCE_URL,R.RESOURCE_TYPE,R.CREATE_TIME,R.STATUS
		,RS.RESOURCE_STRUCTURE_ID,RS.RESOURCE_LEVEL,RS.PARENT_RESOURCE_ID,RS.RESOURCE_SORTNUMBER
		 FROM  T_SYS_RESOURCE_STRUCTURE RS   JOIN   T_SYS_RESOURCE R ON  R.RESOURCE_ID = RS.RESOURCE_ID
		where RS.RESOURCE_ID=paramId;
    drop temporary table if  exists  tmp_table;
END;


DROP PROCEDURE IF EXISTS `ResourcesIterative`;
CREATE PROCEDURE `ResourcesIterative`(paramId varchar(30))
BEGIN
         declare tRESOURCE_ID varchar(30) character set utf8;
         declare tRESOURCE_NAME varchar(50) character set utf8;
         declare tRESOURCE_URL varchar(200) character set utf8;
         declare tRESOURCE_TYPE varchar(2) character set utf8;
         declare tCREATE_TIME timestamp;
         declare tSTATUS varchar(2) character set utf8;
         declare tRESOURCE_STRUCTURE_ID varchar(30) character set utf8;
         declare tRESOURCE_LEVEL int;
         declare tPARENT_RESOURCE_ID varchar(30) character set utf8;
		 declare tRESOURCE_SORTNUMBER int;

         declare cur1 CURSOR FOR
		 select R.RESOURCE_ID,R.RESOURCE_NAME,R.RESOURCE_URL,R.RESOURCE_TYPE,R.CREATE_TIME,R.STATUS
		,RS.RESOURCE_STRUCTURE_ID,RS.RESOURCE_LEVEL,RS.PARENT_RESOURCE_ID,RS.RESOURCE_SORTNUMBER
		 FROM  T_SYS_RESOURCE_STRUCTURE RS   JOIN   T_SYS_RESOURCE R ON  R.RESOURCE_ID = RS.RESOURCE_ID
		 where RS.PARENT_RESOURCE_ID=paramId
		 ORDER BY RS.RESOURCE_SORTNUMBER;


         declare CONTINUE HANDLER FOR SQLSTATE '02000' SET tRESOURCE_ID = null;

         OPEN cur1 ;
         FETCH cur1 INTO tRESOURCE_ID,tRESOURCE_NAME,tRESOURCE_URL,tRESOURCE_TYPE,tCREATE_TIME,tSTATUS,tRESOURCE_STRUCTURE_ID,tRESOURCE_LEVEL,tPARENT_RESOURCE_ID,tRESOURCE_SORTNUMBER;
         WHILE ( tRESOURCE_ID is not null )  DO
           insert into tmp_table values(tRESOURCE_ID,tRESOURCE_NAME,tRESOURCE_URL,tRESOURCE_TYPE,tCREATE_TIME,tSTATUS,tRESOURCE_STRUCTURE_ID,tRESOURCE_LEVEL,tPARENT_RESOURCE_ID,tRESOURCE_SORTNUMBER);
           call ResourcesIterative(tRESOURCE_ID);
           FETCH cur1 INTO tRESOURCE_ID,tRESOURCE_NAME,tRESOURCE_URL,tRESOURCE_TYPE,tCREATE_TIME,tSTATUS,tRESOURCE_STRUCTURE_ID,tRESOURCE_LEVEL,tPARENT_RESOURCE_ID,tRESOURCE_SORTNUMBER;
         END WHILE;
         CLOSE cur1;
END;
commit;

[color=red]以上为自上往下递归，自下往上递归，更改游标按照PARENT_RESOURCE_ID递归即可[/color]