oracle 查询语句结果 循环执行update 原创

oracle 查询语句结果 循环执行update

BEGIN
FOR rec IN (SELECT column1, column2 FROM your_table WHERE your_condition) LOOP
-- 这里可以使用rec.column1和rec.column2来进行你需要的逻辑处理
UPDATE other_table SET some_column = 'some_value' WHERE condition = rec.column1;
END LOOP;
END;

实例

BEGIN
FOR rec IN (select CQ.QUOTA_ID,
(select sum(b.USED_AMOUNT)
from CFB_QUOTA_SUB b
join CFB_BUDGET_CODE_SUB c
on b.BUDGET_CODE = c.BUDGET_CODE and c.SUB_UNIT_ID = CBC.SUB_UNIT_ID and c.TERM_ID = CBC.TERM_ID
and c.CURRENCY_NO = CBC.CURRENCY_NO and c.BUDGET_ACCOUNT_CODE =
CTA.BUDGET_ACCOUNT_CODE) USED_AMOUNT
from CFB_TEMPLATE_ACCOUNT CTA
join CFB_BUDGET_CODE CBC on CBC.BUDGET_ACCOUNT_CODE = CTA.BUDGET_ACCOUNT_CODE
join CFB_BUDGET_CODE_SUB CBCS
on CBCS.SUB_UNIT_ID = CBC.SUB_UNIT_ID and CBCS.TERM_ID = CBC.TERM_ID
and CBCS.CURRENCY_NO = CBC.CURRENCY_NO and CBCS.BUDGET_ACCOUNT_CODE = CTA.BUDGET_ACCOUNT_CODE
join CFB_QUOTA CQ on CBC.BUDGET_CODE = CQ.BUDGET_CODE
join CFB_QUOTA_SUB CQS on CBCS.BUDGET_CODE = CQS.BUDGET_CODE
where 1 = 1
and CTA.REPORT_WAY in ('INPUT', 'AUTO')
and CTA.TEMPLATE_ID = '149' --模板
and CBC.TERM_ID = '301' -- 期间
and CBC.SUB_UNIT_ID = '370' -- 单位
and CBC.CURRENCY_NO = 'CNY'
and CBC.BUDGET_ACCOUNT_NAME = '其他购买商品、接受劳务支付的现金' --科目编码
) LOOP
-- 这里可以使用rec.column1和rec.column2来进行你需要的逻辑处理
UPDATE CFB_QUOTA SET USED_AMOUNT = rec.USED_AMOUNT WHERE QUOTA_ID = rec.QUOTA_ID;
END LOOP;
END;