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

CREATE OR REPLACE PROCEDURE shanglifeecif.partytimeline_zx() 
IS
DECLARE
	p_count int
BEGIN
	
	 
	  insert into shanglifeecif.partytimeline (
		TripID,
		PolicyNo,
		PartyID,
		name,
		PRole,
		Scenario,
		SDate,
		DESCRIBE,
		created_by ,
		created_time 
	) SELECT 
				reflect("java.util.UUID", "randomUUID"),
		   		m.contnos,
		   		max(i.customerno) customerno,
		   		max(m.econtactsName) econtactsName,
		   		'投保人' AS tbr,
		   		'咨询' AS zx,
		   		m.starttime,
		   		max(m.reasonsecondname) reasonsecondname,
		   		'admin',
				sysdate()
		from dsj.cc_record_main  m
		LEFT JOIN dsj.policy_information i ON m.contnos = i.contno
	WHERE m.contnos IS NOT NULL  GROUP BY m.contnos,m.starttime;
	dbms_output.put_line('partytimeline_zx函数跑批完成！');
	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;