如何使用PL/SQL以下一行start_time为end_time计算时间戳时长？

需求说明 #

现有数据表仅包含start_time字段，需将每一行的下一行start_time作为当前行的end_time，并计算两行时间的间隔时长。

表结构示例 #

当前表（假设表名为time_records）

期望结果表

解决方案 #

Oracle中可以用LEAD()分析函数直接实现，这是最高效的方式；如果需要封装到PL/SQL块中，也可以基于该查询扩展。

1. 基础SQL查询（直接获取结果）

SELECT
    id,
    start_time,
    LEAD(start_time) OVER (ORDER BY id) AS end_time,
    CASE
        WHEN LEAD(start_time) OVER (ORDER BY id) IS NOT NULL THEN
            TO_CHAR(TRUNC((LEAD(start_time) OVER (ORDER BY id) - start_time) * 24)) || '小时' ||
            TO_CHAR(TRUNC(MOD((LEAD(start_time) OVER (ORDER BY id) - start_time) * 24 * 60, 60))) || '分钟'
        ELSE
            NULL
    END AS duration
FROM
    time_records
ORDER BY
    id;

代码说明

• LEAD(start_time) OVER (ORDER BY id)：LEAD分析函数用于获取当前行的下一行数据，这里按id排序保证顺序正确，若最后一行没有下一行，返回NULL。
• 时长计算：通过时间差乘以24得到总小时数，取整后拼接"小时"；用MOD函数提取剩余分钟数，取整后拼接"分钟"。

2. PL/SQL块（批量处理或输出结果）

如果需要在PL/SQL环境中执行并输出结果，可以用游标封装查询：

DECLARE
    CURSOR c_time_data IS
        SELECT
            id,
            start_time,
            LEAD(start_time) OVER (ORDER BY id) AS end_time,
            CASE
                WHEN LEAD(start_time) OVER (ORDER BY id) IS NOT NULL THEN
                    TO_CHAR(TRUNC((LEAD(start_time) OVER (ORDER BY id) - start_time) * 24)) || '小时' ||
                    TO_CHAR(TRUNC(MOD((LEAD(start_time) OVER (ORDER BY id) - start_time) * 24 * 60, 60))) || '分钟'
                ELSE
                    NULL
            END AS duration
        FROM
            time_records
        ORDER BY
            id;
    v_id          time_records.id%TYPE;
    v_start_time  time_records.start_time%TYPE;
    v_end_time    time_records.start_time%TYPE;
    v_duration    VARCHAR2(50);
BEGIN
    OPEN c_time_data;
    LOOP
        FETCH c_time_data INTO v_id, v_start_time, v_end_time, v_duration;
        EXIT WHEN c_time_data%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE(
            'ID: ' || v_id ||
            ' | 开始时间: ' || TO_CHAR(v_start_time, 'YYYY-MM-DD HH24:MI:SS') ||
            ' | 结束时间: ' || NVL(TO_CHAR(v_end_time, 'YYYY-MM-DD HH24:MI:SS'), '无') ||
            ' | 时长: ' || NVL(v_duration, '无')
        );
    END LOOP;
    CLOSE c_time_data;
END;
/

代码说明

• 定义游标c_time_data，包含上述SQL查询的结果。
• 通过循环遍历游标，用DBMS_OUTPUT.PUT_LINE输出每一行的详细信息，NVL函数处理NULL值，避免输出空白。

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