oracle 导出身份证号_ORACLE对身份证号码处理相关的SQL汇总

isIDCard;

--3 获取年龄，那获取生日类似，也就不show了

create or replace getAge(p_IDcard varchar2) return integer is

IDcardlen integer;

IDcardyear integer;

begin

IDcardlen :=Length(p_IDcard);

if isidcard(p_IDcard)=1 and IDcardlen = 18 then

IDcardyear := to_number(substr(p_IDcard,7,4));

end if;

if isidcard(p_IDcard)=1 and IDcardlen = 15 then

IDcardyear := to_number('19'||substr(p_IDcard,7,2));

end if;

return to_number(to_char(sysdate,'yyyy'))-IDcardyear;

end getAge;

--3 获取年龄，精确到日

create or replace getAge(p_IDcard varchar2) return integer is

IDcardlen integer;

IDcardyear integer;

begin

IDcardlen :=Length(p_IDcard);

if isidcard(p_IDcard)=1 and IDcardlen = 18 then

IDcardyear := to_number(substr(p_IDcard,7,8));

end if;

if isidcard(p_IDcard)=1 and IDcardlen = 15 then

IDcardyear := to_number('19'||substr(p_IDcard,7,6));

end if;

return to_number(to_char(sysdate,'yyyyMMdd'))-IDcardyear;

end getAge;

--4 获取性别

create or replace getSex(p_IDcard varchar2) return varchar2 is

IDcardlen integer;

begin

IDcardlen :=Length(p_IDcard);

if isidcard(p_IDcard)<>1 then

return null;

end if;

if IDcardlen = 18 and Substr(p_IDcard,17,1) in (1,3,5,7,9) then

return ('男');

end if;

if IDcardlen = 18 and Substr(p_IDcard,17,1) in (2,4,6,8,0)then

return ('女');

end if;

if IDcardlen = 15 and Substr(p_IDcard,15,1) in (1,3,5,7,9) then

return ('男');

end if;

if IDcardlen = 15 and Substr(p_IDcard,15,1) in (2,4,6,8,0)then

return ('女');

end if;

end getSex;

/* (三)总结用途

主要结合某实际项目，说三点：

(1)实现15位/18位身份证号码从15位到18位

这个当然不能轻易机械的升级了，不过如果有重复数据时，判断重复数据，保留最新号码信息绝对是个好办法，如同时存在15位和18位，是否可以留18位就可以了？

(3)智能校验：判断身份证输入，以及关联的性别、*/