ShangHaiLifeECIF/sql-achievement/存储过程/洛阳测试环境使用sql整理/客户信息存储过程.sql

CREATE OR REPLACE PROCEDURE shanglifeecif.init_individual_main() -- 初始化 数据全部插入
IS    
DECLARE
	individual_count int
	strsql string
BEGIN
	--先验是否一个客户id有多条记录对应不通的证件类别
	--初始化 身份证 用户
	shanglifeecif.init_individual_0(individual_count);

	--初始化  护照用户
	individual_count:=individual_count+1;
	shanglifeecif.init_individual_1(individual_count);
	--初始化 驾照用户
	individual_count:=individual_count+1;
	shanglifeecif.init_individual_3(individual_count);
	--其他 证件类型用户
	individual_count:=individual_count+1;
	shanglifeecif.init_individual_other(individual_count);
	
	--更新用户等级信息
	shanglifeecif.up_t_customers_class_1();
	
	--更新职业信息
	--shanglifeecif.up_health_insurance_listing_1();
	--更新其它信息
	shanglifeecif.up_other_customerinfo()

END;

/
CREATE OR REPLACE PROCEDURE shanglifeecif.init_individual_0( individual_count OUT int) -- 初始化 身份证
IS    
BEGIN
	
	--查询出此次处理的数据并出表中
	insert into shanglifeecif.individual (
		indid,
		custid,
		scustid,
	 	name,
	 	gender,
	 	birthday,
	 	idcard,
	 	created_time,
	 	created_by
	)
	SELECT 
		row_number()over(),
		'CP'||lpad(row_number()over(),10,'0'),	
		scustid ,
		max(name) as name,
		max(gender) as gender ,
		max(birthday) as birthday,
		max(idcard) as idcard,
		sysdate,
		'admin'
		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
				shanghailifeecif.policy_information 
			WHERE customerno IS NOT NULL AND idtype=0
			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
				shanghailifeecif.policy_information
			WHERE insuredno IS NOT NULL AND insuredidtype=0
			) tmpTable GROUP BY scustid
		--已经存在的数据行数
		select count(0) into individual_count from shanglifeecif.individual

END;
/
CREATE OR REPLACE PROCEDURE shanglifeecif.init_individual_1(individual_count INOUT  int) -- 初始化 护照
IS    
BEGIN
	
	--查询出此次处理的数据并出表中
	insert into shanglifeecif.individual (
			indid,
			custid,
			scustid,
		 	name,
		 	gender,
		 	birthday,
		 	passport,
		 	created_time,
		 	created_by
	)
	SELECT 
		row_number()over(),
		'CP'||lpad(row_number()over(),10,'0'),	
		scustid ,
		max(name) as name,
		max(gender) as gender ,
		max(birthday) as birthday,
		max(idcard) as idcard,
		sysdate,
		'admin'
		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
				shanghailifeecif.policy_information 
			WHERE customerno IS NOT NULL AND idtype=1
			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
				shanghailifeecif.policy_information
			WHERE insuredno IS NOT NULL AND insuredidtype=1
			) tmpTable GROUP BY scustid

	--已经存在的数据行数
	select count(0) into individual_count from shanglifeecif.individual


END;
/
CREATE OR REPLACE PROCEDURE shanglifeecif.init_individual_3(individual_count INOUT  int) -- 初始化 驾照
IS    
BEGIN
	
	--查询出此次处理的数据并出表中
	insert into shanglifeecif.individual(
		indid,
		custid,
		scustid,
	 	name,
	 	gender,
	 	birthday,
	 	dlicense,
	 	created_time,
	 	created_by
	)
	SELECT 
		row_number()over(),
		'CP'||lpad(row_number()over(),10,'0'),	
		scustid ,
		max(name) as name,
		max(gender) as gender ,
		max(birthday) as birthday,
		max(idcard) as idcard,
		sysdate,
		'admin'
		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
				shanghailifeecif.policy_information 
			WHERE customerno IS NOT NULL AND idtype=3
			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
				shanghailifeecif.policy_information
			WHERE insuredno IS NOT NULL AND insuredidtype=3
			) tmpTable GROUP BY scustid

	--已经存在的数据行数
	select count(0) into individual_count from shanglifeecif.individual
END;
/
CREATE OR REPLACE PROCEDURE shanglifeecif.init_individual_other(individual_count INOUT  int) -- 初始化除了以上三种证件信息
IS    
BEGIN
	
	--查询出此次处理的数据并出表中
	insert into shanglifeecif.individual(
			indid,
			custid,
			scustid,
		 	name,
		 	gender,
		 	birthday,
		 	otheridnumber,
		 	created_time,
		 	created_by
	)
	SELECT 
		row_number()over(),
		'CP'||lpad(row_number()over(),10,'0'),	
		scustid ,
		max(name) as name,
		max(gender) as gender ,
		max(birthday) as birthday,
		max(idcard) as idcard,
		sysdate,
		'admin'
		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
				shanghailifeecif.policy_information 
			WHERE customerno IS NOT NULL AND idtype not in (0,1,3)
			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
				shanghailifeecif.policy_information
			WHERE insuredno IS NOT NULL AND insuredidtype not in (0,1,3)
			) tmpTable GROUP BY scustid

	--已经存在的数据行数
	select count(0) into individual_count from shanglifeecif.individual	

END;
/
CREATE OR REPLACE PROCEDURE shanglifeecif.up_t_customers_class_1() -- 创建存储过程
IS    
BEGIN
	UPDATE shanglifeecif.individual a SET (
			CustClass	,--	客户等级
			ConValue	,--	贡献度分
			Awarded3	,--	家庭加分2
			Awarded2	,--	续期加分
			Awarded1	,--	保单加分
			SOValue	,--	总分值
			EndDate,	--	客户等级失效日期
			Height,		--身高
			Weight,		--体重
			BMI,
			PIncome,	--个人年收入
			FIncome,	--家庭年收入
			IncomeSource,	--收入来源
			SIStatus,	--社保情况
			Ethnic,  --民族情况
			Nation, --国籍
			MaritalStat, --婚姻
			Employer,--工作单位
			Education,--学历
			Dday, --死亡日期
			regtype,  --户籍类型
			ZIPCODE,--邮编
			HPhone,--电话
			PMPhone,--手机
			email, -- 邮箱  
			RAL    --联系地址
		) = (
			select 
					b.CLASS_VALUE,--客户等级
					b.CONTRIBUTION_VALUE,--贡献度分
					b.AWARDED3, --家庭加分
					b.AWARDED2, --续期加分
					b.AWARDED1, --保单加分
					b.TOTAL_VALUE, --总分值
					trim(b.END_DATE) as END_DATE, --失效日期
					b.STATURE,	--身高
					b.AVOIRDUPOIS,--体重
					b.BMI,        --根据身高体重计算
					b.YEARINCOME,	--个人年收入
					b.FAMILYYEARSALARY,	--家庭年收入
					trim(b.INCOMESOURCE) as INCOMESOURCE,	--收入来源
					trim(b.SOCIALINSUFLAG) as SOCIALINSUFLAG,	--社保情况
					trim(b.NATIONALITY) as NATIONALITY, --民族情况
					trim(b.NATIVEPLACE) as NATIVEPLACE, --国籍
					trim(b.MARRIAGE) as MARRIAGE,--婚姻
					trim(b.GRPNAME) as GRPNAME,--工作单位名称
					trim(b.DEGREE) as DEGREE,--学历
					trim(b.DEATHDATE) as DEATHDATE,--死亡日期
					trim(b.DENTYPE) as DENTYPE,--户籍类型
					trim(b.ZIPCODE) as ZIPCODE,--邮编
					trim(b.PHONE) as PHONE,--电话
					trim(b.MOBILE) as MOBILE,--手机
					trim(b.EMAIL) as EMAIL,--邮箱
					trim(b.POSTALADDRESS) as POSTALADDRESS --联系地址
				from shanghailifeecif.t_customer_class b
				where b.CUSTOMER_ID = a.scustid
		) WHERE 1=1 ;

END;
/
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 shanghailifeecif.policy_information p);
	--更新客户类型投保人、被保人
	UPDATE shanglifeecif.individual i SET i.custtype = '被保人' WHERE 
	i.scustid IN (SELECT pi.insuredno FROM shanghailifeecif.policy_information pi);
	--更新客户类型投保人、被保人
	UPDATE shanglifeecif.individual i SET i.custtype = '投保人、被保人' WHERE 
	i.scustid IN (SELECT p.customerno FROM shanghailifeecif.policy_information p)
	AND i.scustid IN (SELECT pi.insuredno FROM shanghailifeecif.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.CUSTOMGETPOLDATE desc) rd,p.customerno,p.SALECOM FROM shanghailifeecif.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.CUSTOMGETPOLDATE desc) rd,p.insuredno,p.SALECOM FROM shanghailifeecif.policy_information p
		) t WHERE t.rd = 1 AND si1.scustid = t.insuredno
	)
	
END;
/
/*
CREATE OR REPLACE PROCEDURE shanglifeecif.up_health_insurance_listing_1() -- 创建存储过程
IS    
BEGIN
	--更新个人信息表
	UPDATE shanglifeecif.individual a SET (
		OccupationId	,--	职业代码
		Occupation,	--	职业名称
		HomeAdress
	) = (
		SELECT 
			t.appntoccupationcode,
			t.appntoccupationname,
			t.APPNTHOMEADDRESS
		FROM (
			select 
				 appntno,
				 max(appntoccupationcode) appntoccupationcode,--职业代码
				 max(appntoccupationname) appntoccupationname,--职业名称
				max(APPNTHOMEADDRESS) APPNTHOMEADDRESS --住址 
			from shanghailifeecif.HEALTH_INSURANCE_LISTING 
			WHERE appntoccupationcode is not null
			and appntoccupationname is not NULL
			group by appntno
		) t WHERE t.appntno = a.scustid 
	);
	
END;
*/
BEGIN
	shanglifeecif.init_individual_main();
end ;