根据 身份证号 生成出生日期

--查询15位和18位
select to_char('19')||  SUBSTR (to_char(sfzh), 7, 2) || '-'|| SUBSTR (to_char(sfzh), 9,2) || '-' || SUBSTR (to_char(sfzh),11,2)
from xzcbry1 where length(sfzh) = 15
 
select SUBSTR (sfzh, 7, 4) || '-'|| SUBSTR (sfzh, 11,2) || '-' || SUBSTR (sfzh,13,2)
from xzcbry1 where length(sfzh) = 18

 

 

--查询有问题的身份证号  位数不对，月份不对，日期不对
/*update xzcbry1 set csrq =null */
select * from xzcbry1
where (length(SFZH)<>15 and length(SFZH)<>18)
or (length(SFZH)=15 and ((Substr(SFZH,9,2)>12)
or (Substr(SFZH,11,2) > 31)
or (Substr(SFZH,9,2) in (01,03,05,07,08,10,12) and Substr(SFZH,11,2)>31)
or (Substr(SFZH,9,2) in (04,06,09,11) and Substr(SFZH,11,2)>30)
or (Substr(SFZH,9,2)=02 and Substr(SFZH,11,2)>29)))
or (length(sfzh)=18 and (Substr(sfzh,7,2)<'19' or Substr(sfzh,7,2)>'20'
or (Substr(sfzh,11,2)>12)
or (Substr(sfzh,11,2) in (01,03,05,07,08,10,12) and Substr(sfzh,13,2)>31)
or (Substr(sfzh,11,2) in (04,06,09,11) and Substr(sfzh,13,2)>30)
or (Substr(sfzh,11,2)=02 and Substr(sfzh,13,2)>29))) for update

-- 性别有错误
select xm,sfzh,xb
from xzcbry1
where (((length(sfzh)=15) and (Substr(sfzh,15,1) in (1,3,5,7,9)) and xb<>'男')
or ((length(sfzh)=15) and (Substr(sfzh,15,1) in (2,4,6,8,0)) and xb<>'女'))
or (((length(sfzh)=18) and (Substr(sfzh,17,1) in (1,3,5,7,9)) and xb<>'男')
or ((length(sfzh)=18) and (Substr(sfzh,17,1) in (2,4,6,8,0)) and xb<>'女'))

 

 

 

 

创建 oracle procedure

create or replace procedure RYCSRQ_NEW
is
  cursor cur_xzcbry is
 select *
 from xzcbry1
 order by xh;

 v_xzcbry cur_xzcbry%rowtype;

  V_MAXXMDM CHAR(6);
  v_xmdm char(6);
  v_lsybbh char(16);
  v_hzbh char(16);
  v_hzxm char(10);
  v_qydm char(2) := '';
  v_xh number;
  v_kxh number;
  i number(8);
  v_czdm char(6);
  v_csrq date;
  v_hh number;
  v_xb char(2);
begin
    i := 0;

 

 

   V_MAXXMDM:='000000';

 

    open cur_xzcbry;
    fetch cur_xzcbry into v_xzcbry;
    while cur_xzcbry%found loop

 

   begin
  -- 根据身份证号生成出生日期
     if length(v_xzcbry.sfzh) >0  then
             if lengthb(v_xzcbry.sfzh)=18 then
                v_csrq:=to_date(substrb(v_xzcbry.sfzh,7,4)||'-'||substrb(v_xzcbry.sfzh,11,2)||'-'||substrb(v_xzcbry.sfzh,13,2),'yyyy-mm-dd');
             elsif lengthb(v_xzcbry.sfzh)=15 then
                v_csrq:=to_date('19'||substrb(v_xzcbry.sfzh,7,2)||'-'||substrb(v_xzcbry.sfzh,9,2)||'-'||substrb(v_xzcbry.sfzh,11,2),'yyyy-mm-dd');
             else
                begin
                 v_csrq:=to_date(v_xzcbry.csrq,'yyyy-mm-dd');
                exception
                 when others then
                 v_csrq:= to_date('2009-01-01','YYYY-MM-DD');
                end;
             end if;
     else
               begin
                 v_csrq:=to_date(v_xzcbry.csrq,'yyyy-mm-dd');
                exception
                 when others then
                 v_csrq:= to_date('2009-01-01','YYYY-MM-DD');
                end;
     end if;

     update xzcbry1 set csrq= v_csrq  where xh=v_xzcbry.xh;

    exception
          when others then
               null;
    end;

   fetch cur_xzcbry into v_xzcbry;
    end loop;
    close cur_xzcbry;

 

    dbms_output.put_line(to_char(i));
exception
 when others then
 rollback;
 if cur_xzcbry%isopen then
  close cur_xzcbry;
 end if;

end;