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

CREATE OR REPLACE PROCEDURE shanglifeecif.surrender_protection_temp() IS
DECLARE
BEGIN
		 DELETE FROM shanglifeecif.surrenderprotectiontemp;
		 insert into shanglifeecif.surrenderprotectiontemp(
			tbid,
		 	scustid,
		 	tbday
		)SELECT row_number()over(),p.customerno,max(DATEDIFF(t.edorappdate,p.cvalidate)) AS tbday FROM dsj.lpedoritem t,dsj.policy_information p 
	WHERE t.contno = p.contno and t.edortype='CT' AND edorstate = 0 GROUP BY p.customerno;
	dbms_output.put_line('surrender_protection_temp函数跑批完成！');	
	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	;