mysql 存储过程递归_MySQL用存储过程实现递归查询（二）

I have only recently started working heavily

with stored procedures and functions in MySQL. After years in the

Oracle world with advanced stored procedures, functions and packages,

I’ve had to come to grips with the shortcomings of MySQL. One of those

is recursive functions. MySQL allows recursive stored procedures, but

not recursive stored functions. Here is my workaround…

First create your main logic as a stored procedure with an OUT variable:

DELIMITER |

CREATE PROCEDURE my_recursive_proc(a_some_parameter INTEGER, OUT a_result INTEGER)

BEGIN

DECLARE v_my_number INTEGER DEFAULT 0;

-- Have to set max_sp_recursion_depth inside the stored procedure

SET max_sp_recursion_depth := 20;

SET v_my_number := v_my_number + a_some_parameter;

IF v_my_number < 100 THEN

CALL my_recursive_proc(10, v_my_number);

END IF;

SET a_result := v_my_number;

END |

DELIMITER ;

So now I have a recursive procedure that calls itself adding 10 to

the initial number until we get to 100. In the real world we'd be doing

something serious like descending through related child records until we

find some search result. What I really want now is a function I can

call in a WHERE clause somewhere.

Here's all we have to do:

CREATE FUNCTION my_recursive_func(a_some_parameter INTEGER)

RETURNS INTEGER

BEGIN

DECLARE v_result INTEGER DEFAULT 0;

CALL my_recursive_proc(a_some_parameter, v_result);

RETURN v_result;

END |

DELIMITER ;

Now in my SELECT statements I can do:

SELECT * FROM my_table WHERE my_recursive_func(some_column) = 100;