如何统计受影响记录数并以输出参数返回？存储过程求助

针对你提出的需求——用初始值为0的IN参数跟踪待修改条目数，将Table2中在Table1上次更新后修改的记录合并到Table1（仅当Table2的last_update大于Table1的max(modified_date)时插入），最终用OUT参数返回受影响记录数，我来梳理代码的问题和调整方案：

初始代码（存在逻辑错误） #

初始代码的游标处理和循环逻辑有明显问题，比如在循环内反复打开游标、错误的退出条件，还存在插入目标表错误的问题：

create or replace procedure test_proc (rUpdated_Row_Count IN NUMBER, rUpdated_Row_Count_2 OUT NUMBER) is 
    CURSOR c1 is select max(modified_date) as max_modified_date from table1; 
    l_var c1%ROWTYPE; 
    ----------- 
    CURSOR c2 is select table2_id , last_update from table2; 
    k_var c2%ROWTYPE; 
BEGIN 
    LOOP 
        Open c1; 
        Fetch c1 into l_var; 
        Open c2; 
        Fetch c2 into k_var; 
        EXIT WHEN c1%NOTFOUND; 
        IF k_var.last_update > l_var.max_modified_date THEN 
            -- 错误：应该插入Table1而不是Table2
            Insert into table2(table2_id, last_update) values(null, k_var.last_update); 
            commit; 
            rUpdated_Row_Count_2 := rUpdated_Row_Count + 1; 
        END IF; 
    END LOOP; 
    Close c1; 
    Close c2; 
END test_proc; 

修改后的优化代码（修复核心逻辑） #

你调整后的代码把游标打开移到了循环外，但仍存在插入表错误、IN参数直接修改的问题，我进一步优化了逻辑：

create or replace procedure test_proc (rUpdated_Row_Count IN NUMBER, rUpdated_Row_Count_2 OUT NUMBER) is 
    CURSOR c1 is select max(modified_date) as max_modified_date from table1; 
    l_var c1%ROWTYPE; 
    ----------- 
    CURSOR c2 is select table2_id , last_update from table2; 
    k_var c2%ROWTYPE; 
    -- 新增局部变量存储计数，IN参数为只读，不能直接累加
    l_total_count NUMBER := rUpdated_Row_Count;
BEGIN 
    Open c1;
    -- 只需要获取一次Table1的最大修改日期，无需重复执行
    Fetch c1 into l_var;
    Close c1; -- 提前关闭不需要的游标
    
    Open c2; 
    LOOP 
        Fetch c2 into k_var; 
        EXIT WHEN c2%NOTFOUND; 
        IF k_var.last_update > l_var.max_modified_date THEN 
            -- 修正：插入目标表为Table1，字段对应业务逻辑
            Insert into table1(table1_id, modified_date) values(k_var.table2_id, k_var.last_update); 
            l_total_count := l_total_count + 1; 
        END IF; 
    END LOOP; 
    Close c2; 
    -- 循环结束后统一提交，减少数据库事务开销
    commit;
    -- 最终赋值给OUT参数
    rUpdated_Row_Count_2 := l_total_count;
END test_proc; 

可复现测试数据 #

用于验证存储过程的测试表和初始化数据：

Create table table1 ( table1_id number, modified_date date ); 
Create table table2 ( table2_id number, last_update date ); 

insert into table1(table1_id, modified_date) values(1, sysdate); 
insert into table1(table1_id, modified_date) values(2, sysdate); 
insert into table1(table1_id, modified_date) values(3, sysdate -1); 

insert into table2(table2_id, last_update) values(1, sysdate + 1); 
insert into table2(table2_id, last_update) values(2, sysdate + 2); 

额外优化建议 #

• 游标c1仅需执行一次，因为max(modified_date)是单一聚合值，没必要在循环中重复获取
• 避免在循环内频繁提交事务，建议所有插入操作完成后统一提交，提升性能并减少事务风险
• 若Table2数据量较大，可考虑用BULK COLLECT批量读取数据，再批量插入Table1，进一步优化执行效率

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