含可空复合唯一键的表数据插入与ID返回问题咨询

核心问题是SQL中NULL的比较逻辑：SQL标准里，NULL = NULL的结果是UNKNOWN而非TRUE，所以原查询条件无法匹配到包含NULL的复合键记录，导致误判为不存在；但插入时数据库会根据唯一约束的规则（NULL视为相等）检测到冲突，从而抛出错误。

以下提供两种可靠的解决方式，优先推荐第二种（原子性操作，避免并发问题）：

方式1：修正查询逻辑+捕获唯一约束异常 #

调整查询条件，手动处理每个字段的NULL匹配；同时捕获插入时的唯一约束冲突异常，查询并返回已存在的ID。

存储过程代码 #

CREATE OR REPLACE PROCEDURE save_demo(
    p_demo_a IN NUMBER,
    p_demo_b IN NUMBER,
    p_demo_c IN NUMBER,
    p_demo_id OUT NUMBER
) AS
BEGIN
    -- 修正查询条件：处理NULL的匹配逻辑
    SELECT demo_id
    INTO p_demo_id
    FROM demo
    WHERE (demo_a = p_demo_a OR (demo_a IS NULL AND p_demo_a IS NULL))
      AND (demo_b = p_demo_b OR (demo_b IS NULL AND p_demo_b IS NULL))
      AND (demo_c = p_demo_c OR (demo_c IS NULL AND p_demo_c IS NULL));
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- 尝试插入新记录（需提前创建生成主键的序列demo_seq）
        INSERT INTO demo (demo_id, demo_a, demo_b, demo_c)
        VALUES (demo_seq.NEXTVAL, p_demo_a, p_demo_b, p_demo_c)
        RETURNING demo_id INTO p_demo_id;
    WHEN DUP_VAL_ON_INDEX THEN
        -- 插入冲突时，再次查询获取已存在的ID
        SELECT demo_id
        INTO p_demo_id
        FROM demo
        WHERE (demo_a = p_demo_a OR (demo_a IS NULL AND p_demo_a IS NULL))
          AND (demo_b = p_demo_b OR (demo_b IS NULL AND p_demo_b IS NULL))
          AND (demo_c = p_demo_c OR (demo_c IS NULL AND p_demo_c IS NULL));
END;
/

注：序列创建语句：CREATE SEQUENCE demo_seq START WITH 1 INCREMENT BY 1;

方式2：使用MERGE语句（推荐） #

MERGE是原子性操作，可在同一语句中完成“匹配则获取ID，不匹配则插入”，避免两次操作的并发问题，代码更简洁。

存储过程代码 #

CREATE OR REPLACE PROCEDURE save_demo(
    p_demo_a IN NUMBER,
    p_demo_b IN NUMBER,
    p_demo_c IN NUMBER,
    p_demo_id OUT NUMBER
) AS
BEGIN
    MERGE INTO demo d
    USING (SELECT p_demo_a AS a, p_demo_b AS b, p_demo_c AS c FROM dual) src
    ON (
        (d.demo_a = src.a OR (d.demo_a IS NULL AND src.a IS NULL))
        AND (d.demo_b = src.b OR (d.demo_b IS NULL AND src.b IS NULL))
        AND (d.demo_c = src.c OR (d.demo_c IS NULL AND src.c IS NULL))
    )
    WHEN MATCHED THEN
        -- 匹配时，获取已存在的ID
        UPDATE SET p_demo_id = d.demo_id
    WHEN NOT MATCHED THEN
        -- 不匹配时，插入新记录并返回ID
        INSERT (demo_id, demo_a, demo_b, demo_c)
        VALUES (demo_seq.NEXTVAL, src.a, src.b, src.c)
        RETURNING demo_id INTO p_demo_id;
END;
/

注：Oracle 12cR1+版本可使用IS NOT DISTINCT FROM简化匹配条件，将ON子句中的每个字段比较替换为d.demo_a IS NOT DISTINCT FROM src.a，逻辑等价且更简洁。

额外说明 #

• 确保表已正确创建复合唯一约束：

  ALTER TABLE demo ADD CONSTRAINT uk_demo_abc UNIQUE (demo_a, demo_b, demo_c);
  

• 代理主键依赖序列自动生成，避免手动赋值引发的冲突问题。

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