求PL/SQL脚本：按QTY降序分配数值更新QTY_REQ列

需求概述 #

编写PL/SQL脚本，按以下规则将指定总数值分配到表的QTY_REQ列：

• 处理顺序：按QTY列降序排序，优先处理QTY值更高的行
• 赋值限制：每行QTY_REQ的赋值不能超过该行QTY列的数值
• 终止条件：累计分配的数值总和达到指定目标值时停止

示例场景 #

• 当目标数值为7时：
  • 第一行（QTY=4，LOC=10800B41）：QTY_REQ赋值4（累计4）
  • 第二行（QTY=2，LOC=10800A01）：QTY_REQ赋值2（累计6）
  • 第三行（QTY=2，LOC=10800B01）：QTY_REQ赋值1（累计7，完成分配）
• 当目标数值为5时：
  • 第一行（QTY=4，LOC=10800B41）：QTY_REQ赋值4（累计4）
  • 第二行（QTY=2，LOC=10800A01）：QTY_REQ赋值1（累计5，完成分配），后续行不再处理

解决方案 #

方案1：PL/SQL游标逐行处理 #

适合需要明确控制分配过程的场景，脚本如下：

DECLARE
    v_target_qty   NUMBER := 7; -- 可修改为目标分配数值，如5
    v_remaining_qty NUMBER := v_target_qty;
    v_assign_qty   NUMBER;
BEGIN
    -- 按QTY降序遍历数据行
    FOR rec IN (
        SELECT loc, qty
        FROM your_table -- 替换为实际表名
        ORDER BY qty DESC
    ) LOOP
        EXIT WHEN v_remaining_qty <= 0;
        
        -- 计算当前行可分配的数值：取剩余值与当前行QTY的较小值
        v_assign_qty := LEAST(rec.qty, v_remaining_qty);
        
        -- 更新当前行的QTY_REQ
        UPDATE your_table
        SET qty_req = v_assign_qty
        WHERE loc = rec.loc;
        
        -- 更新剩余待分配数值
        v_remaining_qty := v_remaining_qty - v_assign_qty;
    END LOOP;
    
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('分配完成，剩余未分配数值：' || v_remaining_qty);
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('分配失败，错误信息：' || SQLERRM);
END;
/

方案2：纯SQL更新（无PL/SQL块） #

适合数据量不大、无需显式过程控制的场景，利用分析函数实现：

DECLARE
    v_target_qty NUMBER := 7; -- 替换为目标数值
BEGIN
    UPDATE your_table t
    SET qty_req = (
        SELECT LEAST(qty, GREATEST(0, v_target_qty - COALESCE(SUM(qty) OVER (ORDER BY qty DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0)))
        FROM your_table
        WHERE loc = t.loc
    )
    WHERE (
        SELECT COALESCE(SUM(qty) OVER (ORDER BY qty DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 0)
        FROM your_table
        WHERE loc = t.loc
    ) <= v_target_qty;
    
    COMMIT;
END;
/

内容的提问来源于stack exchange，提问作者leesider