Oracle递归CTE改造：关联locations表结合日期函数填充schedule表

实现逻辑 #

• 调用generate_dates_pipelined函数生成指定范围的所有日期，每个日期叠加83760秒（对应23:16:00）作为当日时段生成的基准起始时间
• 对locations表生成有序行号，和生成的时段行号一一关联，保证每个时段对应唯一的location_id，适配生产环境location_id不连续的场景
• 保留原有跨午夜自动终止的逻辑，符合schedule表的同天约束要求

完整代码 #

-- 查看生成结果的查询语句，确认无误后可直接替换为INSERT语句
WITH date_series AS (
  -- 调整generate_dates_pipelined的两个入参即可修改生成的日期范围
  SELECT column_value + 83760/86400 AS base_start_time
  FROM TABLE(generate_dates_pipelined(DATE'2021-08-19', DATE'2021-08-19'))
),
time_slots AS (
  SELECT
    base_start_time + (LEVEL-1) * INTERVAL '10' MINUTE AS start_date,
    base_start_time + (LEVEL-1) * INTERVAL '10' MINUTE + INTERVAL '5' MINUTE AS end_date,
    LEVEL AS rn
  FROM date_series
  CONNECT BY 
    base_start_time + (LEVEL-1) * INTERVAL '10' MINUTE < TRUNC(base_start_time) + INTERVAL '1' DAY
    AND LEVEL <= (SELECT COUNT(*) FROM locations)
),
location_rn AS (
  -- 可自行修改ORDER BY规则调整location_id的匹配顺序
  SELECT 
    location_id,
    ROW_NUMBER() OVER (ORDER BY location_id) AS rn
  FROM locations
)
SELECT
  1 AS schedule_id, -- 后续封装存储过程可替换为入参变量
  l.location_id,
  t.start_date,
  t.end_date
FROM time_slots t
JOIN location_rn l ON t.rn = l.rn;

-- 插入数据到schedule表的语句
INSERT INTO schedule (schedule_id, location_id, start_date, end_date)
WITH date_series AS (
  SELECT column_value + 83760/86400 AS base_start_time
  FROM TABLE(generate_dates_pipelined(DATE'2021-08-19', DATE'2021-08-19'))
),
time_slots AS (
  SELECT
    base_start_time + (LEVEL-1) * INTERVAL '10' MINUTE AS start_date,
    base_start_time + (LEVEL-1) * INTERVAL '10' MINUTE + INTERVAL '5' MINUTE AS end_date,
    LEVEL AS rn
  FROM date_series
  CONNECT BY 
    base_start_time + (LEVEL-1) * INTERVAL '10' MINUTE < TRUNC(base_start_time) + INTERVAL '1' DAY
    AND LEVEL <= (SELECT COUNT(*) FROM locations)
),
location_rn AS (
  SELECT 
    location_id,
    ROW_NUMBER() OVER (ORDER BY location_id) AS rn
  FROM locations
)
SELECT
  1 AS schedule_id,
  l.location_id,
  t.start_date,
  t.end_date
FROM time_slots t
JOIN location_rn l ON t.rn = l.rn;

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