Oracle converting an INSERT to MERGE ot not EXISTS

Question

I have the following code, which is working fine. If I run the procedure more than once with the same values I get a PRIMARY KEY violation, which I expect. Could the INSERT be converted into a MERGE or NOT EXISTS to avoid this issue?

The examples I saw online appear to be using literal values or an ON statement with the MERGE.

As I am a novice SQL developer any help or sample code, which reflects my requirement would be greatly appreciated.

Thanks in advance to all who answer.

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 table schedule_assignment(
       schedule_id number(4),
       schedule_date DATE,
       employee_id NUMBER(6) DEFAULT 0,
constraint sa_chk check (schedule_date=trunc(schedule_date, 'dd')),
          constraint sa_pk primary key (schedule_id, schedule_date)
  );

CREATE OR REPLACE PROCEDURE 
create_schedule_assignment (
  p_schedule_id IN NUMBER,
  p_start_date  IN DATE,
  p_end_date   IN DATE
)
IS
BEGIN
  INSERT INTO schedule_assignment(
  schedule_id,
   schedule_date
  )
  SELECT 
        p_schedule_id,
         COLUMN_VALUE
  FROM   TABLE(generate_dates_pipelined(p_start_date, p_end_date));
END;

EXEC create_schedule_assignment (1, DATE '2021-08-21', DATE '2021-08-30');

Answer 1

Rewrite procedure to

SQL> CREATE OR REPLACE PROCEDURE
  2  create_schedule_assignment (
  3    p_schedule_id IN NUMBER,
  4    p_start_date  IN DATE,
  5    p_end_date   IN DATE
  6  )
  7  IS
  8  BEGIN
  9    merge into schedule_assignment s
 10      using (select p_schedule_id as schedule_id,
 11                    column_value  as schedule_date
 12             from table(generate_dates_pipelined(p_start_date, p_end_date))
 13            ) x
 14      on (    x.schedule_id = s.schedule_id
 15          and x.schedule_date = s.schedule_date
 16         )
 17      when not matched then insert (schedule_id, schedule_date)
 18        values (x.schedule_id, x.schedule_date);
 19  END;
 20  /

Procedure created.

SQL>

Testing: initially, table is empty:

SQL> select schedule_id, min(schedule_date) mindat, max(schedule_date) maxdate, count(*)
  2  from schedule_assignment group by schedule_id;

no rows selected

Run the procedure for the 1st time:

SQL> EXEC create_schedule_assignment (1, DATE '2021-08-21', DATE '2021-08-30');

PL/SQL procedure successfully completed.

Table contents:

SQL> select schedule_id, min(schedule_date) mindat, max(schedule_date) maxdate, count(*)
  2  from schedule_assignment group by schedule_id;

SCHEDULE_ID MINDAT     MAXDATE      COUNT(*)
----------- ---------- ---------- ----------
          1 21/08/2021 30/08/2021         10

Run the procedure with same parameters again:

SQL> EXEC create_schedule_assignment (1, DATE '2021-08-21', DATE '2021-08-30');

PL/SQL procedure successfully completed.

SQL> EXEC create_schedule_assignment (1, DATE '2021-08-21', DATE '2021-08-30');

PL/SQL procedure successfully completed.

SQL> EXEC create_schedule_assignment (1, DATE '2021-08-21', DATE '2021-08-30');

PL/SQL procedure successfully completed.

Result: nothing changed, no rows in table (but no error either):

SQL> select schedule_id, min(schedule_date) mindat, max(schedule_date) maxdate, count(*)
  2  from schedule_assignment group by schedule_id;

SCHEDULE_ID MINDAT     MAXDATE      COUNT(*)
----------- ---------- ---------- ----------
          1 21/08/2021 30/08/2021         10

SQL>

Run the procedure with the same SCHEDULE_ID, but different dates:

SQL> EXEC create_schedule_assignment (1, DATE '2021-08-29', DATE '2021-09-02');

PL/SQL procedure successfully completed.

SQL> select schedule_id, min(schedule_date) mindat, max(schedule_date) maxdate, count(*)
  2  from schedule_assignment group by schedule_id;

SCHEDULE_ID MINDAT     MAXDATE      COUNT(*)
----------- ---------- ---------- ----------
          1 21/08/2021 02/09/2021         13

SQL>

Right; number of rows is now increased to 13 (was 10 previously, because 31.08., 01.09. and 02.09. were added).

New SCHEDULE_ID:

SQL> EXEC create_schedule_assignment (2, DATE '2021-09-05', DATE '2021-09-07');

PL/SQL procedure successfully completed.

SQL> select schedule_id, min(schedule_date) mindat, max(schedule_date) maxdate, count(*)
  2  from schedule_assignment group by schedule_id;

SCHEDULE_ID MINDAT     MAXDATE      COUNT(*)
----------- ---------- ---------- ----------
          1 21/08/2021 02/09/2021         13
          2 05/09/2021 07/09/2021          3

SQL>

Looks OK to me.