参考:http://blog.csdn.net/wzy0623/article/details/53924307
oracle
CREATE OR REPLACE FUNCTION GET_FIRSTBMBH
(p_bmbh IN varchar2 --部门编号
)
RETURN VARCHAR2
IS
RESULT VARCHAR2 (20);
BEGIN
SELECT BMBH INTO RESULT FROM (
SELECT BMBH FROM ZC_BM
WHERE CC = (SELECT CSZ FROM ZC_XTCS WHERE CSBH='BMCC')
START WITH BMBH=p_bmbh
CONNECT BY PRIOR SSBMBH=BMBH);
RETURN (RESULT);
END GET_FIRSTBMBH;
mysql实现上述的函数
寻找根节点 把需要查找的插入到这个表
nDepth 没用 createParentLst
mysql;CREATE DEFINER = `zc`@`%` PROCEDURE `NewProc`(IN rootId varchar(100),IN nDepth varchar(100))
BEGINDECLARE done INT DEFAULT 0;
DECLARE b varchar(100);
DECLARE cur1 CURSOR FOR SELECT ssbmbh FROM zc_bm where bmbh=rootId ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET max_sp_recursion_depth=12;
-- insert into tmpLst values (null,rootId,nDepth);
INSERT INTO tmpLst VALUES (null,rootId,nDepth);
OPEN cur1;
FETCH cur1 INTO b;
WHILE done=0 DO
CALL createParentLst(b,nDepth+1);
FETCH cur1 INTO b;
END WHILE;
CLOSE cur1;
END;
调用这个存储过程
call createParentLst('','1');
产生如下结果
p_bmbh 没用
CREATE DEFINER = `zc`@`%` FUNCTION `NewProc`(p_bmbh varchar(20))
RETURNS varchar(20)
BEGIN
DECLARE RESULT VARCHAR (20);
SELECT bmbh INTO RESULT FROM (
select bmbh from zc_bm ,tmpLst where bmbh=rootId and cc=1
) A
WHERE 1=1;
RETURN (RESULT);
END;
完美的实现的oracle的功能
延伸:得到子节点
CREATE DEFINER = `zc`@`%` PROCEDURE `NewProc`(IN rootId varchar(100),IN nDepth varchar(100))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE b varchar(100);
DECLARE cur1 CURSOR FOR SELECT bmbh FROM zc_bm where ssbmbh=rootId;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET max_sp_recursion_depth=12;
-- insert into tmpLst values (null,rootId,nDepth);
INSERT INTO tmpLst VALUES (NULL,rootId,nDepth);
OPEN cur1;
FETCH cur1 INTO b;
WHILE done=0 DO
CALL createChildLst(b,nDepth+1);
FETCH cur1 INTO b;
END WHILE;
CLOSE cur1;
END;