如何修改Oracle存储过程调用pipelined函数批量生成多日日程

你可以选择两种方式实现需求，两种方式都用到了你提到的generate_dates_pipelined管道函数：

方案1：修改原有存储过程（支持单日/多日通用） #

直接修改CREATE_SCHEDULE存储过程的参数和逻辑，兼容单日期和多日期生成需求，代码如下：

CREATE OR REPLACE PROCEDURE CREATE_SCHEDULE(
  i_schedule_id IN PLS_INTEGER,
  i_start_date  IN DATE, -- 替换原有i_base_date，传单日时和i_end_date传相同值即可
  i_end_date    IN DATE,
  i_offset      IN PLS_INTEGER DEFAULT 0, 
  i_incr        IN PLS_INTEGER DEFAULT 10,
  i_duration    IN PLS_INTEGER DEFAULT 5
)
AS 
  l_offset   interval day to second;
  l_incr     interval day to second;
  l_duration interval day to second;
BEGIN
  l_offset   := NUMTODSINTERVAL(i_offset, 'SECOND') ;
  l_incr     := NUMTODSINTERVAL(i_incr, 'MINUTE') ;
  l_duration := NUMTODSINTERVAL(i_duration, 'MINUTE') ;

  MERGE INTO schedule dst
  USING (
    SELECT   i_schedule_id AS schedule_id,
             l.location_id,
             d.COLUMN_VALUE AS base_date,
             d.COLUMN_VALUE + l_offset + (l_incr * (ROWNUM - 1)) AS start_date,
             d.COLUMN_VALUE + l_offset + (l_incr * (ROWNUM - 1)) + l_duration AS end_date
    FROM     TABLE(generate_dates_pipelined(i_start_date, i_end_date)) d
    CROSS JOIN locations l
    WHERE  l.location_type = 'G'
  ) src
  ON (   src.schedule_id = dst.schedule_id
     AND src.location_id = dst.location_id
     AND src.base_date   = dst.base_date
  )
  WHEN NOT MATCHED THEN
    INSERT (
      schedule_id,
      location_id,
      base_date,
      start_date,
      end_date
    ) VALUES (
      src.schedule_id,
      src.location_id,
      src.base_date,
      src.start_date,
      src.end_date
    );
END;
/

调用示例： #

生成2024年5月1日到2024年5月7日的日程：

EXEC CREATE_SCHEDULE(1, DATE'2024-05-01', DATE'2024-05-07', CONVERT_TO_SECONDS('16:00:00'));
/

如果需要保持原有单日期调用习惯，传相同的起止日期即可：

EXEC CREATE_SCHEDULE(1, TRUNC(SYSDATE), TRUNC(SYSDATE), CONVERT_TO_SECONDS('16:00:00'));
/

方案2：上层封装新存储过程（不改动原有逻辑，兼容性更好） #

如果不想修改已经在使用的原有存储过程，可以新增一个批量调用的封装存储过程，原有单日期逻辑完全保留：

CREATE OR REPLACE PROCEDURE CREATE_SCHEDULE_BULK(
  i_schedule_id IN PLS_INTEGER,
  i_start_date  IN DATE,
  i_end_date    IN DATE,
  i_offset      IN PLS_INTEGER DEFAULT 0, 
  i_incr        IN PLS_INTEGER DEFAULT 10,
  i_duration    IN PLS_INTEGER DEFAULT 5
)
AS
BEGIN
  -- 遍历管道函数返回的所有日期，逐个调用原有单日程生成存储过程
  FOR cur IN (SELECT COLUMN_VALUE AS base_date FROM TABLE(generate_dates_pipelined(i_start_date, i_end_date))) LOOP
    CREATE_SCHEDULE(
      i_schedule_id => i_schedule_id,
      i_base_date => cur.base_date,
      i_offset => i_offset,
      i_incr => i_incr,
      i_duration => i_duration
    );
  END LOOP;
END;
/

调用示例： #

EXEC CREATE_SCHEDULE_BULK(1, DATE'2024-05-01', DATE'2024-05-07', CONVERT_TO_SECONDS('16:00:00'));
/

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