`
haofeng0305
  • 浏览: 31224 次
  • 性别: Icon_minigender_1
  • 来自: 上海
社区版块
存档分类
最新评论

数据清洗3

阅读更多

CREATE OR REPLACE PACKAGE BODY NETS_TCIMS_PC_CLEANOUT
IS

  /***********************************************************
  --功能说明:  清洗城市
  --参数说明:
  --调用函数:
  --修改记录:  create by ex-qiuweisheng001/ex-liujiali001
  --注意事项:  这个是清洗规则中首当其冲的过程
  --*********************************************************/
  PROCEDURE SP_CLEAN_CITY
  IS
     p_id     NUMBER;         -- 日志记录id
     p_errmsg varchar2(500);  -- 错误记录
  BEGIN

    -- 操作日志记录
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,2,NULL,'01',NULL,NULL,NULL,NULL);

    -- truncate 清空   表
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_CLEAN_CITY_STG_TMP');
   
    --标识无效数据  无城市编码即为无效
    INSERT /*+APPEND*/
      INTO PC_CLEAN_CITY_STG_TMP
           (SYS_ID)
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
             A.SYS_ID
       FROM IDL_SEP_SRC_DATA A,
            IDL_EX_BATCH     B
       WHERE (A.CITY IS NULL OR A.SECONDARY_ORG IS NULL OR THIRD_ORG IS NULL)
         AND A.TCIMS_BATCH_ID = B.TCIMS_BATCH_ID
         AND B.SERIES_TYPE = '01'
         AND B.BATCH_STATUS = '11'
         AND B.PREPARE_FLAG = '1';
    COMMIT;
   
   -- 更新本次操作日志
   NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,2,NULL,'02',NULL,NULL,NULL,NULL);

  EXCEPTION
     WHEN OTHERS THEN
        p_errmsg := substr(sqlerrm,1,500);
        NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,2,NULL,'03',p_errmsg,NULL,NULL,NULL);
        RAISE;
  END SP_CLEAN_CITY;

  /***********************************************************
  --功能说明:  清洗车牌号码
  --参数说明:
  --调用函数:
  --修改记录:  create by ex-qiuweisheng001/ex-liujiali001
  --*********************************************************/
  PROCEDURE SP_CLEAN_VEHICLE_NO
  IS
     p_id     NUMBER;         -- 日志记录id
     p_errmsg varchar2(500);  -- 错误记录
  BEGIN

    -- 操作日志记录
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,22,NULL,'01',NULL,NULL,NULL,NULL);

    -- truncate 清空  PC_CLEAN_VEHICLE_NO_STG_TMP 表
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_CLEAN_VEHICLE_NO_STG_TMP');

    --去掉特殊符号。回车,头尾的全半角空格、Tab、全角横杠转换为半角横杠,去空格 ,车牌后面的‘.’
    --全角转换为半角
    INSERT /*+APPEND*/
      INTO PC_CLEAN_VEHICLE_NO_1_TMP
           (SYS_ID,VEHICLE_NO,CITY)
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
             A.SYS_ID,
             REPLACE(TRANSLATE(REPLACE(REPLACE(TO_SINGLE_BYTE(A.VEHICLE_NO),
                                            CHR(13),''),
                                       CHR(10),''),
             '-.*'||NETS_TCIMS_COM_CLEANOUT.V_SPECIAL_CHR_EN||NETS_TCIMS_COM_CLEANOUT.V_SPECIAL_CHR_ZN,
             '-                                      '),
                     ' ',
                     '') VEHICLE_NO,
             A.CITY
       FROM IDL_SEP_SRC_DATA A,
            IDL_EX_BATCH     B
       WHERE A.TCIMS_BATCH_ID = B.TCIMS_BATCH_ID
         AND B.SERIES_TYPE = '01'
         AND B.BATCH_STATUS = '11'
         AND B.PREPARE_FLAG = '1';
    COMMIT;

    --根据所在的城市,补充缺失的车牌前缀
    INSERT /*+APPEND*/
      INTO PC_CLEAN_VEHICLE_NO_2_TMP
           (SYS_ID,VEHICLE_NO,CITY)
      SELECT /*+PARALLEL(A 4)*/
             SYS_ID,
             CASE
               WHEN SUBSTR(A.VEHICLE_NO, 1, 2) <>
                    (SELECT MAX(B.SHORTEN)
                       FROM BDL_RULE_PROVINCE_CITY B
                      WHERE B.CITY = A.CITY) THEN
                (SELECT MAX(B.SHORTEN)
                   FROM BDL_RULE_PROVINCE_CITY B
                  WHERE B.CITY = A.CITY) || SUBSTR(A.VEHICLE_NO, 3)
               ELSE
                VEHICLE_NO
             END VEHICLE_NO,
             A.CITY
        FROM PC_CLEAN_VEHICLE_NO_1_TMP A;
    COMMIT;

    -- 补充车牌号码中缺少的 '-' 分隔符
    INSERT /*+APPEND*/
    INTO PC_CLEAN_VEHICLE_NO_3_TMP
         (SYS_ID,VEHICLE_NO,CITY)
      SELECT /*+PARALLEL(A 4)*/
             SYS_ID,
             CASE
               WHEN INSTR(VEHICLE_NO, '-') = 0 THEN
                SUBSTR(VEHICLE_NO, 1, 2) || '-' || SUBSTR(VEHICLE_NO, 3)
               ELSE
                VEHICLE_NO
             END VEHICLE_NO,
             CITY
       FROM PC_CLEAN_VEHICLE_NO_2_TMP A;
    COMMIT;

    -- 将车牌不完整或车牌无内容数据补充“*”,
    -- 注:车牌不完整定义:长度小于8(补充上“-”时)
    INSERT /*+APPEND*/
      INTO PC_CLEAN_VEHICLE_NO_4_TMP
           (SYS_ID,VEHICLE_NO,CITY)
      SELECT /*+PARALLEL(A)*/
             SYS_ID,
             CASE
               WHEN LENGTH(VEHICLE_NO) < 8 OR VEHICLE_NO IS NULL THEN
                VEHICLE_NO || '*'
               ELSE
                VEHICLE_NO
             END VEHICLE_NO,
             CITY
        FROM PC_CLEAN_VEHICLE_NO_3_TMP A;
    COMMIT;

    -- 6.车牌以[京,津,冀,晋,蒙,辽,吉,黑,沪,苏,浙,皖,闽,赣,
    --          云,鲁,豫,鄂,湘,粤,桂,琼,渝,川,黔,滇,藏,陕,甘,青,宁,新][A-Z]-开头,
    --          长度大于等于8,或含有"*"号车牌为正确车牌,其他需要手工清洗
    INSERT /*+APPEND*/
      INTO PC_CLEAN_VEHICLE_NO_5_TMP
           (SYS_ID,VEHICLE_NO,CITY,CLEAN_STATUS)
        SELECT /*+PARALLEL(A 4)*/
               SYS_ID,
               VEHICLE_NO,
               CITY,
               CASE
                 WHEN (SUBSTR(A.VEHICLE_NO, 1, 1) IN
                      ('京', '津', '冀', '晋', '蒙', '辽', '吉', '黑', '沪', '苏', '浙', '皖',
                       '闽', '赣', '云', '鲁', '豫', '鄂', '湘', '粤', '桂', '琼', '渝', '川',
                       '黔', '滇', '藏', '陕', '甘', '青', '宁', '新')
                      AND UPPER(SUBSTR(A.VEHICLE_NO, 2, 1)) IN
                      ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N',
                      'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z')
                      AND SUBSTR(A.VEHICLE_NO, 3, 1) = '-' AND LENGTH(A.VEHICLE_NO) >= 8) OR
                      INSTR(VEHICLE_NO, '*') > 0 THEN
                      '1'
                 ELSE
                      '0'
               END CLEAN_STATUS
          FROM PC_CLEAN_VEHICLE_NO_4_TMP A;
    COMMIT;


    --标示车牌号码完整  (为后续比对做准备)
    INSERT /*+APPEND*/
      INTO PC_CLEAN_VEHICLE_NO_STG_TMP
           (SYS_ID,VEHICLE_NO,CITY,CLEAN_STATUS,VEHICLE_NO_INTEGRITY)
      SELECT /*+PARALLEL(A 4)*/
             SYS_ID,
             UPPER(VEHICLE_NO),
             CITY,
             CLEAN_STATUS,
             CASE
               WHEN LENGTH(A.VEHICLE_NO) >= 8 AND SUBSTR(A.VEHICLE_NO, -1) <> '*' THEN
                '1'
               ELSE
                '0'
             END VEHICLE_NO_INTEGRITY
        FROM PC_CLEAN_VEHICLE_NO_5_TMP A;
    COMMIT;

   -- 更新本次操作日志
   NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,22,NULL,'02',NULL,NULL,NULL,NULL);

  EXCEPTION
     WHEN OTHERS THEN
        p_errmsg := substr(sqlerrm,1,500);
        NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,22,NULL,'03',p_errmsg,NULL,NULL,NULL);
        RAISE;
  END SP_CLEAN_VEHICLE_NO;

  /***********************************************************
  --功能说明:  清洗联系电话,
  --参数说明:
  --调用函数:
  --修改记录:  create by ex-qiuweisheng001/ex-liujiali001
  --注意事项: 必须在车牌清洗之后运行
  --*********************************************************/
  PROCEDURE SP_CLEAN_TELEPHONE_NO
  IS
    p_id        NUMBER;         -- 日志记录id
    p_errmsg    VARCHAR2(500);  -- 错误记录
    v_sql       VARCHAR2(4000);
    v_sql_split VARCHAR2(4000);

    CURSOR cur_TEL_SPLIT IS
      SELECT SP.SPLITFIX FROM BDL_RULE_TEL_SPLIT SP;

  BEGIN
    -- 操作日志记录
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,26,NULL,'01',NULL,NULL,NULL,NULL);
   
    -- truncate 清空  PC_CLEAN_TEL_NO_STG_TMP 表   
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_CLEAN_TEL_NO_STG_TMP');
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_CLEAN_TELEPHONE_STG_TMP');
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_STG_TEL_NO_TMP');    

    --回车,头尾的全半角空格、Tab、全角横杠转换为半角横杠,
    --全角转换为半角
    INSERT /*+APPEND*/
    INTO PC_CLEAN_TEL_NO_1_TMP
         (
          SYS_ID,
          TEL_NO,
          TEL_NO_VALID,
          CITY,
          VEHICLE_NO,
          ADDRESS
         )
      SELECT /*+PARALLEL(C 2) PARALLEL(A 2) PARALLEL(B 2)*/
             A.SYS_ID,
             CASE
               WHEN LENGTH(TRIM(A.TELEPHONE_NUMBER)) > 5 THEN
                    TRIM(REPLACE(REPLACE(TO_SINGLE_BYTE(A.TELEPHONE_NUMBER),
                                   CHR(13), ''),CHR(10),''))
             END TEL_NO,
             CASE
               WHEN LENGTH(TRIM(A.TELEPHONE_NUMBER)) > 5 THEN
                    '1'
               ELSE
                    '0'
             END TEL_NO_VALID,
             --B.CITY,
             (select city_name from BDL_COM_DISTRICT_ORG WHERE city_code = C.CITY) CITY,
             A.VEHICLE_NO,
             A.ADDRESS
        FROM PC_CLEAN_VEHICLE_NO_STG_TMP C,
             IDL_SEP_SRC_DATA            A,
             IDL_EX_BATCH                B
             /*(SELECT SYS_ID, VEHICLE_NO, CITY, CLEAN_STATUS, VEHICLE_NO_INTEGRITY
                FROM PC_CLEAN_VEHICLE_NO_STG_TMP
               WHERE CLEAN_STATUS = '1') B*/
        WHERE A.SYS_ID = C.SYS_ID(+)
          AND A.TCIMS_BATCH_ID = B.TCIMS_BATCH_ID
          AND B.SERIES_TYPE = '01'
          AND B.BATCH_STATUS = '11'
          AND B.PREPARE_FLAG = '1';
    COMMIT;

    --取分割区号-座机-分机的符号串,'''-'',''x'',''*'',''*'',''呼'',''转'''
    FOR arow IN cur_TEL_SPLIT LOOP
      v_sql       := v_sql || trim(arow.splitfix);
      v_sql_split := v_sql_split || '-';
    END LOOP;

    --用户定义的区号和分机分隔符转换为"-"
    IF v_sql IS NOT NULL THEN
      INSERT /*+APPEND*/
        INTO PC_CLEAN_TEL_NO_2_TMP
             (
              SYS_ID,
              TEL_NO,
              TEL_NO_VALID,
              CITY,
              VEHICLE_NO,
              ADDRESS
             )       
        SELECT /*+PARALLEL(A 4)*/
               SYS_ID,
               CASE
                 WHEN TEL_NO_VALID = '1' THEN
                  TRANSLATE(TEL_NO, v_sql, v_sql_split)
                 ELSE
                  TEL_NO
               END TEL_NO,
               TEL_NO_VALID,
               CITY,
               A.VEHICLE_NO,
               A.ADDRESS
          FROM PC_CLEAN_TEL_NO_1_TMP A;
      COMMIT;
    ELSE
      INSERT /*+APPEND*/
        INTO PC_CLEAN_TEL_NO_2_TMP
             (
              SYS_ID,
              TEL_NO,
              TEL_NO_VALID,
              CITY,
              VEHICLE_NO,
              ADDRESS
             )
        SELECT /*+PARALLEL(A 4)*/
               A.SYS_ID, A.TEL_NO, A.TEL_NO_VALID, A.CITY,A.VEHICLE_NO,A.ADDRESS
          FROM PC_CLEAN_TEL_NO_1_TMP A;
      COMMIT;
    END IF;

    --将空格,非数字替换为斜杠,
    --4.只保留半角数字和“-”,“/”,其他字符全部删除
    --将空格,非数字替换为斜杠,同时对电话分列,分隔符为'/' NDC_TMP_TEL_NO_4
    INSERT /*+APPEND*/
      INTO PC_CLEAN_TEL_NO_NO_USE_TMP
           (clean_tel_result)
      SELECT /*+PARALLEL(A 4)*/
             NETS_TCIMS_COM_CLEANOUT.CLEAN_TEL(A.SYS_ID, A.TEL_NO, A.CITY,A.VEHICLE_NO,A.ADDRESS)
        FROM PC_CLEAN_TEL_NO_2_TMP A
       WHERE A.TEL_NO_VALID = '1';
    COMMIT;

    -- 识别手机号码
    INSERT /*+APPEND*/
      INTO PC_CLEAN_TEL_NO_5_TMP
           (SPLIT_SYSID, SYS_ID, TEL_NO)
      SELECT /*+PARALLEL(A 4)*/
             SPLIT_SYSID,
             SYS_ID,
             LTRIM(REPLACE(TEL_NO, '-', ''),'0') TEL_NO
        FROM PC_CLEAN_TEL_NO_4_TMP A
       WHERE SUBSTR(A.TEL_NO, 1, 3) IN
             (SELECT PREFIX FROM BDL_RULE_MOBILE_PREFIX);
    COMMIT;

    -- 识别座机号码并取区号
    INSERT /*+APPEND*/
      INTO PC_CLEAN_TEL_NO_6_TMP
           (
            SPLIT_SYSID,
            SYS_ID,
            CODE,
            TEL_NO,
            CITY,
            VEHICLE_NO,
            ADDRESS
           )
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
             A.SPLIT_SYSID,
             A.SYS_ID,
             CASE
               WHEN (INSTR(A.TEL_NO, '-') BETWEEN 3 AND 4) THEN
                '0'||SUBSTR(A.TEL_NO, 1, INSTR(A.TEL_NO, '-') - 1)
               ELSE
                B.CODE
             END CODE,
             CASE
               WHEN (INSTR(A.TEL_NO, '-') BETWEEN 3 AND 4) THEN
                SUBSTR(A.TEL_NO, INSTR(A.TEL_NO, '-') + 1)
               ELSE
                A.TEL_NO
             END TEL_NO,
             A.CITY,
             A.VEHICLE_NO,
             A.ADDRESS
        FROM PC_CLEAN_TEL_NO_4_TMP A,
             BDL_RULE_TEL_CITY_CODE B
       WHERE NOT EXISTS (SELECT 1
                FROM PC_CLEAN_TEL_NO_5_TMP TT
               WHERE A.SPLIT_SYSID = TT.SPLIT_SYSID)
         AND A.CITY = B.CITY_NAME;
    COMMIT;

    --转换特殊的 升位区域
    INSERT /*+APPEND*/
      INTO PC_CLEAN_TEL_NO_3_TMP
           (
            SPLIT_SYSID,
            SYS_ID,
            CODE,
            TEL_NO,
            RISE_AREA
           )
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
             A.SPLIT_SYSID,
             A.SYS_ID,
             A.CODE,
             A.TEL_NO,
             NVL(B.RISE_AREA,A.CITY) RISE_AREA
        FROM PC_CLEAN_TEL_NO_6_TMP  A,
             (SELECT C.CODE, D.RISE_AREA
                FROM PC_CLEAN_TEL_NO_6_TMP C,
                     BDL_RULE_TEL_RISE_AREA D
               WHERE ((C.VEHICLE_NO LIKE D.VEHICLE_NO AND D.ADDRESS IS NULL)
                  OR (C.ADDRESS LIKE D.ADDRESS AND D.VEHICLE_NO IS NULL)
                  OR (C.ADDRESS LIKE D.ADDRESS AND C.VEHICLE_NO LIKE D.VEHICLE_NO))) B
        WHERE A.CODE = B.CODE(+);
    COMMIT;        


    -- 座机号码升位
    -- 请确保号码升位规则不重叠
    INSERT /*+APPEND*/
    INTO PC_CLEAN_TEL_NO_7_TMP
           (
            SPLIT_SYSID,
            SYS_ID,
            CODE,
            TEL_NO,
            RISE_AREA
           )
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
             A.SPLIT_SYSID,
             A.SYS_ID,
             A.CODE,
             CASE
               WHEN B.action = 'PRE_INSERT' THEN
                B.to_pattern || A.TEL_NO
               WHEN B.action = 'REPLACE' THEN
                SUBSTR(A.TEL_NO, B.LEFT_START, B.LEFT_LEN) || B.to_pattern ||
                SUBSTR(A.TEL_NO, B.RIGHT_START)
             END TEL_NO,
             A.RISE_AREA
        FROM PC_CLEAN_TEL_NO_3_TMP A,
             BDL_RULE_TEL_CITY_PATTERN B
       WHERE ((INSTR(A.TEL_NO, '-') > 0 AND
             SUBSTR(A.TEL_NO, 1, INSTR(A.TEL_NO, '-') - 1) LIKE
             B.OLD_PATTERN) OR
             (INSTR(A.TEL_NO, '-') < 1 AND A.TEL_NO LIKE B.OLD_PATTERN))
         AND B.LIKE_MODEL = '1'
         AND B.DATA_TYPE = 'PC'
         AND A.RISE_AREA = B.CITY_NAME;
    COMMIT;

    /*
    8******->81******
    *******->8*******
    除去LIKE_MODEL='1'的升位
    */
    INSERT /*+APPEND*/
    INTO PC_CLEAN_TEL_NO_7_TMP
           (
            SPLIT_SYSID,
            SYS_ID,
            CODE,
            TEL_NO,
            RISE_AREA
           )
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
             A.SPLIT_SYSID,
             A.SYS_ID,
             A.CODE,
             CASE
               WHEN B.action = 'PRE_INSERT' THEN
                B.to_pattern || A.TEL_NO
               WHEN B.action = 'REPLACE' THEN
                SUBSTR(A.TEL_NO, B.LEFT_START, B.LEFT_LEN) || B.to_pattern ||
                SUBSTR(A.TEL_NO, B.RIGHT_START)
             END TEL_NO,
             A.RISE_AREA
        FROM PC_CLEAN_TEL_NO_3_TMP         A,
             BDL_RULE_TEL_CITY_PATTERN     B
       WHERE ((INSTR(A.TEL_NO, '-') > 0 AND
             SUBSTR(A.TEL_NO, 1, INSTR(A.TEL_NO, '-') - 1) LIKE
             B.OLD_PATTERN) OR
             (INSTR(A.TEL_NO, '-') < 1 AND A.TEL_NO LIKE B.OLD_PATTERN))
         AND NOT EXISTS(SELECT 1 FROM LA_CLEAN_TEL_NO_7_TMP C
                         WHERE C.SPLIT_SYSID = A.SPLIT_SYSID)
         AND B.LIKE_MODEL = '2'
         AND B.DATA_TYPE = 'PC'
         AND A.RISE_AREA = B.CITY_NAME;
    COMMIT;

    /*******************************************
    处理 第三种模式 升位:
      例如: 广东省 “湛江 ”
    前提: 除去 LIKE_MODEL = '1' 和 '2' 的升位
    ********************************************/
    INSERT /*+APPEND*/
    INTO PC_CLEAN_TEL_NO_7_TMP
           (
            SPLIT_SYSID,
            SYS_ID,
            CODE,
            TEL_NO,
            RISE_AREA
           )
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
             A.SPLIT_SYSID,
             A.SYS_ID,
             A.CODE,
             CASE
               WHEN B.action = 'PRE_INSERT' THEN
                  SUBSTR(A.TEL_NO, B.LEFT_START, B.LEFT_LEN) || A.TEL_NO
               WHEN B.action = 'REPLACE' THEN
                B.to_pattern || SUBSTR(A.TEL_NO, B.LEFT_START, B.LEFT_LEN) || A.TEL_NO
               END TEL_NO,
             A.RISE_AREA
        FROM PC_CLEAN_TEL_NO_3_TMP         A,
             BDL_RULE_TEL_CITY_PATTERN     B
       WHERE ((INSTR(A.TEL_NO, '-') > 0 AND
             SUBSTR(A.TEL_NO, 1, INSTR(A.TEL_NO, '-') - 1) LIKE
             B.OLD_PATTERN) OR
             (INSTR(A.TEL_NO, '-') < 1 AND A.TEL_NO LIKE B.OLD_PATTERN))
         AND NOT EXISTS(SELECT 1 FROM LA_CLEAN_TEL_NO_7_TMP C
                         WHERE C.SPLIT_SYSID = A.SPLIT_SYSID)
         AND B.LIKE_MODEL = '3'
         AND B.DATA_TYPE = 'PC'
         AND A.RISE_AREA = B.CITY_NAME;
    COMMIT;

    /*******************************************
    处理 第四种模式 升位:
      例如: 特例模式 “泉州 ”
    前提: 除去 LIKE_MODEL = '1' 、 '2' 和 ‘3’的升位
    ********************************************/
    INSERT /*+APPEND*/
    INTO PC_CLEAN_TEL_NO_7_TMP
           (
            SPLIT_SYSID,
            SYS_ID,
            CODE,
            TEL_NO,
            RISE_AREA
           )
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
             A.SPLIT_SYSID,
             A.SYS_ID,
             A.CODE,
             CASE
               WHEN B.action = 'PRE_INSERT' THEN
                B.to_pattern || A.TEL_NO
               WHEN B.action = 'REPLACE' THEN
                B.to_pattern || SUBSTR(A.TEL_NO, B.LEFT_START, B.LEFT_LEN) || B.MIDDLE_INSERT || SUBSTR(A.TEL_NO, B.RIGHT_START)
             END TEL_NO,
             A.RISE_AREA
        FROM PC_CLEAN_TEL_NO_3_TMP         A,
             BDL_RULE_TEL_CITY_PATTERN     B
       WHERE ((INSTR(A.TEL_NO, '-') > 0 AND
             SUBSTR(A.TEL_NO, 1, INSTR(A.TEL_NO, '-') - 1) LIKE
             B.OLD_PATTERN) OR
             (INSTR(A.TEL_NO, '-') < 1 AND A.TEL_NO LIKE B.OLD_PATTERN))
         AND NOT EXISTS(SELECT 1 FROM LA_CLEAN_TEL_NO_7_TMP C
                         WHERE C.SPLIT_SYSID = A.SPLIT_SYSID)
         AND B.LIKE_MODEL = '4'
         AND B.DATA_TYPE = 'PC'
         AND A.RISE_AREA = B.CITY_NAME;
    COMMIT;

    --同时把正确的号码放入
    INSERT /*+APPEND*/
    INTO PC_CLEAN_TEL_NO_7_TMP
           (
            SPLIT_SYSID,
            SYS_ID,
            CODE,
            TEL_NO,
            RISE_AREA
           )
      SELECT /*+PARALLEL(A 2) PARALLEL(C 2)*/
             A.SPLIT_SYSID, A.SYS_ID, A.CODE, A.TEL_NO, A.RISE_AREA
        FROM PC_CLEAN_TEL_NO_3_TMP A,
             BDL_RULE_TEL_CITY_CODE C
       WHERE ((INSTR(A.TEL_NO, '-') > 0 AND
             LENGTH(SUBSTR(A.TEL_NO, 1, INSTR(A.TEL_NO, '-') - 1)) =
             C.VALID_LEGTH) OR (INSTR(A.TEL_NO, '-') < 1 AND
             LENGTH(A.TEL_NO) = C.VALID_LEGTH))
         AND NOT EXISTS(SELECT 1 FROM LA_CLEAN_TEL_NO_7_TMP B
                         WHERE B.SPLIT_SYSID = A.SPLIT_SYSID)
         AND A.RISE_AREA = C.CITY_NAME
         AND C.DATA_TYPE = 'PC';
    COMMIT;


    -- 电话号码清洗结果    将清洗完的数据插入的STG表,并标识清洗状态
    INSERT /*+APPEND*/
      INTO PC_CLEAN_TEL_NO_8_TMP
           (
            SPLIT_SYSID,
            SYS_ID,
            CODE,
            TEL_NO,
            CITY,
            CLEAN_STATUS
           )
     SELECT /*+PARALLEL(t1 2) PARALLEL(t5 2) PARALLEL(tt 2)*/
            NVL2(tt.CODE,TT.SPLIT_SYSID,T5.SPLIT_SYSID) SPLIT_SYSID,
            t1.SYS_ID SYS_ID,
            tt.CODE   CODE,
            nvl(nvl(tt.TEL_NO,t5.TEL_NO),t1.TEL_NO) TEL_NO,
            t1.CITY   CITY,
            nvl(nvl2(tt.code,tt.CLEAN_STATUS,t5.CLEAN_STATUS),0) CLEAN_STATUS
       FROM PC_CLEAN_TEL_NO_1_TMP t1,
            (SELECT SPLIT_SYSID,
                    SYS_ID,
                    TEL_NO,
                    DECODE(LENGTH(TEL_NO), 11, '1', '0') CLEAN_STATUS
               FROM PC_CLEAN_TEL_NO_5_TMP) t5,
            (SELECT TL.SPLIT_SYSID,
                    TL.SYS_ID,
                    DECODE(NVL(T7.SPLIT_SYSID, 0),
                                    0,
                                    TL.CODE,
                                    T7.CODE) CODE,
                    DECODE(NVL(T7.SPLIT_SYSID, 0),
                                    0,
                                    TL.TEL_NO,
                                    T7.TEL_NO) TEL_NO,
                   DECODE(NVL(T7.SPLIT_SYSID, 0), 0, '0', '1') CLEAN_STATUS
               FROM PC_CLEAN_TEL_NO_3_TMP TL, PC_CLEAN_TEL_NO_7_TMP T7
              WHERE TL.SPLIT_SYSID = T7.SPLIT_SYSID(+)) tt
       WHERE t1.sys_id = t5.sys_id(+)
         AND t1.sys_id = tt.sys_id(+);
    COMMIT;

    -- 电话号码清洗结果    将清洗完的数据插入的STG表,并标识清洗状态
    INSERT /*+APPEND*/
      INTO PC_CLEAN_TEL_NO_STG_TMP
           (
            SPLIT_SYSID,
            SYS_ID,
            CODE,
            TEL_NO,
            CITY,
            CLEAN_STATUS
           )
      SELECT SPLIT_SYSID,
             SYS_ID,
             CODE,
             TEL_NO,
             CITY,
             CLEAN_STATUS
        FROM (SELECT /*+PARALLEL(A 4)*/
                     A.SPLIT_SYSID,
                     A.SYS_ID,
                     A.CODE,
                     A.TEL_NO,
                     A.CITY,
                     A.CLEAN_STATUS,
                     ROW_NUMBER() OVER(PARTITION BY A.SYS_ID,A.CODE||A.TEL_NO ORDER BY A.SYS_ID) RN_NO
                FROM PC_CLEAN_TEL_NO_8_TMP A)
        WHERE RN_NO = 1;
    COMMIT;
   
    -- 合并电话号码
    INSERT /*+APPEND*/
      INTO PC_CLEAN_TELEPHONE_STG_TMP
           (
            SYS_ID,
            TEL_NO,
            CITY,
            CLEAN_STATUS
           )
    SELECT SYS_ID,
           substr(LTRIM(MAX(SYS_CONNECT_BY_PATH(TEL_NO, ',')), ','),1,1000) TEL_NO,
           CITY,
           '1' CLEAN_STATUS
     FROM (SELECT /*+PARALLEL(A 4)*/
                  A.SYS_ID,
                  NVL2(A.CODE,A.CODE||'-','') || A.TEL_NO TEL_NO,
                  A.CITY,
                  A.CLEAN_STATUS,
                  ROW_NUMBER() OVER(PARTITION BY A.SYS_ID ORDER BY NVL2(A.CODE,A.CODE||'-','') || A.TEL_NO) RN_BY_ID,
                  ROW_NUMBER() OVER(ORDER BY A.SYS_ID, NVL2(A.CODE,A.CODE||'-','') || A.TEL_NO) + TO_NUMBER(A.SYS_ID) RN
             FROM PC_CLEAN_TEL_NO_STG_TMP A
            WHERE A.CLEAN_STATUS = '1')
     START WITH RN_BY_ID = 1
    CONNECT BY RN - 1 = PRIOR RN
     GROUP BY SYS_ID,CITY ORDER BY SYS_ID;      
    COMMIT;
   
    -- 增加一个 代理电话标识  STG 表
    INSERT /*+APPEND*/
      INTO PC_STG_TEL_NO_TMP
           (
            SPLIT_SYSID,
            SYS_ID,
            CODE,
            TEL_NO,
            CITY,
            IS_PROXY_PHONE_FLAG,
            TELEPHONE_TYPE
           )
    SELECT A.SPLIT_SYSID,
           A.SYS_ID,
           A.CODE,
           A.TEL_NO,
           A.CITY,
           CASE
             WHEN RN >= 4 THEN
                  'Y'
             ELSE
                  'N'
           END IS_PROXY_PHONE_FLAG,
           NVL2(A.CODE,'02','01') TELEPHONE_TYPE          
      FROM (SELECT /*+PARALLEL(B 4)*/
                   B.SPLIT_SYSID,
                   B.SYS_ID,
                   B.CODE,
                   B.TEL_NO,
                   B.CITY,
                   ROW_NUMBER()OVER(PARTITION BY B.CODE,B.TEL_NO ORDER BY B.SYS_ID ASC) RN
         FROM PC_CLEAN_TEL_NO_STG_TMP B
        WHERE B.CLEAN_STATUS = '1') A;
    COMMIT;
 
 /*  
    -- 后续需要通过 判断 sys_id 下的所有电话号码重复, 记录标识为“代理电话重复数据”
       需要和 更新比对确认
    -- 2009年11月23日 确认  这项功能 在入 BDL 层时更新 
 */
 
    -- 更新本次操作日志
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,26,NULL,'02',NULL,NULL,NULL,NULL);

  EXCEPTION
     WHEN OTHERS THEN
        p_errmsg := substr(sqlerrm,1,500);
        NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,26,NULL,'03',p_errmsg,NULL,NULL,NULL);
        RAISE;
  END SP_CLEAN_TELEPHONE_NO;

  /***********************************************************
  --功能说明:  清洗号牌种类
  --参数说明:
  --调用函数:
  --修改记录:  create by ex-qiuweisheng001/ex-liujiali001
  --*********************************************************/
  PROCEDURE SP_CLEAN_BRAND_TYPE
  IS
    p_id        NUMBER;         -- 日志记录id
    p_errmsg    VARCHAR2(500);  -- 错误记录

  BEGIN

    -- 操作记录
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,3,NULL,'01',NULL,NULL,NULL,NULL);

    --清空结果临时表
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_CLEAN_BRAND_TYPE_CD_STG_TMP');
   
    -- 去回车,头尾的全半角空格、Tab
    INSERT /*+APPEND*/
      INTO PC_CLEAN_BRAND_TYPE_1_TMP
           (SYS_ID,BRAND_TYPE,BRAND_TYPE_CODE)
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
             A.SYS_ID,
             NVL(TRIM(REPLACE(REPLACE(TO_SINGLE_BYTE(A.BRAND_TYPE),CHR(13),''),
                         CHR(10),'')),'小型汽车') BRAND_TYPE,
             TRIM(REPLACE(REPLACE(TO_SINGLE_BYTE(A.BRAND_TYPE_CODE),CHR(13),''),
                    CHR(10),'')) BRAND_TYPE_CODE
       FROM IDL_SEP_SRC_DATA A,
            IDL_EX_BATCH     B
      WHERE (BRAND_TYPE IS NOT NULL
         OR BRAND_TYPE_CODE IS NOT NULL)
         AND A.TCIMS_BATCH_ID = B.TCIMS_BATCH_ID
         AND B.SERIES_TYPE = '01'
         AND B.BATCH_STATUS = '11'
         AND B.PREPARE_FLAG = '1' ;
    COMMIT;   

    -- 转换 号牌种类 代码
    INSERT /*+APPEND*/
      INTO PC_CLEAN_BRAND_TYPE_2_TMP
           (SYS_ID,BRAND_TYPE,BRAND_TYPE_CODE)
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
             A.SYS_ID,
             A.BRAND_TYPE,
             CASE
               WHEN A.BRAND_TYPE_CODE IN
                    (SELECT BRAND_TYPE_CODE FROM BDL_RULE_REF_BRAND_TYPE) THEN
                    A.BRAND_TYPE_CODE
               ELSE
                    B.BRAND_TYPE_CODE
             END BRAND_TYPE_CODE
       FROM PC_CLEAN_BRAND_TYPE_1_TMP A,
            (SELECT DISTINCT BRAND_TYPE, BRAND_TYPE_CODE
              FROM BDL_RULE_REF_BRAND_TYPE)   B
      WHERE A.BRAND_TYPE = B.BRAND_TYPE(+);
    COMMIT;

    -- 辨别 是否需要手工清洗
    INSERT /*+APPEND*/
      INTO PC_CLEAN_BRAND_TYPE_CD_STG_TMP
           (SYS_ID,BRAND_TYPE_CODE,CLEAN_STATUS)
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
             A.SYS_ID,
             B.BRAND_TYPE_CODE,
             CASE
                 WHEN B.BRAND_TYPE_CODE IS NULL --AND B.BRAND_TYPE IS NULL
                      THEN
                      '0'
                 ELSE
                      '1'
             END CLEAN_STATUS
       FROM PC_CLEAN_BRAND_TYPE_2_TMP A,
            BDL_RULE_REF_BRAND_TYPE   B
      WHERE A.BRAND_TYPE_CODE = B.BRAND_TYPE_CODE(+);
    COMMIT;
       
    -- 更新本次操作日志
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,3,NULL,'02',NULL,NULL,NULL,NULL);

  EXCEPTION
     WHEN OTHERS THEN
        p_errmsg := substr(sqlerrm,1,500);
        NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,3,NULL,'03',p_errmsg,NULL,NULL,NULL);
        RAISE;
  END SP_CLEAN_BRAND_TYPE;

  /***********************************************************
  --功能说明:  清洗使用性质及代码
  --参数说明:
  --调用函数:
  --修改记录:  create by ex-qiuweisheng001/ex-liujiali001
  --*********************************************************/
  PROCEDURE SP_CLEAN_USAGE_ATTRIBUTE
  IS
    p_id        NUMBER;         -- 日志记录id
    p_errmsg    VARCHAR2(500);  -- 错误记录
  BEGIN

    -- 操作记录
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,4,NULL,'01',NULL,NULL,NULL,NULL);

    --清空结果临时表
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_CLEAN_USAGE_CODE_STG_TMP');

    --去回车,头尾的全半角空格、Tab、全角横杠、半角横杠
    INSERT /*+APPEND*/
    INTO PC_CLEAN_USAGE_ATTRIBUTE_1_TMP
         (SYS_ID,USAGE_ATTRIBUTE,USAGE_CODE)
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
             A.SYS_ID,
             NVL(trim(REPLACE(REPLACE(TO_SINGLE_BYTE(A.USAGE_ATTRIBUTE), CHR(13), ''),
                    CHR(10),'')),'非营运') USAGE_ATTRIBUTE,
             trim(REPLACE(REPLACE(TO_SINGLE_BYTE(A.USAGE_CODE), CHR(13), ''),
                    CHR(10),''))USAGE_CODE
        FROM IDL_SEP_SRC_DATA A,
             IDL_EX_BATCH     B
        WHERE A.TCIMS_BATCH_ID = B.TCIMS_BATCH_ID
          AND B.SERIES_TYPE = '01'
          AND B.BATCH_STATUS = '11'
          AND B.PREPARE_FLAG = '1';
    COMMIT;

    -- 按照代码转换表对使用性质名称进行转换
    INSERT /*+APPEND*/
      INTO PC_CLEAN_USAGE_ATTRIBUTE_2_TMP
           (SYS_ID,USAGE_ATTRIBUTE,USAGE_CODE)
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
             A.SYS_ID,
             B.USAGE_ATTRIBUTE,
             CASE
               WHEN A.USAGE_CODE IN
                   (SELECT USAGE_CODE FROM BDL_RULE_USAGE_ATTRIBUTE) THEN
                    A.USAGE_CODE
               ELSE
                    B.USAGE_CODE
              END USAGE_CODE
        FROM PC_CLEAN_USAGE_ATTRIBUTE_1_TMP A,
             BDL_RULE_USAGE_ATTRIBUTE       B
        WHERE A.USAGE_ATTRIBUTE = B.USAGE_ATTRIBUTE(+);
    COMMIT;

    -- 不在使用性质代码表中且该字段不为空的数据定义为需手工清洗数据
    -- 将符合标准的记录标识为清洗完成
    INSERT /*+APPEND*/
      INTO PC_CLEAN_USAGE_CODE_STG_TMP
           (SYS_ID,USAGE_CODE,USAGE_ATTRIBUTE,CLEAN_STATUS)
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
             A.SYS_ID,
             B.USAGE_CODE,
             B.USAGE_ATTRIBUTE,
             CASE
               WHEN B.USAGE_CODE IS NULL THEN
                    '0'
               ELSE
                    '1'
             END CLEAN_STATUS
        FROM PC_CLEAN_USAGE_ATTRIBUTE_2_TMP A,
             BDL_RULE_USAGE_ATTRIBUTE       B
        WHERE A.USAGE_CODE = B.USAGE_CODE(+);
    COMMIT;

    -- 更新本次操作日志
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,4,NULL,'02',NULL,NULL,NULL,NULL);

  EXCEPTION
     WHEN OTHERS THEN
        p_errmsg := substr(sqlerrm,1,500);
        NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,4,NULL,'03',p_errmsg,NULL,NULL,NULL);
        RAISE;
  END SP_CLEAN_USAGE_ATTRIBUTE;

  /***********************************************************
  --功能说明:  清洗发动机号
  --参数说明:
  --调用函数:
  --修改记录:  create by ex-qiuweisheng001/ex-liujiali001
  --*********************************************************/
  PROCEDURE SP_CLEAN_ENGINE_NO
  IS
    p_id        NUMBER;         -- 日志记录id
    p_errmsg    VARCHAR2(500);  -- 错误记录

    v_str       VARCHAR2(100) := '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
   
  BEGIN

    -- 操作记录
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,6,NULL,'01',NULL,NULL,NULL,NULL);

    --清空结果临时表
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_CLEAN_ENGINE_NO_STG_TMP');

    --去掉特殊符号。回车,头尾的全半角空格、Tab、全角横杠转换为半角横杠,去空格
    INSERT /*+APPEND*/
    INTO PC_CLEAN_ENGINE_NO_STG_TMP
         (SYS_ID,ENGINE_NUMBER)
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
             A.SYS_ID,
             TRANSLATE(
                REPLACE(UPPER(REPLACE(REPLACE(TO_SINGLE_BYTE(A.ENGINE_NUMBER), CHR(13), ''),
                                   CHR(10), '')),'E+',''),
                v_str||REPLACE(UPPER(REPLACE(REPLACE(TO_SINGLE_BYTE(A.ENGINE_NUMBER), CHR(13), ''),CHR(10), '')),'E+',''),
                v_str
                ) ENGINE_NUMBER
        FROM IDL_SEP_SRC_DATA A,
            IDL_EX_BATCH     B
       WHERE A.TCIMS_BATCH_ID = B.TCIMS_BATCH_ID
         AND B.SERIES_TYPE = '01'
         AND B.BATCH_STATUS = '11'
         AND B.PREPARE_FLAG = '1' ;
    COMMIT;

    -- 更新本次操作日志
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,6,NULL,'02',NULL,NULL,NULL,NULL);

  EXCEPTION
     WHEN OTHERS THEN
        p_errmsg := substr(sqlerrm,1,500);
        NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,6,NULL,'03',p_errmsg,NULL,NULL,NULL);
        RAISE;
  END SP_CLEAN_ENGINE_NO;

  /***********************************************************
  --功能说明:  清洗车架号
  --参数说明:
  --调用函数:
  --修改记录:  create by ex-qiuweisheng001/ex-liujiali001
  --*********************************************************/
  PROCEDURE SP_CLEAN_VEHICLE_FRAME_NO
  IS
    p_id        NUMBER;         -- 日志记录id
    p_errmsg    VARCHAR2(500);  -- 错误记录

    v_str       VARCHAR2(100) := '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
   
  BEGIN

    -- 操作记录
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,7,NULL,'01',NULL,NULL,NULL,NULL);

    --清空结果临时表
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_CLEAN_VEHICLE_FRAME_STG_TMP');

    --去掉特殊符号。回车,头尾的全半角空格、Tab、全角横杠转换为半角横杠,去空格
    --只包含数据和字母,非数字和字母字符全部删除
    INSERT /*+APPEND*/
      INTO PC_CLEAN_VEHICLE_FRAME_STG_TMP
           (SYS_ID,VEHICLE_FRAME)
    SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
           A.SYS_ID,
           TRANSLATE(
             REPLACE(UPPER(REPLACE(REPLACE(TO_SINGLE_BYTE(A.VEHICLE_FRAME), CHR(13), ''),CHR(10), '')), 'E+', ''),
             v_str || REPLACE(UPPER(REPLACE(REPLACE(TO_SINGLE_BYTE(A.VEHICLE_FRAME), CHR(13), ''),CHR(10), '')), 'E+', ''),
             v_str) VEHICLE_FRAME
     FROM IDL_SEP_SRC_DATA A,
            IDL_EX_BATCH     B
       WHERE A.TCIMS_BATCH_ID = B.TCIMS_BATCH_ID
         AND B.SERIES_TYPE = '01'
         AND B.BATCH_STATUS = '11'
         AND B.PREPARE_FLAG = '1' ;
    COMMIT;

    -- 更新本次操作日志
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,7,NULL,'02',NULL,NULL,NULL,NULL);

  EXCEPTION
     WHEN OTHERS THEN
        p_errmsg := substr(sqlerrm,1,500);
        NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,7,NULL,'03',p_errmsg,NULL,NULL,NULL);
        RAISE;
  END SP_CLEAN_VEHICLE_FRAME_NO;

  /***********************************************************
  --功能说明:  清洗车辆种类
  --参数说明:
  --调用函数:
  --修改记录:  create by ex-qiuweisheng001/ex-liujiali001
  --*********************************************************/
  PROCEDURE SP_CLEAN_VEHICLE_TYPE
  IS
    p_id        NUMBER;         -- 日志记录id
    p_errmsg    VARCHAR2(500);  -- 错误记录

  BEGIN

    -- 操作记录
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,5,NULL,'01',NULL,NULL,NULL,NULL);

    --清空结果临时表
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_CLEAN_VEHIC_TYPE_CD_STG_TMP');

    --去回车,头尾的全半角空格、Tab
    INSERT /*+APPEND*/
      INTO PC_CLEAN_VEHICLE_TYPE_1_TMP
           (SYS_ID,VEHICLE_TYPE,VEHICLE_TYPE_CODE)
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
             A.SYS_ID,
             REPLACE(REPLACE(TO_SINGLE_BYTE(A.VEHICLE_TYPE), CHR(13), ''),
                  CHR(10), '') VEHICLE_TYPE,
             REPLACE(REPLACE(TO_SINGLE_BYTE(A.VEHICLE_TYPE_CODE), CHR(13), ''),
                  CHR(10), '') VEHICLE_TYPE_CODE
       FROM IDL_SEP_SRC_DATA A,
            IDL_EX_BATCH     B
       WHERE (A.VEHICLE_TYPE IS NOT NULL
         OR A.VEHICLE_TYPE_CODE IS NOT NULL)
         AND A.TCIMS_BATCH_ID = B.TCIMS_BATCH_ID
         AND B.SERIES_TYPE = '01'
         AND B.BATCH_STATUS = '11'
         AND B.PREPARE_FLAG = '1';
    COMMIT;

    -- 按照代码转换表对车名称进行转换,将符合标准的记录标识为清洗完成
    INSERT /*+APPEND*/
      INTO PC_CLEAN_VEHICLE_TYPE_2_TMP
           (SYS_ID,VEHICLE_TYPE,VEHICLE_TYPE_CODE)
    SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
           A.SYS_ID,
           B.VEHICLE_TYPE,
           CASE
             WHEN A.VEHICLE_TYPE_CODE IN
                  (SELECT VEHICLE_TYPE_CODE FROM BDL_RULE_VEHICLE_TYPE) THEN
                  A.VEHICLE_TYPE_CODE
             ELSE
                  B.VEHICLE_TYPE_CODE
             END VEHICLE_TYPE_CODE
       FROM PC_CLEAN_VEHICLE_TYPE_1_TMP A,
            BDL_RULE_VEHICLE_TYPE       B
       WHERE A.VEHICLE_TYPE = B.VEHICLE_TYPE(+) ;
     COMMIT;


    -- 不在使用性质代码表中且该字段不为空的数据定义为需手工清洗数据
    INSERT /*+APPEND*/
      INTO PC_CLEAN_VEHIC_TYPE_CD_STG_TMP
           (SYS_ID,VEHICLE_TYPE_CODE,VEHICLE_TYPE,CLEAN_STATUS )
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
             A.SYS_ID,
             B.VEHICLE_TYPE_CODE,
             B.VEHICLE_TYPE,
             CASE
                 WHEN B.VEHICLE_TYPE_CODE IS NULL AND B.VEHICLE_TYPE IS NULL THEN
                      '0'
                 ELSE
                      '1'
             END CLEAN_STATUS
        FROM PC_CLEAN_VEHICLE_TYPE_2_TMP A,
             BDL_RULE_VEHICLE_TYPE       B
       WHERE A.VEHICLE_TYPE_CODE = B.VEHICLE_TYPE_CODE(+) ;
    COMMIT;

    -- 更新本次操作日志
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,5,NULL,'02',NULL,NULL,NULL,NULL);

  EXCEPTION
     WHEN OTHERS THEN
        p_errmsg := substr(sqlerrm,1,500);
        NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,5,NULL,'03',p_errmsg,NULL,NULL,NULL);
        RAISE;
  END SP_CLEAN_VEHICLE_TYPE;

  /***********************************************************
  --功能说明:  清洗称谓
  --参数说明:
  --调用函数:
  --修改记录:  create by ex-qiuweisheng001/ex-liujiali001
  --*********************************************************/
  PROCEDURE SP_CLEAN_SALUTATION
  IS
    p_id        NUMBER;         -- 日志记录id
    p_errmsg    VARCHAR2(500);  -- 错误记录
  BEGIN

    -- 操作记录
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,8,NULL,'01',NULL,NULL,NULL,NULL);

    --清空结果临时表
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_CLEAN_SALUTATION_STG_TMP');

    --去回车,头尾的全半角空格、Tab
    INSERT /*+APPEND*/
      INTO PC_CLEAN_SALUTATION_1_TMP
           (SYS_ID,SALUTATION)
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
             A.SYS_ID,
             TRIM(REPLACE(REPLACE(TO_SINGLE_BYTE(A.SALUTATION), CHR(13), '')
                 , CHR(10), '')) SALUTATION
       FROM IDL_SEP_SRC_DATA A,
            IDL_EX_BATCH     B
       WHERE A.TCIMS_BATCH_ID = B.TCIMS_BATCH_ID
         AND B.SERIES_TYPE = '01'
         AND B.BATCH_STATUS = '11'
         AND B.PREPARE_FLAG = '1';
    COMMIT;

    --转换新称谓
    INSERT /*+APPEND*/
      INTO PC_CLEAN_SALUTATION_STG_TMP
           (SYS_ID,SALUTATION)
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
             A.SYS_ID,
             NVL(B.NEW_SALUTATION,A.SALUTATION) SALUTATION
       FROM PC_CLEAN_SALUTATION_1_TMP A,
            BDL_RULE_SALUTATION B
      WHERE A.SALUTATION = B.OLD_SALUTATION(+);
    COMMIT;

    -- 更新本次操作日志
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,8,NULL,'02',NULL,NULL,NULL,NULL);

  EXCEPTION
     WHEN OTHERS THEN
        p_errmsg := substr(sqlerrm,1,500);
        NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,8,NULL,'03',p_errmsg,NULL,NULL,NULL);
        RAISE;
  END SP_CLEAN_SALUTATION;

  /***********************************************************
  --功能说明:  清洗客户性别
  --参数说明:
  --调用函数:
  --修改记录:  create by ex-qiuweisheng001/ex-liujiali001
  --注意事项:  整合数据时,用 身份证 转换过来的数据 替换(空值除外)
  --*********************************************************/
  PROCEDURE SP_CLEAN_SEX
  IS
    p_id        NUMBER;         -- 日志记录id
    p_errmsg    VARCHAR2(500);  -- 错误记录
  BEGIN

    -- 操作记录
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,9,NULL,'01',NULL,NULL,NULL,NULL);

    --清空结果临时表
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_CLEAN_SEX_STG_TMP');

    --去回车,头尾的全半角空格、Tab
    INSERT /*+APPEND*/
      INTO PC_CLEAN_SEX_1_TMP
           (SYS_ID,SEX)
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
             A.SYS_ID,
             TRIM(REPLACE(REPLACE(TO_SINGLE_BYTE(A.SEX), CHR(13), ''),chr(10),'')) SEX
       FROM IDL_SEP_SRC_DATA A,
            IDL_EX_BATCH     B
       WHERE A.TCIMS_BATCH_ID = B.TCIMS_BATCH_ID
         AND B.SERIES_TYPE = '01'
         AND B.BATCH_STATUS = '11'
         AND B.PREPARE_FLAG = '1';
    COMMIT;

    --转换
    INSERT /*+APPEND*/
      INTO PC_CLEAN_SEX_STG_TMP
           (SYS_ID,SEX)
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
             A.SYS_ID,
             NVL(B.NEW_SEX,A.SEX) SEX
       FROM PC_CLEAN_SEX_1_TMP A,
            BDL_RULE_SEX       B
       WHERE A.SEX = B.OLD_SEX(+);
    COMMIT;

    -- 更新本次操作日志
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,9,NULL,'02',NULL,NULL,NULL,NULL);

  EXCEPTION
     WHEN OTHERS THEN
        p_errmsg := substr(sqlerrm,1,500);
        NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,9,NULL,'03',p_errmsg,NULL,NULL,NULL);
        RAISE;
  END SP_CLEAN_SEX;

  /***********************************************************
  --功能说明:  清洗主驾人性别
  --参数说明:
  --调用函数:
  --修改记录:  create by ex-qiuweisheng001/ex-liujiali001
  --*********************************************************/
  PROCEDURE SP_CLEAN_MAIN_DRIVER_SEX
  IS
    p_id        NUMBER;         -- 日志记录id
    p_errmsg    VARCHAR2(500);  -- 错误记录
  BEGIN

    -- 操作记录
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,10,NULL,'01',NULL,NULL,NULL,NULL);

    --清空结果临时表
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_CLEAN_DRIVER_SEX_STG_TMP');

    --去回车,头尾的全半角空格、Tab
    INSERT /*+APPEND*/
      INTO PC_CLEAN_DRIVER_SEX_1_TMP
           (SYS_ID,MAIN_DRIVER_SEX)
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
             A.SYS_ID,
             TRIM(REPLACE(REPLACE(TO_SINGLE_BYTE(A.MAIN_DRIVER_SEX), CHR(13), ''),chr(10),'')) MAIN_DRIVER_SEX
       FROM IDL_SEP_SRC_DATA A,
            IDL_EX_BATCH     B
       WHERE A.TCIMS_BATCH_ID = B.TCIMS_BATCH_ID
         AND B.SERIES_TYPE = '01'
         AND B.BATCH_STATUS = '11'
         AND B.PREPARE_FLAG = '1';
    COMMIT;

    --转换
    INSERT /*+APPEND*/
      INTO PC_CLEAN_DRIVER_SEX_STG_TMP
           (SYS_ID,MAIN_DRIVER_SEX)
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
             A.SYS_ID,
             NVL(B.NEW_SEX,A.MAIN_DRIVER_SEX) MAIN_DRIVER_SEX
       FROM PC_CLEAN_DRIVER_SEX_1_TMP A,
            BDL_RULE_SEX              B
       WHERE A.MAIN_DRIVER_SEX = B.OLD_SEX(+);
    COMMIT;

    -- 更新本次操作日志
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,10,NULL,'02',NULL,NULL,NULL,NULL);

  EXCEPTION
     WHEN OTHERS THEN
        p_errmsg := substr(sqlerrm,1,500);
        NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,10,NULL,'03',p_errmsg,NULL,NULL,NULL);
        RAISE;
  END SP_CLEAN_MAIN_DRIVER_SEX;


  /***********************************************************
  --功能说明:  清洗 吨位\核定座位数\车辆价值\排气量
  --参数说明:
  --调用函数:
  --修改记录:  create by ex-qiuweisheng001/ex-liujiali001
  --*********************************************************/
  PROCEDURE SP_CLEAN_TON_SEAT_VALU_EXHAUST
  IS
    p_id        NUMBER;         -- 日志记录id
    p_errmsg    VARCHAR2(500);  -- 错误记录
  BEGIN

    -- 操作记录
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,11,NULL,'01',NULL,NULL,NULL,NULL);

    --清空结果临时表
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_CLEAN_SOME_NUMBER_STG_TMP');

    --去回车,头尾的全半角空格、Tab
    INSERT /*+APPEND*/
      INTO PC_CLEAN_SOME_NUMBER_1_TMP
           (SYS_ID,TON_NUMBER,SEAT_NUMBER,VEHICLE_VALUE,EXHAUST)
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
             A.SYS_ID,
             translate(TRIM(REPLACE(TO_SINGLE_BYTE(A.TON_NUMBER), CHR(13), '')),
                '0123456789.'||TRIM(REPLACE(TO_SINGLE_BYTE(A.TON_NUMBER), CHR(13), '')),
                '0123456789.'
             ) TON_NUMBER,
             translate(TRIM(REPLACE(TO_SINGLE_BYTE(A.SEAT_NUMBER), CHR(13), '')),
                '0123456789.'||TRIM(REPLACE(TO_SINGLE_BYTE(A.SEAT_NUMBER), CHR(13), '')),
                '0123456789.'
             ) SEAT_NUMBER,
             translate(TRIM(REPLACE(TO_SINGLE_BYTE(A.VEHICLE_VALUE), CHR(13), '')),
                '0123456789.'||TRIM(REPLACE(TO_SINGLE_BYTE(A.VEHICLE_VALUE), CHR(13), '')),
                '0123456789.'
             ) VEHICLE_VALUE,
             translate(TRIM(REPLACE(TO_SINGLE_BYTE(A.EXHAUST), CHR(13), '')),
                '0123456789.'||TRIM(REPLACE(TO_SINGLE_BYTE(A.EXHAUST), CHR(13), '')),
                '0123456789.'
             ) EXHAUST
       FROM IDL_SEP_SRC_DATA A,
            IDL_EX_BATCH     B
       WHERE A.TCIMS_BATCH_ID = B.TCIMS_BATCH_ID
         AND B.SERIES_TYPE = '01'
         AND B.BATCH_STATUS = '11'
         AND B.PREPARE_FLAG = '1';
    COMMIT;

    INSERT /*+APPEND*/
      INTO PC_CLEAN_SOME_NUMBER_STG_TMP
           (SYS_ID,TON_NUMBER,SEAT_NUMBER,VEHICLE_VALUE,EXHAUST)
      SELECT /*+PARALLEL(A 4)*/
             A.SYS_ID,
             TO_NUMBER(A.TON_NUMBER) TON_NUMBER,
             CASE
               WHEN TO_NUMBER(SEAT_NUMBER) < 999 THEN
                    TO_NUMBER(SEAT_NUMBER)
               ELSE
                    NULL
             END SEAT_NUMBER,
             TO_NUMBER(A.VEHICLE_VALUE) VEHICLE_VALUE,
             TO_NUMBER(A.EXHAUST)       EXHAUST
       FROM PC_CLEAN_SOME_NUMBER_1_TMP A;
    COMMIT;

    -- 更新本次操作日志
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,11,NULL,'02',NULL,NULL,NULL,NULL);

  EXCEPTION
     WHEN OTHERS THEN
        p_errmsg := substr(sqlerrm,1,500);
        NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,11,NULL,'03',p_errmsg,NULL,NULL,NULL);
        RAISE;
  END SP_CLEAN_TON_SEAT_VALU_EXHAUST;

  /***********************************************************
  --功能说明:  清洗 车身颜色
  --参数说明:
  --调用函数:
  --修改记录:  create by ex-qiuweisheng001/ex-liujiali001
  --*********************************************************/
  PROCEDURE SP_CLEAN_VEHICLE_BODY_COLOR
  IS
    p_id        NUMBER;         -- 日志记录id
    p_errmsg    VARCHAR2(500);  -- 错误记录
  BEGIN
    -- 操作记录
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,12,NULL,'01',NULL,NULL,NULL,NULL);

    --清空结果临时表
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_CLEAN_CAR_COLOR_STG_TMP');

    --去回车,头尾的全半角空格、Tab
    INSERT /*+APPEND*/
      INTO PC_CLEAN_CAR_COLOR_STG_TMP
           (SYS_ID,VEHICLE_BODY_COLOR)
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
             A.SYS_ID,
             TRIM(REPLACE(REPLACE(TO_SINGLE_BYTE(A.VEHICLE_BODY_COLOR), CHR(13), '')
                  , CHR(10), '')) VEHICLE_BODY_COLOR
        FROM IDL_SEP_SRC_DATA A,
            IDL_EX_BATCH     B
       WHERE A.TCIMS_BATCH_ID = B.TCIMS_BATCH_ID
         AND B.SERIES_TYPE = '01'
         AND B.BATCH_STATUS = '11'
         AND B.PREPARE_FLAG = '1';
    COMMIT;

    -- 更新本次操作日志
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,12,NULL,'02',NULL,NULL,NULL,NULL);

  EXCEPTION
     WHEN OTHERS THEN
        p_errmsg := substr(sqlerrm,1,500);
        NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,12,NULL,'03',p_errmsg,NULL,NULL,NULL);
        RAISE;
  END SP_CLEAN_VEHICLE_BODY_COLOR;

  /***********************************************************
  --功能说明:  清洗 身份证件号码
  --参数说明:
  --调用函数:
  --修改记录:  create by ex-qiuweisheng001/ex-liujiali001
  --*********************************************************/
  PROCEDURE SP_CLEAN_ID_NUMBER
  IS
    p_id        NUMBER;         -- 日志记录id
    p_errmsg    VARCHAR2(500);  -- 错误记录
  BEGIN
    -- 操作记录
    NETS_TCIMS_COMM.SP_LOG_RECORDER(p_id,21,NULL,'01',NULL,NULL,NULL,NULL);

    --清空结果临时表
    NETS_TCIMS_COMM.SP_TRUNCATE('TCIMSLOGTMP','PC_CLEAN_ID_NUMBER_STG_TMP');

    --去掉特殊符号。  全角转换为半角
    INSERT /*+APPEND*/
    INTO PC_CLEAN_ID_NUMBER_1_TMP
         (SYS_ID,ID_NUMBER)
      SELECT /*+PARALLEL(A 2) PARALLEL(B 2)*/
             A.SYS_ID,
             CASE
               WHEN SUBSTR(TO_SINGLE_BYTE(TRIM(A.ID_NUMBER)), -1) IN ('X', 'x') THEN
                TRANSLATE(TO_SINGLE_BYTE(TRIM(A.ID_NUMBER)),
                          '0123456789' || TO_SINGLE_BYTE(TRIM(A.ID_NUMBER)), '0123456789') || 'X'
               ELSE
                TRANSLATE(TO_SINGLE_BYTE(TRIM(A.ID_NUMBER)),
                          '0123456789' || TO_SINGLE_BYTE(TRIM(A.ID_NUMBER)), '0123456789')
             END ID_NUMBER
        FROM IDL_SEP_SRC_DATA A,
            IDL_EX_BATCH     B
       WHERE A.ID_TYPE = '01'
         AND A.TCIMS_BATCH_ID = B.TCIMS_BATCH_ID
         AND B.SERIES_TYPE = '01'
         AND B.BATCH_STATUS = '11'
         AND B.PREPARE_FLAG = '1';
    COMMIT;

    --15位升18
    INSERT /*+APPEND*/
    INTO PC_CLEAN_ID_NUMBER_2_TMP
         (SYS_ID,ID_NUMBER)
      SELECT /*+PARALLEL(A 4)*/
       A.SYS_ID,
       CASE
  <

分享到:
评论

相关推荐

    数据清洗PPT.zip

    3. SQL:用于处理数据库中的数据清洗任务,如DELETE、UPDATE语句。 4. Excel:适合小规模数据的初步清洗,如删除重复项、填充空值等。 5. ETL工具:如Informatica、DataStage等,可自动化进行大规模数据清洗。 四、...

    数据清洗研究综述 数据清洗现状

    ### 数据清洗研究综述 #### 一、引言 数据质量管理是信息系统建设和维护中的关键环节。随着信息技术的迅速发展和广泛应用,数据量呈现爆炸性增长趋势。然而,数据的快速增长也伴随着数据质量问题的日益凸显。据...

    数据清洗ppt-181119.zip

    清华大学课程ppt----数据清洗并没有统一的定义,其定义依赖于具体的应用领域。从广义上讲,数据清洗是将原始数据进行精简以去除冗余和消除不一致,并使剩余的数据转换成可接收的标准格式的过程;而狭义上的数据清洗...

    数据清洗数据源.zip

    数据清洗是数据分析过程中的关键步骤,它涉及到对原始数据进行检查、处理和转换,以确保数据的质量和准确性,为后续的数据分析和挖掘提供可靠的基础。在这个"数据清洗数据源.zip"压缩包中,包含了用于教育和培训...

    清华大学精品大数据之数据清洗课程PPT课件(38页)含习题 第3章 数据清洗ETL基本技术方法.pptx

    数据清洗是大数据领域中至关重要的一个环节,它涉及到数据的质量保证和有效利用。清华大学的这份精品课程《大数据之数据清洗》深入浅出地讲解了数据清洗的ETL(Extraction-Transformation-Loading)基本技术方法,...

    大数据——数据清洗加工技术架构方案(修改版)

    "大数据——数据清洗加工技术架构方案(修改版)" 在大数据时代,数据清洗加工技术架构方案是一个非常重要的组成部分。数据清洗是指对原始数据进行处理、转换和标准化,以确保数据的质量和一致性。今天,我们将对...

    mapreduce项目 数据清洗

    在这个"MapReduce项目 数据清洗"中,我们将探讨如何使用MapReduce对遗传关系族谱数据进行处理和清洗,以便进行后续分析。 1. **Map阶段**: 在Map阶段,原始数据被分割成多个小块(split),然后分配到不同的工作...

    Python3爬虫、数据清洗与可视化配套资源

    《Python3爬虫、数据清洗与可视化》配套资源是一份专为初学者设计的实践教程,旨在引导读者从基础开始掌握Python在数据获取、处理和展示方面的能力。这份资源包括了代码实例和数据集,帮助学习者通过实际操作来深化...

    数据清洗研究综述

    数据清洗是信息处理技术中的一个重要环节,旨在提高数据质量,使之能够准确反映现实世界的情况,并有效地支持组织的日常运作和决策。随着各行各业积累了大量数据,数据质量问题日益凸显,包括错误、不合法值、空值、...

    数据清洗规则模板.xlsx

    数据清洗规则

    python数据清洗

    ### Python 数据清洗知识点详解 #### 一、数据清洗的重要性及必要性 数据清洗在整个数据分析过程中扮演着极其重要的角色。在大数据时代,数据无处不在,然而,并非所有的数据都能直接拿来使用。通常情况下,原始...

    yolo数据集的清洗工具.zip

    yolo数据集的清洗工具 yolo数据集的清洗工具 yolo数据集的清洗工具 yolo数据集的清洗工具 yolo数据集的清洗工具 yolo数据集的清洗工具 yolo数据集的清洗工具 yolo数据集的清洗工具 yolo数据集的清洗工具 yolo数据集...

    数据清洗研究.rar

    3. 数据清洗工具和技术: - Python库:Pandas用于数据操作,Numpy进行数值计算,Missingno用于缺失值可视化,Scikit-learn实现插补方法。 - R语言:dplyr包进行数据操作,tidyr处理不规则数据,imputeTS处理时间...

    Excel数据清洗工具

    Excel数据清洗工具是一款专为Excel文件设计的数据处理软件。它可以有效地帮助用户完成一系列的数据清洗任务,如删除重复值、处理缺失值、格式化数据等。无论是单个文件还是整个文件夹中的多个文件,这款工具都能够...

    数据治理中数据清洗步骤及最佳实践.pdf

    3. 定期进行数据清洗。随着数据的不断更新和积累,定期的数据清洗是保持数据质量的必要手段。 4. 利用自动化工具。在数据清洗过程中,可以利用自动化工具来识别和处理常见问题,以提高效率。 5. 对数据清洗过程和...

    17-数据清洗-清洗电商评论数据1

    数据清洗在IT行业中是数据预处理的关键步骤,尤其在电商数据分析中,确保数据的质量和准确性至关重要。本节主要探讨如何清洗电商评论数据,涉及到的主要知识点包括数据清洗的定义、Json数据解析以及爬虫获取的数据...

    Web 数据清洗研究

    【Web 数据清洗研究】 随着互联网的快速发展,Web 数据已经成为数据获取的重要来源。然而,Web 数据的质量问题不容忽视,因为它们往往包含大量“脏数据”,如滥用缩写、数据输入错误、重复记录、丢失值等,这直接...

    数据清洗指南.pdf

    文档中介绍了一系列具体的数据清洗步骤,如通过缺失数据热图可视化缺失值、使用Pandas库来分析数据集的结构、使用Pandas提供的数据清洗功能进行数据处理等。这些步骤都是分步指南的一部分,帮助读者逐步掌握数据清洗...

    ETL中的数据清洗设计

    (3)数据清洗的具体实现过程:包括数据校验、数据转换、数据合并、数据删除等步骤。 在选择ETL处理方式时,需要考虑到数据清洗的需求。如果需要进行复杂的数据清洗,数据库中的ETL处理方式是最好的选择。这是因为...

Global site tag (gtag.js) - Google Analytics