时间戳

首页 > 解决方案 > Oracle 将 INSERT 转换为 MERGE ot not EXISTS

sql - Oracle 将 INSERT 转换为 MERGE ot not EXISTS

问题描述

我有以下代码，它工作正常。如果我使用相同的值多次运行该过程，我会得到 PRIMARY KEY 违规，这是我所期望的。是否可以将 INSERT 转换为 MERGE 或 NOT EXISTS 以避免此问题？

我在网上看到的示例似乎是使用文字值或带有 MERGE 的 ON 语句。

由于我是一名新手 SQL 开发人员，因此将不胜感激任何反映我的要求的帮助或示例代码。

提前感谢所有回答的人。

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');

标签： sqloraclemerge

解决方案

将过程重写为

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>

测试：最初，表是空的：

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

no rows selected

第一次运行该过程：

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

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 30/08/2021         10

再次使用相同的参数运行该过程：

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.

结果：没有任何变化，表中没有行（但也没有错误）：

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>

SCHEDULE_ID使用相同但不同的日期运行该过程：

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>

对; 行数现在增加到 13（之前是 10，因为添加了 31.08.、01.09.和 02.09.）。

新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>

对我来说看起来不错。

推荐阅读