CREATE OR REPLACE PROCEDURE shanglifeecif.same_customer() -- 创建主存储过程
IS 
DECLARE
	scount int
BEGIN
 DELETE FROM shanglifeecif.samecustomer;

 INSERT INTO shanglifeecif.samecustomer(
  		sid,
  		scustid,
  		name,
  		gender,
  		birthday,
  		idcard,
  		Passport,
  		Dlicense,
  		OtherIdNumber,
  		sameType
) 
SELECT 
	reflect("java.util.UUID", "randomUUID"),
	sc1.scustid,
	sc1.name,
	sc1.gender,
	sc1.birthday,
	CASE sc1.idtype WHEN '0' THEN sc1.idcard END AS idcard,
	CASE sc1.idtype WHEN '1' THEN sc1.idcard END AS passport,
	CASE sc1.idtype WHEN '3' THEN sc1.idcard END AS dlicense,
	CASE  WHEN sc1.idtype <> '0' AND sc1.idtype <> '1' AND sc1.idtype <> '3' THEN sc1.idcard END AS otherIdnumber,
	0
FROM shanglifeecif.customertotaltemp sc1 
WHERE sc1.mobile IS NOT NULL AND EXISTS (SELECT 1 FROM shanglifeecif.customertotaltemp sc2 WHERE sc1.scustid != sc2.scustid 
		AND sc1.name = sc2.name AND sc1.gender = sc2.gender AND sc1.idtype = sc2.idtype AND sc1.mobile = sc2.mobile);
		
	--三要素疑似相同指姓名、性别、出生日期一致   1
	INSERT INTO shanglifeecif.samecustomer(
  		sid,
  		scustid,
  		name,
  		gender,
  		birthday,
  		idcard,
  		Passport,
  		Dlicense,
  		OtherIdNumber,
  		sameType

) 
SELECT 
	reflect("java.util.UUID", "randomUUID"),
	sc1.scustid,
	sc1.name,
	sc1.gender,
	sc1.birthday,
	CASE sc1.idtype WHEN '0' THEN sc1.idcard END AS idcard,
	CASE sc1.idtype WHEN '1' THEN sc1.idcard END AS passport,
	CASE sc1.idtype WHEN '3' THEN sc1.idcard END AS dlicense,
	CASE  WHEN sc1.idtype <> '0' AND sc1.idtype <> '1' AND sc1.idtype <> '3' THEN sc1.idcard END AS otherIdnumber,
	1
FROM shanglifeecif.customertotaltemp sc1 
WHERE EXISTS(SELECT 1 FROM shanglifeecif.customertotaltemp sc2 WHERE sc2.scustid!=sc1.scustid AND sc1.name = sc2.name AND sc1.gender = sc2.gender AND sc1.birthday = sc2.birthday )
ORDER BY sc1.name,sc1.gender,sc1.birthday
	--两要素疑似相同指姓名、证件号码一致 2
	INSERT INTO shanglifeecif.samecustomer(
  		sid,
  		scustid,
  		name,
  		gender,
  		birthday,
  		idcard,
  		Passport,
  		Dlicense,
  		OtherIdNumber,
  		sameType

) 
SELECT 
	reflect("java.util.UUID", "randomUUID"),
	sc1.scustid,
	sc1.name,
	sc1.gender,
	sc1.birthday,
	CASE sc1.idtype WHEN '0' THEN sc1.idcard END AS idcard,
	CASE sc1.idtype WHEN '1' THEN sc1.idcard END AS passport,
	CASE sc1.idtype WHEN '3' THEN sc1.idcard END AS dlicense,
	CASE  WHEN sc1.idtype <> '0' AND sc1.idtype <> '1' AND sc1.idtype <> '3' THEN sc1.idcard END AS otherIdnumber,
	2
FROM shanglifeecif.customertotaltemp sc1 

WHERE sc1.idcard is not null and  EXISTS (SELECT 1 FROM shanglifeecif.customertotaltemp sc2 WHERE sc2.scustid!=sc1.scustid AND sc1.name = sc2.name AND sc1.idcard = sc2.idcard);

dbms_output.put_line('same_customer函数跑批完成！');	
END;