Блог

У меня есть процедура, которая работает так, как задумано. Проблема в том, что он создает расписание только на один день за раз.

Мой вопрос заключается в том, можно ли предпочтительно изменить процедуру(или обернуть) для вызова функции generate_dates_pipelined, чтобы можно было создавать сразу несколько расписаний?

Чтобы изменить процедуру, я подозреваю, что мне понадобится что-то подобное, но я не уверен, как это сделать с текущим кодом.

ИЗ ТАБЛИЦЫ(generate_dates_pipelined(p_start_date, p_end_date))

Заранее спасибо за ваше терпение, помощь и опыт, а также алку, который ответил.

 
ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';

CREATE OR REPLACE TYPE nt_date IS TABLE OF DATE;
/

CREATE OR REPLACE FUNCTION generate_dates_pipelined(
  p_from IN DATE,
  p_to   IN DATE
)
  RETURN nt_date PIPELINED DETERMINISTIC
IS
  v_start DATE := TRUNC(LEAST(p_from, p_to));
  v_end   DATE := TRUNC(GREATEST(p_from, p_to));
BEGIN
  LOOP
    PIPE ROW (v_start);
    EXIT WHEN v_start >= v_end;
    v_start := v_start   INTERVAL '1' DAY;
  END LOOP;
  RETURN;
END generate_dates_pipelined;
/

CREATE OR REPLACE FUNCTION CONVERT_TO_SECONDS( 
  i_date_string IN VARCHAR2 
)
RETURN INTEGER DETERMINISTIC
AS
BEGIN
  RETURN ( TO_DATE(i_date_string, 'HH24:MI:SS')
         - TO_DATE('00:00:00', 'HH24:MI:SS')
         ) * 86400;
END;
/  


CREATE TABLE locations AS
    SELECT level AS location_id,
       'Door ' || level AS location_name,

    CASE round(dbms_random.value(1,3)) 
            WHEN 1 THEN 'G' 
            WHEN 2 THEN 'G' 
            WHEN 3 THEN 'G' 
         END AS location_type

    FROM   dual
    CONNECT BY level <= 5;

     ALTER TABLE locations 
         ADD ( CONSTRAINT locations_pk
       PRIMARY KEY (location_id));

create table schedule(
      seq_num NUMBER  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
       schedule_id NUMBER(4),
       location_id number(4),
       base_date DATE,
       start_date DATE,
       end_date DATE,
          constraint schedule_pk primary key (schedule_id, location_id, base_date),
         CONSTRAINT start_min check (start_date=trunc(start_date,'MI')),   
       CONSTRAINT end_min check (end_date=trunc(end_date,'MI')),
 CONSTRAINT end_gt_start CHECK (end_date >= start_date)
      );


CREATE OR REPLACE PROCEDURE CREATE_SCHEDULE(
  i_schedule_id IN PLS_INTEGER,
  i_base_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,
             i_base_date AS base_date,
             i_base_date   l_offset   (l_incr * (ROWNUM - 1))
               AS start_date,
             i_base_date   l_offset   (l_incr * (ROWNUM - 1))   l_duration
               AS end_date
    FROM     locations l
    where  location_id in ( 
      select location_id
      from   locations
      where  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;
/


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

 

Если я правильно интерпретирую ваше требование, то простого цикла должно быть достаточно. (Не самый эффективный механизм, но, вероятно, адекватный)

 CREATE OR REPLACE PROCEDURE CREATE_SCHEDULE(
  i_schedule_id IN PLS_INTEGER,
  -- i_base_date   IN DATE,
  p_start_date in date,
  p_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;
  
  i_base_date date;
BEGIN
  l_offset   := NUMTODSINTERVAL(i_offset, 'SECOND') ;
  l_incr     := NUMTODSINTERVAL(i_incr, 'MINUTE') ;
  l_duration := NUMTODSINTERVAL(i_duration, 'MINUTE') ;

  for i in ( select column_value each_date from TABLE(generate_dates_pipelined(p_start_date, p_end_date)) )
  loop

    i_base_date := i.each_date;

    MERGE INTO schedule dst
    USING (
      SELECT   i_schedule_id AS schedule_id,
               l.location_id,
               i_base_date AS base_date,
               i_base_date   l_offset   (l_incr * (ROWNUM - 1))
                 AS start_date,
               i_base_date   l_offset   (l_incr * (ROWNUM - 1))   l_duration
                 AS end_date
      FROM     locations l
      where  location_id in ( 
        select location_id
        from   locations
        where  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 loop;      
END;
/
 

Я был немного ленив и просто назначил «i_base_date» в качестве переменной, поэтому СЛИЯНИЕ осталось нетронутым.