ShangHaiLifeECIF/sql-achievement/存储过程/客户信息koucx.sql

--初始化个人信息
CREATE OR REPLACE PROCEDURE shanglifeecif.initIndividual() IS
DECLARE
indidcount INT;
BEGIN
	--清空数据
	DELETE FROM shanglifeecif.individual;
	indidcount :=1;
	--初始化非本人身份证客户信息
	INSERT INTO shanglifeecif.individual(
					indid,
					CustID,
					scustid,
					name,
					gender,
					birthday,
					idcard,
					Passport,
					Dlicense,
					CREATED_BY,
					CREATED_TIME) 
	SELECT row_number()over(),"CP" || lpad(row_number()over(),9,'0'),pi.CUSTOMERNO,pi.NAME,pi.SEX,to_char(pi.BIRTHDAY,'yyyy-MM-dd HH:mm:ss'),pi.idcard,pi.Passport,pi.Dlicense,'koucx',sysdate() FROM(
		SELECT CUSTOMERNO,NAME,SEX,BIRTHDAY,
			max(CASE IDTYPE WHEN '0' THEN IDNO ELSE null END) idcard,
			max(CASE IDTYPE WHEN '1' THEN IDNO ELSE NULL END) Passport,
			max(CASE IDTYPE WHEN '3' THEN IDNO ELSE NULL END) Dlicense
			FROM shanghailifeecif.policy_information WHERE IDNO IS NOT NULL GROUP BY CUSTOMERNO,NAME,SEX,BIRTHDAY,IDTYPE,IDNO
		UNION 
		SELECT INSUREDNO AS CUSTOMERNO,INSUREDNAME AS NAME,INSUREDSEX AS SEX,INSUREDBIRTHDAY AS BIRTHDAY,
			max(CASE INSUREDIDTYPE WHEN '0' THEN insuredidno ELSE null END) idcard,
			max(CASE INSUREDIDTYPE WHEN '1' THEN insuredidno ELSE NULL END) Passport,
			max(CASE INSUREDIDTYPE WHEN '3' THEN insuredidno ELSE NULL END) Dlicense
		FROM shanghailifeecif.policy_information WHERE INSUREDIDNO IS NOT NULL GROUP BY INSUREDNO,INSUREDNAME,INSUREDSEX,INSUREDBIRTHDAY,INSUREDIDTYPE,insuredidno
	) AS pi;
	
	/*SELECT count(*) INTO indidcount FROM shanglifeecif.individual; 
	--初始化非本人护照客户信息
	INSERT INTO shanglifeecif.individual(
					indid,
					CustID,
					scustid,
					name,
					gender,
					birthday,
					Passport,
					CREATED_BY,
					CREATED_TIME) 
	SELECT row_number()over()+indidcount+1,"CP" || lpad(row_number()over()+indidcount,9,'0'),pi.CUSTOMERNO,pi.NAME,pi.SEX,to_char(pi.BIRTHDAY,'yyyy-MM-dd HH:mm:ss'),pi.IDNO,'koucx',sysdate() FROM(
		SELECT CUSTOMERNO,NAME,SEX,BIRTHDAY,IDTYPE,IDNO FROM shanghailifeecif.policy_information WHERE IDNO IS NOT NULL AND IDTYPE = 1 AND RELATIONTOAPPNT<>'本人' GROUP BY CUSTOMERNO,NAME,SEX,BIRTHDAY,IDTYPE,IDNO
		UNION 
		SELECT INSUREDNO AS CUSTOMERNO,INSUREDNAME AS NAME,INSUREDSEX AS SEX,INSUREDBIRTHDAY AS BIRTHDAY,INSUREDIDTYPE AS IDTYPE,insuredidno AS IDNO FROM shanghailifeecif.policy_information WHERE INSUREDIDNO IS NOT NULL AND INSUREDIDTYPE = 1 AND RELATIONTOAPPNT<>'本人' GROUP BY INSUREDNO,INSUREDNAME,INSUREDSEX,INSUREDBIRTHDAY,INSUREDIDTYPE,insuredidno
	) AS pi;
	
	SELECT count(*) INTO indidcount FROM shanglifeecif.individual; 
	--初始化非本人驾驶证客户信息
	INSERT INTO shanglifeecif.individual(
					indid,
					CustID,
					scustid,
					name,
					gender,
					birthday,
					Dlicense,
					CREATED_BY,
					CREATED_TIME) 
	SELECT row_number()over()+indidcount+1,"CP" || lpad(row_number()over()+indidcount,9,'0'),pi.CUSTOMERNO,pi.NAME,pi.SEX,to_char(pi.BIRTHDAY,'yyyy-MM-dd HH:mm:ss'),pi.IDNO,'koucx',sysdate() FROM(
		SELECT CUSTOMERNO,NAME,SEX,BIRTHDAY,
			max(CASE IDTYPE WHEN '0' THEN IDNO ELSE null END) idcard,
			max(CASE IDTYPE WHEN '1' THEN IDNO ELSE NULL END) Passport,
			max(CASE IDTYPE WHEN '3' THEN IDNO ELSE NULL END) Dlicense
			FROM shanghailifeecif.policy_information WHERE IDNO IS NOT NULL AND IDTYPE = 3 AND RELATIONTOAPPNT<>'本人' GROUP BY CUSTOMERNO,NAME,SEX,BIRTHDAY,IDTYPE,IDNO
		UNION 
		SELECT INSUREDNO AS CUSTOMERNO,INSUREDNAME AS NAME,INSUREDSEX AS SEX,INSUREDBIRTHDAY AS BIRTHDAY,INSUREDIDTYPE AS IDTYPE,insuredidno AS IDNO FROM shanghailifeecif.policy_information WHERE INSUREDIDNO IS NOT NULL AND INSUREDIDTYPE = 3 AND RELATIONTOAPPNT<>'本人' GROUP BY INSUREDNO,INSUREDNAME,INSUREDSEX,INSUREDBIRTHDAY,INSUREDIDTYPE,insuredidno
	) AS pi;
	
	
	SELECT count(*) INTO indidcount FROM shanglifeecif.individual; 
	--初始化单独处理本人客户信息
	INSERT INTO shanglifeecif.individual(
					indid,
					CustID,
					scustid,
					name,
					gender,
					birthday,
					idcard,
					Passport,
					Dlicense,
					CREATED_BY,
					CREATED_TIME) 
	SELECT row_number()over()+indidcount+1,"CP" || lpad(row_number()over()+indidcount,9,'0'),pi.CUSTOMERNO,pi.NAME,pi.SEX,to_char(pi.BIRTHDAY,'yyyy-MM-dd HH:mm:ss'),pi.idcard,pi.Passport,pi.Dlicense,'koucx',sysdate() FROM(
		SELECT CUSTOMERNO,NAME,SEX,BIRTHDAY,
		max(CASE IDTYPE WHEN '0' THEN IDNO ELSE CASE INSUREDIDTYPE WHEN '0' THEN insuredidno ELSE null END END) idcard,
		max(CASE IDTYPE WHEN '1' THEN IDNO ELSE CASE INSUREDIDTYPE WHEN '1' THEN insuredidno ELSE NULL END END) Passport,
		max(CASE IDTYPE WHEN '3' THEN IDNO ELSE CASE INSUREDIDTYPE WHEN '3' THEN insuredidno ELSE NULL END END) Dlicense
		 FROM shanghailifeecif.policy_information WHERE IDNO IS NOT NULL AND RELATIONTOAPPNT ='本人' GROUP BY CUSTOMERNO,NAME,SEX,BIRTHDAY,IDTYPE,IDNO
	) AS pi;*/
	
	EXCEPTION
			WHEN HIVE_EXCEPTION THEN 
				 INSERT INTO shanglifeecif.exception_log(log_code,log_msg,log_time) VALUES (sqlcode(),sqlerrm(),sysdate());
			WHEN Others THEN
				INSERT INTO shanglifeecif.exception_log(log_code,log_msg,log_time) VALUES (sqlcode(),sqlerrm(),sysdate());
END	;