create or replace procedure tmp_txda_cw is
--存储过程声明
vdwm varchar(10); --以下字段用于对应TXDA_CW表中的字段
vbmdm varchar(10);
vtbr varchar(10);
vshbzh varchar(100);
vxm varchar(100);
vxb varchar(50);
vcsrq varchar(100);
vgzrq varchar(50);
vtxymd varchar(50);
vtlbh varchar(20);
vcheck number;

cwshbzh int := 0; --以下变量用于统计各个字段的错误出现次数
cwxm int := 0;
cwxb int := 0;
cwcsrq int := 0;
cwgzrq int := 0;
cwtxymd int := 0; --添加一个退休日期

begin
for c in (select dwm, bmdm, tbr, shbzh, xm, xb, csny, gzrq, txymd, tlb
from tmp_new_table1 full join tflb
on tlbh = tflh
where ((( CHECKXM(xm)=0 or
instr(xm, ' ') in (1, length(xm)) or
(asciistr(xm) like '%%' and instr(xm, ' ') between 2 and
length(XM) - 1)) and
txtz in
(select txtz
from tmp_new_table1
where xm not like '%·%'
or instr(xm, '·') in (1, length(xm)))) or
(tbr is null) or nvl(SHBZH.isvalid(shbzh), 'a') <> 'a' or
substr(shbzh, length(shbzh), length(shbzh)) = 'x' or --检验身份证最后一位是小写x的情况
(xb is null or xb not in ('0', '1')) or
(csny is null or csny > gzrq) or
(GZRQ is null or GZRQ > txymd)or --判断出生日期和工作日期的逻辑关系
(txymd is null )) --退休日期的对比
and substr(txtz, 1, 1) not in ('3', '8')) loop
--为属性赋值
vdwm := c.dwm;
vbmdm := c.bmdm;
vtbr := c.tbr;
vshbzh := c.shbzh;
vxm := c.xm;
vxb := c.xb;
vcsrq := to_char(c.csny, 'yyyymm');
vgzrq := to_char(c.gzrq, 'yyyymm');
vtxymd := to_char(c.txymd, 'yyyymm');
vtlbh := c.tlb; --已改

--------校验身份证，调用函数--------
vcheck := CHECK_SHBZH(c.shbzh);
IF VCHECK IN ('0', '1') THEN
  IF length(c.shbzh) = 15 THEN
    vshbzh  := vshbzh || '(15位请升级为18位、请核对出生日期)';
    cwshbzh := cwshbzh + 1;
  END IF;
ELSIF VCHECK = '2' THEN
  vshbzh  := vshbzh || '(身份证号长度有错、请核对出生日期)';
  cwshbzh := cwshbzh + 1;
ELSIF VCHECK = '3' THEN
  vshbzh  := vshbzh || '(身份证号中日期出错、请核对出生日期)';
  cwshbzh := cwshbzh + 1;
ELSIF VCHECK = '4' THEN
  vshbzh  := vshbzh || '(身份证号中数字处出现字母、请核对出生日期)';
  cwshbzh := cwshbzh + 1;
ELSIF VCHECK = '5' THEN
  vshbzh  := vshbzh || '(身份证校验出错、请核对出生日期)';
  cwshbzh := cwshbzh + 1;
ELSIF c.shbzh is null THEN
  vshbzh  := vshbzh || '(不能为空)';
  cwshbzh := cwshbzh + 1;
ELSIF length(c.shbzh)=18 THEN
  vshbzh  :=vshbzh ||'(两个出生日期不一致，请核对出生日期)';
  cwshbzh := cwshbzh + 1;
END IF;

IF substr(c.shbzh, length(c.shbzh)) = 'x' THEN
  vshbzh  := vshbzh || '(不能为小写x、请核对出生日期)';
  cwshbzh := cwshbzh + 1;
END IF;

--------校验姓名--------
IF length(trim(c.xm)) <>
   length(trim(replace(replace(replace(c.xm, ' ', ''), chr(10), ''),
                       chr(9),
                       ''))) THEN
  vxm  := c.xm || '(中间含有空格)';
  cwxm := cwxm + 1;
END IF;

begin
  select tbr
    into c.tbr
    from tmp_new_table1
    where (( 
          checkxm(xm) = 0 or  --checkxm校验名字函数
          (asciistr(xm) like '%\%' and instr(xm, ' ') between 2 and
          length(trim(XM)) - 1)) and tbr = c.tbr and
         tbr in (select tbr from tmp_new_table1
                   where xm not like '%·%'
                      or instr(xm, '·') in (1, length(xm))));
  

  vxm  := vxm || '(姓名不符合规则)';
  cwxm := cwxm + 1;
exception
  WHEN NO_DATA_FOUND THEN
    vxm := vxm;
END;

--------校验性别--------
IF length(c.shbzh) = 15 THEN
  IF mod(to_number(substr(c.shbzh, 15, 1)), 2) <> to_number(c.xb) THEN
    vxb  := vxb || '(与身份证性别位不符)';
    cwxb := cwxb + 1;
  END iF;

ELSIF length(c.shbzh) = 18 THEN
--还要判断第17位是英文的
IF regexp_like(substr(c.shbzh,17,1),'^([a-z]+|[A-Z])$') THEN
vxb := vxb || '(第17位与身份证性别位不符)';
cwxb := cwxb + 1;
END IF;
ELSIF length(c.shbzh) = 18 THEN
IF mod(to_number(substr(c.shbzh, 17, 1)), 2) <> to_number(c.xb) THEN
vxb := vxb || '(与身份证性别位不符)';
cwxb := cwxb + 1;
END IF;
END IF;

IF c.xb is null THEN
  vxb  := vxb || '(不能为空)';
  cwxb := cwxb + 1;
END IF;

--------校验出生日期--------
IF c.csny is null THEN
  vcsrq  := '(不能为空)';
  cwcsrq := cwcsrq + 1;
ELSIF length(c.shbzh) = 15 THEN
  IF substr(c.shbzh, 7, 4) <> substr(vcsrq, 3) THEN
    vcsrq  := vcsrq || '(与身份证日期校验有误)';
    cwcsrq := cwcsrq + 1;
  END IF;
ELSIF length(c.shbzh) = 18 THEN
  IF substr(c.shbzh, 7, 6) <> vcsrq THEN
    vcsrq  := vcsrq || '(与身份证日期校验有误)';
    cwcsrq := cwcsrq + 1;
  END IF;
END IF;

IF c.csny is not null THEN
  IF c.gzrq < c.csny THEN
    vcsrq  := vcsrq || '(与工作日期逻辑不符)';
    cwcsrq := cwcsrq + 1;
  END IF;
END IF;

--------校验工作日期--------
IF c.gzrq is null THEN
  IF c.txymd < c.gzrq THEN
    vgzrq  := vgzrq || '(与退休日期逻辑不符)';
    cwgzrq := cwgzrq + 1;
  END IF;
END IF;

--------校验退休日期--------
IF c.txymd is null THEN
  vtxymd  := '(不能为空）';
  cwtxymd := cwtxymd + 1;
END IF;

--插入拼接后的字段信息到TXDA_CW表中
insert into TXDA_CW2
  (dwm, bmdm, tbr, shbzh, xm, xb, csny, gzrq, txymd, tlbh)   --表里的列
values
  (vdwm, vbmdm, vtbr, vshbzh, vxm, vxb, vcsrq, vgzrq, vtxymd, vtlbh); --最后一个要改

commit;   

end loop;

--插入统计得到的各个字段出现的错误数到TXDA_CW_count表中
insert into TXDA_CW_COUNT2
(cshbzh, cxm, cxb, ccsrq, cgzrq, ctxymd)
values
(cwshbzh, cwxm, cwxb, cwcsrq, cwgzrq, cwtxymd);

commit;
end;