CREATE OR REPLACE PROCEDURE shanglifeecif.update_insuredinfo() 
IS    
BEGIN
		--更新被保人信息
		   UPDATE shanglifeecif.individual a SET (
			ZIPCODE,--邮编
			HPhone,--电话
			PMPhone,--手机
			email, -- 邮箱  
			RAL,    --联系地址
			sobirth --省份
		) = (
			SELECT 
				t.zipcode,
				t.phone,
				t.mobile,
				t.email,
				regexp_replace(trim(t.address),'[0-9]','*') address,
				regexp_extract(t.address,'(.*?)省|(.*?)市',0) province
			FROM (
				SELECT
				row_number()OVER(PARTITION BY ls.customerno ORDER BY ls.modifydate desc) rn,
				ls.customerno,--客户号
				CASE 
					WHEN ls.postaladdress IS NULL 
						THEN 
							CASE WHEN ls.homeaddress IS NULL THEN ls.companyaddress ELSE ls.homeaddress END
						ELSE ls.postaladdress
					END AS address,	
				ls.phone,
				ls.zipcode,--邮编
				CASE WHEN ls.email IS NULL THEN ls.email2 ELSE ls.email END AS email,
				CASE WHEN ls.mobile IS NULL THEN ls.mobile2 ELSE ls.mobile END AS mobile
				FROM dsj.lcaddress ls
			) t where t.rn = 1 AND t.customerno = a.scustid
		) WHERE a.custtype = '被保人';

	UPDATE shanglifeecif.individual a SET (
			Height,		--身高
			Weight,		--体重
			BMI,
			PIncome,	--个人年收入
			Ethnic,  --民族情况
			Nation, --国籍
			MaritalStat, --婚姻
			Education,--学历
			SIStatus,--社保
			IncomeSource,--收入来源
			Occupation,--职业
			regtype  --户籍类型
		) = (
			SELECT 
				t.stature,--身高
				t.avoirdupois,--体重
				t.bmi,
				t.yearincome,
				t.nationality,--民族
				t.nativeplace,--国籍
				t.marriage, --婚姻状况
				t.degree,   --学历
				t.socialinsuflag,--社保
				t.incomesource,--收入来源
				t.occupationcode,--职业
				t.dentype
			FROM (
				SELECT
				row_number()OVER(PARTITION BY ld.insuredno ORDER BY ld.modifydate desc) rn,
				ld.insuredno,--被保人客户号
				ld.appntno,--投保人客户号
				ld.name,--被保人名称
				ld.sex,--被保人性别
				ld.birthday,--被保人出生日期
				ld.idtype,--证件类型
				ld.idno,--证件号码
				ld.marriage,--婚姻状况
				ld.occupationcode,--职业
				ld.nativeplace,--国籍
				ld.nationality,--民族
				ld.stature,--身高
				ld.avoirdupois,--体重
				round((ld.avoirdupois/POWER((ld.stature/100),2)),2) bmi,
				ld.degree,--学历
				ld.dentype,--居民类型 
				ld.socialinsuflag,--社保情况
				ld.incomesource,--收入来源
				ld.yearincome --年收入 
			FROM dsj.lcinsured2 ld
			) t WHERE t.rn = 1 AND t.insuredno = a.scustid
		) WHERE a.custtype = '被保人';
		dbms_output.put_line('update_insuredinfo函数跑批完成！');
		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;