Recursive MySQL stored procedure call

The TABLE tb_system_category has the following structure: [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, www.2cto.com primary key ('id') ENGINE = InnoDB AUTO_INCREMENT = 126 default charset = utf8; the stored procedure is required to use the parent category code (c_code) obtain all sub-categories and sub-categories ".
Use the following stored procedures: 1. The primary stored procedure is used to create a temporary table and perform operations on other stored procedures or functions. The temporary table is used to store the codes of each sub-category. Process: create a temporary table -- call the Stored Procedure (category_findCodesByParentCode_queryAndInsert) to obtain the code for all subcategories and grandchildren classes and store the code in the temporary table -- call the function (category_generateResult) generate result string -- delete temporary table data -- return the generated string. [Java] create procedure category_findCodesByParentCode (in cCode varchar (200) begin -- called function or stored PROCEDURE: callback, category_generateResult -- called in function or stored PROCEDURE: no declare cRand varchar (50) default RAND (); declare result varchar (4000); create temporary table if not exists partition (c_result varchar (4000), c_rand varchar (50 )); www.2cto.com set max_sp _ Recursion_depth = 100; call Sequence (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. Recursively retrieve all sub-categories and store them in temporary tables. Process: Query sub-category codes based on the parent category code, and store the sub-category code to a temporary table through pointer iteration-call the function (category_findChildrenCountByCode) check whether a sub-category has a sub-category. If no sub-category exists, recursively call the Stored Procedure (category_findCodesByParentCode_queryAndInsert) to obtain the sub-category. [Java] create procedure category_findCodesByParentCode_queryAndInsert (in cCode varchar (200), in cRand varchar (50) begin -- called function or stored PROCEDURE: callback, callback -- called in a function or stored procedure: 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; www.2cto.com if category_findChildrenCountByCode (thisCode)> 0 then call category_findCodesByParentCode_queryAndInsert (thisCode, cRand); end if; fetch cur into thisCode; end while; close cur; end 3. Obtain the number of subcategories based on the classification code. [Java] create function category_findChildrenCountByCode (cCode varchar (200) RETURNS int (11) BEGIN -- called FUNCTION or stored procedure: None -- called FUNCTION or stored procedure: 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. find the result from the temporary table and combine it into a string. [Java] create function category_generateResult (cRand varchar (50) RETURNS varchar (4000) CHARSET utf8 www.2cto.com BEGIN -- called FUNCTION or stored procedure: None -- called on FUNCTION or stored procedure: includeclare finished int default 0; declare result varchar (20000) default ''; declare thisResult varchar (200) default''; declare cur cursor for select c_result from cursor 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 in MySQL, the function cannot be used directly for recursion, In the preceding example, the stored procedure is called recursively, and the required values are stored in the temporary table. Then, the temporary table is operated to obtain the result. Author geloin