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

oracle树查询

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

RS.PARENT_RESOURCE_ID = #{resourceId}

RS.PARENT_RESOURCE_ID = '0'

CONNECT BY PRIOR RS.RESOURCE_ID=RS.PARENT_RESOURCE_ID

ORDER BY RS.RESOURCE_SORTNUMBER

mysql 存储过程实现

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;

以上为自上往下递归，自下往上递归，更改游标按照PARENT_RESOURCE_ID递归即可

分享到：

2016-05-10 10:49

浏览 231

分类:数据库

评论