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;