PL/SQL如何使用WHILE循环实现表列所有值全部归零的业务逻辑？

核心实现思路 #

• 以「是否存在余额大于0的孩子」作为WHILE循环的判断条件，满足条件就持续执行交易逻辑
• 每次循环随机筛选1名有余额的孩子，再匹配1款价格不超过该孩子当前余额的糖果，避免扣减后出现负余额
• 交易完成后更新孩子余额，重新判断循环条件，直到所有孩子余额全部为0
• 增加异常捕获逻辑，处理余额不足以购买任意糖果的边界场景

完整存储过程代码 #

CREATE OR REPLACE PROCEDURE sales IS
    -- 存储选中的孩子信息
    v_kid_name  VARCHAR2(20);
    v_kid_money NUMBER;
    -- 存储选中的糖果信息
    v_candy_name  VARCHAR2(20);
    v_candy_price NUMBER;
    -- 存储有余额的孩子数量，作为循环判断条件
    v_has_money NUMBER;
BEGIN
    -- 初始化循环条件：统计当前有余额的孩子数量
    SELECT COUNT(*) INTO v_has_money FROM kids WHERE money > 0;

    -- WHILE循环主体
    WHILE v_has_money > 0 LOOP
        -- 1. 随机取1名有余额的孩子
        SELECT kid_name, money
        INTO v_kid_name, v_kid_money
        FROM (
            SELECT kid_name, money
            FROM kids
            WHERE money > 0
            ORDER BY DBMS_RANDOM.VALUE -- 随机排序取第一条实现随机选取
        ) WHERE ROWNUM = 1;

        -- 2. 随机取1款该孩子买得起的糖果
        SELECT candy_name, price
        INTO v_candy_name, v_candy_price
        FROM (
            SELECT candy_name, price
            FROM candy
            WHERE price <= v_kid_money
            ORDER BY DBMS_RANDOM.VALUE
        ) WHERE ROWNUM = 1;

        -- 3. 扣减孩子余额
        UPDATE kids
        SET money = money - v_candy_price
        WHERE kid_name = v_kid_name;

        -- 可选：打印交易日志，调试时可开启
        -- DBMS_OUTPUT.PUT_LINE(v_kid_name||'购买'||v_candy_name||'，花费'||v_candy_price||'，剩余'||(v_kid_money - v_candy_price));

        -- 提交事务，若需要批量提交可移到循环结束后
        COMMIT;

        -- 更新循环条件：重新统计有余额的孩子数量
        SELECT COUNT(*) INTO v_has_money FROM kids WHERE money > 0;
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('执行完成：所有孩子余额已清零');

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('执行中断：存在孩子余额不足以购买任意糖果，无法完成清零');
        ROLLBACK;
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('执行出错：'||SQLERRM);
        ROLLBACK;
END sales;
/

执行方法 #

执行前先开启输出日志，再调用存储过程即可：

SET SERVEROUTPUT ON;
BEGIN
    sales;
END;
/

注意事项 #

• 执行前需要确保candy表中存在价格足够低的糖果，保证余额最少的孩子也能买到至少1款糖果，否则会触发中断异常
• 如果对性能要求较高，可以把循环内的COMMIT移到WHILE循环结束之后，减少事务提交次数

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