Oracle Bulk Collection未初始化集合报错：批量分集合插入更新问题

问题背景 #

已通过游标将数据批量收集至T_USERRECORD集合中，需在T_USERRECORD的循环内按以下规则填充插入、更新用的独立集合，最后在循环外执行批量插入与更新，但遇到「引用未初始化集合」错误：

• 新记录：填充插入专用集合
• 已有记录：填充更新专用集合，且仅更新有变化的字段
• 预留删除逻辑与审计日志逻辑

错误原因 #

1. T_USERRECORD_INSERT和T_USERRECORD_UPDATE集合仅声明未初始化，直接调用集合属性（如COUNT、FIRST）会触发未初始化错误
2. 循环内错误地再次FETCH游标到目标集合，导致游标指针跳跃，数据处理逻辑混乱
3. 新记录判断条件错误（用USERID IS NULL不符合业务逻辑，应检查目标表QM_USERDATA是否存在该用户ID）
4. 批量操作前未判断集合是否为空，空集合会导致FORALL语句报错
5. 游标硬编码固定输入字符串，未使用传入的PC_USER_INPUT参数，无法处理动态输入

修正后的代码 #

CREATE OR REPLACE PACKAGE PKG_USERRECORD IS
  PROCEDURE PR_CREATE_USERRECORD_STRING(PC_USER_INPUT IN VARCHAR2,
                                        PC_STATUS     OUT VARCHAR2,
                                        PC_MESSAGE    OUT VARCHAR2);
END PKG_USERRECORD;
/

CREATE OR REPLACE PACKAGE BODY PKG_USERRECORD IS
  CONST_SUCCESS CONSTANT CHAR(1) := 'S';
  CONST_FAILURE CONSTANT CHAR(1) := 'F';

  PROCEDURE PR_CREATE_USERRECORD_STRING(PC_USER_INPUT IN VARCHAR2,
                                        PC_STATUS     OUT VARCHAR2,
                                        PC_MESSAGE    OUT VARCHAR2) IS
    CURSOR CUR_USERSTRING IS
      SELECT REGEXP_SUBSTR(USER_INPUT, '([^~]*)(~|$)', 1, 1, NULL, 1) USERID,
             REGEXP_SUBSTR(USER_INPUT, '([^~]*)(~|$)', 1, 2, NULL, 1) USERNAME,
             REGEXP_SUBSTR(USER_INPUT, '([^~]*)(~|$)', 1, 3, NULL, 1) DATE_OF_JOINING,
             REGEXP_SUBSTR(USER_INPUT, '([^~]*)(~|$)', 1, 4, NULL, 1) CREATED_BY,
             REGEXP_SUBSTR(USER_INPUT, '([^~]*)(~|$)', 1, 5, NULL, 1) CREATION_DATE,
             REGEXP_SUBSTR(USER_INPUT, '([^~]*)(~|$)', 1, 6, NULL, 1) MODIFIED_BY,
             REGEXP_SUBSTR(USER_INPUT, '([^~]*)(~|$)', 1, 7, NULL, 1) MODIFIED_DATE
        FROM (
              SELECT REGEXP_SUBSTR(PC_USER_INPUT, '[^^]+', 1, LEVEL) USER_INPUT
                FROM DUAL
              CONNECT BY LEVEL <= LENGTH(PC_USER_INPUT) -
                       LENGTH(REPLACE(PC_USER_INPUT, '^')) + 1
              );

    TYPE T_CUR_USERSTRING IS TABLE OF CUR_USERSTRING%ROWTYPE;
    T_USERRECORD        T_CUR_USERSTRING;
    -- 初始化集合
    T_USERRECORD_INSERT T_CUR_USERSTRING := T_CUR_USERSTRING();
    T_USERRECORD_UPDATE T_CUR_USERSTRING := T_CUR_USERSTRING();
    
    -- 用于查询现有记录的变量
    V_EXIST_USERNAME        QM_USERDATA.USERNAME%TYPE;
    V_EXIST_DATE_OF_JOINING QM_USERDATA.DATE_OF_JOINING%TYPE;
    V_EXIST_CREATED_BY      QM_USERDATA.CREATED_BY%TYPE;
    V_EXIST_CREATION_DATE   QM_USERDATA.CREATION_DATE%TYPE;
    V_EXIST_MODIFIED_BY     QM_USERDATA.MODIFIED_BY%TYPE;
    V_EXIST_MODIFIED_DATE   QM_USERDATA.MODIFIED_DATE%TYPE;

  BEGIN
    PC_STATUS := CONST_SUCCESS;
    PC_MESSAGE := '操作成功';

    OPEN CUR_USERSTRING;
    LOOP
      FETCH CUR_USERSTRING BULK COLLECT INTO T_USERRECORD;

      IF T_USERRECORD.COUNT = 0 THEN
        EXIT;
      END IF;

      FOR I IN T_USERRECORD.FIRST .. T_USERRECORD.LAST LOOP
        -- 跳过USERID为空的无效记录
        IF T_USERRECORD(I).USERID IS NULL OR T_USERRECORD(I).USERID = '' THEN
          CONTINUE;
        END IF;

        -- 查询目标表是否存在该记录
        BEGIN
          SELECT USERNAME, DATE_OF_JOINING, CREATED_BY, CREATION_DATE, MODIFIED_BY, MODIFIED_DATE
            INTO V_EXIST_USERNAME, V_EXIST_DATE_OF_JOINING, V_EXIST_CREATED_BY,
                 V_EXIST_CREATION_DATE, V_EXIST_MODIFIED_BY, V_EXIST_MODIFIED_DATE
            FROM QM_USERDATA
           WHERE USERID = T_USERRECORD(I).USERID;

          -- 记录存在，判断是否有字段变化
          IF (T_USERRECORD(I).USERNAME != V_EXIST_USERNAME OR
              T_USERRECORD(I).DATE_OF_JOINING != V_EXIST_DATE_OF_JOINING OR
              T_USERRECORD(I).CREATED_BY != V_EXIST_CREATED_BY OR
              T_USERRECORD(I).CREATION_DATE != V_EXIST_CREATION_DATE OR
              T_USERRECORD(I).MODIFIED_BY != V_EXIST_MODIFIED_BY OR
              T_USERRECORD(I).MODIFIED_DATE != V_EXIST_MODIFIED_DATE) THEN
            -- 扩展更新集合并添加元素
            T_USERRECORD_UPDATE.EXTEND;
            T_USERRECORD_UPDATE(T_USERRECORD_UPDATE.LAST) := T_USERRECORD(I);
          END IF;
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            -- 记录不存在，添加到插入集合
            T_USERRECORD_INSERT.EXTEND;
            T_USERRECORD_INSERT(T_USERRECORD_INSERT.LAST) := T_USERRECORD(I);
        END;
      END LOOP;

      -- 执行批量插入（判断集合非空）
      IF T_USERRECORD_INSERT.COUNT > 0 THEN
        FORALL N IN T_USERRECORD_INSERT.FIRST .. T_USERRECORD_INSERT.LAST
          INSERT INTO QM_USERDATA VALUES T_USERRECORD_INSERT(N);
        -- 清空插入集合，避免下一次循环重复插入
        T_USERRECORD_INSERT.DELETE;
      END IF;

      -- 执行批量更新（判断集合非空）
      IF T_USERRECORD_UPDATE.COUNT > 0 THEN
        FORALL M IN T_USERRECORD_UPDATE.FIRST .. T_USERRECORD_UPDATE.LAST
          UPDATE QM_USERDATA
             SET USERNAME        = T_USERRECORD_UPDATE(M).USERNAME,
                 DATE_OF_JOINING = T_USERRECORD_UPDATE(M).DATE_OF_JOINING,
                 CREATED_BY      = T_USERRECORD_UPDATE(M).CREATED_BY,
                 CREATION_DATE   = T_USERRECORD_UPDATE(M).CREATION_DATE,
                 MODIFIED_BY     = T_USERRECORD_UPDATE(M).MODIFIED_BY,
                 MODIFIED_DATE   = T_USERRECORD_UPDATE(M).MODIFIED_DATE
           WHERE USERID = T_USERRECORD_UPDATE(M).USERID;
        -- 清空更新集合
        T_USERRECORD_UPDATE.DELETE;
      END IF;
    END LOOP;
    CLOSE CUR_USERSTRING;
    COMMIT;

  EXCEPTION
    WHEN OTHERS THEN
      IF CUR_USERSTRING%ISOPEN THEN
        CLOSE CUR_USERSTRING;
      END IF;
      PC_STATUS := CONST_FAILURE;
      PC_MESSAGE := 'ERROR IN FETCHING LIST ' || SQLCODE || ' ' || SQLERRM || ' PR_CREATE_USERRECORD_STRING';
      ROLLBACK;
  END PR_CREATE_USERRECORD_STRING;
END PKG_USERRECORD;
/

-- 测试调用
DECLARE
  V_STATUS  CHAR(1);
  V_MESSAGE VARCHAR2(5000);
BEGIN
  PKG_USERRECORD.PR_CREATE_USERRECORD_STRING(
    '1~AKASH~01-AUG-22~5~01-AUG-22~4~04-AUG-22^2~AJAY~02-AUG-22~6~02-AUG-22~4~04-AUG-22^3~MEGHA~02-AUG-22~6~02-AUG-22~4~04-AUG-22',
    V_STATUS,
    V_MESSAGE
  );
  DBMS_OUTPUT.PUT_LINE(V_STATUS || ' ' || V_MESSAGE);
END;
/

关键修改点说明 #

1. 集合初始化：声明时直接用T_CUR_USERSTRING()初始化插入、更新集合，避免未初始化错误
2. 游标参数化：将游标内的硬编码字符串替换为传入的PC_USER_INPUT参数，支持动态输入
3. 新/旧记录判断：通过查询QM_USERDATA判断记录是否存在，替换原错误的USERID IS NULL条件
4. 字段变化判断：对比现有记录与新数据的字段值，仅将有变化的记录加入更新集合
5. 空集合判断：执行FORALL前先检查集合COUNT是否大于0，避免空集合报错
6. 循环内集合清空：每次批量操作后清空插入、更新集合，避免下一次循环重复处理
7. 异常处理优化：添加游标是否打开的判断，避免关闭已关闭的游标，同时添加回滚操作

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