日志表结构
-- Create table
create table AUDITFLOW_LOG
(
PROCEDURE_NAME VARCHAR2(100),
STARTTIME DATE,
ENDTIME DATE,
STATUS VARCHAR2(10) not null,
MESSAGE VARCHAR2(200),
PROCEDURE_ID VARCHAR2(20),
SQLCOUNT NUMBER
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64
next 8
minextents 1
maxextents unlimited
);
-- Add comments to the columns
comment on column AUDITFLOW_LOG.PROCEDURE_NAME
is '存储名称';
comment on column AUDITFLOW_LOG.STARTTIME
is '开始时间';
comment on column AUDITFLOW_LOG.ENDTIME
is '结束时间';
comment on column AUDITFLOW_LOG.STATUS
is '状态 1开始,2结束,3异常';
comment on column AUDITFLOW_LOG.MESSAGE
is '详细日志,异常信息';
comment on column AUDITFLOW_LOG.PROCEDURE_ID
is '存储id';
comment on column AUDITFLOW_LOG.SQLCOUNT
is '数据变化量';
--存储开始
CREATE OR REPLACE PROCEDURE PRO_CD_CURRENCYEXCH AS
A DATE;
--B DATE;
C VARCHAR2(5);
D DATE;
ERRORCODE VARCHAR2(100); --异常码
ERRORMSG VARCHAR2(200); --异常信息
sqlcount number;--变化条数
E NUMBER;
BEGIN
E := 0;
--删除日志表 ,内容:删除相同的批次OPR_ID的信息
DELETE AUDITFLOW_LOG A WHERE A.PROCEDURE_NAME = 'PRO_CD_CURRENCYEXCH' ;
commit;
--插入日志表 ,内容:插入SP_T_ALL整体存储过程的信息
INSERT INTO AUDITFLOW_LOG (PROCEDURE_NAME, --存储过程名称
STARTTIME, --开始时间
ENDTIME, --结束时间
STATUS, --状态编码 未开始:1 进行中:2 已完成:3
MESSAGE, --详细日志
PROCEDURE_ID,--ID
SQLCOUNT
)
VALUES ('PRO_CD_CURRENCYEXCH',
SYSDATE,
SYSDATE,
'1', ----提取状态,1表示开始,2表示完成,3表示异常
'正常',
'01',
'0');
SELECT MIN(EXCHDATE) INTO A FROM CD_CURRENCYEXCH;
SELECT MAX(EXCHDATE) INTO D FROM CD_CURRENCYEXCH;
--B := to_date(to_char(A+1,'yyyy-mm-dd'),'yyyy-mm-dd');
LOOP
A := TO_DATE(TO_CHAR(A+1,'YYYY,MM,DD'),'YYYY-MM-DD');
--DBMS_OUTPUT.PUT_LINE(B);
SELECT COUNT(1)
INTO C
FROM CD_CURRENCYEXCH
WHERE TRUNC(EXCHDATE, 'DD') = TRUNC(A, 'DD');
IF C = 0 THEN
--EXCUTE IMMEDIATE'
INSERT INTO CD_CURRENCYEXCH
(EXCHDATE,
BASECURRENCY,
EXCHCURRENCY,
EXCHRATE,
VALIDSTATUS,
INVALIDDATE,
FLAG,
MODIFYDATE)
SELECT EXCHDATE+1,
BASECURRENCY,
EXCHCURRENCY,
EXCHRATE,
VALIDSTATUS,
INVALIDDATE+1,
FLAG,
MODIFYDATE+1
FROM CD_CURRENCYEXCH
WHERE TRUNC(EXCHDATE,'DD') = TRUNC(A, 'DD')-1 ;
SQLCOUNT := SQL%ROWCOUNT;
ELSE
SQLCOUNT := 0;
END IF;
COMMIT;
--B := B+1;
E := E + SQLCOUNT;
EXIT WHEN A = trunc(SYSDATE-1,'DD');
END LOOP;
--更新插入所有存储日志
UPDATE AUDITFLOW_LOG
SET (ENDTIME,SQLCOUNT,STATUS,MESSAGE) = (SELECT SYSDATE,E,'2','完成' FROM DUAL)
WHERE PROCEDURE_NAME = 'PRO_CD_CURRENCYEXCH';
COMMIT;
--更新日志表,内容:更新存储过程中异常信息
EXCEPTION
WHEN OTHERS THEN
ERRORCODE := SQLCODE;
ERRORMSG := SUBSTR(SQLERRM, 1, 200);
DBMS_OUTPUT.PUT_LINE(SQLCODE || '----' || ERRORMSG);
UPDATE AUDITFLOW_LOG
SET STATUS = '3',
MESSAGE = ERRORCODE || '----' || ERRORMSG,
ENDTIME = SYSDATE
WHERE PROCEDURE_NAME = 'PRO_CD_CURRENCYEXCH';
COMMIT;
END PRO_CD_CURRENCYEXCH;