Oracle数据库设置END_DATE：基于同用户下一条START_DATE

现有一张CONTRACT表，需新增END_DATE字段，填充规则如下：

• 同一USER_ID的多条记录，某条记录的END_DATE为该用户下一条记录的START_DATE
• 若为该用户唯一记录，END_DATE设为未来指定日期（如01.01.2050）

示例表结构 #

ID | USER_ID | START_DATE | END_DATE
-------------------------------------
1  | 1       | 01.01.2015 |
2  | 1       | 01.01.2016 |
3  | 1       | 01.07.2018 |
4  | 1       | 01.08.2021 |
5  | 2       | 01.01.2015 |
6  | 3       | 01.01.2016 |
7  | 3       | 01.07.2018 |
8  | 4       | 01.08.2021 |

预期结果 #

ID | USER_ID | START_DATE | END_DATE
-------------------------------------
1  | 1       | 01.01.2015 | 01.01.2016
2  | 1       | 01.01.2016 | 01.07.2018
3  | 1       | 01.07.2018 | 01.08.2021
4  | 1       | 01.08.2021 | 01.01.2050
5  | 2       | 01.01.2015 | 01.01.2050
6  | 3       | 01.01.2016 | 01.07.2018
7  | 3       | 01.07.2018 | 01.01.2050
8  | 4       | 01.08.2021 | 01.01.2050

用户已尝试编写游标循环代码，但不知如何推进：

DECLARE
    CURSOR c_contract
        IS
        SELECT
            USER_ID
        FROM
            CONTRACT
        ORDER BY START_DATE;
BEGIN
    FOR r_contract IN c_contract
        LOOP
            dbms_output.put_line( r_contract.USER_ID );
        END LOOP;
END;
/

步骤1：新增END_DATE字段 #

首先执行语句添加日期类型字段：

ALTER TABLE CONTRACT ADD END_DATE DATE;

步骤2：使用窗口函数批量更新（推荐） #

相比游标逐行处理，用LEAD()窗口函数可以更高效完成批量更新：

MERGE INTO CONTRACT t
USING (
    SELECT 
        ID,
        LEAD(START_DATE) OVER (PARTITION BY USER_ID ORDER BY START_DATE) AS NEXT_START_DATE
    FROM CONTRACT
) s
ON (t.ID = s.ID)
WHEN MATCHED THEN UPDATE
SET t.END_DATE = NVL(s.NEXT_START_DATE, TO_DATE('01.01.2050', 'DD.MM.YYYY'));

代码说明：

• LEAD(START_DATE) OVER (PARTITION BY USER_ID ORDER BY START_DATE)：按USER_ID分组、START_DATE排序，获取当前记录的下一条记录起始日期
• NVL(..., TO_DATE('01.01.2050', 'DD.MM.YYYY'))：若当前是用户最后一条/唯一记录，则用指定未来日期填充END_DATE

游标方式实现（若需保留游标逻辑） #

如果必须使用游标，可修改代码直接在查询中获取下一条记录信息，避免循环内重复查询：

DECLARE
    CURSOR c_contract IS
        SELECT 
            ID,
            USER_ID,
            START_DATE,
            LEAD(START_DATE) OVER (PARTITION BY USER_ID ORDER BY START_DATE) AS NEXT_START_DATE
        FROM CONTRACT
        ORDER BY USER_ID, START_DATE;
BEGIN
    FOR r_contract IN c_contract LOOP
        UPDATE CONTRACT
        SET END_DATE = NVL(r_contract.NEXT_START_DATE, TO_DATE('01.01.2050', 'DD.MM.YYYY'))
        WHERE ID = r_contract.ID;
    END LOOP;
    COMMIT;
END;
/

说明：

• 游标查询中提前通过窗口函数获取下一条记录的起始日期
• 循环结束后记得提交事务

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