整体目标
以规范的形式集中所有的客户信息数据，提高对客户资源的管理能力，实施差异化客户服务和业务管理的需求，达到经营客户的目标。
功能模块
客户数据清洗转换
数据清洗规则重点针对个人客户关键信息项设计，以保证客户的识别和归并的准确性。
1 个人客户的关键信息项包括：证件类型、证件号码、个人姓名、联系方式（手机、固定电话、电子邮件）
2 除客户关键信息项之外，还包括对客户其他重要信息项的清洗规则。
promptprompt Creating function FUNC_CARDNUM_STANDARDprompt =======================================promptCREATE OR REPLACE FUNCTION CICECIF_DEV.FUNC_CARDNUM_STANDARD(ICARD VARCHAR2) RETURN VARCHAR2 IS -- Purpose : 从原有的15位身份证号转换成新的18位 TYPE TIARRAY IS TABLE OF INTEGER; TYPE TCARRAY IS TABLE OF CHAR(1); RESULT VARCHAR2(100); W TIARRAY; A TCARRAY; S INTEGER;BEGIN IF LENGTH(ICARD) <> 15 OR ICARD IS NULL THEN RESULT := ICARD; ELSE W := TIARRAY(7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2, 1); A := TCARRAY('1', '0', 'X', '9', '8', '7', '6', '5', '4', '3', '2'); RESULT := SUBSTR(ICARD, 1, 6) || '19' || SUBSTR(ICARD, 7, 9); S := 0; BEGIN FOR I IN 1 .. 17 LOOP S := S + TO_NUMBER(SUBSTR(RESULT, I, 1)) * W(I); END LOOP; EXCEPTION WHEN OTHERS THEN RETURN ''; END; S := S MOD 11; RESULT := RESULT || A(S + 1); END IF; RETURN(RESULT);END FUNC_CARDNUM_STANDARD;/promptprompt Creating function FUNC_CHECKNUM_EXT2prompt ====================================promptCREATE OR REPLACE FUNCTION CICECIF_DEV.FUNC_CHECKNUM_EXT2(CARDTYPE VARCHAR2, IDCARD VARCHAR2 /*, sex VARCHAR2*/)/*--------------------------------这个函数有点小问题 idcard 身份证号码 sex 性别 1男2女或'' */ /* 验证新版和旧版身份证的合法性 合法返回1 不合法返回0*/ RETURN NUMBER IS CHECKSTR CHAR(11) := '10X98765432'; SUMCODE NUMBER(20); CARDYEAR NUMBER(4); CARDMONTH NUMBER(2); CARDDAY NUMBER(2); -- SEXCODE NUMBER(1); /*"北京11天津12河北13河北14内蒙古15辽宁21吉林22黑龙江23上海31江苏32浙江33安徽34福建35江西36山东37河南41湖北42 湖南43广东44广西45海南46重庆50四川51贵州52云南53西藏54陕西61甘肃62青海63宁夏64新疆65台湾71香港81澳门82国外91"*/ CHECKPROCODE VARCHAR2(300) := '11|12|13|14|15|21|22|23|31|32|33|34|35|36|37|41|42|43|44|45|46|50|51|52|53|54|61|62|63|64|65|71|81|82|91';BEGIN /*验证不为空*/ IF CARDTYPE = '身份证' THEN IF (IDCARD IS NULL) THEN BEGIN RETURN 0; END; END IF; /*验证长度是否正确*/ IF (LENGTH(IDCARD) != 18 AND LENGTH(IDCARD) != 15) THEN BEGIN RETURN 0; END; END IF; IF (LENGTH(replace(IDCARD,chr(10),'')) = 18) THEN BEGIN /*如果是新版身份证*/ IF (NOT REGEXP_LIKE(SUBSTR(IDCARD, 1, 17), '^[[:digit:]]+$')) THEN -- IF (REGEXP_LIKE(IDCARD, '[^0-9][0-9]*.$')) THEN /*验证身份证前17位是否全是数字*/ BEGIN RETURN 0; END; END IF; /*校验码*/ SUMCODE := TO_NUMBER(SUBSTR(IDCARD, 1, 1)) * 7 + TO_NUMBER(SUBSTR(IDCARD, 2, 1)) * 9 + TO_NUMBER(SUBSTR(IDCARD, 3, 1)) * 10 + TO_NUMBER(SUBSTR(IDCARD, 4, 1)) * 5 + TO_NUMBER(SUBSTR(IDCARD, 5, 1)) * 8 + TO_NUMBER(SUBSTR(IDCARD, 6, 1)) * 4 + TO_NUMBER(SUBSTR(IDCARD, 7, 1)) * 2 + TO_NUMBER(SUBSTR(IDCARD, 8, 1)) * 1 + TO_NUMBER(SUBSTR(IDCARD, 9, 1)) * 6 + TO_NUMBER(SUBSTR(IDCARD, 10, 1)) * 3 + TO_NUMBER(SUBSTR(IDCARD, 11, 1)) * 7 + TO_NUMBER(SUBSTR(IDCARD, 12, 1)) * 9 + TO_NUMBER(SUBSTR(IDCARD, 13, 1)) * 10 + TO_NUMBER(SUBSTR(IDCARD, 14, 1)) * 5 + TO_NUMBER(SUBSTR(IDCARD, 15, 1)) * 8 + TO_NUMBER(SUBSTR(IDCARD, 16, 1)) * 4 + TO_NUMBER(SUBSTR(IDCARD, 17, 1)) * 2; IF (SUBSTR(CHECKSTR, (SUMCODE MOD 11) + 1, 1) != SUBSTR(UPPER(IDCARD), 18, 1)) THEN BEGIN RETURN 0; END; END IF; CARDYEAR := TO_NUMBER(SUBSTR(IDCARD, 7, 4)); /*取年*/ CARDMONTH := TO_NUMBER(SUBSTR(IDCARD, 11, 2)); /*取月*/ CARDDAY := TO_NUMBER(SUBSTR(IDCARD, 13, 2)); /*取日*/ --SEXCODE := TO_NUMBER(SUBSTR(IDCARD, 17, 1)); END; ELSE BEGIN /*如果是旧版身份证*/ IF (NOT REGEXP_LIKE(IDCARD, '^[[:digit:]]+$')) THEN /*验证身份证是否全是数字*/ BEGIN RETURN 0; END; END IF; CARDYEAR := TO_NUMBER('19' || SUBSTR(IDCARD, 7, 2)); /*取年*/ CARDMONTH := TO_NUMBER(SUBSTR(IDCARD, 9, 2)); /*取月*/ CARDDAY := TO_NUMBER(SUBSTR(IDCARD, 11, 2)); /*取日*/ -- SEXCODE := TO_NUMBER(SUBSTR(IDCARD, 15, 1)); END; END IF; /*验证省行政代码是否合法*/ IF (INSTR(CHECKPROCODE, SUBSTR(IDCARD, 0, 2)) <= 0) THEN BEGIN RETURN 0; END; END IF; /* \*验证性别*\ IF ((sex = '1' AND (sexCode MOD 2) = 0) OR (sex = '2' AND (sexCode MOD 2) != 0)) THEN BEGIN RETURN 0; END; END IF; */ /*验证出生年份是否正确*/ /*如果小于1900或大于今年都不正确*/ IF (CARDYEAR < 1900 OR CARDYEAR > TO_NUMBER(TO_CHAR(SYSDATE, 'yyyy'))) THEN BEGIN RETURN 0; END; END IF; IF EXTRACT(YEAR FROM SYSDATE) - CARDYEAR >= 100 THEN BEGIN RETURN 0; END; END IF; IF (CARDMONTH > 12 OR CARDMONTH <= 0) THEN /*验证月份是否正确*/ BEGIN RETURN 0; END; END IF; IF (IDCARD) IN ('111111111111111', '111111111111111111') THEN BEGIN RETURN 0; END; END IF; IF (CARDMONTH = 2 AND ((CARDYEAR MOD 4 = 0 AND CARDYEAR MOD 100 != 0) OR (CARDYEAR MOD 400 = 0))) THEN /*如果月份是2月且是润年*/ BEGIN IF (CARDDAY > 29 OR CARDDAY = 0) THEN /*验证日期是否正确*/ BEGIN RETURN 0; END; END IF; END; ELSE BEGIN IF (CARDDAY = 0) THEN BEGIN RETURN 0; END; END IF; IF ((CARDMONTH = 1 OR CARDMONTH = 3 OR CARDMONTH = 5 OR CARDMONTH = 7 OR CARDMONTH = 8 OR CARDMONTH = 10 OR CARDMONTH = 12) AND CARDDAY > 31) THEN /*验证日期是否正确*/ BEGIN RETURN 0; END; END IF; IF ((CARDMONTH = 4 OR CARDMONTH = 6 OR CARDMONTH = 9 OR CARDMONTH = 11) AND CARDDAY > 30) THEN /*验证日期是否正确*/ BEGIN RETURN 0; END; END IF; IF (CARDMONTH = 2 AND CARDDAY > 28) THEN /*验证日期是否正确*/ BEGIN RETURN 0; END; END IF; END; END IF; ELSE IF REGEXP_LIKE(IDCARD, '[1]{9,}') THEN RETURN 0; END IF; IF NOT REGEXP_LIKE(IDCARD, '[0]|[6-9]') THEN RETURN 0; END IF; END IF; RETURN 1; /*EXCEPTION WHEN OTHERS THEN \*如果你希望看到报错, 就把下面的注释行打开*\ raise;*/END;/promptprompt Creating function FUNC_CHECKBIRTHDAYprompt ====================================promptCREATE OR REPLACE FUNCTION CICECIF_DEV.FUNC_CHECKBIRTHDAY(BIRTHDAY IN VARCHAR2, IDENTIFYNUMBER IN VARCHAR2) RETURN CHAR IS --RESULT date :=''; RESULT varchar2(20) :='';BEGIN/* IF BIRTHDAY IS NOT NULL THEN RESULT := to_date(BIRTHDAY, 'yyyy-mm-dd'); END IF;*/ IF /*BIRTHDAY IS NULL AND*/ length(IDENTIFYNUMBER) = 18 AND FUNC_CHECKNUM_EXT2('身份证',IDENTIFYNUMBER) = 1 THEN RETURN SUBSTR(IDENTIFYNUMBER, 7, 8); END IF; IF/* BIRTHDAY IS NULL AND*/ LENGTH(IDENTIFYNUMBER) = 15 AND FUNC_CHECKNUM_EXT2('身份证',IDENTIFYNUMBER) = 1 THEN RETURN '19' || SUBSTR(IDENTIFYNUMBER, 7, 6); END IF; IF BIRTHDAY IS NULL AND FUNC_CHECKNUM_EXT2('身份证',IDENTIFYNUMBER) = 0 THEN RETURN ''; else RETURN BIRTHDAY; END IF; RETURN RESULT;END;/*CREATE OR REPLACE FUNCTION FUNC_CHECKBIRTHDAY(BIRTHDAY IN VARCHAR2, IDENTIFYNUMBER IN VARCHAR2) RETURN CHAR IS RESULT date := to_date('', 'yyyy-mm-dd');BEGIN\* IF BIRTHDAY IS NOT NULL THEN RESULT := to_date(BIRTHDAY, 'yyyy-mm-dd'); END IF;*\ IF\* BIRTHDAY IS NULL AND*\ LENGTH(IDENTIFYNUMBER) = 15 AND FUNC_CHECKNUM_EXT2('身份证',IDENTIFYNUMBER) = 1 THEN RESULT := to_date('19' || SUBSTR(IDENTIFYNUMBER, 7, 6), 'yyyy-mm-dd'); END IF; IF \*BIRTHDAY IS NULL AND*\ length(IDENTIFYNUMBER) = 18 AND FUNC_CHECKNUM_EXT2('身份证',IDENTIFYNUMBER) = 1 THEN RESULT := to_date(SUBSTR(IDENTIFYNUMBER, 7, 8), 'yyyy-mm-dd'); END IF; IF BIRTHDAY IS NULL AND FUNC_CHECKNUM_EXT2('身份证',IDENTIFYNUMBER) = 0 THEN RESULT := to_date('', 'yyyy-mm-dd'); END IF; RETURN RESULT;END;*//promptprompt Creating function FUNC_CHECKDATEprompt ================================promptCREATE OR REPLACE FUNCTION CICECIF_DEV.FUNC_CHECKDATE(str_date VARCHAR2) RETURN NUMBER AS v_date DATE;BEGIN BEGIN v_date := to_date(str_date, 'yyyy-mm-dd hh24:mi:ss');--格式1 yyyy-mm-dd hh24:mi:ss RETURN 1; EXCEPTION WHEN OTHERS THEN NULL; END; BEGIN v_date := to_date(str_date, 'yy-mon-dd hh24:mi:ss');--格式2 yy-mon-dd hh24:mi:ss RETURN 2; EXCEPTION WHEN OTHERS THEN NULL; END; RETURN 3; ---非时间格式END;/promptprompt Creating function FUNC_CHECKDATESprompt =================================promptCREATE OR REPLACE FUNCTION CICECIF_DEV.FUNC_CHECKDATES(CUS_DATE IN VARCHAR2) RETURN CHAR ISBEGINIF SUBSTR(CUS_DATE,1,4) not between 1900 and 2030 then return 0; end if;IF SUBSTR(CUS_DATE,6,2) not between 1 and 12 then return 0; end if; IF SUBSTR(CUS_DATE,9,2) not between 1 and 31 then return 0; end if; return 1;END;/promptprompt Creating function FUNC_CHECKEMAIL_EXTprompt =====================================promptCREATE OR REPLACE FUNCTION CICECIF_DEV.FUNC_CHECKEMAIL_EXT(VI_NUM IN VARCHAR2) RETURN CHAR IS VI_COUNT INTEGER; TI CHAR(1); VI CHAR(1); --YI CHAR(1); --PI CHAR(1); /* 判断手机号是否连续增长或连续减少 */BEGIN VI_COUNT := 0; FOR I IN 1 .. LENGTH(VI_NUM) - 1 LOOP VI := SUBSTR(VI_NUM, I, 1); TI := SUBSTR(VI_NUM, I + 1, 1); --YI := substr(vi_num, i + 2, 1); --PI := substr(vi_num, i + 3, 1); IF (VI = (TI - 1) OR VI = (TI + 1)) /*TI - VI = YI - TI and TI - VI <> 0 and PI - YI = YI - TI and PI - YI <> 0*/ THEN VI_COUNT := VI_COUNT + 1; END IF; END LOOP; IF VI_COUNT >= LENGTH(VI_NUM) - 1 THEN RETURN 0; --格式错误 ELSE RETURN 1; --格式正确 END IF;END;/promptprompt Creating function FUNC_CHECKNUM_EXTprompt ===================================promptCREATE OR REPLACE FUNCTION CICECIF_DEV.FUNC_CHECKNUM_EXT(CARDTYPE VARCHAR2, IDCARD VARCHAR2 /*, sex VARCHAR2*/)/* idcard 身份证号码 sex 性别 1男2女或'' */ /* 验证新版和旧版身份证的合法性 合法返回1 不合法返回0*/ RETURN NUMBER IS CHECKSTR CHAR(11) := '10X98765432'; SUMCODE NUMBER(20); CARDYEAR NUMBER(4); CARDMONTH NUMBER(2); CARDDAY NUMBER(2); -- SEXCODE NUMBER(1); /*"北京11天津12河北13河北14内蒙古15辽宁21吉林22黑龙江23上海31江苏32浙江33安徽34福建35江西36山东37河南41湖北42 湖南43广东44广西45海南46重庆50四川51贵州52云南53西藏54陕西61甘肃62青海63宁夏64新疆65台湾71香港81澳门82国外91"*/ CHECKPROCODE VARCHAR2(300) := '11|12|13|14|15|21|22|23|31|32|33|34|35|36|37|41|42|43|44|45|46|50|51|52|53|54|61|62|63|64|65|71|81|82|91';BEGIN /*验证不为空*/ IF CARDTYPE = '身份证' THEN IF (IDCARD IS NULL) THEN BEGIN RETURN 0; END; END IF; /*验证长度是否正确*/ IF (LENGTH(IDCARD) != 18 AND LENGTH(IDCARD) != 15) THEN BEGIN RETURN 0; END; END IF; IF (LENGTH(IDCARD) = 18) THEN BEGIN /*如果是新版身份证*/ --IF (NOT REGEXP_LIKE(SUBSTR(IDCARD, 1, 17), '^[[:digit:]]+$')) THEN IF (REGEXP_LIKE(IDCARD, '[^0-9][0-9]*.$')) THEN /*验证身份证前17位是否全是数字*/ BEGIN RETURN 0; END; END IF; /*校验码*/ SUMCODE := TO_NUMBER(SUBSTR(IDCARD, 1, 1)) * 7 + TO_NUMBER(SUBSTR(IDCARD, 2, 1)) * 9 + TO_NUMBER(SUBSTR(IDCARD, 3, 1)) * 10 + TO_NUMBER(SUBSTR(IDCARD, 4, 1)) * 5 + TO_NUMBER(SUBSTR(IDCARD, 5, 1)) * 8 + TO_NUMBER(SUBSTR(IDCARD, 6, 1)) * 4 + TO_NUMBER(SUBSTR(IDCARD, 7, 1)) * 2 + TO_NUMBER(SUBSTR(IDCARD, 8, 1)) * 1 + TO_NUMBER(SUBSTR(IDCARD, 9, 1)) * 6 + TO_NUMBER(SUBSTR(IDCARD, 10, 1)) * 3 + TO_NUMBER(SUBSTR(IDCARD, 11, 1)) * 7 + TO_NUMBER(SUBSTR(IDCARD, 12, 1)) * 9 + TO_NUMBER(SUBSTR(IDCARD, 13, 1)) * 10 + TO_NUMBER(SUBSTR(IDCARD, 14, 1)) * 5 + TO_NUMBER(SUBSTR(IDCARD, 15, 1)) * 8 + TO_NUMBER(SUBSTR(IDCARD, 16, 1)) * 4 + TO_NUMBER(SUBSTR(IDCARD, 17, 1)) * 2; IF (SUBSTR(CHECKSTR, (SUMCODE MOD 11) + 1, 1) != SUBSTR(UPPER(IDCARD), 18, 1)) THEN BEGIN RETURN 0; END; END IF; CARDYEAR := TO_NUMBER(SUBSTR(IDCARD, 7, 4)); /*取年*/ CARDMONTH := TO_NUMBER(SUBSTR(IDCARD, 11, 2)); /*取月*/ CARDDAY := TO_NUMBER(SUBSTR(IDCARD, 13, 2)); /*取日*/ --SEXCODE := TO_NUMBER(SUBSTR(IDCARD, 17, 1)); END; ELSE BEGIN /*如果是旧版身份证*/ IF (NOT REGEXP_LIKE(IDCARD, '^[[:digit:]]+$')) THEN /*验证身份证是否全是数字*/ BEGIN RETURN 0; END; END IF; CARDYEAR := TO_NUMBER('19' || SUBSTR(IDCARD, 7, 2)); /*取年*/ CARDMONTH := TO_NUMBER(SUBSTR(IDCARD, 9, 2)); /*取月*/ CARDDAY := TO_NUMBER(SUBSTR(IDCARD, 11, 2)); /*取日*/ -- SEXCODE := TO_NUMBER(SUBSTR(IDCARD, 15, 1)); END; END IF; /*验证省行政代码是否合法*/ IF (INSTR(CHECKPROCODE, SUBSTR(IDCARD, 0, 2)) <= 0) THEN BEGIN RETURN 0; END; END IF; /* \*验证性别*\ IF ((sex = '1' AND (sexCode MOD 2) = 0) OR (sex = '2' AND (sexCode MOD 2) != 0)) THEN BEGIN RETURN 0; END; END IF; */ /*验证出生年份是否正确*/ /*如果小于1900或大于今年都不正确*/ IF (CARDYEAR < 1900 OR CARDYEAR > TO_NUMBER(TO_CHAR(SYSDATE, 'yyyy'))) THEN BEGIN RETURN 0; END; END IF; IF EXTRACT(YEAR FROM SYSDATE) - CARDYEAR >= 100 THEN BEGIN RETURN 0; END; END IF; IF (CARDMONTH > 12 OR CARDMONTH <= 0) THEN /*验证月份是否正确*/ BEGIN RETURN 0; END; END IF; IF (IDCARD) IN ('111111111111111', '111111111111111111') THEN BEGIN RETURN 0; END; END IF; IF (CARDMONTH = 2 AND ((CARDYEAR MOD 4 = 0 AND CARDYEAR MOD 100 != 0) OR (CARDYEAR MOD 400 = 0))) THEN /*如果月份是2月且是润年*/ BEGIN IF (CARDDAY > 29 OR CARDDAY = 0) THEN /*验证日期是否正确*/ BEGIN RETURN 0; END; END IF; END; ELSE BEGIN IF (CARDDAY = 0) THEN BEGIN RETURN 0; END; END IF; IF ((CARDMONTH = 1 OR CARDMONTH = 3 OR CARDMONTH = 5 OR CARDMONTH = 7 OR CARDMONTH = 8 OR CARDMONTH = 10 OR CARDMONTH = 12) AND CARDDAY > 31) THEN /*验证日期是否正确*/ BEGIN RETURN 0; END; END IF; IF ((CARDMONTH = 4 OR CARDMONTH = 6 OR CARDMONTH = 9 OR CARDMONTH = 11) AND CARDDAY > 30) THEN /*验证日期是否正确*/ BEGIN RETURN 0; END; END IF; IF (CARDMONTH = 2 AND CARDDAY > 28) THEN /*验证日期是否正确*/ BEGIN RETURN 0; END; END IF; END; END IF; ELSE IF REGEXP_LIKE(IDCARD, '[1]{9,}') THEN RETURN 0; END IF; IF NOT REGEXP_LIKE(IDCARD, '[0]|[6-9]') THEN RETURN 0; END IF; END IF; RETURN 1; /*EXCEPTION WHEN OTHERS THEN \*如果你希望看到报错, 就把下面的注释行打开*\ raise;*/END;/promptprompt Creating function FUNC_CHECKORGCODEprompt ===================================promptCREATE OR REPLACE FUNCTION CICECIF_DEV.FUNC_CHECKORGCODE(ORGANIZATIONCODE VARCHAR2)/* 功能：验证组织机构代码，成功返回1，失败返回0 organizationCode：要验证的组织机构代码*/ RETURN NUMBER AS CODESUM NUMBER(10) := 0; CODE VARCHAR(100); CODE_9 VARCHAR(1); C9 NUMBER(2); /*字符与字符的值，每个字符后两位为该字符的字符数值*/ CI CHAR(250) := '000101202303404505606707808909A10B11C12D13E14F15G16H17I18J19K20L21M22N23O24P25Q26R27S28T29U30V31W32X33Y34Z35'; /*前8位字符的加权因子*/ TYPE V_AR IS VARRAY(10) OF NUMBER; WI V_AR := V_AR(3, 7, 9, 10, 5, 8, 4, 2);BEGIN /*判断是否为null*/ IF (ORGANIZATIONCODE IS NULL) THEN BEGIN RETURN 0; END; END IF; CODE := RTRIM(LTRIM(REPLACE(ORGANIZATIONCODE, '-', ''))); /*把-,前后空格去掉*/ /*验证长度是否正确*/ /*验证机构代码是由数字和大写字母组成*/ IF (LENGTH(CODE) != 9 OR NOT REGEXP_LIKE(CODE, '^[A-Z0-9]+$')) THEN BEGIN RETURN 0; END; END IF; /*前8位字符的字符数值分别乘于该位的加权因子，然后求和*/ FOR I IN 1 .. WI.COUNT LOOP CODESUM := CODESUM + TO_NUMBER(SUBSTR(CI, INSTR(CI, SUBSTR(CODE, I, 1)) + 1, 2)) * WI(I); END LOOP; /* 计算校验码C9*/ C9 := 11 - (CODESUM MOD 11); CODE_9 := SUBSTR(CODE, 9, 1); /*验证校验码C9*/ /*当C9的值为10时，校验码应是拉丁字母X */ /*当C9的值为11时校验码应是0*/ /*验证第9位是否等于计算出的校验结果*/ IF ((C9 = 10 AND CODE_9 = 'X') OR (C9 = 11 AND CODE_9 = '0') OR (CODE_9 = TO_CHAR(C9))) THEN BEGIN RETURN 1; END; END IF; RETURN 0; /*EXCEPTION WHEN OTHERS THEN RAISE;*/END FUNC_CHECKORGCODE;/promptprompt Creating function FUNC_CHECKICARDprompt =================================promptCREATE OR REPLACE FUNCTION CICECIF_DEV.FUNC_CHECKICARD(ICARD IN VARCHAR2) RETURN CHAR IS /* 判断客户信息中客户证件号格式是否正确 */BEGIN IF ICARD IS NULL THEN RETURN 0; END IF; IF ICARD LIKE '%字第%' THEN RETURN 1; --格式正确 END IF; IF FUNC_CHECKORGCODE(ICARD) = 0 AND FUNC_CHECKNUM_EXT('身份证',ICARD) = 0 THEN RETURN 0; --格式错误 ELSIF FUNC_CHECKORGCODE(ICARD) = 1 OR FUNC_CHECKNUM_EXT('身份证',ICARD) = '1' OR ICARD LIKE '%字第%' THEN RETURN 1; --格式正确 END IF;END;/promptprompt Creating function FUNC_CHECKMOBILE_EXTprompt ======================================promptCREATE OR REPLACE FUNCTION CICECIF_DEV.FUNC_CHECKMOBILE_EXT(VI_NUM IN VARCHAR2) RETURN CHAR IS VI_COUNT INTEGER; TI CHAR(1); VI CHAR(1); YI CHAR(1); PI CHAR(1); /* 判断手机号是否连续增长或连续减少 */BEGIN VI_COUNT := 0; FOR I IN 2 .. LENGTH(VI_NUM) - 3 LOOP VI := SUBSTR(VI_NUM, I, 1); TI := SUBSTR(VI_NUM, I + 1, 1); YI := SUBSTR(VI_NUM, I + 2, 1); PI := SUBSTR(VI_NUM, I + 3, 1); IF (ASCII(VI) = 79 OR ASCII(VI) = 111) THEN VI := 0; END IF; IF (ASCII(TI) = 79 OR ASCII(TI) = 111) THEN TI := 0; END IF; IF ASCII(TI) < 48 OR ASCII(TI) > 57 THEN RETURN 0; END IF; IF ASCII(VI) < 48 OR ASCII(VI) > 57 THEN RETURN 0; END IF; IF /*(VI = (TI + 1) or VI = (TI - 1))*/ TI - VI = YI - TI AND YI - TI = PI - YI /*and TI - VI = 1 and YI - TI = 1 */ THEN VI_COUNT := VI_COUNT + 1; END IF; END LOOP; IF VI_COUNT >= 4 THEN RETURN 0; --格式错误 ELSE RETURN 1; --格式正确 END IF;END;/promptprompt Creating function FUNC_CHECKNAMEprompt ================================promptCREATE OR REPLACE FUNCTION CICECIF_DEV.FUNC_CHECKNAME(CUS_NAME IN VARCHAR2) RETURN CHAR ISBEGIN IF CUS_NAME IS NULL OR NVL(LENGTH(REPLACE(CUS_NAME, ' ', '')), 0) <= 1 THEN RETURN 0; END IF; IF REGEXP_LIKE(CUS_NAME, '^%[0-9]|[0-9]{2,}$') THEN RETURN 0; END IF; IF REGEXP_LIKE(CUS_NAME, '^%[a-zA-Z]|[a-zA-Z]{2,}$') THEN RETURN 0; END IF; /* IF REGEXP_LIKE(CUS_NAME, '^[.|-|_]|[-|_]$') THEN RETURN 0; END IF;*/ IF CUS_NAME = '--' THEN RETURN 0; END IF; IF (CUS_NAME IN ('张三', '李四', '王五'/*, '自然人'*/) OR CUS_NAME LIKE '测试%' OR REGEXP_LIKE(CUS_NAME, '测[a-zA-Z0-9]试') OR REGEXP_LIKE(CUS_NAME, '测([.|-|_]|[\|-])试')) THEN RETURN 0; --格式错误 ELSE RETURN 1; --格式正确 END IF;END;/promptprompt Creating function FUNC_CHECKSEXprompt ===============================promptCREATE OR REPLACE FUNCTION CICECIF_DEV.FUNC_CHECKSEX(SEX IN VARCHAR2, IDENTIFYNUMBER IN VARCHAR2) RETURN CHAR IS RESULT VARCHAR2(2) := '0';BEGIN/* IF SEX IS NOT NULL THEN RESULT := SEX; END IF;*/ IF /*SEX IS NULL AND*/ FUNC_CHECKNUM_EXT2('身份证',IDENTIFYNUMBER) = 1 AND LENGTH(IDENTIFYNUMBER) = 18 AND SUBSTR(IDENTIFYNUMBER, 17, 1) IN ('1', '3', '5', '7', '9') THEN RESULT := '1'; END IF; IF /*SEX IS NULL AND */FUNC_CHECKNUM_EXT2('身份证',IDENTIFYNUMBER) = 1 AND LENGTH(IDENTIFYNUMBER) = 18 AND SUBSTR(IDENTIFYNUMBER, 17, 1) IN ('0', '2', '4', '6', '8') THEN RESULT := '2'; END IF; IF /*SEX IS NULL AND*/ FUNC_CHECKNUM_EXT2('身份证',IDENTIFYNUMBER) = 1 AND LENGTH(IDENTIFYNUMBER) = 15 AND SUBSTR(IDENTIFYNUMBER, 15, 1) IN ('1', '3', '5', '7', '9') THEN RESULT := '1'; END IF; IF /*SEX IS NULL AND*/ FUNC_CHECKNUM_EXT2('身份证',IDENTIFYNUMBER) = 1 AND LENGTH(IDENTIFYNUMBER) = 15 AND SUBSTR(IDENTIFYNUMBER, 15, 1) IN ('0', '2', '4', '6', '8') THEN RESULT := '2'; END IF; IF SEX IS NULL AND FUNC_CHECKNUM_EXT2('身份证',IDENTIFYNUMBER) = 0 THEN RESULT := '0'; /* else RESULT := SEX;*/ END IF; RETURN RESULT;END;/promptprompt Creating function FUNC_CHECK_EMAILprompt ==================================promptCREATE OR REPLACE FUNCTION CICECIF_DEV.FUNC_CHECK_EMAIL(CUS_EMAIL IN VARCHAR2) RETURN CHAR IS /* 判断客户信息中email格式是否正确 */ FLAG NUMBER := 0;BEGIN IF CUS_EMAIL IS NULL THEN RETURN 0; END IF; IF NOT REGEXP_LIKE(SUBSTR(CUS_EMAIL, INSTR(CUS_EMAIL, '@') + 1, INSTR(CUS_EMAIL, '.') - 1 - INSTR(CUS_EMAIL, '@')), '[6-9]|[a-zA-Z]') THEN RETURN 0; END IF; IF NOT REGEXP_LIKE(SUBSTR(CUS_EMAIL, INSTR(CUS_EMAIL, '.', -1)), '[a-zA-Z]') THEN RETURN 0; END IF; /* SELECT COUNT(1) INTO FLAG FROM CIF_BLACK_EMAIL WHERE EMAIL = LOWER(CUS_EMAIL); IF FLAG > 0 THEN RETURN 0; END IF;*/ IF (NOT REGEXP_LIKE(CUS_EMAIL, '^\w+([-]?|[.]?)\w+\@([A-Za-z0-9]+((\.|-)[A-Za-z0-9]+)*){2,}\.[A-Za-z0-9]{2,}$')) THEN RETURN 0; --格式错误 ELSIF LOWER(CUS_EMAIL) LIKE '%@qq%' AND REGEXP_LIKE(SUBSTR(CUS_EMAIL, INSTR(CUS_EMAIL, '@') + 1, INSTR(CUS_EMAIL, '.') - 1 - INSTR(CUS_EMAIL, '@')), '[0-9]') AND ((LENGTH(SUBSTR(CUS_EMAIL, 1, INSTR(CUS_EMAIL, '@', 1, 1) - 1)) >= 5 AND LENGTH(SUBSTR(CUS_EMAIL, 1, INSTR(CUS_EMAIL, '@', 1, 1) - 1)) <= 11 AND NOT REGEXP_LIKE(SUBSTR(CUS_EMAIL, 1, INSTR(CUS_EMAIL, '@', 1, 1) - 1), '\D') AND FUNC_CHECKEMAIL_EXT(SUBSTR(CUS_EMAIL, 1, INSTR(CUS_EMAIL, '@', 1, 1) - 1)) = '0') OR (LENGTH(SUBSTR(CUS_EMAIL, 1, INSTR(CUS_EMAIL, '@', 1, 1) - 1)) < 5 OR LENGTH(SUBSTR(CUS_EMAIL, 1, INSTR(CUS_EMAIL, '@', 1, 1) - 1)) > 11)) THEN RETURN 0; ELSE RETURN 1; --格式正确 END IF;END;/promptprompt Creating function FUNC_CHECK_MOBILEprompt ===================================promptCREATE OR REPLACE FUNCTION CICECIF_DEV.FUNC_CHECK_MOBILE(CUS_MOBILE IN VARCHAR2) RETURN CHAR IS /* 判断客户信息中手机号格式是否正确 */BEGIN /* IF CUS_MOBILE IS NULL THEN RETURN 0; END IF;*/ IF NOT REGEXP_LIKE(CUS_MOBILE, '[0-9]{11}') THEN RETURN 0; --格式错误 END IF; IF (CUS_MOBILE IS NULL --为空 OR LENGTH(CUS_MOBILE) <> '11' --不等于11位 OR SUBSTR(CUS_MOBILE, 1, 2) NOT IN ('13', '14', '15', '17', '18') --不是13等等开头 OR REGEXP_LIKE(CUS_MOBILE, '\d*([0-9]{1}+[0-9]{1}+)\1{4,}\d*') --类似1313131…… OR REGEXP_LIKE(CUS_MOBILE, '\d*(\d)\1{6,}\d*') --某一位数连续重复5次 OR FUNC_CHECKMOBILE_EXT(CUS_MOBILE) = '0' --连续增长7位以上 ) THEN RETURN 0; --格式错误 ELSE RETURN 1; --格式正确 END IF;END;/promptprompt Creating function FUNC_CHECK_MOBILE_TESTprompt ========================================promptCREATE OR REPLACE FUNCTION CICECIF_DEV.FUNC_CHECK_MOBILE_test(CUS_MOBILE1 IN VARCHAR2, CUS_MOBILE2 IN VARCHAR2) RETURN CHAR IS /* 判断客户信息中手机号格式是否正确 */ -- c_flag varchar2(2);BEGIN /* IF CUS_MOBILE IS NULL THEN RETURN 0; END IF;*/ -- c_flag := '0'; IF NOT REGEXP_LIKE(CUS_MOBILE1, '[0-9]{11}') THEN IF NOT REGEXP_LIKE(CUS_MOBILE2, '[0-9]{11}') THEN RETURN 0; END IF; END IF; IF (CUS_MOBILE1 IS NULL --为空 OR LENGTH(CUS_MOBILE1) <> '11' --不等于11位 OR SUBSTR(CUS_MOBILE1, 1, 2) NOT IN ('13', '14', '15', '17', '18') --不是13等等开头 OR REGEXP_LIKE(CUS_MOBILE1, '\d*([0-9]{1}+[0-9]{1}+)\1{4,}\d*') --类似1313131…… OR REGEXP_LIKE(CUS_MOBILE1, '\d*(\d)\1{6,}\d*') --某一位数连续重复5次 OR regexp_like(CUS_MOBILE1,'0123|1234|2345|3456|4567|5678|6789|7890|01234|12345|23456|34567|45678|56789|67890|012345|123456|234567|345678|456789|567890|0123456|1234567|2345678|3456789|4567890|01234567|12345678|23456789|34567890|012345678|123456789|234567890|0123456789|1234567890|')--'0123456|1234567|2345678|3456789' -- OR FUNC_CHECKMOBILE_EXT(CUS_MOBILE1) = '0' --连续增长7位以上 ) THEN IF (CUS_MOBILE2 IS NULL --为空 OR LENGTH(CUS_MOBILE2) <> '11' --不等于11位 OR SUBSTR(CUS_MOBILE2, 1, 2) NOT IN ('13', '14', '15', '17', '18') --不是13等等开头 OR REGEXP_LIKE(CUS_MOBILE2, '\d*([0-9]{1}+[0-9]{1}+)\1{4,}\d*') --类似1313131…… OR REGEXP_LIKE(CUS_MOBILE2, '\d*(\d)\1{6,}\d*') --某一位数连续重复5次 OR regexp_like(CUS_MOBILE2,'0123|1234|2345|3456|4567|5678|6789|7890|01234|12345|23456|34567|45678|56789|67890|012345|123456|234567|345678|456789|567890|0123456|1234567|2345678|3456789|4567890|01234567|12345678|23456789|34567890|012345678|123456789|234567890|0123456789|1234567890|') --OR FUNC_CHECKMOBILE_EXT(CUS_MOBILE2) = '0' --连续增长7位以上 ) THEN RETURN 0; ELSE RETURN 2; END IF; ELSE IF (CUS_MOBILE2 IS NULL --为空 OR LENGTH(CUS_MOBILE2) <> '11' --不等于11位 OR SUBSTR(CUS_MOBILE2, 1, 2) NOT IN ('13', '14', '15', '17', '18') --不是13等等开头 OR REGEXP_LIKE(CUS_MOBILE2, '\d*([0-9]{1}+[0-9]{1}+)\1{4,}\d*') --类似1313131…… OR REGEXP_LIKE(CUS_MOBILE2, '\d*(\d)\1{6,}\d*') --某一位数连续重复5次 OR regexp_like(CUS_MOBILE2,'0123|1234|2345|3456|4567|5678|6789|7890|01234|12345|23456|34567|45678|56789|67890|012345|123456|234567|345678|456789|567890|0123456|1234567|2345678|3456789|4567890|01234567|12345678|23456789|34567890|012345678|123456789|234567890|0123456789|1234567890|') --OR FUNC_CHECKMOBILE_EXT(CUS_MOBILE2) = '0' --连续增长7位以上 ) THEN RETURN 1; ELSE RETURN 3; END IF; END IF;END;/promptprompt Creating function FUNC_CHECK_PHONEprompt ==================================promptCREATE OR REPLACE FUNCTION CICECIF_DEV.FUNC_CHECK_PHONE(CUS_PHONE IN VARCHAR2) RETURN CHAR IS /* 判断客户信息中固定电话号码格式是否正确 */BEGIN IF NOT REGEXP_LIKE(REGEXP_REPLACE(CUS_PHONE, '[-转]', ''), '\d') THEN RETURN 0; --格式错误 ELSIF (CUS_PHONE IS NULL --为空 OR NOT REGEXP_LIKE(CUS_PHONE, '^((0[0-9]{2,3})(-|--)?)?([2-9][0-9]{6,7})+((-|转)[0-9]{1,4})?$') ----格式不是【区号-电话号-加拨】|【区号--电话号-加拨】 ) THEN RETURN 0; --格式错误 ELSE RETURN 1; --格式正确 END IF;END;/promptprompt Creating function FUNC_CUST_GRADEprompt =================================promptCREATE OR REPLACE FUNCTION CICECIF_DEV.FUNC_CUST_GRADE(CUS_MOBILE IN VARCHAR2) RETURN CHAR IS /* 判断客户信息中手机号格式是否正确 */BEGIN /* IF CUS_MOBILE IS NULL THEN RETURN 0; END IF;*/ IF NOT REGEXP_LIKE(CUS_MOBILE, '[0-9]{11}') THEN RETURN 0; --格式错误 END IF; IF (CUS_MOBILE IS NULL --为空 OR LENGTH(CUS_MOBILE) <> '11' --不等于11位 OR SUBSTR(CUS_MOBILE, 1, 2) NOT IN ('13', '14', '15', '17', '18') --不是13等等开头 OR REGEXP_LIKE(CUS_MOBILE, '\d*([0-9]{1}+[0-9]{1}+)\1{4,}\d*') --类似1313131…… OR REGEXP_LIKE(CUS_MOBILE, '\d*(\d)\1{6,}\d*') --某一位数连续重复5次 OR FUNC_CHECKMOBILE_EXT(CUS_MOBILE) = '0' --连续增长7位以上 ) THEN RETURN 0; --格式错误 ELSE RETURN 1; --格式正确 END IF;END;/