Posted on 2010-01-31 17:48
断点 阅读(226)
评论(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 ;