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;