ShangHaiLifeECIF/sql-achievement/存储过程/个人信息开发qxp.sql

-----------------------------------------------------------------------
--创建数据源
CREATE  TABLE policy_information(
	contno varchar2(200) DEFAULT NULL COMMENT '保单号',  -- dialect: ORACLE
	riskcode varchar2(200) DEFAULT NULL COMMENT '险种代码',  -- dialect: ORACLE
	riskname varchar2(200) DEFAULT NULL COMMENT '险种名称',  -- dialect: ORACLE
	riskperiod varchar2(200) DEFAULT NULL COMMENT '险种分类',  -- dialect: ORACLE
	risktype varchar2(200) DEFAULT NULL COMMENT '险种设计类型',
	payintv varchar2(200) DEFAULT NULL COMMENT '缴费方式',  
	security varchar2(200) not NULL COMMENT '保障年期',  
	payendyear number not NULL COMMENT '缴费期间', 
	payendyearflag varchar2(200) not NULL COMMENT '缴费年期单位',  
	insuyer number not NULL COMMENT '保险年期',
	insuyerflag varchar2(200) not NULL COMMENT '保险年期单位',
	paycount number not NULL COMMENT '续保次数',
	signdate timestamp not NULL COMMENT '承保日期',
	cvalidate timestamp not NULL COMMENT '生效日期', 
	enddate timestamp not NULL COMMENT '保单终止日期', 
	modifydate timestamp not null comment '更新时间',
	prem number not null COMMENT '保费',
	appflag varchar2(200) not NULL COMMENT '保单状态',
	customerno varchar2(200) not null comment '客户号',
	name varchar2(200) not null comment '客户姓名',
	sex varchar2(200) not null comment '客户性别',
	birthday timestamp not null comment '客户出生日期',
	idtype varchar2(200) not null comment '客户证件类型',
	idno varchar2(200) not null comment '客户证件号',
	insuredno varchar2(200) not null comment '被保人号',
	insuredname varchar2(200) not null comment '被保人姓名',
	insuredsex varchar2(200) not null comment '被保人性别',
	insuredbirthday timestamp not null comment '被保人出生日期',
	insuredidtype varchar2(200) not null comment '被保人证件类型',
	insuredidno varchar2(200) not null comment '被保人证件号码',
	relationtoappnt varchar2(200) not null comment '与投保人关系',
	salechnl varchar2(200) not null comment '销售渠道',
	salecom varchar2(200) not null comment '业绩归属',
	salechannels varchar2(200) not NULL COMMENT '二级业绩归属', 
	customgetpoldate timestamp not null comment '客户投保日期',
	prem_js number not null comment 'prem_js'
)
COMMENT '投保清单'
STORED AS CSVFILE ;

--推送数据命令

--hdfs dfs -put /home/frontData/ /tmp/myData/

LOAD DATA INPATH '/tmp/myData/policy_information.txt' OVERWRITE INTO TABLE qxp.policy_information;

--------------------------------------------------------------------------------------------------------------------
TRUNCATE TABLE individual;
--创建search目的表
DROP TABLE IF EXISTS individual;
CREATE  TABLE qxp.individual_search(
  indid string DEFAULT NULL COMMENT '个体ID', 
  custid string DEFAULT NULL COMMENT '全局客户号',
  SCustID string DEFAULT NULL COMMENT '客户号',   
  created_by string DEFAULT NULL COMMENT '创建人', 
  created_time timestamp DEFAULT NULL COMMENT '创建时间', 
  updated_by string DEFAULT NULL COMMENT '更新人', 
  updated_time timestamp DEFAULT NULL COMMENT '更新时间',
  name string DEFAULT NULL COMMENT '名称', 
  birthday date DEFAULT NULL COMMENT '出生日期', 
  nobirth string DEFAULT NULL COMMENT '出生国家',  -- dialect: ORACLE
  sobirth string DEFAULT NULL COMMENT '出生省份',  -- dialect: ORACLE
  cobirth string DEFAULT NULL COMMENT '出生城市',  -- dialect: ORACLE
  homeadress string DEFAULT NULL COMMENT '籍贯',
  dday date DEFAULT NULL COMMENT '死亡日期', 
  gender string DEFAULT NULL COMMENT '性别',  -- dialect: ORACLE
  ethnic string DEFAULT NULL COMMENT '民族',  -- dialect: ORACLE
  nation string DEFAULT NULL COMMENT '国籍',  -- dialect: ORACLE
  regresidence string DEFAULT NULL COMMENT '户口所在地', 
  maritalstat string DEFAULT NULL COMMENT '婚姻状况',  -- dialect: ORACLE
  maritalstatdate date DEFAULT NULL COMMENT '婚姻状况日期', 
  offspring string DEFAULT NULL COMMENT '生育状况',  -- dialect: ORACLE
  support string DEFAULT NULL COMMENT '赡养状况',  -- dialect: ORACLE
  empstat string DEFAULT NULL COMMENT '就业状况',  -- dialect: ORACLE
  empstatdate string DEFAULT NULL COMMENT '就业状况日期',  -- dialect: ORACLE
  raid string DEFAULT NULL COMMENT '居住地址', 
  ral string DEFAULT NULL COMMENT '居住地址不分段', 
  al string DEFAULT NULL COMMENT '曾用地址不分段', 
  coadress int DEFAULT NULL COMMENT '地址登录数量', 
  pmphone string DEFAULT NULL COMMENT '常用手机', 
  mhone string DEFAULT NULL COMMENT '曾用手机', 
  sophone int DEFAULT NULL COMMENT '手机登录数量', 
  height decimal(8,2) DEFAULT NULL COMMENT '身高', 
  weight decimal(8,2) DEFAULT NULL COMMENT '体重', 
  cillness string DEFAULT NULL COMMENT '当前疾病',  -- dialect: ORACLE
  pillness string DEFAULT NULL COMMENT '曾患疾病',  -- dialect: ORACLE
  bmi decimal(8,2) DEFAULT NULL COMMENT 'BMI体制指数', 
  evadate date DEFAULT NULL COMMENT '健康评估日期', 
  cohevaluation int DEFAULT NULL COMMENT '健康评估登录数量', 
  hphone string DEFAULT NULL COMMENT '家庭电话', 
  occupation string DEFAULT NULL COMMENT '职业',  -- dialect: ORACLE
  odate date DEFAULT NULL COMMENT '职业时间', 
  employer string DEFAULT NULL COMMENT '工作单位', 
  wphone string DEFAULT NULL COMMENT '工作电话', 
  empdate date DEFAULT NULL COMMENT '工作单位时间', 
  idcard string DEFAULT NULL COMMENT '身份证', 
  passport string DEFAULT NULL COMMENT '护照', 
  dlicense string DEFAULT NULL COMMENT '驾驶证', 
  education string DEFAULT NULL COMMENT '最高学历',  -- dialect: ORACLE
  university string DEFAULT NULL COMMENT '毕业学校', 
  edate date DEFAULT NULL COMMENT '学历时间', 
  wechat string DEFAULT NULL COMMENT '微信', 
  weibo string DEFAULT NULL COMMENT '微博', 
  email string DEFAULT NULL COMMENT '邮箱', 
  qq string DEFAULT NULL COMMENT 'QQ', 
  tiktok string DEFAULT NULL COMMENT '抖音', 
  saccount1 string DEFAULT NULL COMMENT '社交账号1', 
  sa1cat string DEFAULT NULL COMMENT '社交账号1类别',  -- dialect: ORACLE
  saccount2 string DEFAULT NULL COMMENT '社交账号2', 
  sa2cat string DEFAULT NULL COMMENT '社交账号2类别',  -- dialect: ORACLE
  ctype string DEFAULT NULL COMMENT '首选联系方式',  -- dialect: ORACLE
  father string DEFAULT NULL COMMENT '父亲', 
  mother string DEFAULT NULL COMMENT '母亲', 
  mate string DEFAULT NULL COMMENT '配偶', 
  child1 string DEFAULT NULL COMMENT '子女1', 
  child2 string DEFAULT NULL COMMENT '子女2', 
  cochild int DEFAULT NULL COMMENT '子女登录数量', 
  fmhistory string DEFAULT NULL COMMENT '家族病史',  -- dialect: ORACLE
  rpid string DEFAULT NULL COMMENT '房产', 
  rpdescribe string DEFAULT NULL COMMENT '房产描述', 
  corp int DEFAULT NULL COMMENT '房产登录数量', 
  vid string DEFAULT NULL COMMENT '车辆', 
  vdescribe string DEFAULT NULL COMMENT '车辆描述', 
  covehicle int DEFAULT NULL COMMENT '车辆登录数量', 
  anniversary1 date DEFAULT NULL COMMENT '纪念日1', 
  a1describe string DEFAULT NULL COMMENT '纪念日1类型',  -- dialect: ORACLE
  anniversary2 date DEFAULT NULL COMMENT '纪念日2', 
  a2describe string DEFAULT NULL COMMENT '纪念日2类型',  -- dialect: ORACLE
  soanniversary int DEFAULT NULL COMMENT '纪念日登录数量', 
  custtype string DEFAULT NULL COMMENT '客户类型',  -- dialect: ORACLE
  bankname string DEFAULT NULL COMMENT '开户银行',  -- dialect: ORACLE
  tobankcard string DEFAULT NULL COMMENT '银行卡类型',  -- dialect: ORACLE
  accname string DEFAULT NULL COMMENT '账户名', 
  bankaccno string DEFAULT NULL COMMENT '银行帐号', 
  cobaccount int DEFAULT NULL COMMENT '账户登录数量', 
  sotlirisk decimal(32,8) DEFAULT NULL COMMENT '传统寿险种保额合计', 
  soairisk decimal(32,8) DEFAULT NULL COMMENT '意外类险种保额合计', 
  sosiirisk decimal(32,8) DEFAULT NULL COMMENT '重疾类险种保额合计', 
  somcirisk decimal(32,8) DEFAULT NULL COMMENT '医疗费用类险种保额合计', 
  someirisk decimal(32,8) DEFAULT NULL COMMENT '医疗补贴类险种保额合计', 
  soefirisk decimal(32,8) DEFAULT NULL COMMENT '教育金类险种保额合计', 
  sopirisk decimal(32,8) DEFAULT NULL COMMENT '养老金类险种保额合计', 
  sowmirisk decimal(32,8) DEFAULT NULL COMMENT '财富管理险种保额合计', 
  custclass int DEFAULT NULL COMMENT '客户等级', 
  convalue int DEFAULT NULL COMMENT '贡献度分', 
  awarded3 int DEFAULT NULL COMMENT '家庭加分', 
  awarded2 int DEFAULT NULL COMMENT '续期加分', 
  awarded1 int DEFAULT NULL COMMENT '保单加分', 
  sovalue int DEFAULT NULL COMMENT '总分值', 
  enddate date DEFAULT NULL COMMENT '客户等级失效日期', 
  systemtag1 string DEFAULT NULL COMMENT '系统标签域1', 
  systemtag2 string DEFAULT NULL COMMENT '系统标签域2', 
  systemtag3 string DEFAULT NULL COMMENT '系统标签域3', 
  cocommunication int DEFAULT NULL COMMENT '接触次数', 
  lcdate date DEFAULT NULL COMMENT '最近接触时间', 
  lctype string DEFAULT NULL COMMENT '最近接触类型',  -- dialect: ORACLE
  cocomplaint int DEFAULT NULL COMMENT '投诉次数', 
  lcptdate date DEFAULT NULL COMMENT '最近投诉时间', 
  lcptreason string DEFAULT NULL COMMENT '最近投诉原因',  -- dialect: ORACLE
  lcptlink string DEFAULT NULL COMMENT '最近投诉环节',  -- dialect: ORACLE
  lcptresult string DEFAULT NULL COMMENT '最近投诉处理结果',  -- dialect: ORACLE
  lcptduration decimal(8,2) DEFAULT NULL COMMENT '最近投诉处理时长', 
  lcptperson string DEFAULT NULL COMMENT '最近投诉人',  -- dialect: ORACLE
  soppremium decimal(32,8) DEFAULT NULL COMMENT '累计已缴保费', 
  lappdate date DEFAULT NULL COMMENT '最近投保日期', 
  cndate date DEFAULT NULL COMMENT '最近报案日期', 
  cnstatus string DEFAULT NULL COMMENT '最近报案状态',  -- dialect: ORACLE
  cocnotification int DEFAULT NULL COMMENT '报案登录数量', 
  fadate date DEFAULT NULL COMMENT '首次投保日期', 
  ladate date DEFAULT NULL COMMENT '最近投保日期', 
  lpno string DEFAULT NULL COMMENT '最近保险单号', 
  lpstate string DEFAULT NULL COMMENT '最近保单状态',  -- dialect: ORACLE
  lpname string DEFAULT NULL COMMENT '最近投保险种名称', 
  copolicy int DEFAULT NULL COMMENT '保单登录数量', 
  iaccno1 string DEFAULT NULL COMMENT '保险账户1', 
  iaccno2 string DEFAULT NULL COMMENT '保险账户2', 
  iaccno3 string DEFAULT NULL COMMENT '保险账户3', 
  soiaccount1 decimal(32,8) DEFAULT NULL COMMENT '保险账户1余额', 
  soiaccount2 decimal(32,8) DEFAULT NULL COMMENT '保险账户2余额', 
  soiaccount3 decimal(32,8) DEFAULT NULL COMMENT '保险账户3余额', 
  coiaccount int DEFAULT NULL COMMENT '保险账户登录数量',
	Label1	string	 COMMENT 	'Label1' ,
	Label2	string	 COMMENT 	'Label2' ,
	Label3	string	 COMMENT 	'Label3' ,
	Label4	string	 COMMENT 	'Label4' ,
	Label5	string	 COMMENT 	'Label5' ,
	Label6	string	 COMMENT 	'Label6' ,
	Label7	string	 COMMENT 	'Label7' ,
	Label8	string	 COMMENT 	'Label8' ,
	Label9	string	 COMMENT 	'Label9' ,
	Label10	string	 COMMENT 	'Label10' ,
	Label11	string	 COMMENT 	'Label11' ,
	Label12	string	 COMMENT 	'Label12' ,
	Label13	string	 COMMENT 	'Label13' ,
	Label14	string	 COMMENT 	'Label14' ,
	Label15	string	 COMMENT 	'Label15' ,
	Label16	string	 COMMENT 	'Label16' ,
	Label17	string	 COMMENT 	'Label17' ,
	Label18	string	 COMMENT 	'Label18' ,
	Label19	string	 COMMENT 	'Label19' ,
	Label20	string	 COMMENT 	'Label20' ,
	Label21	string	 COMMENT 	'Label21' ,
	Label22	string	 COMMENT 	'Label22' ,
	Label23	string	 COMMENT 	'Label23' ,
	Label24	string	 COMMENT 	'Label24' ,
	Label25	string	 COMMENT 	'Label25' ,
	Label26	string	 COMMENT 	'Label26' ,
	Label27	string	 COMMENT 	'Label27' ,
	Label28	string	 COMMENT 	'Label28' ,
	Label29	string	 COMMENT 	'Label29' ,
	Label30	string	 COMMENT 	'Label30' ,
	Label31	string	 COMMENT 	'Label31' ,
	Label32	string	 COMMENT 	'Label32' ,
	Label33	string	 COMMENT 	'Label33' ,
	Label34	string	 COMMENT 	'Label34' ,
	Label35	string	 COMMENT 	'Label35' ,
	Label36	string	 COMMENT 	'Label36' ,
	Label37	string	 COMMENT 	'Label37' ,
	Label38	string	 COMMENT 	'Label38' ,
	Label39	string	 COMMENT 	'Label39' ,
	Label40	string	 COMMENT 	'Label40' ,
	Label41	string	 COMMENT 	'Label41' ,
	Label42	string	 COMMENT 	'Label42' ,
	Label43	string	 COMMENT 	'Label43' ,
	Label44	string	 COMMENT 	'Label44' ,
	Label45	string	 COMMENT 	'Label45' ,
	Label46	string	 COMMENT 	'Label46' ,
	Label47	string	 COMMENT 	'Label47' ,
	Label48	string	 COMMENT 	'Label48' ,
	Label49	string	 COMMENT 	'Label49' ,
	Label50	string	 COMMENT 	'Label50' ,
	Label51	string	 COMMENT 	'Label51' ,
	Label52	string	 COMMENT 	'Label52' ,
	Label53	string	 COMMENT 	'Label53' ,
	Label54	string	 COMMENT 	'Label54' ,
	Label55	string	 COMMENT 	'Label55' ,
	Label56	string	 COMMENT 	'Label56' ,
	Label57	string	 COMMENT 	'Label57' ,
	Label58	string	 COMMENT 	'Label58' ,
	Label59	string	 COMMENT 	'Label59' ,
	Label60	string	 COMMENT 	'Label60' ,
	Label61	string	 COMMENT 	'Label61' ,
	Label62	string	 COMMENT 	'Label62' ,
	Label63	string	 COMMENT 	'Label63' ,
	Label64	string	 COMMENT 	'Label64' ,
	Label65	string	 COMMENT 	'Label65' ,
	Label66	string	 COMMENT 	'Label66' ,
	Label67	string	 COMMENT 	'Label67' ,
	Label68	string	 COMMENT 	'Label68' ,
	Label69	string	 COMMENT 	'Label69' ,
	Label70	string	 COMMENT 	'Label70' ,
	Label71	string	 COMMENT 	'Label71' ,
	Label72	string	 COMMENT 	'Label72' ,
	Label73	string	 COMMENT 	'Label73' ,
	Label74	string	 COMMENT 	'Label74' ,
	Label75	string	 COMMENT 	'Label75' ,
	Label76	string	 COMMENT 	'Label76' ,
	Label77	string	 COMMENT 	'Label77' ,
	Label78	string	 COMMENT 	'Label78' ,
	Label79	string	 COMMENT 	'Label79' ,
	Label80	string	 COMMENT 	'Label80' ,
	Label81	string	 COMMENT 	'Label81' ,
	Label82	string	 COMMENT 	'Label82' ,
	Label83	string	 COMMENT 	'Label83' ,
	Label84	string	 COMMENT 	'Label84' ,
	Label85	string	 COMMENT 	'Label85' ,
	Label86	string	 COMMENT 	'Label86' ,
	Label87	string	 COMMENT 	'Label87' ,
	Label88	string	 COMMENT 	'Label88' ,
	Label89	string	 COMMENT 	'Label89' ,
	Label90	string	 COMMENT 	'Label90' ,
	Label91	string	 COMMENT 	'Label91' ,
	Label92	string	 COMMENT 	'Label92' ,
	Label93	string	 COMMENT 	'Label93' ,
	Label94	string	 COMMENT 	'Label94' ,
	Label95	string	 COMMENT 	'Label95' ,
	Label96	string	 COMMENT 	'Label96' ,
	Label97	string	 COMMENT 	'Label97' ,
	Label98	string	 COMMENT 	'Label98' ,
	Label99	string	 COMMENT 	'Label99' ,
	Label100	string	 COMMENT 	'Label100' ,
	Label101	string	 COMMENT 	'Label101' ,
	Label102	string	 COMMENT 	'Label102' ,
	Label103	string	 COMMENT 	'Label103' ,
	Label104	string	 COMMENT 	'Label104' ,
	Label105	string	 COMMENT 	'Label105' ,
	Label106	string	 COMMENT 	'Label106' ,
	Label107	string	 COMMENT 	'Label107' ,
	Label108	string	 COMMENT 	'Label108' ,
	Label109	string	 COMMENT 	'Label109' ,
	Label110	string	 COMMENT 	'Label110' ,
	Label111	string	 COMMENT 	'Label111' ,
	Label112	string	 COMMENT 	'Label112' ,
	Label113	string	 COMMENT 	'Label113' ,
	Label114	string	 COMMENT 	'Label114' ,
	Label115	string	 COMMENT 	'Label115' ,
	Label116	string	 COMMENT 	'Label116' ,
	Label117	string	 COMMENT 	'Label117' ,
	Label118	string	 COMMENT 	'Label118' ,
	Label119	string	 COMMENT 	'Label119' ,
	Label120	string	 COMMENT 	'Label120' 

)
COMMENT '个人信息'
STORED AS ES
with shard number 10
replication 1;
---------------------------------------------------------------------------------------、
--创建 自增id
--创建序列

CREATE SEQUENCE globaleCusId_sequence
    INCREMENT BY 1
	START WITH 1
	NOMAXVALUE
	NOMINVALUE
	NOCYCLE
	CACHE 1;



-----------------------------------------------------------------------------------
-- 增量数据
--------------------------------------------------------------------------------------------
--创建存储过程 插入individual_search 
!set plsqlUseSlash true
SET plsql.catch.hive.exception=true; --使用HIVE_EXCEPTION捕获Hive异常
--set plsql.compile.dml.check.semantic=false; --禁止编译过程对PL/SQL内部语法进行检查。
CREATE OR REPLACE PROCEDURE insertOrUpdate_individual() -- 创建存储过程
IS    
DECLARE

BEGIN
	MERGE INTO individual is1 using 
	(
		SELECT scustid ,name,gender ,birthday, idcard
		FROM (
			SELECT
				customerno AS scustid,--投保人
				name AS name,
				sex AS gender,
				birthday AS  birthday,
				idtype AS idtype , 
				idno AS idcard
			FROM
				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
			FROM
				policy_information
			WHERE insuredno IS NOT NULL AND insuredidtype=0
			) tmpTable
		LIMIT IN 0,100000
		) is2 on (is2.scustid = is1.scustid and is2.name = is1.name 
			and is2.gender = is1.gender and is2.birthday = is1.birthday and is2.idcard = is1.idcard)
	 WHEN NOT MATCHED THEN INSERT (
		 	scustid,
		 	name,
		 	gender,
		 	birthday,
		 	idcard,
		 	
		 	custid,
		 	indid,
		 	created_time,
		 	created_by

	 	) VALUES  (
		 	is2.scustid,
		 	is2.name,
		 	is2.gender,
		 	is2.birthday,
		 	is2.idcard,
		 	'CP'||lpad(globaleCusId_sequence.nextval,10,'0'),
		 	globaleCusId_sequence.CURRVAL,
		 	sysdate,
		 	'qxp'
	 	)
	EXCEPTION
			WHEN HIVE_EXCEPTION THEN 
				 log_exception('insertOrUpdate_individual',sqlerrm(),sqlcode())
			WHEN Others THEN
				 log_exception('insertOrUpdate_individual',sqlerrm(),sqlcode())
END;

--------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------


-------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------

=============================================================================================================================================================
==============================================================利用行号全部插入用户数据==========================================================================
=============================================================================================================================================================
--初始化存储过程
-- 身份证 0 
-- 护照 1 
-- 军人证（军官证） 2 
-- 驾照 3 
-- 户口本 4 
-- 学生证 5 
-- 工作证 6 
-- 出生证 7 
-- 其它 8 
-- 无证件 9 
-- 士兵证 A 
-- 回乡证 B 
-- 临时身份证 C 
-- 警官证 D 
-- 台胞证 E 
-- 港、澳通行证 F 
-- 外国人永久居留身份证 I 
-- 港澳台居民居住证 J 
-- 港澳居民居住证 K 
-- 台湾居民居住证 M 
DROP SEQUENCE IF EXISTS globaleCusId_sequence;
!set plsqlUseSlash true
SET plsql.catch.hive.exception=true; --使用HIVE_EXCEPTION捕获Hive异常
--set plsql.compile.dml.check.semantic=false; --禁止编译过程对PL/SQL内部语法进行检查。
CREATE OR REPLACE PROCEDURE init_individual_main() -- 初始化 数据全部插入
IS    
DECLARE
	individual_count int
	strsql string
BEGIN
	--先验是否一个客户id有多条记录对应不通的证件类别
	--初始化 身份证 用户
	init_individual_0(individual_count);

	--初始化  护照用户
	individual_count:=individual_count+1;
	init_individual_1(individual_count);
	--初始化 驾照用户
	individual_count:=individual_count+1;
	init_individual_3(individual_count);
	--其他 证件类型用户
	individual_count:=individual_count+1;
	init_individual_other(individual_count);

	--初始化 全局序列
	individual_count:=individual_count+1;
	
	strsql:='
		DROP SEQUENCE IF EXISTS globaleCusId_sequence;
		CREATE SEQUENCE globaleCusId_sequence
	    INCREMENT BY 1
		START WITH '|| individual_count||'
		NOMAXVALUE
		NOMINVALUE
		NOCYCLE
		CACHE 1;
	';	

	EXECUTE IMMEDIATE strsql

	--更新用户等级信息
	up_t_customers_class_1();
	
	--更新职业信息
	up_health_insurance_listing_1();

	EXCEPTION
			WHEN HIVE_EXCEPTION THEN 
				 log_exception('init_individual_main',sqlerrm(),sqlcode())
			WHEN Others THEN
				 log_exception('init_individual_main',sqlerrm(),sqlcode())
END;






!set plsqlUseSlash true
SET plsql.catch.hive.exception=true; --使用HIVE_EXCEPTION捕获Hive异常
--set plsql.compile.dml.check.semantic=false; --禁止编译过程对PL/SQL内部语法进行检查。
CREATE OR REPLACE PROCEDURE init_individual_0( individual_count OUT int) -- 初始化 省份证
IS    
BEGIN
	
	--查询出此次处理的数据并出表中
	insert into individual (
		indid,
		custid,

		scustid,
	 	name,
	 	gender,
	 	birthday,
	 	idcard,

	 	created_time,
	 	created_by
	)
	SELECT 
		row_number()over(),
		'CP'||lpad(row_number()over(),10,'0'),

		scustid ,
		name,
		gender ,
		birthday,
		idcard ,
		
		sysdate,
		'qxp'
		FROM (
			SELECT
				customerno AS scustid,--投保人
				name AS name,
				sex AS gender,
				birthday AS  birthday,
				idtype AS idtype , 
				idno AS idcard
			FROM
				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
			FROM
				policy_information
			WHERE insuredno IS NOT NULL AND insuredidtype=0
			) tmpTable
	--已经存在的数据行数
	select count(0) into individual_count from individual
	EXCEPTION
			WHEN HIVE_EXCEPTION THEN 
				 log_exception('init_individual_0',sqlerrm(),sqlcode())
			WHEN Others THEN
				 log_exception('init_individual_0',sqlerrm(),sqlcode())
END;


!set plsqlUseSlash true
SET plsql.catch.hive.exception=true; --使用HIVE_EXCEPTION捕获Hive异常
--set plsql.compile.dml.check.semantic=false; --禁止编译过程对PL/SQL内部语法进行检查。
CREATE OR REPLACE PROCEDURE init_individual_1(individual_count INOUT  int) -- 初始化 护照
IS    
BEGIN
	
	--查询出此次处理的数据并出表中
	insert into individual (
			indid,
			custid,
		 	
			scustid,
		 	name,
		 	gender,
		 	birthday,
		 	passport,
		 	
		 	
		 	created_time,
		 	created_by
	)
	SELECT 
		row_number()over()+individual_count,
		'CP'||lpad(row_number()over()+individual_count,10,'0'),

		scustid ,
		name,
		gender ,
		birthday,
		idcard ,

		sysdate,
		'qxp'
		FROM (
			SELECT
				customerno AS scustid,--投保人
				name AS name,
				sex AS gender,
				birthday AS  birthday,
				idtype AS idtype , 
				idno AS idcard
			FROM
				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
			FROM
				policy_information
			WHERE insuredno IS NOT NULL AND insuredidtype=1
			) tmpTable

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

	EXCEPTION
			WHEN HIVE_EXCEPTION THEN 
				 log_exception('init_individual_1',sqlerrm(),sqlcode())
			WHEN Others THEN
				 log_exception('init_individual_1',sqlerrm(),sqlcode())
END;
!set plsqlUseSlash true
SET plsql.catch.hive.exception=true; --使用HIVE_EXCEPTION捕获Hive异常
--set plsql.compile.dml.check.semantic=false; --禁止编译过程对PL/SQL内部语法进行检查。
CREATE OR REPLACE PROCEDURE init_individual_3(individual_count INOUT  int) -- 初始化 护照
IS    
BEGIN
	
	--查询出此次处理的数据并出表中
	insert into individual(
		indid,
		custid,

		scustid,
	 	name,
	 	gender,
	 	birthday,
	 	dlicense,

	 	created_time,
	 	created_by
	)
	SELECT 
		row_number()over()+individual_count,
		'CP'||lpad(row_number()over()+individual_count,10,'0'),
		
		scustid ,
		name,
		gender ,
		birthday,
		idcard ,
		
		sysdate,
		'qxp'
		FROM (
			SELECT
				customerno AS scustid,--投保人
				name AS name,
				sex AS gender,
				birthday AS  birthday,
				idtype AS idtype , 
				idno AS idcard
			FROM
				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
			FROM
				policy_information
			WHERE insuredno IS NOT NULL AND insuredidtype=3
			) tmpTable

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

	EXCEPTION
			WHEN HIVE_EXCEPTION THEN 
				 log_exception('init_individual_3',sqlerrm(),sqlcode())
			WHEN Others THEN
				 log_exception('init_individual_3',sqlerrm(),sqlcode())
END;

!set plsqlUseSlash true
SET plsql.catch.hive.exception=true; --使用HIVE_EXCEPTION捕获Hive异常
--set plsql.compile.dml.check.semantic=false; --禁止编译过程对PL/SQL内部语法进行检查。
CREATE OR REPLACE PROCEDURE init_individual_other(individual_count INOUT  int) -- 初始化 护照
IS    
BEGIN
	
	--查询出此次处理的数据并出表中
	insert into 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,
		'qxp'
		FROM (
			SELECT
				customerno AS scustid,--投保人
				name AS name,
				sex AS gender,
				birthday AS  birthday,
				idtype AS idtype , 
				idno AS idcard
			FROM
				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
			FROM
				policy_information
			WHERE insuredno IS NOT NULL AND insuredidtype in (0,1,3)
			) tmpTable

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

	EXCEPTION
			WHEN HIVE_EXCEPTION THEN 
				 log_exception('init_individual_other',sqlerrm(),sqlcode())
			WHEN Others THEN
				 log_exception('init_individual_other',sqlerrm(),sqlcode())
END;

--创建存储过程 插入individual_search 
!set plsqlUseSlash true
SET plsql.catch.hive.exception=true; --使用HIVE_EXCEPTION捕获Hive异常
--set plsql.compile.dml.check.semantic=false; --禁止编译过程对PL/SQL内部语法进行检查。
CREATE OR REPLACE PROCEDURE up_t_customers_class_1() -- 创建存储过程
IS    
BEGIN
	UPDATE individual a SET (
			CustClass	,--	客户等级
			ConValue	,--	贡献度分
			Awarded3	,--	家庭加分2
			Awarded2	,--	续期加分
			Awarded1	,--	保单加分
			SOValue	,--	总分值
			EndDate	--	客户等级失效日期
		) = (
			select 
					CLASS_VALUE	,--客户等级
					CONTRIBUTION_VALUE ,--贡献度分
					AWARDED3, --家庭加分
					AWARDED2, --续期加分
					AWARDED1, --保单加分
					TOTAL_VALUE, --总分值
					END_DATE --失效日期
				from t_customer_class b
				where b.CUSTOMER_ID = a.scustid;
		) WHERE 1=1 ;
	EXCEPTION
			WHEN HIVE_EXCEPTION THEN 
				 log_exception('up_t_customers_class_1',sqlerrm(),sqlcode())
			WHEN Others THEN
				 log_exception('up_t_customers_class_1',sqlerrm(),sqlcode())
END;
/
DROP TABLE IF EXISTS occupation_tmp;
CREATE  TABLE 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;
--根据HEALTH_INSURANCE_LISTING表更新
CREATE OR REPLACE PROCEDURE up_health_insurance_listing_1() -- 创建存储过程
IS    
BEGIN
	--清楚临时表数据
	delete occupation_tmp;
	--插入去重数据到临时表
	insert into occupation_tmp(
		scustid,
		OccupationId,
		Occupation,
		HomeAdress
	)
	select 
		appntno,
		appntoccupationcode	,--职业代码
		appntoccupationname ,
		APPNTHOMEADDRESS--职业名称
	from HEALTH_INSURANCE_LISTING 
	WHERE appntoccupationcode is not null
	and appntoccupationname is not null
	group by appntno,appntoccupationcode,appntoccupationname,APPNTHOMEADDRESS

	--更新个人信息表

	UPDATE individual a SET (
		OccupationId	,--	职业代码
		Occupation,	--	职业名称
		HomeAdress
	) = (
		select 
			OccupationId	,--职业代码
			Occupation,--职业名称
			APPNTHOMEADDRESS 
		from occupation_tmp b
		where b.scustid = a.scustid 
	) WHERE 1=1 ;
	EXCEPTION
			WHEN HIVE_EXCEPTION THEN 
				 log_exception('up_health_insurance_listing_1',sqlerrm(),sqlcode())
			WHEN Others THEN
				 log_exception('up_health_insurance_listing_1',sqlerrm(),sqlcode())
END;


=======================================================================================================================================
===================================================用户关系信息解析插入====================================================================
=======================================================================================================================================


--更新individual 的用户关系
CREATE OR REPLACE PROCEDURE up_individual_relationship() -- 创建存储过程
IS    
BEGIN
	--清楚临时表数据
	
	EXCEPTION
			WHEN HIVE_EXCEPTION THEN 
				 log_exception('up_individual_relationship',sqlerrm(),sqlcode())
			WHEN Others THEN
				 log_exception('up_individual_relationship',sqlerrm(),sqlcode())
END;

=======================================================================================================================================
===================================================用户上游客户ID去重临时表====================================================================
=======================================================================================================================================


DROP TABLE IF EXISTS scustid_unique_tmp;
CREATE  TABLE scustid_unique_tmp( 
	scustid string    COMMENT '上游客户号' ,
   	indid string COMMENT '客户号' 
)
COMMENT '用户上游客户ID去重临时表'
STORED AS ES
with shard number 10
replication 1;

--更新individual 的用户关系
CREATE OR REPLACE PROCEDURE up_scustid_unique_tmp() -- 创建存储过程
IS    
BEGIN
	--清楚临时表数据
	delete from scustid_unique_tmp;

	--插入临时表数据
	insert into scustid_unique_tmp(scustid,indid)
		select scustid,min(indid) from individual_search1 group by scustid;

	EXCEPTION
			WHEN HIVE_EXCEPTION THEN 
				 log_exception('up_scustid_unique_tmp',sqlerrm(),sqlcode())
			WHEN Others THEN
				 log_exception('up_scustid_unique_tmp',sqlerrm(),sqlcode())
END;


=====================================================================================================================
1,105,462  ---581292 插入投保

1,105,500  ---578591 插入被保人

1,119,154  ---156901 插入UNION   
1,119,154  ---row_number()over(),  使用row_number()over()  329312 
row_number()over() 行号