Posted on 2010-01-31 17:48
斷點 閱讀(237)
評論(0) 編輯 收藏 所屬分類:
Oracle DBA
需求：對WEB_CUS_CLENT機構為空的進行修改，通過WEB_CUS_CLENT客戶編碼查找投保人WEB_PLY_APPLICANT的申請單號，通過申請單號查找web_PLY_BASE查找承保機構。
CREATE OR REPLACE PROCEDURE V6.P_WEB_CUS_CLINT_DPT
IS
--增量抽取客戶信息數據
v_task_start_date date ;
v_task_end_date date ;
v_sql_code number :=0 ;
v_sql_msg VARCHAR2(4000) := '' ; --sql錯誤信息
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; --任務開始時間和任務結束時間
SELECT SYSDATE INTO v_task_end_date FROM dual;
v_sql_msg := '對WEB_CUS_CLENT機構為空的進行修改';
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||'對WEB_CUS_CLENT機構為空的進行修改';
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;
--寫任務日志
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; --任務結束時間
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 ;