MySQL的存储过程不支持递归

-- FUNCTION check_asset_group_mirror_relationship

DROP FUNCTION IF EXISTS check_asset_group_mirror_relationship;
DELIMITER |
CREATE FUNCTION check_asset_group_mirror_relationship(group_id bigint(20)) RETURNS int
BEGIN
    DECLARE group_type varchar(255);
    DECLARE mirror_flag int default 0;
	DECLARE parent_flag int default 0;
	DECLARE parent_group_id bigint(20);
	DECLARE done int default 0;
	DECLARE parent_group_ids CURSOR FOR select agr.parent_asset_group_id from asset_group_relation agr where agr.child_asset_group_id = group_id;
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
	select asset_group_type into group_type from asset_group ag
		where ag.id=group_id;
    IF group_type = 'GROUP' THEN
        SET group_type = 'ASSET_GROUP';
    END IF;
    select count(1) into mirror_flag from mirror_relationship mr
        where mr.group_type = group_type and mr.group_id = group_id;
    IF mirror_flag = 0 THEN
        select count(1) into parent_flag from asset_group_relation agr
            where agr.child_asset_group_id = group_id;
        IF parent_flag > 0 THEN
            OPEN parent_group_ids;
            REPEAT
                FETCH parent_group_ids INTO parent_group_id;
                IF NOT done THEN
                    IF mirror_flag = 0 THEN
                        SET mirror_flag = check_asset_group_mirror_relationship(parent_group_id);
                    END IF;
                END IF;
            UNTIL done END REPEAT;
            CLOSE parent_group_ids;
        END IF;
    END IF;
    RETURN mirror_flag;
END|
DELIMITER ;

然后去调用check_asset_group_mirror_relationship(2),MySQL报Error 1424: Recursive stored functions and triggers are not allowed.

评论

2 楼 hideto 2008-03-07  

需要返回值的时候就可以给stored functions的参数类型设置为OUT，其他为IN，有点类似于传值和传引用的意思

1 楼 hideto 2008-03-07  

结果发现MySQL的报错很有意思Recursive stored functions and triggers are not allowed.，但Store Procedure应该支持，结果试了一下，果然支持，不过要记得设置max_sp_recursion_depth.

后来又出现数据不对的问题，发现没法调试，老邓给我的意见是创建临时表然后insert变量进去来手工调试，结果还真发现了问题。

另外一个tip是写递归存储过程时可以多加一个变量count一下递归次数，次数过大就不再递归了，免得由于死循环等问题将MySQL挂掉。