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();

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,
		custtype,
	 	created_time,
	 	created_by
	)
	SELECT 
		row_number()over(),
		'CP'||lpad(row_number()over(),10,'0'),	

		scustid ,
		name,
		gender ,
		birthday,
		idcard ,
		max(custtype) AS custtype,
		sysdate,
		'admin'
		FROM (
			SELECT
				customerno AS scustid,--投保人
				name AS name,
				sex AS gender,
				birthday AS  birthday,
				idtype AS idtype , 
				idno AS idcard,
				"投保人" AS custtype
			FROM
				shanghailifeecif.policy_information 
			WHERE customerno IS NOT NULL AND idtype=0
			UNION 
			SELECT 
				insuredno AS scustid,--被保人
				insuredname AS name,
				insuredsex AS gender,
				insuredbirthday AS  birthday,
				insuredidtype AS idtype , 
				insuredidno AS idcard,
				"被保人" AS custtype
			FROM
				shanghailifeecif.policy_information
			WHERE insuredno IS NOT NULL AND insuredidtype=0
			) tmpTable GROUP BY scustid ,name,gender,birthday,idcard
	--已经存在的数据行数
	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,
		 	custtype,
		 	
		 	created_time,
		 	created_by
	)
	SELECT 
		row_number()over()+individual_count,
		'CP'||lpad(row_number()over()+individual_count,10,'0'),

		scustid ,
		name,
		gender ,
		birthday,
		idcard ,
		max(custtype) as custtype,
		sysdate,
		'admin'
		FROM (
			SELECT
				customerno AS scustid,--投保人
				name AS name,
				sex AS gender,
				birthday AS  birthday,
				idtype AS idtype , 
				idno AS idcard,
				"投保人" as custtype
			FROM
				shanghailifeecif.policy_information 
			WHERE customerno IS NOT NULL AND idtype=1
			UNION 
			SELECT 
				insuredno AS scustid,--被保人
				insuredname AS name,
				insuredsex AS gender,
				insuredbirthday AS  birthday,
				insuredidtype AS idtype , 
				insuredidno AS idcard,
				"被保人" as custtype
			FROM
				shanghailifeecif.policy_information
			WHERE insuredno IS NOT NULL AND insuredidtype=1
			) tmpTable GROUP BY scustid ,name,gender ,birthday,idcard

	--已经存在的数据行数
	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,
		custtype,
	 	created_time,
	 	created_by
	)
	SELECT 
		row_number()over()+individual_count,
		'CP'||lpad(row_number()over()+individual_count,10,'0'),
		
		scustid ,
		name,
		gender ,
		birthday,
		idcard ,
		max(custtype) as custtype,
		sysdate,
		'admin'
		FROM (
			SELECT
				customerno AS scustid,--投保人
				name AS name,
				sex AS gender,
				birthday AS  birthday,
				idtype AS idtype , 
				idno AS idcard,
				"投保人" as custtype
			FROM
				shanghailifeecif.policy_information 
			WHERE customerno IS NOT NULL AND idtype=3
			UNION 
			SELECT 
				insuredno AS scustid,--被保人
				insuredname AS name,
				insuredsex AS gender,
				insuredbirthday AS  birthday,
				insuredidtype AS idtype , 
				insuredidno AS idcard,
				"被保人" as custtype
			FROM
				shanghailifeecif.policy_information
			WHERE insuredno IS NOT NULL AND insuredidtype=3
			) tmpTable GROUP BY scustid ,name,gender ,birthday,idcard

	--已经存在的数据行数
	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,
		 	
		 	
		 	created_time,
		 	created_by
	)
	SELECT 
		row_number()over()+individual_count,
		'CP'||lpad(row_number()over()+individual_count,10,'0'),
		scustid ,
		name,
		gender ,
		birthday,
		sysdate,
		'admin'
		FROM (
			SELECT
				customerno AS scustid,--投保人
				name AS name,
				sex AS gender,
				birthday AS  birthday,
				idtype AS idtype , 
				idno AS idcard,
				"投保人" as custtype
			FROM
				shanghailifeecif.policy_information 
			WHERE customerno IS NOT NULL AND idtype not in (0,1,3)
			UNION
			SELECT 
				insuredno AS scustid,--被保人
				insuredname AS name,
				insuredsex AS gender,
				insuredbirthday AS  birthday,
				insuredidtype AS idtype , 
				insuredidno AS idcard,
				"被保人" as custtype
			FROM
				shanghailifeecif.policy_information
			WHERE insuredno IS NOT NULL AND insuredidtype in (0,1,3)
			) tmpTable GROUP BY scustid ,name,gender ,birthday,idcard

	--已经存在的数据行数
	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 
					CLASS_VALUE	,--客户等级
					CONTRIBUTION_VALUE ,--贡献度分
					AWARDED3, --家庭加分
					AWARDED2, --续期加分
					AWARDED1, --保单加分
					TOTAL_VALUE, --总分值
					END_DATE, --失效日期
					STATURE,	--身高
					AVOIRDUPOIS,--体重
					BMI,        --根据身高体重计算
					YEARINCOME,	--个人年收入
					FAMILYYEARSALARY,	--家庭年收入
					INCOMESOURCE,	--收入来源
					SOCIALINSUFLAG,	--社保情况
					NATIONALITY, --民族情况
					NATIVEPLACE, --国籍
					MARRIAGE,--婚姻
					GRPNAME,--工作单位名称
					DEGREE,--学历
					DEATHDATE,--死亡日期
					DENTYPE,--户籍类型
					ZIPCODE,--邮编
					PHONE,--电话
					MOBILE,--手机
					EMAIL,--邮箱
					POSTALADDRESS --联系地址

				from shanghailifeecif.t_customer_class b
				where b.CUSTOMER_ID = a.scustid
		) WHERE 1=1 ;

END;
/
DROP TABLE IF EXISTS shanglifeecif.occupation_tmp;
CREATE  TABLE shanglifeecif.occupation_tmp(
  scustid string not NULL COMMENT '客户号',   
  OccupationId string DEFAULT NULL COMMENT '名称', 
  Occupation string DEFAULT NULL COMMENT '出生日期' ,
   HomeAdress string DEFAULT NULL COMMENT '籍贯'
  )
 COMMENT 'occupation_tmp 客户职业临时表' STORED AS ES 
with shard number 10
replication 1;

CREATE OR REPLACE PROCEDURE shanglifeecif.up_health_insurance_listing_1() -- 创建存储过程
IS    
BEGIN
	--清楚临时表数据
	delete shanglifeecif.occupation_tmp;
	--插入去重数据到临时表
	insert into shanglifeecif.occupation_tmp(
		scustid,
		OccupationId,
		Occupation,
		HomeAdress
	)
	select 
		appntno,
		appntoccupationcode	,--职业代码
		appntoccupationname ,
		APPNTHOMEADDRESS--职业名称
	from shanghailifeecif.HEALTH_INSURANCE_LISTING 
	WHERE appntoccupationcode is not null
	and appntoccupationname is not null
	group by appntno,appntoccupationcode,appntoccupationname,APPNTHOMEADDRESS

	--更新个人信息表

	UPDATE shanglifeecif.individual a SET (
		OccupationId	,--	职业代码
		Occupation,	--	职业名称
		HomeAdress
	) = (
		select 
			OccupationId	,--职业代码
			Occupation,--职业名称
			HomeAdress 
		from shanglifeecif.occupation_tmp b
		where b.scustid = a.scustid 
	) WHERE 1=1 ;

	/*--更新被保人手机号
	UPDATE shanglifeecif.individual a SET (
		PMPhone
	) = (
		select 
			INSUREDMOBILE
			
		from shanghailifeecif.policy_information b
		where b.INSUREDNO = a.scustid 
	) WHERE 1=1 ;*/
	
END;
/
BEGIN
	shanglifeecif.init_individual_main();
end ;