根据身份证号码批量修改 出生日期，性别

1.根据身份证号码批量修改数据库出生日期
update fydj_commper cp
   set cp.fydjcp_birthday =  to_date(substr(cp.fydjcp_idnumber, 7, 4) || '-'||to_number(substr(cp.fydjcp_idnumber, 11, 2)) || '-' ||
          substr(cp.fydjcp_idnumber, 13, 2),'yyyy-MM-dd')
 where cp.fydjcp_idnumber is not null
   and length(cp.fydjcp_idnumber) = 18

   and to_number(substr(cp.fydjcp_idnumber, 11, 2))<=12  and  to_number(substr(cp.fydjcp_idnumber, 13, 2))<=31

2.根据身份证修改性别

 begin
  for res in ( SELECT CASE sex WHEN 0 THEN '1'  WHEN 1 THEN '0'  ELSE '其他' END AS sex ,idnum FROM (
SELECT MOD(NVL(SUBSTR(CP.FYDJCP_IDNUMBER, 15, 1), 0), 2) AS sex ,cp.fydjcp_idnumber idnum FROM fydj_commper cp WHERE LENGTH(cp.fydjcp_idnumber)=15
UNION
SELECT MOD(NVL(SUBSTR(CP.FYDJCP_IDNUMBER, 17, 1), 0), 2) AS sex ,cp.fydjcp_idnumber idnum FROM fydj_commper cp WHERE LENGTH(cp.fydjcp_idnumber)=18)) loop
    update fydj_commper per
       set  per.fydjcp_sex = res.sex
     where per.fydjcp_idnumber = res.idnum;
  end loop;
end;