MySQL存储过程递归调用
MySQL存储过程递归调用
有分类表tb_system_category,结构如下:- 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;
使用以下存储过程:
1. 主存储过程,作用是创建临时表,并操作其他存储过程或函数实现需求,其中临时表的作用是存储每个子分类的代号。流程:创建临时表——调用存储过程(category_findCodesByParentCode_queryAndInsert)取得所有子分类及孙子分类的代码并存入临时表中——调用函数(category_generateResult)生成结果字符串——删除临时表数据——返回生成的字符串。
- 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
- 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
|
评论暂时关闭