--(1)存储过程准备
---(1.1) 创建报错表结构
---(1.2)pkg 创建
CREATE OR REPLACE PACKAGE pkg_error_log AS
DFT_PK1 CHAR(32) := 'XXXX';
DFT_PK2 CHAR(32) := 'XXXX';
DFT_PK3 CHAR(32) := 'XXXX';
DFT_DESC VARCHAR(8):= 'XXXX';
DFT_TIME NUMBER(8):= TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD'));
PROCEDURE pro_error_in(p_pname IN CHAR,p_step IN NUMBER,p_sqlcode IN NUMBER,
p_desc IN VARCHAR := DFT_DESC,p_pk1 IN CHAR := DFT_PK1,
p_pk2 IN CHAR := DFT_PK2,p_pk3 IN CHAR := DFT_PK3);
END pkg_error_log;
/
CREATE OR REPLACE PACKAGE BODY pkg_error_log AS
/***错误记录程序***/
PROCEDURE pro_error_in(p_pname IN CHAR,p_step IN NUMBER,p_sqlcode IN NUMBER,
p_desc IN VARCHAR := DFT_DESC,p_pk1 IN CHAR := DFT_PK1,
p_pk2 IN CHAR := DFT_PK2,p_pk3 IN CHAR := DFT_PK3) IS
BEGIN
INSERT INTO utl_error_log
VALUES(NVL(p_pname,'pro_null'),p_desc,NVL(p_pk1,'NULL'),p_pk2,p_pk3,
NVL(p_step,9999),NVL(p_sqlcode,9999),sysdate);
COMMIT;
END;
END pkg_error_log;
/
--(1.3)squence创建例子(只做参考)
-- Create sequence
create sequence SEQ_1001
minvalue 1
maxvalue 9999999999
start with 1481
increment by 1
cache 20
cycle;
--(1.4)FUNCTION 创建例子(只做参考)
CREATE OR REPLACE FUNCTION FUNC_GENERATE_LSH(sCZLX IN VARCHAR2) RETURN VARCHAR2 IS
sYEAR CHAR(2) := TO_CHAR(SYSDATE,'YY');
sSEQ CHAR(10) := '';
sDqsj CHAR(6) := TO_CHAR(SYSDATE,'YYYYMM');
BEGIN
IF sCZLX = '1001' THEN SELECT TRIM(TO_CHAR(SEQ_1001.NEXTVAL,'0000000000')) INTO sSEQ FROM DUAL;
ELSIF sCZLX = '1049' THEN SELECT TRIM(TO_CHAR(SEQ_1049.NEXTVAL,'0000000000')) INTO sSEQ FROM DUAL;
ELSE SELECT 'EEEEEEEEEEEEEEEE' INTO sSEQ FROM DUAL;
END IF;
IF (sSEQ='EEEEEEEEEEEEEEEE') THEN
RETURN sSEQ;
END IF;
--返回流水号
RETURN (sYEAR || sCZLX || sSEQ);
EXCEPTION
WHEN OTHERS THEN return 'EEEEEEEEEEEEEEEE';
END FUNC_GENERATE_LSH;
--(2) 存储过程
存过中用到的FUNCTION 和squence自行创建(此例子只做参考)
CREATE OR REPLACE PROCEDURE PROC_SJTB_QX AS
i_step NUMBER(9) := 0;
p_RtnCode NUMBER(9) := 0;
p_RtnMsg VARCHAR2(100):='成功';
n_Pch VARCHAR2(16);
s_UploadPath VARCHAR2(50);
BEGIN
--初始化数据
p_RtnCode := 0;
p_RtnMsg := '数据同步成功';
s_UploadPath:='Z:\swoa\uploadfiles\'; --
--测试路径:\\10.2.6.11\webapps\swoa\uploadfiles\ 或 Z:\swoa\uploadfiles\
--(1)正式路径:Z:\bea\user_projects\domains\mydomain\ybswoa\uploadfiles
--(1)获取PCH
i_step := 1;
BEGIN
SELECT FUNC_GENERATE_LSH('4001')
INTO n_Pch
FROM DUAL;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_RtnCode := 1;
p_RtnMsg := 'PCH获取失败';
RETURN;
END;
--(2)TB_SJTB_CZJL 数据同步操作轨迹表
i_step := 2;
INSERT INTO TB_SJTB_CZJL(
Pch, TBRQ, KSSJ, JSSJ)
…………………………………………………………………………………………………………………………………………………………
业务处理
…………………………………………………………………………………………………………………………………………………………
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
p_RtnCode := SQLCODE;
p_RtnMsg := SQLERRM;
Pkg_Error_Log.Pro_Error_In('PROC_SJTB_QX', i_step, p_RtnCode, p_RtnMsg);
END PROC_SJTB_QX;
/
---(3)定时任务处理
在oracle命令窗口,执行这段语句。
---(1)方式1 sys.dbms_job(每隔2小时执行一次)
variable job number;
begin
sys.dbms_job.submit(job => :job,
what => 'PROC_SJTB_QX;',
next_date => to_date('25-01-2018 01:00:00', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'sysdate+2/24');
commit;
end;
/
---(2)方式2 dbms_scheduler(每天的8,10,12,14,16,18,20,22,0执行)
begin
dbms_scheduler.create_job(
job_name => 'scheduler_sjtb',
job_type => 'STORED_PROCEDURE',
job_action => 'PROC_SJTB_QX',
repeat_interval =>'FREQ=DAILY; BYHOUR=8,10,12,14,16,18,20,22,0',
enabled => true,
auto_drop => false,
comments => '数据同步调度作业');
commit;