Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to implement recursive calls to stored procedures in MySQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

There is a classification table tb_system_category, which is structured as follows:

[java]

CREATE TABLE `tb_system_ roomy` (

`id`int (11) NOT NULL AUTO_INCREMENT

`croomparent`id` int (11) NOT NULL

`cname` varchar (50) NOT NULL

`croomfull _ 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

Requires the use of a stored procedure to "get all child and grandchild classifications according to the parent classification code (c_code)."

Use the following stored procedures:

1. The main stored procedure, which is used to create temporary tables and manipulate other stored procedures or functions to implement requirements, where the temporary table is used to store the code for each subcategory. Process: create temporary table-call stored procedure (category_findCodesByParentCode_queryAndInsert) to get the code of all child and grandchild categories and store them in temporary table-call function (category_generateResult) to generate result string-delete temporary table data-return the generated string.

[java]

CREATE PROCEDURE category_findCodesByParentCode (in cCode varchar)

Begin

-- function or stored procedure called: category_findCodesByParentCode_queryAndInsert, category_generateResult

-- called in a function or stored procedure: none

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)

); www.2cto.com

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 0d1')

Prepare stmt from @ mySql

Execute stmt

End

two。 All child classifications and grandchild classifications are recursively obtained and stored in the temporary table. Process: query the subordinate child classification code according to the parent classification code, and iterate it through the pointer-- store the child classification code in the temporary table during the iteration-- call the function (category_findChildrenCountByCode) to check whether the child classification has a subordinate classification, if not; if so, recursively call the stored procedure (category_findCodesByParentCode_queryAndInsert) to get the grandson classification.

[java]

CREATE PROCEDURE category_findCodesByParentCode_queryAndInsert (in cCode varchar), in cRand varchar (50)

Begin

-- function or stored procedure called: category_findChildrenCountByCode, category_findCodesByParentCode_queryAndInsert

-- 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 (centering results) 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. The number of subcategories is obtained according to the classification code.

[java]

CREATE FUNCTION category_findChildrenCountByCode (cCode varchar) RETURNS int (11)

BEGIN

-- function or stored procedure called: none

-- called in a 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. The result is checked out from the temporary table and combined into a string.

[java]

CREATE FUNCTION category_generateResult (cRand varchar (50)) RETURNS varchar (4000) CHARSET utf8 www.2cto.com

BEGIN

-- function or stored procedure called: none

-- called in a function or stored procedure: 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

In MySQL, functions cannot be directly used for recursion. In the above example, stored procedure recursive calls are used to store the required values in a temporary table, and then the results are obtained by manipulating the temporary table.

After reading the above, do you have any further understanding of how to implement recursive calls to stored procedures in MySQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.