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;