如何在Oracle存储过程中添加数据存在性检查避免重复插入

要实现仅当数据不存在时执行插入，有三种常用方案，可根据业务场景和并发需求选择：

方案一：INSERT结合NOT EXISTS子句 #

直接在插入语句中通过NOT EXISTS判断目标表中是否已有相同记录，不存在则执行插入。需先明确表的唯一标识字段（示例假设餐厅名、用户名、评分日期为唯一组合，可根据实际唯一键调整）。

修改后的存储过程代码：

CREATE OR REPLACE PROCEDURE AddNewReview(
  RESTAURANTNAME IN VARCHAR2 (100)
, USERNAME IN VARCHAR2 (100)
, Stars IN Int
, RATINGDATE IN DATE 
) AS 
BEGIN
  INSERT INTO newReview (RestaurantName, UserName, Stars, RatingDate)
  SELECT RESTAURANTNAME, USERNAME, Stars, RATINGDATE
  FROM DUAL
  WHERE NOT EXISTS (
    SELECT 1 
    FROM newReview
    WHERE RestaurantName = AddNewReview.RESTAURANTNAME
      AND UserName = AddNewReview.USERNAME
      AND RatingDate = AddNewReview.RATINGDATE -- 若唯一键仅为前两个字段，删除此行
  );
  
  -- 可选：反馈执行结果
  IF SQL%ROWCOUNT > 0 THEN
    DBMS_OUTPUT.PUT_LINE('评论插入成功');
  ELSE
    DBMS_OUTPUT.PUT_LINE('该评论已存在，未执行插入');
  END IF;
END AddNewReview;
/

方案二：使用MERGE语句 #

利用Oracle的MERGE合并操作，仅当源数据与目标表无匹配记录时执行插入：

CREATE OR REPLACE PROCEDURE AddNewReview(
  RESTAURANTNAME IN VARCHAR2 (100)
, USERNAME IN VARCHAR2 (100)
, Stars IN Int
, RATINGDATE IN DATE 
) AS 
BEGIN
  MERGE INTO newReview t
  USING (
    SELECT RESTAURANTNAME AS rn, USERNAME AS un, Stars AS s, RATINGDATE AS rd FROM DUAL
  ) s
  ON (t.RestaurantName = s.rn AND t.UserName = s.un AND t.RatingDate = s.rd)
  WHEN NOT MATCHED THEN
    INSERT (RestaurantName, UserName, Stars, RatingDate)
    VALUES (s.rn, s.un, s.s, s.rd);
  
  -- 可选：反馈执行结果
  IF SQL%ROWCOUNT > 0 THEN
    DBMS_OUTPUT.PUT_LINE('评论插入成功');
  ELSE
    DBMS_OUTPUT.PUT_LINE('该评论已存在，未执行插入');
  END IF;
END AddNewReview;
/

方案三：唯一约束+异常捕获（推荐高并发场景） #

先在数据库层面给表添加唯一约束，确保数据唯一性，再在存储过程中捕获重复插入的异常：

1. 添加唯一约束 #

ALTER TABLE newReview ADD CONSTRAINT uk_review_unique UNIQUE (RestaurantName, UserName, RatingDate);

2. 修改存储过程 #

CREATE OR REPLACE PROCEDURE AddNewReview(
  RESTAURANTNAME IN VARCHAR2 (100)
, USERNAME IN VARCHAR2 (100)
, Stars IN Int
, RATINGDATE IN DATE 
) AS 
BEGIN
  INSERT INTO newReview (RestaurantName, UserName, Stars, RatingDate)
  VALUES (RESTAURANTNAME, USERNAME, Stars, RATINGDATE);
  DBMS_OUTPUT.PUT_LINE('评论插入成功');
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    DBMS_OUTPUT.PUT_LINE('该评论已存在，未执行插入');
END AddNewReview;
/

这种方案能避免高并发场景下的竞态问题，从数据库层面保证数据不会重复。

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