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

CREATE OR REPLACE PROCEDURE shanglifeecif.customertotaltemp() IS
DECLARE
BEGIN
		 DELETE FROM shanglifeecif.customertotaltemp;
		insert into shanglifeecif.customertotaltemp(
			scustid,
		 	name,
		 	gender,
		 	birthday,
		 	idcard,
		 	idtype
		)
		SELECT 
			scustid ,
			max(name) as name,
			max(gender) as gender ,
			to_char(max(birthday),"yyyy-MM-dd")  birthday,
			max(idcard) as idcard,
			max(idtype) AS idtype
		FROM (
			SELECT
				trim(customerno) AS scustid,--投保人
				trim(name) AS name,
				trim(sex) AS gender,
				trim(birthday) AS  birthday,
				trim(idtype) AS idtype , 
				trim(idno) AS idcard
			FROM
				dsj.policy_information 
			WHERE customerno IS NOT NULL
			UNION 
			SELECT 
				trim(insuredno) AS scustid,--被保人
				trim(insuredname) AS name,
				trim(insuredsex) AS gender,
				trim(insuredbirthday) AS  birthday,
				trim(insuredidtype) AS idtype , 
				trim(insuredidno) AS idcard
			FROM
				dsj.policy_information
			WHERE insuredno IS NOT NULL
			) tmpTable GROUP BY scustid;
			
		UPDATE shanglifeecif.customertotaltemp sct SET sct.mobile = (SELECT trim(t.MOBILE) FROM dsj.t_customer_class t WHERE sct.scustid=t.customer_id);
	dbms_output.put_line('customertotaltemp函数跑批完成！');
	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	;