《Mysql學習MySQL存儲過程遞歸調用實例》要點：
本文介紹了Mysql學習MySQL存儲過程遞歸調用實例，希望對您有用。如果有疑問，可以聯系我們。
導讀：分類表tb_system_category,結構如下：
CREATE TABLE `tb_system_category` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c_pare...
分類表tb_system_category,結構如下：
?MYSQL入門
CREATE TABLE `tb_system_category` (?
? `id` int(11) NOT NULL AUTO_INCREMENT,?
? `c_parent_id` int(11) NOT NULL,?
? `c_name` varchar(50) NOT NULL,?
? `c_full_name` varchar(200) DEFAULT NULL,?
? `c_code` varchar(50) NOT NULL,?
? `c_describe` text,?
? PRIMARY KEY (`id`)?
) ENGINE=InnoDB AUTO_INCREMENT=126 DEFAULT CHARSET=utf8;?
要求使用存儲過程“根據父分類代號(c_code)取得所有子分類及孫子分類”.MYSQL入門
使用以下存儲過程：
1,主存儲過程,作用是創建臨時表,并操作其他存儲過程或函數實現需求,其中臨時表的作用是存儲每個子分類的代號.MYSQL入門
流程：創建臨時表——調用存儲過程(category_findCodesByParentCode_queryAndInsert)取得所有子分類及孫子分類的代碼并存入臨時表中——調用函數(category_generateResult)生成結果字符串——刪除臨時表數據——返回生成的字符串.
?MYSQL入門
CREATE PROCEDURE category_findCodesByParentCode(in cCode varchar(200))?
begin?
-- 調用的函數或存儲過程：category_findCodesByParentCode_queryAndInsert、category_generateResult?
-- 被調用于函數或存儲過程：無?
??? declare cRand varchar(50) default RAND();?
??? declare result varchar(4000);?
?
??? create temporary table if not exists tb_system_temp_category_categoryTree(?
??????? c_result varchar(4000),?
??????? c_rand varchar(50)?
??? );?
?
??? set max_sp_recursion_depth? = 100;?
?
??? call category_findCodesByParentCode_queryAndInsert_zh(cCode, cRand);?
?????
??? set result = category_generateResult(cRand);?
?
??? set @mySql = CONCAT('delete from tb_system_temp_category_categoryTree where c_rand = "',cRand,'"');?
??? prepare stmt from @mySql;?
??? execute stmt;?
?
??? set @mySql = CONCAT('select "', result, '" from tb_system_user limit 0,1');?
??? prepare stmt from @mySql;?
??? execute stmt;?
end?
2,遞歸取得所有子分類及孫子分類并存儲到臨時表中.流程：根據父分類代號查詢下級子分類代號,并通過指針迭代之——在迭代過程中,將子分類的代號存入臨時表——調用函數(category_findChildrenCountByCode)檢查子分類是否有下級分類,若無不管之；
若有則遞歸調用存儲過程(category_findCodesByParentCode_queryAndInsert)取得孫子分類.
?MYSQL入門
CREATE PROCEDURE category_findCodesByParentCode_queryAndInsert(in cCode varchar(200), in cRand varchar(50))?
begin?
-- 調用的函數或存儲過程：category_findChildrenCountByCode、category_findCodesByParentCode_queryAndInsert?
-- 被調用于函數或存儲過程：category_findCodesByParentCode?
??? declare finished int default 0;?
??? declare thisCode varchar(200);?
??? declare cur cursor for select c_code from tb_system_category where c_parent_id in (select id from tb_system_category where c_code = cCode);?
??? declare continue handler for not found set finished = 1;?
??? open cur;?
??? fetch cur into thisCode;?
??? while finished = 0 do?
??????? set @mySql = CONCAT('insert into tb_system_temp_category_categoryTree(c_result,c_rand) values("',thisCode,'","',cRand,'")');?
??????? prepare stmt from @mySql;?
??????? execute stmt;?
?
??????? if category_findChildrenCountByCode(thisCode) > 0 then?
??????????? call category_findCodesByParentCode_queryAndInsert(thisCode, cRand);?
??????? end if;?
?
??????? fetch cur into thisCode;?
??? end while;?
??? close cur;?
?????
end?
3,根據分類代號取得子分類的個數.
?MYSQL入門
CREATE FUNCTION category_findChildrenCountByCode(cCode varchar(200)) RETURNS int(11)?
BEGIN?
-- 調用的函數或存儲過程：無?
-- 被調用于函數或存儲過程：category_findCodesByParentCode_queryAndInsert?
??? declare finished int default 0;?
??? declare count int;?
??? declare cur cursor for select count(id) from tb_system_category where c_code like CONCAT(cCode,'%') and c_code != cCode;?
??? declare continue handler for not found set finished = 1;?
??? open cur;?
??? fetch cur into count;?
??? close cur;?
??? if count is null then?
??????? return 0;?
??? else?
??????? return count;?
??? end if;?
END?
4. 從臨時表中查出結果并組合成字符串.
?MYSQL入門
CREATE FUNCTION category_generateResult(cRand varchar(50)) RETURNS varchar(4000) CHARSET utf8?
BEGIN?
-- 調用的函數或存儲過程：無?
-- 被調用于函數或存儲過程：category_findCodesByParentCode?
??? declare finished int default 0;?
??? declare result varchar(20000) default '';?
??? declare thisResult varchar(200) default '';?
??? declare cur cursor for select c_result from tb_system_temp_category_categoryTree where c_rand = cRand;?
??? declare continue handler for not found set finished = 1;?
??? open cur;?
??? fetch cur into thisResult;?
??? while finished = 0 do?
?????????
??????? set result = concat(result, thisResult, ',');?
?????
??????? fetch cur into thisResult;?
??? end while;?
??? close cur;?
?
??? if result is null then?
??????? return result;?
??? else?
??????? if RIGHT(result,1) = ',' then?
??????????? set result = SUBSTR(result, 1, CHAR_LENGTH(result) - 1);?
??????? end if;?
??????? return result;?
??? end if;?
END?
在MySQL中,不能夠直接使用函數實現遞歸,在上例中,使用存儲過程遞歸調用,將必要的值存儲到臨時表中,然后通過對臨時表進行操作后取得結果.MYSQL入門
歡迎參與《Mysql學習MySQL存儲過程遞歸調用實例》討論，分享您的想法，維易PHP學院為您提供專業教程。
轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/11591.html
