startwith

mysql如何实现oracle的startwith

oracle

CREATEORREPLACEFUNCTIONGET_FIRSTBMBH

(p_bmbhINvarchar2--部门编号

)

RETURNVARCHAR2

IS

RESULTVARCHAR2(20);

BEGIN

SELECTBMBHINTORESULTFROM(

SELECTBMBHFROMZC_BM

WHERECC=(SELECTCSZFROMZC_XTCSWHERECSBH='BMCC')

STARTWITHBMBH=p_bmbh

CONNECTBYPRIORSSBMBH=BMBH);

RETURN(RESULT);

ENDGET_FIRSTBMBH;

mysql实现上述的函数

寻找根节点把需要查找的插⼊到这个表

nDepth没⽤createParentLst

mysql；CREATEDEFINER=`zc`@`%`PROCEDURE`NewProc`(INrootIdvarchar(100),INnDepthvarchar(100))

BEGIN

DECLAREdoneINTDEFAULT0;

DECLAREbvarchar(100);

DECLAREcur1CURSORFORSELECTssbmbhFROMzc_bmwherebmbh=rootId;

DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;

SETmax_sp_recursion_depth=12;

--inrtintotmpLstvalues(null,rootId,nDepth);

INSERTINTOtmpLstVALUES(null,rootId,nDepth);

OPENcur1;

FETCHcur1INTOb;

WHILEdone=0DO

CALLcreateParentLst(b,nDepth+1);

FETCHcur1INTOb;

ENDWHILE;

CLOSEcur1;

END;

调⽤这个存储过程

callcreateParentLst('','1');

产⽣如下结果

p_bmbh没⽤

CREATEDEFINER=`zc`@`%`FUNCTION`NewProc`(p_bmbhvarchar(20))

RETURNSvarchar(20)

BEGIN

DECLARERESULTVARCHAR(20);

SELECTbmbhINTORESULTFROM(

lectbmbhfromzc_bm,tmpLstwherebmbh=rootIdandcc=1

)A

WHERE1=1;

RETURN(RESULT);

END;

完美的实现的oracle的功能

延伸：得到⼦节点

CREATEDEFINER=`zc`@`%`PROCEDURE`NewProc`(INrootIdvarchar(100),INnDepthvarchar(100))

BEGIN

DECLAREdoneINTDEFAULT0;

DECLAREbvarchar(100);

DECLAREcur1CURSORFORSELECTbmbhFROMzc_bmwheressbmbh=rootId;

DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;

SETmax_sp_recursion_depth=12;

--inrtintotmpLstvalues(null,rootId,nDepth);

INSERTINTOtmpLstVALUES(NULL,rootId,nDepth);

OPENcur1;

FETCHcur1INTOb;

WHILEdone=0DO

CALLcreateChildLst(b,nDepth+1);

FETCHcur1INTOb;

ENDWHILE;

CLOSEcur1;

END;