Oracle PL/SQL如何实现code值减小时将已有记录字段置空

基础表结构 #

涉及两张业务表，初始化结构如下：

CREATE TABLE main_tab (
    seq_id      NUMBER(10),
    e_id        NUMBER(10),
    code        NUMBER(10),
    CONSTRAINT pk_main_tab PRIMARY KEY(seq_id)
);

-- 初始测试数据
INSERT INTO main_tab VALUES(1,11,3);

CREATE TABLE transact_tab (
    seq_id,
    e_id,
    code        NUMBER(10),
    start_date  DATE,
    end_date    DATE
);

业务规则说明 #

数据同步需要满足以下规则：

• 根据main_tab的code字段值，在transact_tab生成从1到code值的连续code记录
• 新插入记录的start_date、end_date默认取当前系统时间SYSDATE
• 仅当前生效的最大code值对应的记录，end_date字段为NULL，其余历史有效code记录的end_date为SYSDATE
• 当main_tab的code值减小时，超出新code范围的历史记录需要将start_date、end_date均置为NULL

原有代码缺陷 #

原有实现仅能支持code值增大的场景，无法处理code值减小的逻辑，代码如下：

DECLARE 
  l_transact_row transact_tab%ROWTYPE;
BEGIN
  FOR r IN (SELECT * FROM main_tab) LOOP
    FOR i IN 1 .. r.code LOOP
      BEGIN
        SELECT * INTO l_transact_row 
          FROM transact_tab
         WHERE e_id = r.e_id AND code = i; 
        IF l_transact_row.end_date IS NULL AND i <> r.code THEN
          UPDATE transact_tab SET end_date = SYSDATE WHERE e_id = r.e_id AND code = i;
        END IF;
      EXCEPTION WHEN NO_DATA_FOUND THEN
        INSERT INTO transact_tab (e_id, code,start_date, end_date)
          VALUES (r.e_id, i, SYSDATE, CASE WHEN i = r.code THEN NULL ELSE SYSDATE END);  
      END ;
    END LOOP;
  END LOOP;
END;

数据库版本：Oracle 18c

以初始code=3的记录为例，当code值修改为2时，transact_tab的预期结果为：

+------+------+------------+----------+
| e_id | code | start_date | end_date |
+------+------+------------+----------+
|   11 |    1 | 19-06-22   | 19-06-22 |
|   11 |    2 | 19-06-22   | NULL     |
|   11 |    3 | NULL       | NULL     |
+------+------+------------+----------+

修正后代码 #

在原有逻辑基础上补充两部分处理：

1. 对之前被置空失效、现在重新落入有效code范围的记录，重置日期字段
2. 新增对超出当前code范围的历史记录的处理，将其日期字段置空

DECLARE 
  l_transact_row transact_tab%ROWTYPE;
BEGIN
  FOR r IN (SELECT * FROM main_tab) LOOP
    -- 处理1到当前有效code范围内的记录
    FOR i IN 1 .. r.code LOOP
      BEGIN
        SELECT * INTO l_transact_row 
          FROM transact_tab
         WHERE e_id = r.e_id AND code = i; 
        
        -- 原逻辑：之前的生效code现在不是最大code，更新end_date
        IF l_transact_row.end_date IS NULL AND i <> r.code THEN
          UPDATE transact_tab 
             SET end_date = SYSDATE 
           WHERE e_id = r.e_id AND code = i;
        END IF;

        -- 新增：之前失效的code重新进入有效范围，重置日期
        IF l_transact_row.start_date IS NULL THEN
          UPDATE transact_tab 
             SET start_date = SYSDATE,
                 end_date = CASE WHEN i = r.code THEN NULL ELSE SYSDATE END
           WHERE e_id = r.e_id AND code = i;
        END IF;
      EXCEPTION WHEN NO_DATA_FOUND THEN
        -- 不存在的记录直接插入
        INSERT INTO transact_tab (e_id, code,start_date, end_date)
          VALUES (r.e_id, i, SYSDATE, CASE WHEN i = r.code THEN NULL ELSE SYSDATE END);  
      END ;
    END LOOP;

    -- 新增：处理大于当前code的失效记录
    FOR r_invalid IN (
      SELECT code 
        FROM transact_tab 
       WHERE e_id = r.e_id 
         AND code > r.code
         AND (start_date IS NOT NULL OR end_date IS NOT NULL)
    ) LOOP
      UPDATE transact_tab 
         SET start_date = NULL,
             end_date = NULL
       WHERE e_id = r.e_id 
         AND code = r_invalid.code;
    END LOOP;
  END LOOP;
  COMMIT;
END;
/

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