Mysql 递归查询

临时写得一个递归查询过程，通过临时表实现，后续再总结

DELIMITER $$

DROP PROCEDURE IF EXISTS `p_childfileid_get` $$

CREATE   PROCEDURE `test`.`p_childfileid_get`(IN idd VARCHAR(32),OUT result INT)

loop1:BEGIN

/*

    func:retrun all the child fileid which belong the given fileid

    vars:

    in idd given fileid

    out result 1 sucess  0 fileid doesn't exists

    liuxc 2012/2/3

*/

DECLARE lev INT;

DECLARE couts INT DEFAULT 1;

SELECT COUNT(*) INTO couts FROM ucloud_file WHERE fileid=idd;

SET lev=1;

IF couts>0 THEN 

DROP TABLE IF EXISTS tmp_id;

CREATE TABLE tmp_id(fileid VARCHAR(32),parentid VARCHAR(32) ,levv INT,userid INT);

INSERT tmp_id SELECT fileid,parentid,lev,id FROM ucloud_file WHERE parentid=idd;

WHILE  ROW_COUNT()>0

DO

SET lev=lev+1;

INSERT tmp_id SELECT t.fileid,t.parentid,lev,t.userid FROM ucloud_file t JOIN tmp_id a ON t.parentid=a.fileid AND levv=lev-1;

END WHILE ;

SET result=1;

ELSE 

SET result=0;

LEAVE loop1;

END IF;

SELECT userid,fileid,parentid,levv AS `level` FROM tmp_id;

DROP TEMPORARY TABLE IF EXISTS tmp_id;

   

END$$

   

DELIMITER ;