mySql实现树形查询的函数存储过程例子.docx
《mySql实现树形查询的函数存储过程例子.docx》由会员分享，可在线阅读，更多相关《mySql实现树形查询的函数存储过程例子.docx（7页珍藏版）》请在冰豆网上搜索。
mySql实现树形查询的函数存储过程例子
mySql实现树形查询的函数存储过程例子
由于mySql没有类似oracle的connectby，而实际业务中又会存在这样的需求，这里搞一个树形查询实现的例子，大家执行写自己想买的存储过程。
1、准备测试表
DROPTABLEIFEXISTS`test_channel`;
CREATETABLE`test_channel`（
`id`int（11）NOTNULLAUTO_INCREMENT,
`cname`varchar（200）DEFAULTNULL,
`parent_id`int（11）DEFAULTNULL,
PRIMARYKEY（`id`）
）ENGINE=InnoDBAUTO_INCREMENT=19DEFAULTCHARSET=utf8;
2、准备一点测试数据
INSERTINTO`test_channel`VALUES（1,'一级',-1）;
INSERTINTO`test_channel`VALUES（2,'二级1',1）;
INSERTINTO`test_channel`VALUES（3,'二级2',1）;
INSERTINTO`test_channel`VALUES（4,'二级3',1）;
INSERTINTO`test_channel`VALUES（5,'二级4',1）;
INSERTINTO`test_channel`VALUES（6,'二级5',1）;
INSERTINTO`test_channel`VALUES（7,'三级11',2）;
INSERTINTO`test_channel`VALUES（8,'三级12',2）;
INSERTINTO`test_channel`VALUES（9,'三级13',2）;
INSERTINTO`test_channel`VALUES（10,'三级21',3）;
INSERTINTO`test_channel`VALUES（11,'三级22',3）;
INSERTINTO`test_channel`VALUES（12,'三级31',4）;
INSERTINTO`test_channel`VALUES（13,'三级32',4）;
INSERTINTO`test_channel`VALUES（14,'三级41',5）;
INSERTINTO`test_channel`VALUES（15,'三级42',5）;
INSERTINTO`test_channel`VALUES（16,'三级51',6）;
INSERTINTO`test_channel`VALUES（17,'三级52',6）;
3、下面就是相应函数和过程，注意按照顺序执行，中间有互相引用
a、从某节点向上追溯根节点，递归生成临时表数据
------------------------------
--Procedurestructureforpro_cre_parentlist
------------------------------
DROPPROCEDUREIFEXISTS`pro_cre_parentlist`;
DELIMITER
CREATEDEFINER=`root`@`%`PROCEDURE`pro_cre_parentlist`（INrootIdINT,INnDepthINT）
BEGIN
DECLAREdoneINTDEFAULT0;
DECLAREbINT;
DECLAREcur1CURSORFORSELECTparent_idFROMtest_channelWHEREid=rootId;
DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;
SETmax_sp_recursion_depth=12;
INSERTINTOtmpLstVALUES（NULL,rootId,nDepth）;
OPENcur1;
FETCHcur1INTOb;
WHILEdone=0DO
CALLpro_cre_parentlist（b,nDepth+1）;
FETCHcur1INTOb;
ENDWHILE;
CLOSEcur1;
END
DELIMITER;
b、实现类似OracleSYS_CONNECT_BY_PATH的功能，递归过程输出某节点id路径
------------------------------
--Procedurestructureforpro_cre_pathlist
------------------------------
DROPPROCEDUREIFEXISTS`pro_cre_pathlist`;
DELIMITER
CREATEDEFINER=`root`@`%`PROCEDURE`pro_cre_pathlist`（INnidINT,INdelimitVARCHAR（10）,INOUTpathstrVARCHAR（1000））
BEGIN
DECLAREdoneINTDEFAULT0;
DECLAREparentidINTDEFAULT0;
DECLAREcur1CURSORFOR
SELECTt.parent_id,CONCAT（CAST（t.parent_idASCHAR）,delimit,pathstr）
FROMtest_channelAStWHEREt.id=nid;
DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;
SETmax_sp_recursion_depth=12;
OPENcur1;
FETCHcur1INTOparentid,pathstr;
WHILEdone=0DO
CALLpro_cre_pathlist（parentid,delimit,pathstr）;
FETCHcur1INTOparentid,pathstr;
ENDWHILE;
CLOSEcur1;
END
DELIMITER;
c、递归过程输出某节点name路径
------------------------------
--Procedurestructureforpro_cre_pnlist
------------------------------
DROPPROCEDUREIFEXISTS`pro_cre_pnlist`;
DELIMITER
CREATEDEFINER=`root`@`%`PROCEDURE`pro_cre_pnlist`（INnidINT,INdelimitVARCHAR（10）,INOUTpathstrVARCHAR（1000））
BEGIN
DECLAREdoneINTDEFAULT0;
DECLAREparentidINTDEFAULT0;
DECLAREcur1CURSORFOR
SELECTt.parent_id,CONCAT（ame,delimit,pathstr）
FROMtest_channelAStWHEREt.id=nid;
DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;
SETmax_sp_recursion_depth=12;
OPENcur1;
FETCHcur1INTOparentid,pathstr;
WHILEdone=0DO
CALLpro_cre_pnlist（parentid,delimit,pathstr）;
FETCHcur1INTOparentid,pathstr;
ENDWHILE;
CLOSEcur1;
END
DELIMITER;
d、调用函数输出id路径
------------------------------
--Functionstructureforfn_tree_path
------------------------------
DROPFUNCTIONIFEXISTS`fn_tree_path`;
DELIMITER
CREATEDEFINER=`root`@`%`FUNCTION`fn_tree_path`（nidINT,delimitVARCHAR（10））RETURNSvarchar（2000）CHARSETutf8
BEGIN
DECLAREpathidVARCHAR（1000）;
SET@pathid=CAST（nidASCHAR）;
CALLpro_cre_pathlist（nid,delimit,@pathid）;
RETURN@pathid;
END
DELIMITER;
e、调用函数输出name路径
------------------------------
--Functionstructureforfn_tree_pathname
------------------------------
DROPFUNCTIONIFEXISTS`fn_tree_pathname`;
DELIMITER
CREATEDEFINER=`root`@`%`FUNCTION`fn_tree_pathname`（nidINT,delimitVARCHAR（10））RETURNSvarchar（2000）CHARSETutf8
BEGIN
DECLAREpathidVARCHAR（1000）;
SET@pathid='';
CALLpro_cre_pnlist（nid,delimit,@pathid）;
RETURN@pathid;
END
DELIMITER;
f、调用过程输出子节点
------------------------------
--Procedurestructureforpro_show_childLst
------------------------------
DROPPROCEDUREIFEXISTS`pro_show_childLst`;
DELIMITER
CREATEDEFINER=`root`@`localhost`PROCEDURE`pro_show_childLst`（INrootIdINT）
BEGIN
DROPTEMPORARYTABLEIFEXISTStmpLst;
CREATETEMPORARYTABLEIFNOTEXISTStmpLst（snoINTPRIMARYKEYAUTO_INCREMENT,idINT,depthINT）;
CALLpro_cre_childlist（rootId,0）;
SELECTtest_channel.id,CONCAT（SPACE（tmpLst.depth*2）,'--',test_ame）NAME,test_channel.parent_id,tmpLst.depth,fn_tree_path（test_channel.id,'/'）path,fn_tree_pathname（test_channel.id,'/'）pathname
FROMtmpLst,test_channelWHEREtmpLst.id=test_channel.idORDERBYtmpLst.sno;
END
DELIMITER;
g、调用过程输出父节点
------------------------------
--Procedurestructureforpro_show_parentLst
------------------------------
DROPPROCEDUREIFEXISTS`pro_show_parentLst`;
DELIMITER
CREATEDEFINER=`root`@`localhost`PROCEDURE`pro_show_parentLst`（INrootIdINT）
BEGIN
DROPTEMPORARYTABLEIFEXISTStmpLst;
CREATETEMPORARYTABLEIFNOTEXISTStmpLst（snoINTPRIMARYKEYAUTO_INCREMENT,idINT,depthINT）;
CALLpro_cre_parentlist（rootId,0）;
SELECTtest_channel.id,CONCAT（SPACE（tmpLst.depth*2）,'--',test_ame）NAME,test_channel.parent_id,tmpLst.depth,fn_tree_path（test_channel.id,'/'）path,fn_tree_pathname（test_channel.id,'/'）pathname
FROMtmpLst,test_channelWHEREtmpLst.id=test_channel.idORDERBYtmpLst.sno;
END
DELIMITER;
注意：
f、g需要在命令行执行哦，否则会报错。
4、测试
CALLp_show_childLst
（1）;
其他测试类似
升级会员 