CREATE OR REPLACE PROCEDURE shanglifeecif.up_other_customerinfo() -- 创建存储过程
IS    
BEGIN
	--更新客户类型投保人
	  UPDATE shanglifeecif.individual i SET i.custtype = '投保人' WHERE 
	i.scustid IN (SELECT p.customerno FROM dsj.policy_information p);
	--更新客户类型投保人、被保人
	 UPDATE shanglifeecif.individual i SET i.custtype = '被保人' WHERE 
	i.scustid IN (SELECT pi.insuredno FROM dsj.policy_information pi);
	--更新客户类型投保人、被保人
	UPDATE shanglifeecif.individual i SET i.custtype = '投保人、被保人' WHERE 
	i.scustid IN (SELECT p.customerno FROM dsj.policy_information p)
	AND i.scustid IN (SELECT pi.insuredno FROM dsj.policy_information pi);
	
	--更新业绩归属
	UPDATE shanglifeecif.Individual si1 SET si1.policybelong = (
		SELECT trim(t.SALECOM) FROM (
			SELECT row_number()OVER(PARTITION BY p.customerno ORDER BY p.polapplydate asc) rd,p.customerno,p.SALECOM FROM dsj.policy_information p
		) t WHERE t.rd = 1 AND si1.scustid = t.customerno
	);
	
	UPDATE shanglifeecif.Individual si1 SET si1.policybelong = (
		SELECT trim(t.SALECOM) FROM (
			SELECT row_number()OVER(PARTITION BY p.insuredno ORDER BY p.polapplydate asc) rd,p.insuredno,p.SALECOM FROM dsj.policy_information p
		) t WHERE t.rd = 1 AND si1.scustid = t.insuredno
	);
	
	--更新职业，民族等信息
	UPDATE shanglifeecif.Individual si1 SET (si1.Occupation,si1.Ethnic,si1.education) = (
		SELECT trim(t.occupationname),trim(t.NATIONALITYNAME),trim(t.degree) FROM (
			SELECT row_number()OVER(PARTITION BY p.customerno ORDER BY p.polapplydate desc) rd,p.customerno,p.occupationname,p.NATIONALITYNAME,p.degree FROM dsj.policy_information p
		) t WHERE t.rd = 1 AND si1.scustid = t.customerno
	);
	
	--更新官微积分
	UPDATE shanglifeecif.individual si SET si.officialCalculus = (
	   SELECT temp.available_points FROM (SELECT t.available_points,t.certificate_no,row_number()OVER(PARTITION BY t.certificate_no ORDER BY t.gmt_created DESC) rd FROM dsj.t_account t) temp  WHERE temp.rd = 1 AND temp.certificate_no = si.idcard
	);
	dbms_output.put_line('up_other_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;