Oracle 中 INSERT 重复主键的更新？

Question

我有一个简单的 INSERT 查询，当主键重复时，我需要使用 UPDATE 来代替。在 MySQL 中这似乎更容易，在 Oracle 中似乎我需要使用 MERGE。

我能找到的 MERGE 的所有示例都有某种“源”和“目标”表，在我的例子中，源和目标是同一个表。我无法理解这些示例来创建我自己的查询。

MERGE 是唯一的方法还是也许有更好的解决方案？

INSERT INTO movie_ratings
VALUES (1, 3, 5)

基本上就是这样，主键是前 2 个值，所以更新会是这样的：

UPDATE movie_ratings
SET rating = 8
WHERE mid = 1 AND aid = 3

我想到使用一个触发器，它会在调用 INSERT 时自动执行 UPDATE 语句，但前提是主键是重复的。这样做有什么问题吗？我需要一些有关触发器的帮助，因为我在尝试理解它们并自己做事时遇到一些困难。

Answer 1

MERGE 是标准 SQL 中的“根据需要执行 INSERT 或 UPDATE”语句，因此在 Oracle SQL 中也可能如此。

是的，您需要一个“表”来合并，但您几乎肯定可以动态创建该表：（

 MERGE INTO Movie_Ratings M
       USING (SELECT 1 AS mid, 3 AS aid, 8 AS rating FROM dual) N
          ON (M.mid = N.mid AND M.aid = N.aid)
       WHEN     MATCHED THEN UPDATE SET M.rating = N.rating
       WHEN NOT MATCHED THEN INSERT(  mid,   aid,   rating)
                             VALUES(N.mid, N.aid, N.rating);

语法未验证。）

Answer 2

执行此操作的典型方法是

• 执行 INSERT 并捕获 DUP_VAL_ON_INDEX，然后执行 UPDATE，而不是
• 先执行 UPDATE，如果 SQL%Rows = 0 则执行 INSERT

您不能在表上编写触发器来对表执行其他操作同一张桌子。这会导致 Oracle 错误（表变化）。

Answer 3

我是一个 T-SQL 专家，但在这种情况下触发器并不是一个好的解决方案。大多数触发器都不是好的解决方案。在 T-SQL 中，我只需执行 IF EXISTS (SELECT * FROM dbo.Table WHERE ...)，但在 Oracle 中，您必须选择计数...

DECLARE 
  cnt NUMBER;
BEGIN
  SELECT COUNT(*)
   INTO cnt
    FROM mytable
  WHERE id = 12345;

  IF( cnt = 0 )
  THEN
    ...
  ELSE
    ...
  END IF;
END;

在这种情况下，您需要 MERGE：

MERGE INTO movie_ratings mr
USING (
  SELECT rating, mid, aid
  WHERE mid = 1 AND aid = 3) mri
ON (mr.movie_ratings_id = mri.movie_ratings_id)

WHEN MATCHED THEN
  UPDATE SET mr.rating = 8 WHERE mr.mid = 1 AND mr.aid = 3

WHEN NOT MATCHED THEN
  INSERT (mr.rating, mr.mid, mr.aid)
  VALUES (1, 3, 8) 

就像我说的，我是一个 T-SQL 人员，但这里的基本思想是“加入” movie_ rating 表本身。如果使用“如果存在”示例不会影响性能，我会使用它来提高可读性。