Oracle 海量 BOM 递归更新实战与 4GL 迁移指南

Oracle 海量 BOM 递归更新实战与 4GL 迁移指南

一、 核心架构设计

在海量 BOM 递归解析与长字符串（VARCHAR2(4000)）向下传递的场景中，传统的内存数组（如 BULK COLLECT 到 SYS.ODCIVARCHAR2LIST）极易触发 ORA-06532: Subscript outside of limit 内存越界错误。

最优解架构：
彻底摒弃内存集合，采用 **CONNECT BY**** 递归查询 + 显式游标流式处理 + 独立事务日志** 的三层架构。通过游标逐行拉取并更新，配合每 5000 行批量 COMMIT，将内存占用降至最低，同时利用 PRAGMA AUTONOMOUS_TRANSACTION 确保异常日志在主事务回滚时依然保留。

---

二、 基础设施：轻量级日志系统

为保障后台定时任务的可追溯性，需预先搭建日志追踪体系。

1. 序列与日志表

-- 清理旧环境（如需重跑）
DROP SEQUENCE seq_bom_upd_imud06_log;

-- 创建自增序列
CREATE SEQUENCE seq_bom_upd_imud06_log START WITH 1 INCREMENT BY 1;

-- 创建日志表
CREATE TABLE sp_bom_upd_imud06_log (
    log_id      NUMBER PRIMARY KEY,
    proc_name   VARCHAR2(100),
    log_level   VARCHAR2(10),
    message     VARCHAR2(4000),
    log_time    TIMESTAMP DEFAULT SYSTIMESTAMP
);

-- 建立复合索引，加速按时间和过程名检索
CREATE INDEX idx_bom_upd_imud06_log ON sp_bom_upd_imud06_log(proc_name, log_time);

2. 自动填充 ID 触发器

CREATE OR REPLACE TRIGGER trg_bom_upd_imud06_log
BEFORE INSERT ON sp_bom_upd_imud06_log
FOR EACH ROW
BEGIN
    IF :NEW.log_id IS NULL THEN
        SELECT seq_bom_upd_imud06_log.NEXTVAL INTO :NEW.log_id FROM DUAL;
    END IF;
END;
/

---

三、 核心存储过程：流式递归更新

CREATE OR REPLACE PROCEDURE sp_update_bom_project_info AS
  v_proc_name CONSTANT VARCHAR2(50) := 'sp_update_bom_project_info';
  v_batch_size CONSTANT PLS_INTEGER := 5000; -- 批量提交阈值
  v_updated_count PLS_INTEGER := 0;
  v_start_time TIMESTAMP := SYSTIMESTAMP;

  -- 【核心】独立事务日志写入过程
  PROCEDURE write_log(p_level VARCHAR2, p_msg VARCHAR2,
                      p_arg1 VARCHAR2 DEFAULT NULL,
                      p_arg2 VARCHAR2 DEFAULT NULL) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    v_final_msg VARCHAR2(4000);
  BEGIN
    v_final_msg := REPLACE(REPLACE(p_msg, '%1', NVL(p_arg1, '')), '%2', NVL(p_arg2, ''));
    INSERT INTO sp_bom_upd_imud06_log(proc_name, log_level, message)
    VALUES(v_proc_name, p_level, SUBSTR(v_final_msg, 1, 4000));
    COMMIT;
  EXCEPTION WHEN OTHERS THEN NULL; 
  END;

BEGIN
  write_log('INFO', '任务启动...');

  -- 使用显式游标流式处理，彻底解决内存数组下标越界(ORA-06532)
  FOR rec IN (
    SELECT bmb03 AS child_id, '/' || t.imaud06 AS root_n, '/' || t.imaud06 || '/' AS root_m
    FROM bmb_file b
    LEFT JOIN bma_file a ON b.bmb01 = a.bma01
    JOIN (
      SELECT ima01, imaud06 FROM ima_file
      WHERE imaacti = 'Y' AND imaud06 IS NOT NULL
        AND NOT EXISTS (SELECT 1 FROM bmb_file WHERE bmb03 = ima01) -- 精准定位真正的顶层节点
    ) t ON b.bmb01 = t.ima01
    WHERE a.bma10 = '2' AND b.bmb05 IS NULL 
    CONNECT BY PRIOR b.bmb03 = b.bmb01
    START WITH b.bmb01 IN (
      SELECT ima01 FROM ima_file WHERE imaacti = 'Y' AND imaud06 IS NOT NULL
        AND NOT EXISTS (SELECT 1 FROM bmb_file WHERE bmb03 = ima01)
    )
  ) LOOP
    BEGIN
      -- 直接在游标循环中执行单行更新，利用索引极速完成
      UPDATE ima_file i SET
        i.imaud06 = CASE WHEN INSTR('/'||NVL(i.imaud06,'')||'/', rec.root_m) = 0
                         THEN NVL(i.imaud06, rec.root_n) || rec.root_n
                         ELSE i.imaud06 END,
        i.ima17   = CASE WHEN INSTR(NVL(i.imaud06,''), '/') > 0 THEN 'Y' ELSE 'N' END
      WHERE i.ima01 = rec.child_id AND i.imaacti = 'Y';
      
      v_updated_count := v_updated_count + SQL%ROWCOUNT;
      
    EXCEPTION WHEN OTHERS THEN
      -- 单行失败不中断全局，记录警告继续执行
      write_log('WARN', '跳过物料[%1]，原因:%2', rec.child_id, SUBSTR(SQLERRM, 1, 200));
    END;

    -- 批量提交控制，防止 Undo 表空间撑爆
    IF MOD(v_updated_count, v_batch_size) = 0 AND v_updated_count > 0 THEN
      COMMIT;
      write_log('INFO', '已处理并累计更新: %1 行', TO_CHAR(v_updated_count));
    END IF;
  END LOOP;
  
  COMMIT;
  write_log('INFO', '任务成功结束! 总耗时: %1 秒, 共更新: %2 行',
           TO_CHAR(ROUND((EXTRACT(DAY FROM (SYSTIMESTAMP - v_start_time))*86400 + 
                          EXTRACT(HOUR FROM (SYSTIMESTAMP - v_start_time))*3600 + 
                          EXTRACT(MINUTE FROM (SYSTIMESTAMP - v_start_time))*60 + 
                          EXTRACT(SECOND FROM (SYSTIMESTAMP - v_start_time))),2)), 
           TO_CHAR(v_updated_count));

EXCEPTION WHEN OTHERS THEN
  ROLLBACK;
  write_log('ERROR', '异常回滚! MSG: %1 | POS: %2',
            SUBSTR(SQLERRM, 1, 2000),
            SUBSTR(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, 1, 2000));
END sp_update_bom_project_info;

---

四、 自动化调度与运维监控

使用 Oracle 原生 DBMS_SCHEDULER 替代老旧的 DBMS_JOB，配置每日高频定时任务。

1. 创建定时任务

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'JOB_SP_UPDATE_BOM_PROJECT_INFO',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN NBJF.SP_UPDATE_BOM_PROJECT_INFO(); END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=7,12,17,20; BYMINUTE=30,0,0,0; BYSECOND=0',
    enabled         => TRUE,
    comments        => '每天7:30, 12:00, 17:00, 20:00 执行BOM项目信息更新imaud06，替代旧版cimp009程序'
  );
END;
/

注：使用 PLSQL_BLOCK 包裹调用，可完美兼容带包名前缀及括号的存储过程执行。

2. 日常运维 SQL 锦囊

查看任务状态（确认是否生效）：

SELECT job_name, enabled, state, last_start_date, next_run_date 
FROM user_scheduler_jobs 
WHERE job_name = 'JOB_SP_UPDATE_BOM_PROJECT_INFO';
-- 期望结果：enabled=TRUE, state=SCHEDULED

手动触发测试：

BEGIN DBMS_SCHEDULER.RUN_JOB('JOB_SP_UPDATE_BOM_PROJECT_INFO'); END;
/

排查报错（双管齐下）：

-- 1. 查调度器系统日志
SELECT log_date, status, error#, additional_info 
FROM user_scheduler_job_log 
WHERE job_name = 'JOB_SP_UPDATE_BOM_PROJECT_INFO' 
ORDER BY log_date DESC;

-- 2. 查自定义业务日志
SELECT * FROM sp_bom_upd_imud06_log ORDER BY log_time DESC;

删除定时任务（备用）：

BEGIN DBMS_SCHEDULER.DROP_JOB('JOB_SP_UPDATE_BOM_PROJECT_INFO'); END;
/

文档梳理由AI生成