mysql如何实现oracle的start with

参考：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))

BEGIN  
      DECLARE 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;