Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE t_ap_recovery_target (
  a_recovery_target_month, 
  a_target_instance,
  a_focus_area_id,
  a_range_id,
  A_IS_RECOVERED
) AS
SELECT '1', 1, 1, 1, 'Y' FROM DUAL UNION ALL
SELECT '1', 1, 1, 1, 'N' FROM DUAL UNION ALL
SELECT '1', 2, 1, 1, 'N' FROM DUAL UNION ALL
SELECT '1', 2, 1, 1, 'N' FROM DUAL;
4 rows affected

CREATE TABLE t_ap_recovery_focusarea (
  id, 
  focus_area
) AS
SELECT 1, 1 FROM DUAL;
1 rows affected

CREATE TABLE range(
  id,
  identifier_class
) AS
SELECT 1, 1 FROM DUAL;
1 rows affected

CREATE PROCEDURE ADD_LOG_INFO(
  x IN VARCHAR2,
  y IN VARCHAR2
)
AS 
BEGIN
  NULL;
END;
/
CREATE SEQUENCE SQ_AP_RECOVERY_HISTORY;
CREATE TABLE t_ap_recovery_history (
    ID NUMBER,
    RECOVERY_TARGET_MONTH VARCHAR2(50),
    TARGET_INSTANCE NUMBER,
    RECOVERY_PROGRESS NUMBER,
    RECOVERY_TARGET NUMBER,
    FAILED_TO_RECOVERY NUMBER,
    FOCUS_AREA NUMBER,
    IDENTIFIER_CLASS NUMBER,
    CREATED_ON DATE
  );
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;
/
SELECT * FROM USER_ERRORS;
BEGIN
  CALCULATE_RECOVERY_HISTORY('1');
END;
/
1 rows affected

SELECT * FROM t_ap_recovery_history;


ID
RECOVERY_TARGET_MONTH
TARGET_INSTANCE
RECOVERY_PROGRESS
RECOVERY_TARGET
FAILED_TO_RECOVERY
FOCUS_AREA
IDENTIFIER_CLASS
CREATED_ON


1
1
1
1
2
1
1
1
2023-02-08 09:51:59

2
1
2
0
2
2
1
1
2023-02-08 09:51:59