在子查询中执行聚合，然后在外部查询中应用序列值：

CREATE 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,
         a_recovery_target_month, 
         a_target_instance, 
         RECOVERY_PROGRESS,
         RECOVERY_TARGET, 
         FAILED_TO_RECOVERY,
         focus_area,
         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
           INNER JOIN t_ap_recovery_focusarea f
           ON (t.a_focus_area_id = f.id)
           INNER JOIN range r
           ON (t.a_range_id = r.id)
    WHERE  t.a_recovery_target_month = p_month
    GROUP BY
           a_target_instance, 
           a_recovery_target_month,
           f.focus_area,
           r.identifier_class
  );
END CALCULATE_RECOVERY_HISTORY;
/

• 注意：如果在存储过程中使用COMMIT，则不能将多个过程链接在一起，如果一个过程失败，则使用ROLLBACK，然后使用所有过程。相反，应该在从中调用过程的块中使用COMMIT。*

fiddle

一个选项是让Oracle创建ID。您没有指定使用的数据库版本，因此触发器肯定是什么工作：

create or replace trigger trg_bi_rec_hist
  before insert on t_ap_recovery_history
  for each row
begin
  :new.id := SQ_AP_RECOVERY_HISTORY.NEXTVAL;
end;
/

这样，过程就不会包含ID列的插入，即

INSERT INTO t_ap_recovery_history (RECOVERY_TARGET_MONTH, ...)
SELECT a_recovery_target_month, ...

另一个选项（如果您的数据库版本支持）是创建ID作为标识列，而不是触发器，例如

SQL> create table test
  2    (id number generated always as identity);

Table created.

或者，如果您不想像上一个答案中那样创建触发器，则过程应如下所示：

create or replace PROCEDURE CALCULATE_RECOVERY_HISTORY(p_month IN VARCHAR2) AS 
l_id NUMBER; 
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)
      with tb as ( 
      SELECT  a_recovery_target_month, 
           a_target_instance, 
           COUNT(CASE WHEN A_IS_RECOVERED = 'Y' THEN 1 END) c1, 
           COUNT(CASE WHEN A_IS_RECOVERED IN ('Y', 'N') THEN 1 END) c2, 
           COUNT(CASE WHEN A_IS_RECOVERED = 'N' THEN 1 END) c3,
           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
      )
      select SQ_AP_RECOVERY_HISTORY.NEXTVAL,
           a_recovery_target_month, 
           a_target_instance, 
           c1, 
           c2, 
           c3,
           focus_area,
           identifier_class,
           sysdate 
      from tb;
    
      COMMIT;
END CALCULATE_RECOVERY_HISTORY;

一般来说，如果使用insert select一次插入大量行，或者使用大规模更新、大规模删除或合并，触发器会对性能造成损害。如果仅使用DML影响少量行，触发器可能会保存复杂性，尽管我宁愿多使用存储过程，少使用触发器。