Mysql中实现Oracle的Start with…Connect By递归树查询(存储过程传参）

**

Mysql中实现Oracle的Start with…Connect By递归树查询

**

最近公司有个已完成的项目要将Oracle数据库替换成Mysql数据库，项目中用到了很多的Connect By，网上可以看到的主要分为两种实现方式。一类是通过函数的方式，另一类是存储过程。因为涉及到改动表比较多，所以写了一个可以传入表名，列名的通用存储过程（函数的方式参数中不支持传入表名及列名）。存储过程如下：

delimiter ;;
CREATE  PROCEDURE `SP_GET_CHILDLIST`( in rootid varchar(20), in columnname varchar(100),in pcolumnname varchar(100), in tablename varchar(30))
begin
DECLARE v_sql VARCHAR(300); 
DECLARE Level int ;
DECLARE opcount int ;
 	drop TABLE IF EXISTS TABLETEMP;
 	CREATE TABLE TABLETEMP(
 		 id VARCHAR(100),
 		 nLevel int
 	);
	Set Level=0 ;
	Set opcount=0 ;
    set v_sql = concat('INSERT into TABLETEMP  SELECT ',columnname,',',Level,' FROM ',tablename,' WHERE ',columnname,' =\'',rootid,'\'');
    set @v_sql=v_sql;   
    prepare stmt from @v_sql;  
    EXECUTE stmt ;     
    deallocate prepare stmt; 
    SELECT count(id) INTO opcount from TABLETEMP  where nLevel=Level;
    WHILE opcount>0 DO
   		 set Level=Level+1 ;
    	 set v_sql = concat('INSERT into TABLETEMP  SELECT a.',columnname,',',Level,' FROM ',tablename,' a,TABLETEMP  b WHERE    ',pcolumnname,'=b.id and B.nLevel=',Level,'-1');
   		 set @v_sql=v_sql;   
    	 prepare stmt from @v_sql;  
    	 EXECUTE stmt ;     
   	     deallocate prepare stmt; 
   	 	 SELECT count(id) INTO opcount from TABLETEMP  where nLevel=Level;
    END WHILE;
END
;;
delimiter ;

rootid为节点值, columnname 为节点字段,pcolumnname 父节点字段, tablename 表名。现有一表core_test节点字段为id,父节点字段为p_id。

call SP_GET_CHILDLIST('1','id','p_id','core_test');

通过调用可以将core_test表中节点为1及其所有子节点的数据插入到临时表TABLETEMP中。