Posted on 2010-01-31 17:48
斷點(diǎn) 閱讀(233)
評(píng)論(0) 編輯 收藏 所屬分類(lèi):
Oracle DBA
需求：對(duì)WEB_CUS_CLENT機(jī)構(gòu)為空的進(jìn)行修改，通過(guò)WEB_CUS_CLENT客戶(hù)編碼查找投保人WEB_PLY_APPLICANT的申請(qǐng)單號(hào)，通過(guò)申請(qǐng)單號(hào)查找web_PLY_BASE查找承保機(jī)構(gòu)。
CREATE OR REPLACE PROCEDURE V6.P_WEB_CUS_CLINT_DPT
IS
--增量抽取客戶(hù)信息數(shù)據(jù)
v_task_start_date date ;
v_task_end_date date ;
v_sql_code number :=0 ;
v_sql_msg VARCHAR2(4000) := '' ; --sql錯(cuò)誤信息
V_Cus_Client Web_Cus_Client%rowtype ;
V_UPD_TM date;
V_APP_NO varchar2(50);
V_DPT_CDE varchar2(50);
V_COUNT number(4,0);
cursor CUR_WEB_CUS_ADD is
select *
from Web_Cus_Client
a where a.C_DPT_CDE is null;
BEGIN
SELECT SYSDATE INTO v_task_start_date FROM dual; --任務(wù)開(kāi)始時(shí)間和任務(wù)結(jié)束時(shí)間
SELECT SYSDATE INTO v_task_end_date FROM dual;
v_sql_msg := '對(duì)WEB_CUS_CLENT機(jī)構(gòu)為空的進(jìn)行修改';
open CUR_WEB_CUS_ADD;
loop
fetch CUR_WEB_CUS_ADD into V_Cus_Client;
exit when CUR_WEB_CUS_ADD% notfound;
v_sql_msg := V_Cus_Client.c_Clnt_Cde||'對(duì)WEB_CUS_CLENT機(jī)構(gòu)為空的進(jìn)行修改';
V_COUNT :=0;
select count(1) into V_COUNT from WEB_PLY_APPLICANT a where a.C_APP_CDE=V_Cus_Client.c_Clnt_Cde;
if(V_COUNT>0) then
select max(T_CRT_TM) into V_UPD_TM from WEB_PLY_APPLICANT a where a.C_APP_CDE=V_Cus_Client.c_Clnt_Cde;
select max(C_APP_NO) into V_APP_NO from WEB_PLY_APPLICANT a where a.T_CRT_TM=V_UPD_TM and a.C_APP_CDE=V_Cus_Client.c_Clnt_Cde;
select C_DPT_CDE into V_DPT_CDE from web_PLY_BASE a where a.C_APP_NO=V_APP_NO;
update WEB_CUS_CLIENT a set a.C_DPT_CDE=V_DPT_CDE where a.C_CLNT_CDE=V_Cus_Client.c_Clnt_Cde;
end if;
commit;
end loop;
close CUR_WEB_CUS_ADD;
--寫(xiě)任務(wù)日志
v_sql_code :=0;
v_sql_msg := 'NORMAL, SUCCESSFUL COMPLETION';
SELECT SYSDATE INTO v_task_end_date FROM dual;
INSERT INTO LOAD_HIS_LOG
( SYS
,JOBNAME
,START_DATE
,END_DATE
,RUN_DATE
,SQL_CODE
,SQL_STATE
)
VALUES
('V5_MID'
,'P_WEB_CUS_CLINT_DPT'
,v_task_start_date
,v_task_end_date
,to_char((v_task_end_date - v_task_start_date) * 86400)
,v_sql_code
,v_sql_msg
);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
v_sql_code := SQLCODE;
v_sql_msg := v_sql_msg || ' ' || ' : ' || SQLERRM;
SELECT SYSDATE INTO v_task_end_date FROM dual; --任務(wù)結(jié)束時(shí)間
ROLLBACK;
INSERT INTO LOAD_HIS_LOG
( SYS
,JOBNAME
,START_DATE
,END_DATE
,RUN_DATE
,SQL_CODE
,SQL_STATE
)
VALUES
('V5_MID'
,'P_WEB_CUS_CLINT_DPT'
,v_task_start_date
,v_task_end_date
,to_char((v_task_end_date - v_task_start_date) * 86400)
,v_sql_code
,v_sql_msg
);
COMMIT;
END ;