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

CREATE OR REPLACE PROCEDURE shanglifeecif.init_all_individual() -- 初始化所有客户信息
IS    
BEGIN
	
	--查询出此次处理的数据并出表中
	 insert into shanglifeecif.individual (
		indid,
		custid,
		scustid,
	 	name,
	 	gender,
	 	birthday,
	 	idcard,
	 	Passport,
  		Dlicense,
  		OtherIdNumber,
	 	created_time,
	 	created_by
	) SELECT 
	row_number()OVER(ORDER BY sc.scustid),
	'CP'||lpad(row_number()over(ORDER BY sc.scustid),10,'0'),	
	sc.scustid,
	sc.name,
	sc.gender,
	sc.birthday,
	CASE sc.idtype WHEN '0' THEN sc.idcard END AS idcard,
	CASE sc.idtype WHEN '1' THEN sc.idcard END AS passport,
	CASE sc.idtype WHEN '3' THEN sc.idcard END AS dlicense,
	CASE  WHEN sc.idtype <> '0' AND sc.idtype <> '1' AND sc.idtype <> '3' THEN sc.idcard END AS otherIdnumber,
	sysdate,
	'admin'
FROM shanglifeecif.customertotaltemp sc ORDER BY sc.scustid;
	
		dbms_output.put_line('init_all_individual函数跑批完成！');
		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;