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

CREATE OR REPLACE PROCEDURE shanglifeecif.data_cleaning() -- 创建主存储过程
IS 
DECLARE
BEGIN
	
	--姓名     sametype:3
	  INSERT INTO shanglifeecif.samecustomer(
  		sid,
  		scustid,
  		name,
  		gender,
  		birthday,
  		idcard,
  		OtherIdNumber,
  		sameType
) 
SELECT 
	reflect("java.util.UUID", "randomUUID"),
	t.customer_id,
	t.customer_name,
	t.gender,
	to_char(t.birthdate,"yyyy-MM-dd") birthdate,
	CASE t.id_type WHEN '0' THEN t.id_no END AS idcard,
	CASE  WHEN t.id_type <> '0' THEN t.id_no END AS otherIdnumber,
	3
FROM dsj.t_customer_class t WHERE t.customer_name RLIKE '^.*[!$^|\=`]{1,}.*$' OR t.customer_name RLIKE '^.*[0-9]{1,}.*$' ;

	--性别     sametype:4
	INSERT INTO shanglifeecif.samecustomer(
  		sid,
  		scustid,
  		name,
  		gender,
  		birthday,
  		idcard,
  		OtherIdNumber,
  		sameType
) 
SELECT 
	reflect("java.util.UUID", "randomUUID"),
	t.customer_id,
	t.customer_name,
	t.gender,
	to_char(t.birthdate,"yyyy-MM-dd") birthdate,
	CASE t.id_type WHEN '0' THEN t.id_no END AS idcard,
	CASE  WHEN t.id_type <> '0' THEN t.id_no END AS otherIdnumber,
	4
FROM dsj.t_customer_class t WHERE t.gender IS NULL AND t.customer_id IN (SELECT p.customerno FROM dsj.policy_information p WHERE p.sex IS null );
	--证件号码     sametype:6
	INSERT INTO shanglifeecif.samecustomer(
  		sid,
  		scustid,
  		name,
  		gender,
  		birthday,
  		idcard,
  		sameType
) 
SELECT 
	reflect("java.util.UUID", "randomUUID"),
	si.scustid,
	si.name,
	si.gender,
	si.birthday,
	si.idcard,
	6
FROM shanglifeecif.individual si WHERE length(si.idcard)<18 AND si.custtype LIKE '%投保人%' AND si.idcard IS NOT null;

	--出生日期     sametype:7
	INSERT INTO shanglifeecif.samecustomer(
  		sid,
  		scustid,
  		name,
  		gender,
  		birthday,
  		idcard,
  		OtherIdNumber,
  		sameType
) 
SELECT 
	reflect("java.util.UUID", "randomUUID"),
	t.customer_id,
	t.customer_name,
	t.gender,
	to_char(t.birthdate,"yyyy-MM-dd") birthdate,
	CASE t.id_type WHEN '0' THEN t.id_no END AS idcard,
	CASE  WHEN t.id_type <> '0' THEN t.id_no END AS otherIdnumber,
	7
FROM dsj.t_customer_class t WHERE t.birthdate IS NULL AND t.customer_id IN (SELECT p.customerno FROM dsj.policy_information p WHERE p.birthday IS null UNION SELECT i.insuredno FROM dsj.policy_information i WHERE i.insuredbirthday IS null);

	--手机号     sametype:8
	INSERT INTO shanglifeecif.samecustomer(
  		sid,
  		scustid,
  		name,
  		gender,
  		birthday,
  		idcard,
  		OtherIdNumber,
  		mobile,
  		sameType
) 
SELECT 
	reflect("java.util.UUID", "randomUUID"),
	t.customer_id,
	t.customer_name,
	t.gender,
	to_char(t.birthdate,"yyyy-MM-dd") birthdate,
	CASE t.id_type WHEN '0' THEN t.id_no END AS idcard,
	CASE  WHEN t.id_type <> '0' THEN t.id_no END AS otherIdnumber,
	t.mobile,
	8
FROM dsj.t_customer_class t WHERE t.mobile NOT RLIKE '^1[0-9]{10}$' AND t.customer_id IN (SELECT p.customerno FROM dsj.policy_information p);

	--邮编     sametype:9
	INSERT INTO shanglifeecif.samecustomer(
  		sid,
  		scustid,
  		name,
  		gender,
  		birthday,
  		idcard,
  		zipcode,
  		sameType
) 
SELECT 
	reflect("java.util.UUID", "randomUUID"),
	si.scustid,
	si.name,
	si.gender,
	si.birthday,
	si.idcard,
	si.zipcode,
	9
FROM shanglifeecif.individual si WHERE (length(si.zipcode)>0 and length(si.zipcode)<6) OR length(si.zipcode)>6;
	--主被保险人性别异常数据     sametype:11
	INSERT INTO shanglifeecif.samecustomer(
  		sid,
  		scustid,
  		name,
  		gender,
  		birthday,
  		idcard,
  		OtherIdNumber,
  		sameType
) 
SELECT 
	reflect("java.util.UUID", "randomUUID"),
	t.customer_id,
	t.customer_name,
	t.gender,
	to_char(t.birthdate,"yyyy-MM-dd") birthdate,
	CASE t.id_type WHEN '0' THEN t.id_no END AS idcard,
	CASE  WHEN t.id_type <> '0' THEN t.id_no END AS otherIdnumber,
	11
FROM dsj.t_customer_class t  WHERE (t.gender IS NULL OR t.gender = '2') AND t.customer_id  IN (SELECT p.insuredno FROM dsj.policy_information p WHERE p.insuredsex IS null);

--邮箱     sametype:14
	INSERT INTO shanglifeecif.samecustomer(
  		sid,
  		scustid,
  		name,
  		gender,
  		birthday,
  		idcard,
  		OtherIdNumber,
  		email,
  		sameType
) 
SELECT 
	reflect("java.util.UUID", "randomUUID"),
	t.customer_id,
	t.customer_name,
	t.gender,
	to_char(t.birthdate,"yyyy-MM-dd") birthdate,
	CASE t.id_type WHEN '0' THEN t.id_no END AS idcard,
	CASE  WHEN t.id_type <> '0' THEN t.id_no END AS otherIdnumber,
	t.email,
	14
FROM dsj.t_customer_class t  WHERE t.email not RLIKE '^([a-zA-Z0-9._%-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,4})*$' AND t.customer_id IN (SELECT p.customerno FROM dsj.policy_information p UNION SELECT pi.insuredno FROM dsj.policy_information pi);

--主被保险人手机异常数据     sametype:16
	INSERT INTO shanglifeecif.samecustomer(
  		sid,
  		scustid,
  		name,
  		gender,
  		birthday,
  		idcard,
  		OtherIdNumber,
  		mobile,
  		sameType
) 
SELECT 
	reflect("java.util.UUID", "randomUUID"),
	t.customer_id,
	t.customer_name,
	t.gender,
	to_char(t.birthdate,"yyyy-MM-dd") birthdate,
	CASE t.id_type WHEN '0' THEN t.id_no END AS idcard,
	CASE  WHEN t.id_type <> '0' THEN t.id_no END AS otherIdnumber,
	t.mobile,
	16
FROM dsj.t_customer_class t  WHERE t.mobile NOT RLIKE '^1[0-9]{10}$' AND t.customer_id IN (SELECT p.insuredno FROM dsj.policy_information p);
--主被保险人证件号码     sametype:17
INSERT INTO shanglifeecif.samecustomer(
  		sid,
  		scustid,
  		name,
  		gender,
  		birthday,
  		idcard,
  		sameType
) 
SELECT 
	reflect("java.util.UUID", "randomUUID"),
	si.scustid,
	si.name,
	si.gender,
	si.birthday,
	si.idcard,
	17
FROM shanglifeecif.individual si WHERE length(si.idcard)<18 AND si.custtype LIKE '%被保人%' AND si.idcard IS NOT null;

	--业务员没错     sametype:18
	INSERT INTO shanglifeecif.samecustomer(
  		sid,
  		scustid,
  		name,
  		gender,
  		birthday,
  		idcard,
  		OtherIdNumber,
  		agentname,
  		sameType
) 
SELECT 
	reflect("java.util.UUID", "randomUUID"),
	t.customer_id,
	t.customer_name,
	t.gender,
	to_char(t.birthdate,"yyyy-MM-dd") birthdate,
	CASE t.id_type WHEN '0' THEN t.id_no END AS idcard,
	CASE  WHEN t.id_type <> '0' THEN t.id_no END AS otherIdnumber,
	p.agentname,
	18
FROM dsj.t_customer_class t,dsj.policy_information p WHERE t.customer_id = p.customerno and p.agentname not RLIKE '^[\u4e00-\u9fa5]|[^\x00-\xff]$';
--业绩归属     sametype:19
	INSERT INTO shanglifeecif.samecustomer(
  		sid,
  		scustid,
  		name,
  		gender,
  		birthday,
  		idcard,
  		OtherIdNumber,
  		salecomname,
  		sameType
) 
SELECT 
	reflect("java.util.UUID", "randomUUID"),
	t.customer_id,
	t.customer_name,
	t.gender,
	to_char(t.birthdate,"yyyy-MM-dd") birthdate,
	CASE t.id_type WHEN '0' THEN t.id_no END AS idcard,
	CASE  WHEN t.id_type <> '0' THEN t.id_no END AS otherIdnumber,
	p.salecomname,
	19
FROM dsj.t_customer_class t,dsj.policy_information p WHERE t.customer_id = p.customerno and  p.salecomname not RLIKE '^[\u4e00-\u9fa5]|[^\x00-\xff]$';
dbms_output.put_line('data_cleaning函数跑批完成！');
END;