PL/SQL迭代循环+Merge：用%ROWTYPE替代%TYPE优化实现问询

现有一段PL/SQL代码，通过迭代循环分析header表记录，用%TYPE定义大量变量判断字段是否需要调整，若需调整则生成记录存入adjust_rows_mapping表，最后通过Merge语句向主表插入正负金额的调整记录。现在希望改用%ROWTYPE简化代码逻辑，同时提升扩展性（支持关联查询、子循环等），以下是具体实现方案。

示例数据 #

Header表示例记录 #

adjust_rows_mapping表插入结果 #

现有代码 #

DECLARE
    v_old_org_code header.org_code%TYPE;
    v_old_proj_code header.project_code%TYPE;
    v_new_org_code header.org_code%TYPE;
    v_new_proj_code header.project_code%TYPE;
    v_old_output_code header.output_code%TYPE;
    v_new_output_code header.output_code%TYPE;
    v_old_version_code header.VERSION%TYPE;
    v_new_version_code header.VERSION%TYPE;
    v_new_amount_dollars header.AMOUNT_DOLLARS%TYPE;
    v_old_amount_dollars header.AMOUNT_DOLLARS%TYPE;
    v_counter NUMBER;
begin
    FOR record IN (SELECT ORG_CODE, PROJECT_CODE, OUTPUT_CODE, VERSION, AMOUNT_DOLLARS FROM HEADER) LOOP
        v_counter := 0;

        v_old_org_code := record.ORG_CODE;
        v_old_proj_code := record.PROJECT_CODE;
        v_old_output_code := record.OUTPUT_CODE;
        v_old_version_code := record.VERSION;
        v_old_amount_dollars := record.AMOUNT_DOLLARS;

        v_new_org_code := record.ORG_CODE;
        v_new_proj_code := record.PROJECT_CODE;
        v_new_output_code := record.OUTPUT_CODE;
        v_new_version_code := record.VERSION;
        v_new_amount_dollars := record.AMOUNT_DOLLARS;

        IF v_old_org_code = 'E4JAZ' THEN
            v_new_org_code := 'F4JAZ';
            v_counter := v_counter + 1;
        end if;

        if v_old_proj_code in ('P04', 'P07') then 
            v_new_proj_code := 'P01';
            v_counter := v_counter + 1;
        end if;

        if v_old_output_code in ('O234') then 
            v_new_output_code := 'test_output';
            v_counter := v_counter + 1;
        END IF;

        if v_counter > 0 then
            insert into adjust_rows_mapping (old_org_code, old_project_code, new_org_code, new_project_code
            , old_output_code, new_output_code)
            values (v_old_org_code, v_old_proj_code , v_new_org_code, v_new_proj_code
            , v_old_output_code, v_new_output_code );
        END IF;
    END LOOP;

    MERGE INTO header dst
    USING (
        WITH adjust_rows (old_org_code, old_project_code, new_org_code, new_project_code, old_output_code, new_output_code) AS (
            SELECT * from adjust_rows_mapping
        )
        SELECT u.org_code,
                u.project_code,
                u.output_code,
                'Actual_Adjust' AS version,
                h.amount_dollars * u.multiplier AS amount_dollars
        FROM   adjust_rows a
                UNPIVOT (
                    (old_org_code, old_project_code, old_output_code, org_code, project_code, output_code)
                    FOR multiplier IN (
                        (old_org_code, old_project_code, old_output_code, old_org_code, old_project_code, old_output_code) AS -1,
                        (old_org_code, old_project_code, old_output_code, new_org_code, new_project_code, new_output_code) AS 1
                    )
                ) u
                INNER JOIN header h
                ON h.org_code = u.old_org_code
                    AND h.project_code = u.old_project_code
                    and h.output_code = u.old_output_code
    ) src
    ON (   src.org_code       = dst.org_code
        AND src.project_code   = dst.project_code
        AND src.output_code    = dst.output_code
        AND src.version        = dst.version
        AND src.amount_dollars = dst.amount_dollars )
    WHEN NOT MATCHED THEN
        INSERT (Org_Code, Project_Code, Output_Code, Version, Amount_Dollars)
        VALUES (src.Org_Code, src.Project_Code, src.Output_Code, src.Version, src.Amount_Dollars);
END;
/

基于%ROWTYPE的改造方案 #

1. 用%ROWTYPE定义变量简化结构 #

替换原有大量%TYPE单个字段变量，直接定义与表结构匹配的行变量，减少冗余：

DECLARE
    -- 存储原始记录的行变量
    v_old_row header%ROWTYPE;
    -- 存储调整后记录的行变量
    v_new_row header%ROWTYPE;
    v_counter NUMBER;
BEGIN

2. 循环中直接赋值行变量 #

FOR循环内直接将查询结果赋值给原始行变量，再复制给新行变量，避免逐个字段赋值：

FOR v_old_row IN (SELECT * FROM HEADER) LOOP
    v_counter := 0;
    -- 复制原始记录作为调整基础
    v_new_row := v_old_row;

3. 简化字段调整逻辑 #

直接操作行变量的字段，代码更简洁，后续新增字段调整只需添加对应判断即可：

-- 调整ORG_CODE
    IF v_old_row.ORG_CODE = 'E4JAZ' THEN
        v_new_row.ORG_CODE := 'F4JAZ';
        v_counter := v_counter + 1;
    END IF;

    -- 调整PROJECT_CODE
    IF v_old_row.PROJECT_CODE IN ('P04', 'P07') THEN
        v_new_row.PROJECT_CODE := 'P01';
        v_counter := v_counter + 1;
    END IF;

    -- 调整OUTPUT_CODE
    IF v_old_row.OUTPUT_CODE IN ('O234') THEN
        v_new_row.OUTPUT_CODE := 'test_output';
        v_counter := v_counter + 1;
    END IF;

4. 插入调整映射表 #

插入时直接引用行变量字段，无需逐个列出变量，新增字段仅需修改INSERT列名：

IF v_counter > 0 THEN
        INSERT INTO adjust_rows_mapping (
            old_org_code, old_project_code, old_output_code,
            new_org_code, new_project_code, new_output_code
        ) VALUES (
            v_old_row.ORG_CODE, v_old_row.PROJECT_CODE, v_old_row.OUTPUT_CODE,
            v_new_row.ORG_CODE, v_new_row.PROJECT_CODE, v_new_row.OUTPUT_CODE
        );
    END IF;
END LOOP;

5. 保留原Merge逻辑（可按需优化） #

原Merge语句逻辑可直接保留，其核心是基于调整映射表生成正负记录，与行变量改造不冲突。若需扩展（比如关联规则表获取调整规则），可直接在循环中通过行变量关联其他表，无需新增大量变量。

完整改造后代码 #

DECLARE
    v_old_row header%ROWTYPE;
    v_new_row header%ROWTYPE;
    v_counter NUMBER;
BEGIN
    FOR v_old_row IN (SELECT * FROM HEADER) LOOP
        v_counter := 0;
        v_new_row := v_old_row;

        -- 调整ORG_CODE
        IF v_old_row.ORG_CODE = 'E4JAZ' THEN
            v_new_row.ORG_CODE := 'F4JAZ';
            v_counter := v_counter + 1;
        END IF;

        -- 调整PROJECT_CODE
        IF v_old_row.PROJECT_CODE IN ('P04', 'P07') THEN
            v_new_row.PROJECT_CODE := 'P01';
            v_counter := v_counter + 1;
        END IF;

        -- 调整OUTPUT_CODE
        IF v_old_row.OUTPUT_CODE IN ('O234') THEN
            v_new_row.OUTPUT_CODE := 'test_output';
            v_counter := v_counter + 1;
        END IF;

        -- 插入调整映射记录
        IF v_counter > 0 THEN
            INSERT INTO adjust_rows_mapping (
                old_org_code, old_project_code, old_output_code,
                new_org_code, new_project_code, new_output_code
            ) VALUES (
                v_old_row.ORG_CODE, v_old_row.PROJECT_CODE, v_old_row.OUTPUT_CODE,
                v_new_row.ORG_CODE, v_new_row.PROJECT_CODE, v_new_row.OUTPUT_CODE
            );
        END IF;
    END LOOP;

    -- 原Merge逻辑保留
    MERGE INTO header dst
    USING (
        WITH adjust_rows (old_org_code, old_project_code, new_org_code, new_project_code, old_output_code, new_output_code) AS (
            SELECT * from adjust_rows_mapping
        )
        SELECT u.org_code,
                u.project_code,
                u.output_code,
                'Actual_Adjust' AS version,
                h.amount_dollars * u.multiplier AS amount_dollars
        FROM   adjust_rows a
                UNPIVOT (
                    (old_org_code, old_project_code, old_output_code, org_code, project_code, output_code)
                    FOR multiplier IN (
                        (old_org_code, old_project_code, old_output_code, old_org_code, old_project_code, old_output_code) AS -1,
                        (old_org_code, old_project_code, old_output_code, new_org_code, new_project_code, new_output_code) AS 1
                    )
                ) u
                INNER JOIN header h
                ON h.org_code = u.old_org_code
                    AND h.project_code = u.old_project_code
                    and h.output_code = u.old_output_code
    ) src
    ON (   src.org_code       = dst.org_code
        AND src.project_code   = dst.project_code
        AND src.output_code    = dst.output_code
        AND src.version        = dst.version
        AND src.amount_dollars = dst.amount_dollars )
    WHEN NOT MATCHED THEN
        INSERT (Org_Code, Project_Code, Output_Code, Version, Amount_Dollars)
        VALUES (src.Org_Code, src.Project_Code, src.Output_Code, src.Version, src.Amount_Dollars);
END;
/

改造优势 #

• 代码简化：减少大量单个字段变量的声明和赋值，代码结构更紧凑清晰。
• 扩展性提升：新增字段调整仅需修改调整逻辑和INSERT列名；关联其他表获取规则时，直接通过行变量关联即可，无需额外定义大量关联字段变量。
• 可读性增强：行变量字段名与表结构一致，逻辑更直观，降低维护成本。

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