如何在存储过程中实现类似批量插入的批量更新以替代游标循环更新？

针对你遇到的游标逐条更新性能问题，以下几种方案可以实现单次批量更新，大幅提升执行效率：

方法1：使用MERGE语句（推荐） #

多数主流数据库（Oracle、SQL Server、PostgreSQL等）都支持MERGE语法，它可以直接关联源数据与目标表，一次完成匹配更新，完全不需要游标，性能和批量插入接近。

MERGE INTO target_table t
USING (
    SELECT col1, col2, col3, col4, col5 
    FROM source_table1
) s
ON (t.col1 = s.col1)
WHEN MATCHED THEN
    UPDATE SET 
        t.col2 = s.col2,
        t.col3 = s.col3,
        t.col4 = s.col4,
        t.col5 = s.col5;

说明：该语句直接通过col1关联源表与目标表，匹配到的记录会被批量更新，逻辑简洁且性能最优，是优先选择的方案。

方法2：动态拼接批量UPDATE语句（适配你的批量插入思路） #

如果必须基于游标获取的数据实现批量更新，可以拼接包含CASE WHEN的单条UPDATE语句，一次性执行：

DECLARE
    CUR_C1 CURSOR FOR 
        SELECT col1, col2, col3, col4, col5 FROM source_table1;
    v_update_sql VARCHAR2(32767);
    v_first_rec BOOLEAN := TRUE;
    v1 VARCHAR2(100); v2 VARCHAR2(100); v3 VARCHAR2(100); v4 VARCHAR2(100); v5 VARCHAR2(100);
BEGIN
    -- 初始化SQL模板
    v_update_sql := 'UPDATE target_table SET ' ||
                    'col2 = CASE col1 ' ||
                    ', col3 = CASE col1 ' ||
                    ', col4 = CASE col1 ' ||
                    ', col5 = CASE col1 ';

    -- 拼接每个字段的CASE分支
    FOR CUR_REC IN CUR_C1 LOOP
        -- 处理单引号转义（避免语法错误）
        v1 := REPLACE(CUR_REC.col1, '''', '''''');
        v2 := REPLACE(CUR_REC.col2, '''', '''''');
        v3 := REPLACE(CUR_REC.col3, '''', '''''');
        v4 := REPLACE(CUR_REC.col4, '''', '''''');
        v5 := REPLACE(CUR_REC.col5, '''', '''''');

        v_update_sql := v_update_sql || 'WHEN ''' || v1 || ''' THEN ''' || v2 || ''' ';
        v_update_sql := v_update_sql || 'WHEN ''' || v1 || ''' THEN ''' || v3 || ''' ';
        v_update_sql := v_update_sql || 'WHEN ''' || v1 || ''' THEN ''' || v4 || ''' ';
        v_update_sql := v_update_sql || 'WHEN ''' || v1 || ''' THEN ''' || v5 || ''' ';
    END LOOP;

    -- 补全CASE的ELSE分支和WHERE过滤条件
    v_update_sql := v_update_sql || 
                    'ELSE col2 END ' ||
                    ', ELSE col3 END ' ||
                    ', ELSE col4 END ' ||
                    ', ELSE col5 END ' ||
                    'WHERE col1 IN (';

    -- 拼接需要更新的col1列表
    OPEN CUR_C1;
    v_first_rec := TRUE;
    LOOP
        FETCH CUR_C1 INTO v1, v2, v3, v4, v5;
        EXIT WHEN CUR_C1%NOTFOUND;
        v1 := REPLACE(v1, '''', '''''');
        IF NOT v_first_rec THEN
            v_update_sql := v_update_sql || ', ';
        END IF;
        v_update_sql := v_update_sql || '''' || v1 || '''';
        v_first_rec := FALSE;
    END LOOP;
    CLOSE CUR_C1;

    v_update_sql := v_update_sql || ')';

    -- 执行动态SQL
    EXECUTE IMMEDIATE v_update_sql;
    COMMIT;
END;
/

说明：这种方式把所有更新逻辑合并为一条SQL执行，避免逐条更新的开销，但需注意处理字段值中的单引号转义，同时要注意SQL长度限制。

方法3：使用集合批量绑定（适合Oracle等支持集合的数据库） #

如果数据库支持集合类型，可以将游标数据批量存入集合，再通过FORALL语句批量更新，安全性和效率都优于动态拼接：

DECLARE
    -- 定义记录类型和集合类型
    TYPE source_rec_type IS RECORD (
        col1 VARCHAR2(100),
        col2 VARCHAR2(100),
        col3 VARCHAR2(100),
        col4 VARCHAR2(100),
        col5 VARCHAR2(100)
    );
    TYPE source_tab_type IS TABLE OF source_rec_type;
    v_source_tab source_tab_type;

    CUR_C1 CURSOR FOR 
        SELECT col1, col2, col3, col4, col5 FROM source_table1;
BEGIN
    -- 批量读取游标数据到集合
    OPEN CUR_C1;
    FETCH CUR_C1 BULK COLLECT INTO v_source_tab;
    CLOSE CUR_C1;

    -- 批量更新
    FORALL i IN 1..v_source_tab.COUNT
        UPDATE target_table
        SET col2 = v_source_tab(i).col2,
            col3 = v_source_tab(i).col3,
            col4 = v_source_tab(i).col4,
            col5 = v_source_tab(i).col5
        WHERE col1 = v_source_tab(i).col1;
    COMMIT;
END;
/

说明：FORALL会将集合中的更新操作批量发送给数据库，比逐条更新效率提升明显，且无需处理SQL拼接的转义问题。若数据量极大，可配合LIMIT参数分批处理。

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