15位身份证升18位身份证的Oracle函数

15位身份证升18位身份证的Oracle函数：

CREATEORREPLACEFUNCTION FUNC_AAC00215_18 （prm_aac002 varchar2）
return varchar2 is
ll_i number（4） := 18;
ll_row number（5） := 0;
var_aac002 varchar2（18）;
ll_sfz18 number（3）;
ll_sfz varchar2（3）;
var_sfz18 varchar（18）;
var_date varchar（10）;
dat_date date;
ll_num number（2）;
s_sfz varchar2（15）;
s_sss varchar2（20）:="X";
v_newaac002 varchar（18）;
n_length number（2）;
V_CHAR varchar2（15）;
s_date varchar2（15）;
begin
if prm_aac002 isnullthen
return"";
end if;
if length（prm_aac002） <> 15 and length（prm_aac002） <> 18 then
return"";
end if;
if length（prm_aac002） = 18 then
n_length := length（prm_aac002）;
FOR n_i IN 1 .. n_length LOOP
V_CHAR := SUBSTR（prm_aac002, n_i, 1）;
IF V_CHAR IN （"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"） AND N_I<18 THEN
v_newaac002 :=prm_aac002;
ELSIF N_I=18 AND V_CHAR IN（"0", "1", "2", "3", "4", "5", "6", "7", "8", "9","X"） THEN
v_newaac002 :=prm_aac002;
else
return"含义非法字符！";
END IF;
END LOOP;
/*2. 取出生日期是否有效*/
s_date := substr（prm_aac002, 7, 8）;
begin
select to_date（s_date, "yyyymmdd"） into dat_date from dual;
exception
when others then
return"出生日期不对！";
end;
if substr（prm_aac002,7, 2）>20 or substr（prm_aac002,7, 2） <19 then
return"年度不对！";
end if;
/*3. 最后位效验*/
while n_length >= 2 loop
begin
select to_number（substr（prm_aac002, 19 - n_length, 1））
into ll_num
from dual;
exception
when others then
return"";
end;
ll_row := ll_row + mod（power（2, （n_length - 1））, 11） *
to_number（substr（prm_aac002, 19 - n_length, 1））;
n_length := n_length - 1;
end loop;
ll_sfz18 := mod（ll_row, 11）;
select decode（to_char（ll_sfz18）,"0","1","1","0","2","X","3","9","4","8","5","7","6","6","7","5","8","4","9","3","10","2"）
into ll_sfz
from dual;
if ll_sfz <> substr（prm_aac002,18, 1） then
return"尾数不对！应为"||ll_sfz;
end if;
RETURN v_newaac002;
end if;
if prm_aac002 = "111111111111111111"then
return"";
end if;
if s_sfz <>"X"then
s_sss := s_sfz;
else
s_sss := prm_aac002;
end if;
if length（prm_aac002）=15 then
var_date := "19" || substr（s_sss, 7, 6）;
begin
select to_date（var_date, "yyyymmdd"） into dat_date from dual;
exception
when others then
return"";
end;
var_aac002 := substr（s_sss, 0, 6） || "19" ||
substr（s_sss, 7, 9）;
while ll_i >= 2 loop
begin
select to_number（substr（var_aac002, 19 - ll_i, 1））
into ll_num
from dual;
exception
when others then
return"";
end;
ll_row := ll_row + mod（power（2, （ll_i - 1））, 11） *
to_number（substr（var_aac002, 19 - ll_i, 1））;
ll_i := ll_i - 1;
end loop;
ll_sfz18 := mod（ll_row, 11）;
select decode（to_char（ll_sfz18）,"0","1","1","0","2","X","3","9","4","8","5","7","6","6","7","5","8","4","9","3","10","2"）
into ll_sfz
from dual;
var_sfz18 := var_aac002 || ll_sfz;
return var_sfz18;
end if;
end func_aac00215_18;
/
show errors;

使用如下:SELECT func_aac00215_18（"371102841104258"） FROM dual; Oracle中的LOB数据类型以及ibatis中处理该类型的typeHandlerOracle 11gr2 RAC环境配置OEM 报ORA-12514错误相关资讯 Oracle函数

Oracle字符串函数总结（06月20日）
Oracle lag（）与lead（）函数（12/01/2015 20:41:24）
Oracle ascii函数（07/26/2015 08:46:01）

Oracle使用简单函数（06月09日）
REGEXP_SUBSTR函数的整理（08/20/2015 20:41:01）
Oracle dump函数（07/26/2015 08:40:01）

本文评论查看全部评论（0）