MySQL的存储过程不支持递归

最新推荐文章于 2025-10-26 01:53:10 发布

最新推荐文章于 2025-10-26 01:53:10 发布 · 579 阅读

文章标签：

#MySQL

Database 专栏收录该内容

31 篇文章

订阅专栏

本文介绍了一个MySQL中关于递归存储函数的问题案例。该案例尝试创建一个检查资产组镜像关系的递归函数，但遇到递归存储函数和触发器不允许的错误。文章详细展示了SQL脚本内容，并指出在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 ;