13
08年4月
oracle pl/sql身份证15位转15位的function
贝贝爸 发表在 技术文章 |工作中经常遇到身份证要从15位转换到18位的数据，朋友给了一个这样的oracle,pl/sql的函数。测试过，没问题贴出来，备用。
create or replace function f_15to18(idCardNum varchar2) return varchar2 is
Result varchar2(20);
i smallint;
num smallint:=0;
sNum varchar2(2);
code char(1);
temp17 varchar2(20);
begin
if length(idCardNum)=18 or idCardNum is null then
Result:=upper(idCardNum);
elsif length(idCardNum)=15 then
temp17:=substr(idCardNum,1,6) || '19' || substr(idCardNum,7,9);
i:=18;
while i>1 loop
num:=num+mod(power(2,i-1),11)*substr(temp17,19-i,1) ;
i:=i-1;
end loop;
num:=mod(num,11);
sNum:=num || '';
if num=0 then
code:='1';
elsif num=1 then
code:='0';
elsif num=2 then
code:='X';
else
code:=(12-num) || '';
end if;
Result:=temp17 || code;
else
Result:= idCardNum;
end if;
return Result;
exception
when others then
dbms_output.put_line(idCardNum);
end f_15to18;
/
Result varchar2(20);
i smallint;
num smallint:=0;
sNum varchar2(2);
code char(1);
temp17 varchar2(20);
begin
if length(idCardNum)=18 or idCardNum is null then
Result:=upper(idCardNum);
elsif length(idCardNum)=15 then
temp17:=substr(idCardNum,1,6) || '19' || substr(idCardNum,7,9);
i:=18;
while i>1 loop
num:=num+mod(power(2,i-1),11)*substr(temp17,19-i,1) ;
i:=i-1;
end loop;
num:=mod(num,11);
sNum:=num || '';
if num=0 then
code:='1';
elsif num=1 then
code:='0';
elsif num=2 then
code:='X';
else
code:=(12-num) || '';
end if;
Result:=temp17 || code;
else
Result:= idCardNum;
end if;
return Result;
exception
when others then
dbms_output.put_line(idCardNum);
end f_15to18;
/
