mysql - SQL - 在存储过程的游标中动态更新记录仅从最后一条记录中获取值

Question

我尝试在存储过程中动态更新表行。但是，更新仅从最后一条记录中获取数据。我更新记录的方法不正确吗？请指出可能的原因以及如何解决这个问题。

SQL 代码段

DROP PROCEDURE IF EXISTS `regionCreditLimit`;
CREATE DEFINER = `root` @`localhost` PROCEDURE `regionCreditLimit`(
    total_sellin INT(11),
     available_credit_value INT(11)
 ) 
BEGIN
DECLARE no_more_rows2 TINYINT(1) DEFAULT 0;
DECLARE region_id INT(11);
DECLARE region_name VARCHAR(50);
DECLARE region_sellin INT(11);
DECLARE cursor2 CURSOR FOR  
                        -- SELLIN DATA BY Region
                        SELECT
                            `sa`.`id` AS `region_id`,
                            `sa`.`name` AS `region_name`,
                            SUM( `orp`.`scan` ) AS `sellin` 
                        FROM
                            `order_out` AS `oro`
                            LEFT JOIN `order_out_product` AS `orp` ON `orp`.`order_id` = `oro`.`id`
                            LEFT JOIN `distributor` AS `d` ON `d`.`id` = `oro`.`new_id`
                            LEFT JOIN `realme-sell-03-25-21`.`trainer_group` AS `tg` ON `tg`.`id` = `d`.`sub_area`
                            LEFT JOIN `realme-sell-03-25-21`.`sub_area` AS `sa` ON `sa`.`id` = `tg`.`sub_area` 
                        WHERE
                            `oro`.`print_time` >= DATE_ADD(NOW(), INTERVAL -3 MONTH)
                            AND `oro`.`status` = 3 
                            AND `d`.`sub_area` IS NOT NULL 
                        GROUP BY
                            `sa`.`id` 
                        ORDER BY
                            `sa`.`id` ASC;
                        
DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET no_more_rows2 := TRUE;
OPEN cursor2;

LOOP2: LOOP
    FETCH 
        cursor2 
    INTO  
        region_id, 
        region_name, 
        region_sellin;
    IF no_more_rows2 THEN
        CLOSE cursor2;
        LEAVE LOOP2;
    END IF;

    SET @share = region_sellin / total_sellin * 100;
    SET @credit_value = @share * available_credit_value / 100;
    
    SELECT 
            @current_region_region_id := `region_id`, 
            @current_region_share := `share`, 
            @current_region_credit_value := `credit_value`, 
            @current_region_custom_credit_value := `custom_credit_value`, 
            @current_region_allow_post_paid := `allow_post_paid`, 
            @current_region_allow_pre_paid := `allow_pre_paid`, 
            @current_region_created_at := `created_at`, 
            @current_region_sellin := `sellin`, 
            @current_region_updated_at := `updated_at`, 
            @current_region_updated_by := `updated_by` 
        FROM `area_region_credit_limit` AS `arcl` 
        WHERE `arcl`.`region_id` = region_id
            AND `arcl`.`area_id` IS NULL;
    
    IF @current_region_region_id THEN
        INSERT INTO area_region_credit_limit_history (`region_id`, `share`, `credit_value`, `custom_credit_value`, `allow_post_paid`, `allow_pre_paid`, `created_at`, `sellin`, `total_sellin`, `updated_at`, `updated_by`)
            VALUES(
                    @current_region_region_id,
                    @current_region_share,
                    @current_region_credit_value,
                    @current_region_custom_credit_value,
                    @current_region_allow_post_paid,
                    @current_region_allow_pre_paid,
                    @current_region_created_at,
                    @current_region_sellin,
                    total_sellin,
                    @current_region_updated_at,
                    @current_region_updated_by
                );

        UPDATE `area_region_credit_limit` 
            SET 
                `share` = @share, 
                `credit_value` = @credit_value,
                `sellin` = region_sellin
            WHERE
                `region_id` = region_id;
    ELSE
        INSERT INTO `area_region_credit_limit` (`region_id`, `share`, `credit_value`, `created_at`, `sellin`)
            VALUES (region_id, @share, @credit_value, NOW(), region_sellin);
    END IF;

    SET @region_sellin = region_sellin;
    SET @region_available_credit = @credit_value;
    CALL `areaCreditLimit` (
        region_id,
        @region_sellin,
        @region_available_credit
    );

END LOOP LOOP2;

END;

更新前的数据

结果

注意 更新脚本应该像在 excel 片段中一样动态更新。请帮我指出问题。感谢！

标签： mysqlsqlstored-procedures