当前位置:Gxlcms > mysql > MySQL存储过程递归调用_MySQL

MySQL存储过程递归调用_MySQL

时间:2021-07-01 10:21:17 帮助过:9人阅读

bitsCN.com 有分类表tb_system_category,结构如下:[java] 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)取得所有子分类及孙子分类”。
使用以下存储过程: 1. 主存储过程,作用是创建临时表,并操作其他存储过程或函数实现需求,其中临时表的作用是存储每个子分类的代号。流程:创建临时表——调用存储过程(category_findCodesByParentCode_queryAndInsert)取得所有子分类及孙子分类的代码并存入临时表中——调用函数(category_generateResult)生成结果字符串——删除临时表数据——返回生成的字符串。[java] 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)取得孙子分类。[java] 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. 根据分类代号取得子分类的个数。[java] 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. 从临时表中查出结果并组合成字符串。[java] 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中,不能够直接使用函数实现递归,在上例中,使用存储过程递归调用,将需要的值存储到临时表中,然后通过对临时表进行操作后取得结果。 作者 geloin bitsCN.com

人气教程排行