SQL & PL/SQL

2Hi,

I have source table called  XXRM_RISKS. And I need to insert data from  XXRM_RISKS table to XXRM_RISK_DIM for all new records. as shown below as an example.

  TABLE NAME =XXRM_RISKS

I'm trying to write stored procedure to load data from above table. surrogate key is unique identifier and it is concatenation of from dt and risk id.  to_dt is end date for each risk id. and from_dt is same as created_dt.

TABLE NAME =XXRM_RISK_DIM

If any risk id is updated in  XXRM_RISKS as shown below

then target table data should be update like shown below.

below is the incomplete stored procedure and i'm still working on it. I need help for the update statement on target table. appreciate you for your help.

CREATE OR REPLACE PROCEDURE SP_RISK
AS
BEGIN
INSERT INTO XXRM_RISK_DIM
SELECT TO_NUMBER( R.RISK_ID
  || TO_CHAR(R.CREATION_DATE,'DDMMYYYY')) SURROGATE_KEY,
  R.RISK_ID,
  R.ACI_IMPACT,
  R.ACI_CRITERIA,
  R.CREATION_DATE FROM_DT,
  '31-DEC-9999' AS TO_DT,
  1 VERSION
FROM XXRM_RISKS R

WHERE  NOT EXISTS ( SELECT D.SURROGATE_KEY,
  D.RISK_ID,
  D.ACI_IMPACT,
  D.ACI_CRITERIA,
  D.FROM_DT,
  D.TO_DT,
D.VERSION
FROM XXRM_RISK_DIM D)
END;
COMMIT;

BEGIN

For i in (SELECT R.RISK_ID, R.LAST_UPDATE_DATE

FROM XXRM_RISKS R,

  XXRM_RISK_DIM D

WHERE R.RISK_ID         =D.RISK_ID

AND TO_CHAR(R.LAST_UPDATE_DATE,'DD-MON-YYYY')= TO_CHAR(SYSDATE,'DD-MON-YYYY')

AND R.LAST_UPDATE_DATE IS NOT NULL)

LOOP

Update XXRM_RISK_DIM C set TO_DT = i.LAST_UPDATE_DATE where C.RISK_ID = i.RISK_ID;

END LOOP;

END;

)
END;
COMMIT;