ShangHaiLifeECIF/sql-achievement/存储过程/人寿测试环境使用sql整理/个体关系信息.sql

CREATE OR REPLACE PROCEDURE shanglifeecif.init_indrelationship_tmp( ) 
IS
DECLARE
	strsql string 
BEGIN
	--删除数据
	DELETE shanglifeecif.indrelationship_tmp;
	--插入数据
	INSERT INTO shanglifeecif.indrelationship_tmp(
		id,
		RSType,
		SCustID1,
		Name1,
		sex1,
		IDCard1,
		Role1,
		SCustID2,
		Name2,
		sex2,
		IDCard2,
		RSSTime,
		Role2
		)
	SELECT 
		row_number()over() AS IRSID,
		max(RELATIONTOAPPNT) AS RSType,
		CUSTOMERNO AS IndID1,
		max(NAME) AS Name1,
		max(sex) AS sex1,
		max(IDNO) AS IDCard1,
		CASE max(RELATIONTOAPPNT) 
			WHEN '子女' 
				THEN CASE max(sex) 
					WHEN 1 THEN '母亲' 
					WHEN 0 THEN '父亲' 
				END
		    WHEN '父母' 
		    	THEN CASE max(sex) 
		    		WHEN 1 THEN '女儿' 
		    		WHEN 0 THEN '儿子' 
		    	END
		    WHEN '配偶' 
		    	THEN CASE max(sex) 
		    		WHEN 1 THEN '妻子' 
		    		WHEN 0 THEN '丈夫' 
		    	END
		    WHEN '祖父母、外祖父母' 
		    	THEN CASE max(sex) 
		    		WHEN 1 THEN '(外)孙女' 
		    		WHEN 0 THEN '(外)祖孙' 
		    	END
		    WHEN '祖孙、外祖孙' 
		    	THEN CASE max(sex) 
		    		WHEN 1 THEN '(外)祖母' 
		    		WHEN 0 THEN '(外)祖父' 
		    	END
			ELSE max(RELATIONTOAPPNT) 
		END AS Role1,
		INSUREDNO AS IndID2,
		max(INSUREDNAME) AS Name2,
		max(insuredsex) AS sex2,
		max(INSUREDIDNO) AS IDCard2,
		min(CUSTOMGETPOLDATE) AS RSSTime,
		CASE max(RELATIONTOAPPNT) 
			WHEN '子女' 
				THEN CASE max(INSUREDSEX) 
					WHEN 1 THEN '女儿' 
					WHEN 0 THEN '儿子' 
				END
		    WHEN '父母' 
		    	THEN CASE max(INSUREDSEX) 
		    		WHEN 1 THEN '母亲' 
		    		WHEN 0 THEN '父亲' 
		    	END
		    WHEN '配偶' 
		    	THEN CASE max(INSUREDSEX) 
		    		WHEN 1 THEN '妻子' 
		    		WHEN 0 THEN '丈夫' 
		    	END
		    WHEN '祖父母、外祖父母' 
		    	THEN CASE max(INSUREDSEX) 
		    		WHEN 1 THEN '(外)祖母' 
		    		WHEN 0 THEN '(外)祖父' 
		    	END
		    WHEN '祖孙、外祖孙' 
		    	THEN CASE max(INSUREDSEX) 
		    		WHEN 1 THEN '(外)孙女' 
		    		WHEN 0 THEN '(外)祖孙' 
		    	END
			ELSE max(RELATIONTOAPPNT) 
		END AS Role2
	FROM POLICY_INFORMATION 
	WHERE RELATIONTOAPPNT<>'本人' AND RELATIONTOAPPNT IS NOT NULL
	GROUP BY CUSTOMERNO,INSUREDNO;
	

	--更新临时表的indid1 indid2
	UPDATE shanglifeecif.indrelationship_tmp a set indid1 = 
		(SELECT b.indid FROM (select 
			indid,row_number() over(partition by scustid) rn ,SCustID
		from shanglifeecif.individual) b where a.SCustID1 = b.SCustID AND b.rn = 1);
	UPDATE shanglifeecif.indrelationship_tmp a set indid2 = 
		(SELECT b.indid FROM (select 
			indid,row_number() over(partition by scustid) rn ,SCustID
		from shanglifeecif.individual) b where a.SCustID2 = b.SCustID AND b.rn = 1);

END;

/
CREATE OR REPLACE PROCEDURE shanglifeecif.init_indrelationship( ) 
IS
DECLARE
BEGIN
	
	DELETE FROM shanglifeecif.indrelationship;
	--插入数据
	INSERT INTO shanglifeecif.indrelationship(
		IRSID, --'个体关系ID' 
		RSType, --'关系类型 其他、配偶、祖父母、外祖父母、祖孙、外祖孙、本人、父母子女' ,
		IndID1, --'个体1' ,
		Name1, --个体1名称
	
		IDCard1, --个体1证件号码
		Role1,--个体1角色 父亲、母亲、儿子、女儿、妻子、丈夫
		IndID2,--个体2
		Name2,--个体2名称	sex2,--个体2手机
		IDCard2,--个体2证件号码
		Role2,--个体2角色 父亲、母亲、儿子、女儿、妻子、丈夫
		RSSTime,--开始时间
		CREATED_BY,--创建人
		CREATED_TIME--
		)
	SELECT 
		id,
		RSType,
		indid1,
		Name1,
		IDCard1,
		Role1,
		indid2,
		Name2,
		IDCard2,
		Role2,
		RSSTime,
		'admin',
		sysdate
	FROM shanglifeecif.indrelationship_tmp where indid1 is not null and indid2 is not null

	
END;
/
BEGIN
	shanglifeecif.init_indrelationship_tmp();
	shanglifeecif.init_indrelationship();
end