123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293 |
- 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;
|