Oracle存储过程执行报错ORA-00001：唯一约束违反，求解决方案

我编写了如下Oracle存储过程：

create or replace PROCEDURE CALCULATE_RECOVERY_HISTORY(p_month IN VARCHAR2) AS 
l_id NUMBER; 
BEGIN
  ADD_LOG_INFO('CALCULATE_RECOVERY_HISTORY', 'Procedure Started');
  
  l_id := SQ_AP_RECOVERY_HISTORY.NEXTVAL;

    INSERT INTO t_ap_recovery_history (ID, RECOVERY_TARGET_MONTH, TARGET_INSTANCE, RECOVERY_PROGRESS, RECOVERY_TARGET, FAILED_TO_RECOVERY, FOCUS_AREA, IDENTIFIER_CLASS, CREATED_ON) 
    SELECT  l_id,
            a_recovery_target_month, 
            a_target_instance, 
            COUNT(CASE WHEN A_IS_RECOVERED = 'Y' THEN 1 END), 
            COUNT(CASE WHEN A_IS_RECOVERED IN ('Y', 'N') THEN 1 END), 
            COUNT(CASE WHEN A_IS_RECOVERED = 'N' THEN 1 END),
            f.focus_area,
            r.identifier_class,
            SYSDATE
            from t_ap_recovery_target t, t_ap_recovery_focusarea f, range r
    where t.a_focus_area_id = f.id and t.a_range_id = r.id
    and t.a_recovery_target_month = p_month
    group by a_target_instance, a_recovery_target_month, f.focus_area, r.identifier_class;
    
      COMMIT;
END CALCULATE_RECOVERY_HISTORY;

执行该存储过程时触发错误：

ORA-00001: unique constraint violated.

我尝试将代码修改为在SELECT子句中直接调用序列：

SELECT  SQ_AP_RECOVERY_HISTORY.NEXTVAL, a_recovery_target_month ... 

但又触发新错误：

Sequence number not allowed here

请问需要如何修改代码才能解决该唯一约束问题？

以下是T_AP_RECOVERY_HISTORY表的定义：

CREATE TABLE "DIMSPST"."T_AP_RECOVERY_HISTORY" 
(    "ID" NUMBER(38,0), 
    "RECOVERY_TARGET_MONTH" VARCHAR2(6 BYTE) DEFAULT TO_CHAR(SYSTIMESTAMP, 'YYYYMM'), 
    "TARGET_INSTANCE" VARCHAR2(20 BYTE), 
    "RECOVERY_PROGRESS" NUMBER(38,0), 
    "RECOVERY_TARGET" NUMBER(38,0), 
    "FAILED_TO_RECOVERY" NUMBER(38,0), 
    "FOCUS_AREA" VARCHAR2(20 BYTE), 
    "IDENTIFIER_CLASS" VARCHAR2(42 BYTE), 
    "CREATED_ON" TIMESTAMP (6), 
     PRIMARY KEY ("ID")
)

错误原因 #

1. ORA-00001错误：你只获取了一次序列值l_id，但INSERT...SELECT会插入多条分组后的记录，所有记录共用同一个l_id，违反了ID主键的唯一性约束。
2. 序列调用不允许的错误：Oracle语法规定，不能在包含GROUP BY的SELECT语句中直接调用序列的NEXTVAL。

可行修改方案 #

方案1：将分组查询嵌套，外层调用序列 #

把分组逻辑放到子查询中，外层查询调用序列生成唯一ID，这样避开了分组语句中调用序列的限制，同时每条插入记录都能获取新的序列值：

create or replace PROCEDURE CALCULATE_RECOVERY_HISTORY(p_month IN VARCHAR2) AS 
BEGIN
  ADD_LOG_INFO('CALCULATE_RECOVERY_HISTORY', 'Procedure Started');

    INSERT INTO t_ap_recovery_history (ID, RECOVERY_TARGET_MONTH, TARGET_INSTANCE, RECOVERY_PROGRESS, RECOVERY_TARGET, FAILED_TO_RECOVERY, FOCUS_AREA, IDENTIFIER_CLASS, CREATED_ON) 
    SELECT  SQ_AP_RECOVERY_HISTORY.NEXTVAL,
            data.a_recovery_target_month, 
            data.a_target_instance, 
            data.RECOVERY_PROGRESS, 
            data.RECOVERY_TARGET, 
            data.FAILED_TO_RECOVERY,
            data.focus_area,
            data.identifier_class,
            SYSDATE
    FROM (
        SELECT  a_recovery_target_month, 
                a_target_instance, 
                COUNT(CASE WHEN A_IS_RECOVERED = 'Y' THEN 1 END) AS RECOVERY_PROGRESS, 
                COUNT(CASE WHEN A_IS_RECOVERED IN ('Y', 'N') THEN 1 END) AS RECOVERY_TARGET, 
                COUNT(CASE WHEN A_IS_RECOVERED = 'N' THEN 1 END) AS FAILED_TO_RECOVERY,
                f.focus_area,
                r.identifier_class
        FROM t_ap_recovery_target t, t_ap_recovery_focusarea f, range r
        WHERE t.a_focus_area_id = f.id AND t.a_range_id = r.id
          AND t.a_recovery_target_month = p_month
        GROUP BY a_target_instance, a_recovery_target_month, f.focus_area, r.identifier_class
    ) data;
    
    COMMIT;
END CALCULATE_RECOVERY_HISTORY;

方案2：用触发器自动生成主键（推荐） #

创建行级触发器，在插入表时自动为ID字段赋值，存储过程无需手动处理ID逻辑，长期维护更简洁：

第一步：创建触发器

CREATE OR REPLACE TRIGGER TRG_T_AP_RECOVERY_HISTORY_ID
BEFORE INSERT ON T_AP_RECOVERY_HISTORY
FOR EACH ROW
BEGIN
  SELECT SQ_AP_RECOVERY_HISTORY.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
/

第二步：修改存储过程

去掉ID相关的变量和赋值，INSERT语句也不用指定ID字段：

create or replace PROCEDURE CALCULATE_RECOVERY_HISTORY(p_month IN VARCHAR2) AS 
BEGIN
  ADD_LOG_INFO('CALCULATE_RECOVERY_HISTORY', 'Procedure Started');

    INSERT INTO t_ap_recovery_history (RECOVERY_TARGET_MONTH, TARGET_INSTANCE, RECOVERY_PROGRESS, RECOVERY_TARGET, FAILED_TO_RECOVERY, FOCUS_AREA, IDENTIFIER_CLASS, CREATED_ON) 
    SELECT  a_recovery_target_month, 
            a_target_instance, 
            COUNT(CASE WHEN A_IS_RECOVERED = 'Y' THEN 1 END), 
            COUNT(CASE WHEN A_IS_RECOVERED IN ('Y', 'N') THEN 1 END), 
            COUNT(CASE WHEN A_IS_RECOVERED = 'N' THEN 1 END),
            f.focus_area,
            r.identifier_class,
            SYSDATE
            FROM t_ap_recovery_target t, t_ap_recovery_focusarea f, range r
    WHERE t.a_focus_area_id = f.id AND t.a_range_id = r.id
      AND t.a_recovery_target_month = p_month
    GROUP BY a_target_instance, a_recovery_target_month, f.focus_area, r.identifier_class;
    
    COMMIT;
END CALCULATE_RECOVERY_HISTORY;

方案选择建议 #

• 方案1适合快速临时修改，不需要额外创建数据库对象；
• 方案2更符合Oracle开发规范，将主键生成逻辑统一管理，避免后续代码重复出错，推荐长期使用。

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