QFQ

DELIMITER $$

CREATE DEFINER=`medtool_dev`@`localhost` PROCEDURE `auto_insert_merge_rules`(IN `tableSchema` VARCHAR(255))

BEGIN

    DECLARE done INT DEFAULT FALSE;

    DECLARE _tableName VARCHAR(255);

    DECLARE grMergeRuleId INT;

    DECLARE cur CURSOR FOR

        SELECT TABLE_NAME

        FROM information_schema.TABLES

        WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = tableSchema;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    SELECT id INTO grMergeRuleId FROM Grp WHERE reference = 'qfq_merge_rule' LIMIT 1;

    OPEN cur;

    read_loop: LOOP

        FETCH cur INTO _tableName;

        IF done THEN

            LEAVE read_loop;

        END IF;

        SET @columnNames = (

            SELECT GROUP_CONCAT(COLUMN_NAME)

            FROM information_schema.COLUMNS

            WHERE TABLE_NAME = _tableName AND COLUMN_NAME LIKE 'pId%' AND TABLE_SCHEMA = tableSchema

        );

        IF @columnNames IS NOT NULL THEN

            IF NOT EXISTS (SELECT 1 FROM Grp AS grMergeRules, Grp AS grType WHERE grMergeRules.name = _tableName AND grMergeRules.grId = grMergeRuleId) THEN

                INSERT INTO Grp (name, grId, value3, comment, value2, enabled)

                VALUES (_tableName, grMergeRuleId, @columnNames, 'Auto generated', '*', 'yes');

            END IF;

        END IF;

    END LOOP;

    CLOSE cur;

END$$

DELIMITER ;