dgtis-modules-data/一期存储过程（最终版）/tb_customerinfo.sql

CREATE OR REPLACE PROCEDURE shanglifeecif.tb_customerinfo() IS
DECLARE
BEGIN
		DELETE FROM shanglifeecif.tbcustomerinfo;
		insert into shanglifeecif.tbcustomerinfo(
			tbid,
		 	scustid,
		 	contno,
		 	sdate,
		 	edate,
		 	salecom,
		 	salecomname
		)SELECT row_number()over(),p.customerno,p.contno,p.cvalidate,t.edorappdate ,p.salecom,p.salecomname FROM dsj.lpedoritem t,dsj.policy_information p 
	WHERE t.contno = p.contno and t.edortype='CT' AND t.edorstate = 0 ;
	dbms_output.put_line('tb_customerinfo函数跑批完成！');	
	EXCEPTION
			WHEN HIVE_EXCEPTION THEN 
				 INSERT INTO shanglifeecif.exception_log(log_id,log_code,log_msg,log_time) VALUES (to_char(sysdate(),'yyyyMMddHHmmss'),sqlcode(),sqlerrm(),sysdate());
			WHEN Others THEN
				INSERT INTO shanglifeecif.exception_log(log_id,log_code,log_msg,log_time) VALUES (to_char(sysdate(),'yyyyMMddHHmmss'),sqlcode(),sqlerrm(),sysdate());	

END	;