-- 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.