dgtis-modules-data/一期存储过程（最终版）/init_indrelationship_main2.sql

CREATE OR REPLACE PROCEDURE shanglifeecif.init_indrelationship_main2( ) 
IS
DECLARE
	s_count int
BEGIN
	
	
	-- 完善被保人关系
/**	*/
	
	insert into shanglifeecif.IndRelationShip(
		irsid,
		RSType,
		IndID1,
		Name1,
		IDCard1,
		Role1,
		IndID2,
		Name2,
		IDCard2,
		Role2
	)SELECT reflect("java.util.UUID", "randomUUID"),
		CASE RSType 
			WHEN '父母' THEN '子女'
			WHEN '子女' THEN '父母'
			WHEN '祖孙、外祖孙' THEN '祖父母、外祖父母'
			WHEN '祖父母、外祖父母' THEN '祖孙、外祖孙'
			WHEN '配偶' THEN '配偶'
			ELSE RSType
		END,
		IndID2,
		Name2,
		IDCard2,
		Role2,
		IndID1,	
		Name1,
		IDCard1,
		Role1
FROM shanglifeecif.IndRelationShip sis ;
	dbms_output.put_line('init_indrelationship_main函数跑批完成！');
	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;