最近在学习oracle,今天写了几个函数用来判断身份证号(15/18)均可,感觉自己做的很麻烦,而且罗嗦,故此,将源代码放在这里,请大家指正。
测试数据如下:
SQL> select isidcard('421123198510142320') from dual;ISIDCARD('421123198510142320')
-------------------------------------------------------------------
性别:男 生日:1985-10-14 地区:湖北省黄冈市罗田县SQL> select isidcard('421123851014232') from dual;ISIDCARD('421123851014232')
-------------------------------------------------------------------
性别:男 生日:1985-10-14或者2085-10-14 地区:湖北省黄冈市罗田县/*
110103198808081226
110103198808121224
11010719880812122x
110101198808121221
110117198808121222
421123198808121223
421125198808121226
42112419880812122x
421123198508121221
*/
数据库中idcardinfo表数据如下:
SQL> set pagesize 30;
SQL> select * from idcardinfo;IDC
---------------------------------------
110100北京市
110103北京市崇文区
110107北京市石景山区
110113北京市顺义区
110200北京市
110101北京市东城区
421123湖北省黄冈市罗田县
421124湖北省黄冈市英山县
110111北京市房山区
421125湖北省黄冈市浠水县
110116北京市怀柔区
421121湖北省黄冈市团风县
421122湖北省黄冈市红安县
110104北京市宣武区
110106北京市丰台区
110114北京市昌平区
110228北京市密云县
110108北京市海淀区
110109北京市门头沟区
110112北京市通州区
110117北京市平谷区
110102北京市西城区
110105北京市朝阳区
110115北京市大兴区
110229北京市延庆县已选择25行。CREATE OR REPLACE FUNCTION isidCard(str varchar2)
RETURN varchar2
IS
areaID char(6) :=substr(str,1,6);
birthdayID char(8);
sexID char(3);
checkID char(1);
areaStr varchar(20);
birthdayStr1 char(10);
birthdayStr2 char(10);
birthdayStr3 char(10);
birthdayStr4 char(10);
sexStr char(3);
flag char(2);
personInfo varchar2(200);begin
if(length(str)!=15 and length(str)!=18)then
return '长度不对!';
else
select checkarea(areaID) into areaStr from dual;
if(areaStr!='NULL') then --地址码是否正确
if(length(str)=15) then --15位
birthdayStr1 :='19'||substr(str,7,6);
birthdayStr2 :='20'||substr(str,7,6);
select checkbrithday(birthdayStr1) into birthdayStr3 from dual;
select checkbrithday(birthdayStr2) into birthdayStr4 from dual; if(birthdayStr3!='NULL' or birthdayStr4!='NULL')then
sexID :=substr(str,13,3);
select getSex(to_number(sexID)) into sexStr from dual;
personInfo :='性别:'||sexStr||' 生日:'||birthdayStr3||'或者'||birthdayStr4||' 地区:'||areaStr;
return personInfo;
else
return '日期错误!';
end if;
else --18位
birthdayID :=substr(str,7,8);
select checkbrithday(birthdayID) into birthdayStr1 from dual; if(birthdayStr1!='NULL')then select checkCode(str) into flag from dual;
if(flag='p')then
sexID :=substr(str,15,3);
select getSex(to_number(sexID)) into sexStr from dual; personInfo :='性别:'||sexStr||' 生日:'||birthdayStr1||' 地区:'||areaStr;
return personInfo;
else
return '校验码错误!';
end if;
else
return '日期错误!';
end if;
end if;
else
return '地址码错误!';
end if;
end if;
end;--地址码
CREATE OR REPLACE FUNCTION checkarea(str varchar2)
return varchar2
is
area varchar2(20);
flag number(2);
begin
select checkarea1(str) into flag from dual;
if(flag=1)then
select substr(idc,7,(length(idc)-6)) into area from idcardinfo where substr(idc,0,6)=str;
return area;
else
return 'NULL';
end if;
end;CREATE OR REPLACE FUNCTION checkarea1(str varchar2)
return number
is
area number(2);
begin
select count(1) into area from idcardinfo where substr(idc,0,6)=str;
return area;
end;
--出生日期
CREATE OR REPLACE FUNCTION checkbrithday(str varchar2)
return varchar2
is
birthday varchar2(10);
begin
select isdate2(str) into birthday from dual;
if(birthday!='NULL')then
return birthday;
else
return 'NULL';
end if;
end;
----
CREATE OR REPLACE FUNCTION isdate2(str varchar2)
RETURN varchar
IS
v_date date;
v_nls varchar2(100) default null;
BEGIN
SELECT 'NLS_DATE_LANGUAGE='''||value||''''
INTO v_nls
FROM v$nls_parameters
WHERE parameter='NLS_DATE_LANGUAGE';
v_date := to_date(str, 'yyyy-mm-dd', v_nls);
RETURN to_char(v_date,'yyyy-mm-dd'); EXCEPTION
WHEN OTHERS THEN
/*如果你希望看到报错, 就把下面的注释行打开*/
--raise;
RETURN 'NULL';
END;
--------------------------------------------------------------------
--性别
CREATE OR REPLACE FUNCTION getSex(str number)
RETURN varchar
is
begin
if(str/2=0)then
return '女';
else
return '男';
end if;
end;
--校验码
CREATE OR REPLACE FUNCTION checkCode(str varchar2)
RETURN varchar
is
wi char(35) :='7*9*10*5*8*4*2*1*6*3*7*9*10*5*8*4*2';
checkstr char(11) :='10X98765432';
sumcode number(10);
checkrel number(2);
lastCode char(1);
begin
sumcode:=to_number(substr(str,1,1))*to_number(substr(wi,1,1))+to_number(substr(str,2,1))*to_number(substr(wi,3,1))+to_number(substr(str,3,1))*to_number(substr(wi,5,2))+to_number(substr(str,4,1))*to_number(substr(wi,8,1))+to_number(substr(str,5,1))*to_number(substr(wi,10,1))+to_number(substr(str,6,1))*to_number(substr(wi,12,1))+to_number(substr(str,7,1))*to_number(substr(wi,14,1))+to_number(substr(str,8,1))*to_number(substr(wi,16,1))+to_number(substr(str,9,1))*to_number(substr(wi,18,1))+to_number(substr(str,10,1))*to_number(substr(wi,20,1))+to_number(substr(str,11,1))*to_number(substr(wi,22,1))+to_number(substr(str,12,1))*to_number(substr(wi,24,1))+to_number(substr(str,13,1))*to_number(substr(wi,26,2))+to_number(substr(str,14,1))*to_number(substr(wi,29,1))+to_number(substr(str,15,1))*to_number(substr(wi,31,1))+to_number(substr(str,16,1))*to_number(substr(wi,33,1))+to_number(substr(str,17,1))*to_number(substr(wi,35,1));
checkrel :=(sumcode mod 11)+1;
select substr(checkstr,checkrel,1) into lastCode from dual;
if(upper(lastCode)=upper(substr(str,18,1)))then
return 'p';
else
return 'np';
end if;
end;
SQL> select isidcard('421123198502295231') from dual;ISIDCARD('421123198502295231')
-------------------------------------------------------
日期错误!
{
string Id = TextBox1.Text.ToString();
string[] arrVarifyCode = ("1,0,x,9,8,7,6,5,4,3,2").Split(',');
string[] Wi = ("7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2").Split(',');
char[] Ai = Id.ToCharArray();
int sum = 0;
for (int i = 0; i < 17; i++)
{
sum += int.Parse(Wi[i]) * int.Parse(Ai[i].ToString());
}
int y = -1;
Math.DivRem(sum, 11, out y);
string lcode = arrVarifyCode[y];
Label1.Text =Label1.Text+ Id + lcode+"(#)";
}生成号码:/*
110103198808081226
110103198808121224
11010719880812122x
110101198808121221
110117198808121222
421123198808121223
421125198808121226
42112419880812122x
421123198508121221
*/
RETURN varchar
is
begin
if(str mod 2 =0)then
return '女';
else
return '男';
end if;
end;
RETURN INT
IS
v_regstr VARCHAR2 (2000);
v_sum NUMBER;
v_mod NUMBER;
v_checkcode CHAR (11) := '10X98765432';
v_checkbit CHAR (1);
v_areacode VARCHAR2 (2000) :='11,12,13,14,15,21,22,23,31,32,33,34,35,36,37,41,42,43,44,45,46,50,51,52,53,54,61,62,63,64,65,71,81,82,91,';
BEGIN
CASE LENGTHB (p_idcard)
WHEN 15
THEN -- 15位
IF INSTRB (v_areacode, SUBSTR (p_idcard, 1, 2) || ',') = 0
THEN
RETURN 0;
END IF; IF MOD (TO_NUMBER (SUBSTRB (p_idcard, 6, 2)) + 1900, 400) = 0
OR ( MOD (TO_NUMBER (SUBSTRB (p_idcard, 6, 2)) + 1900, 100) <>
0
AND MOD (TO_NUMBER (SUBSTRB (p_idcard, 6, 2)) + 1900, 4) = 0
)
THEN -- 闰年
v_regstr :=
'^[1-9][0-9]{5}[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|[1-2][0-9]))[0-9]{3}$';
ELSE
v_regstr :=
'^[1-9][0-9]{5}[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|1[0-9]|2[0-8]))[0-9]{3}$';
END IF; IF REGEXP_LIKE (p_idcard, v_regstr)
THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
WHEN 18
THEN -- 18位
IF INSTRB (v_areacode, SUBSTRB (p_idcard, 1, 2) || ',') = 0
THEN
RETURN 0;
END IF; IF MOD (TO_NUMBER (SUBSTRB (p_idcard, 6, 4)), 400) = 0
OR ( MOD (TO_NUMBER (SUBSTRB (p_idcard, 6, 4)), 100) <> 0
AND MOD (TO_NUMBER (SUBSTRB (p_idcard, 6, 4)), 4) = 0
)
THEN -- 闰年
v_regstr :=
'^[1-9][0-9]{5}19[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|[1-2][0-9]))[0-9]{3}[0-9Xx]$';
ELSE
v_regstr :=
'^[1-9][0-9]{5}19[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|1[0-9]|2[0-8]))[0-9]{3}[0-9Xx]$';
END IF; IF REGEXP_LIKE (p_idcard, v_regstr)
THEN
v_sum :=
( TO_NUMBER (SUBSTRB (p_idcard, 1, 1))
+ TO_NUMBER (SUBSTRB (p_idcard, 11, 1))
)
* 7
+ ( TO_NUMBER (SUBSTRB (p_idcard, 2, 1))
+ TO_NUMBER (SUBSTRB (p_idcard, 12, 1))
)
* 9
+ ( TO_NUMBER (SUBSTRB (p_idcard, 3, 1))
+ TO_NUMBER (SUBSTRB (p_idcard, 13, 1))
)
* 10
+ ( TO_NUMBER (SUBSTRB (p_idcard, 4, 1))
+ TO_NUMBER (SUBSTRB (p_idcard, 14, 1))
)
* 5
+ ( TO_NUMBER (SUBSTRB (p_idcard, 5, 1))
+ TO_NUMBER (SUBSTRB (p_idcard, 15, 1))
)
* 8
+ ( TO_NUMBER (SUBSTRB (p_idcard, 6, 1))
+ TO_NUMBER (SUBSTRB (p_idcard, 16, 1))
)
* 4
+ ( TO_NUMBER (SUBSTRB (p_idcard, 7, 1))
+ TO_NUMBER (SUBSTRB (p_idcard, 17, 1))
)
* 2
+ TO_NUMBER (SUBSTRB (p_idcard, 8, 1)) * 1
+ TO_NUMBER (SUBSTRB (p_idcard, 9, 1)) * 6
+ TO_NUMBER (SUBSTRB (p_idcard, 10, 1)) * 3;
v_mod := MOD (v_sum, 11);
v_checkbit := SUBSTRB (v_checkcode, v_mod + 1, 1); IF v_checkbit = SUBSTRB (p_idcard, 18, 1)
THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
ELSE
RETURN 0;
END IF;
ELSE
RETURN 0; -- 身份证号码位数不对
END CASE;
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END fn_checkidcard;
/
能具体点吗 刘立兄弟
-------------------------------------------------------------------
性别:男 生日:1985-10-14 地区:湖北省黄冈市罗田县我指的是由身份证号,转成 具体的 身份信息, 这个动作,我们不是用procedure来做的,而是把这个逻辑计算,放到我们的一个专门的应用程序里进行计算。还有一次,是直接调用别人的web service来得到这些身份信息的。
(⊙o⊙)哦 你的意思是在应用程序里面来进行这些判断,或者是调用别人的webservice(⊙v⊙)嗯 学习了
=====================================================================
个人观点, 数据库就是数据库,是用来储存数据的,不要让数据库做太多和数据存储无关的事情叻。
=====================================================================
比如320115200712030920
SQL> select * from idcardinfo;IDC
---------------------------------------
110100 北京市
110103北京市崇文区
110107北京市石景山区
110113北京市顺义区
110200北京市
110101 北京市东城区
421123湖北省黄冈市罗田县
421124湖北省黄冈市英山县
110111北京市房山区
421125湖北省黄冈市浠水县
110116北京市怀柔区
421121湖北省黄冈市团风县
421122湖北省黄冈市红安县
110104北京市宣武区
110106北京市丰台区
110114北京市昌平区
110228北京市密云县
110108北京市海淀区
110109 北京市门头沟区
110112北京市通州区
110117北京市平谷区
110102北京市西城区
110105北京市朝阳区
110115 北京市大兴区
110229北京市延庆县已选择25行。
(
p_id_card in varchar2
)
RETURN INTIS
/* 身份证号码验证, 返回值 1 正确 0 错误 */
v_sum number;
v_mod number;
v_checkcode char(11) := '10X98765432';
v_checkbit char(1);
v_checkbit1 char(1);
v_areacode varchar2(2000) := '11,12,13,14,15,21,22,23,31,32,33,34,35,36,37,41,42,43,44,45,46,50,51,52,53,54,61,62,63,64,65,71,81,82,91';beginif p_id_card is null then
--dbms_output.put_line('传入错误!');
return 0;
end if;-- 判断位数
if length(p_id_card) <> 15 and length(p_id_card) <> 18 then
--dbms_output.put_line('位数错误!');
return 0;
end if;-- 判断地区代码
if instr(v_areacode, substr(p_id_card, 1, 2)) = 0 then
--dbms_output.put_line('地区代码错误!');
return 0;
end if;-- 如果是15位
if length(p_id_card) = 15 then
-- 判断第三位-第八位
for i in 3 .. 8 loop
if substr(p_id_card, i, 1) not between '0' and '9' then
--dbms_output.put_line('第' || i || '位数字错误!');
return 0;
end if;
end loop; --判断月份和日期
if substr(p_id_card, 9, 2) not in ('01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12') then
--dbms_output.put_line('月份错误!');
return 0;
end if; --判断闰月
if substr(p_id_card, 9, 2) in ('01', '03', '05', '07', '08', '10', '12') then
if substr(p_id_card, 11, 1) not between '0' and '3' then
--dbms_output.put_line('闰月日期第一位错误!');
return 0;
end if;
if substr(p_id_card, 12, 1) not between '0' and '9' then
--dbms_output.put_line('闰月日期第二位错误!');
return 0;
end if;
if to_number(substr(p_id_card, 11, 2)) not between 1 and 31 then
--dbms_output.put_line('闰月日期错误!');
return 0;
end if;
end if; --判断非闰月
if substr(p_id_card, 9, 2) in ('04', '06', '09', '11') then
if substr(p_id_card, 11, 1) not between '0' and '3' then
--dbms_output.put_line('非闰月日期第一位错误!');
return 0;
end if; if substr(p_id_card, 12, 1) not between '0' and '9' then
--dbms_output.put_line('非闰月日期第二位错误!');
return 0;
end if; if to_number(substr(p_id_card, 11, 2)) not between 1 and 30 then
--dbms_output.put_line('非闰月日期错误!');
return 0;
end if;
end if; --判断2月
if substr(p_id_card, 9, 2) = '02' then
if substr(p_id_card, 11, 1) not between '0' and '2' then
--dbms_output.put_line('二月日期第一位错误!');
return 0;
end if; if substr(p_id_card, 12, 1) not between '0' and '9' then
--dbms_output.put_line('二月日期第二位错误!');
return 0;
end if; if mod(to_number(substr(p_id_card, 7, 2)) + 1900, 400) = 0 or
(mod(to_number(substr(p_id_card, 7, 2)) + 1900, 100) <> 0 and
mod(to_number(substr(p_id_card, 7, 2)) + 1900, 4) = 0) then
-- 闰年
if to_number(substr(p_id_card, 11, 2)) not between 1 and 29 then
--dbms_output.put_line('闰年2月份日期错误!');
return 0;
end if;
else
if to_number(substr(p_id_card, 11, 2)) not between 1 and 28 then
--dbms_output.put_line('非闰年2月份日期错误!');
return 0;
end if;
end if;
end if; --判断13-15位
for i in 13 .. 15 loop
if substr(p_id_card, i, 1) not between '0' and '9' then
--dbms_output.put_line('第' || i || '位错误!');
return 0;
end if;
end loop;
end if;--如果是18位
if length(p_id_card) = 18 then
--判断第二位-第六位
for i in 3 .. 6 loop
if substr(p_id_card, i, 1) not between '0' and '9' then
--dbms_output.put_line('第' || i || '位数字错误!');
return 0;
end if;
end loop; --判断年份
if substr(p_id_card, 7, 2) not in ('19', '20') then
--dbms_output.put_line('年份错误!');
return 0;
end if; --判断9-10位
for i in 9 .. 10 loop
if substr(p_id_card, i, 1) not between '0' and '9' then
--dbms_output.put_line('第' || i || '位数字错误!');
return 0;
end if;
end loop; --判断月份和日期
if substr(p_id_card, 11, 2) not in ('01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12') then
--dbms_output.put_line('月份错误!');
return 0;
end if; --判断闰月
if substr(p_id_card, 11, 2) in ('01', '03', '05', '07', '08', '10', '12') then
if substr(p_id_card, 13, 1) not between '0' and '3' then
--dbms_output.put_line('闰月日期第一位错误!');
return 0;
end if; if substr(p_id_card, 14, 1) not between '0' and '9' then
--dbms_output.put_line('闰月日期第二位错误!');
return 0;
end if; if to_number(substr(p_id_card, 13, 2)) not between 1 and 31 then
--dbms_output.put_line('闰月日期错误!');
return 0;
end if;
end if; --判断非闰月
if substr(p_id_card, 11, 2) in ('04', '06', '09', '11') then
if substr(p_id_card, 13, 1) not between '0' and '3' then
--dbms_output.put_line('非闰月日期第一位错误!');
return 0;
end if; if substr(p_id_card, 14, 1) not between '0' and '9' then
--dbms_output.put_line('非闰月日期第二位错误!');
return 0;
end if; if to_number(substr(p_id_card, 13, 2)) not between 1 and 30 then
--dbms_output.put_line('非闰月日期错误!');
return 0;
end if;
end if; --判断2月
if substr(p_id_card, 11, 2) = '02' then
if substr(p_id_card, 13, 1) not between '0' and '2' then
--dbms_output.put_line('二月日期第一位错误!');
return 0;
end if; if substr(p_id_card, 14, 1) not between '0' and '9' then
--dbms_output.put_line('二月日期第二位错误!');
return 0;
end if; if mod(to_number(substr(p_id_card, 7, 4)), 400) = 0 or
(mod(to_number(substr(p_id_card, 7, 4)), 100) <> 0 and
mod(to_number(substr(p_id_card, 7, 4)), 4) = 0) then
-- 闰年
if to_number(substr(p_id_card, 13, 2)) not between 1 and 29 then
--dbms_output.put_line('闰年2月份日期错误!');
return 0;
end if;
else
if to_number(substr(p_id_card, 13, 2)) not between 1 and 28 then
--dbms_output.put_line('非闰年2月份日期错误!');
return 0;
end if;
end if;
end if; --判断15-17位
for i in 15 .. 17 loop
if substr(p_id_card, i, 1) not between '0' and '9' then
--dbms_output.put_line('第' || i || '位错误!');
return 0;
end if;
end loop; --判断18位
if substr(p_id_card, 18, 1) not between '0' and '9' and
substr(p_id_card, 18, 1) not in ('x', 'X') then
--dbms_output.put_line('第十八位错误!');
return 0;
end if; v_sum:= (to_number(substr(p_id_card, 1, 1)) + to_number(substr(p_id_card, 11, 1))) * 7 +
(to_number(substr(p_id_card, 2, 1)) + to_number(substr(p_id_card, 12, 1))) * 9 +
(to_number(substr(p_id_card, 3, 1)) + to_number(substr(p_id_card, 13, 1))) * 10 +
(to_number(substr(p_id_card, 4, 1)) + to_number(substr(p_id_card, 14, 1))) * 5 +
(to_number(substr(p_id_card, 5, 1)) + to_number(substr(p_id_card, 15, 1))) * 8 +
(to_number(substr(p_id_card, 6, 1)) + to_number(substr(p_id_card, 16, 1))) * 4 +
(to_number(substr(p_id_card, 7, 1)) + to_number(substr(p_id_card, 17, 1))) * 2 +
to_number(substr(p_id_card, 8, 1)) * 1 +
to_number(substr(p_id_card, 9, 1)) * 6 +
to_number(substr(p_id_card, 10, 1)) * 3;
v_mod:= mod(v_sum, 11);
v_checkbit := substr(v_checkcode, v_mod + 1, 1); if v_checkbit = 'X' then
v_checkbit1 := 'x';
else
v_checkbit1 := v_checkbit;
end if; if v_checkbit <> substr(p_id_card, 18, 1) and
v_checkbit1 <> substr(p_id_card, 18, 1) then
--dbms_output.put_line('校验位错误!');
return 0;
end if;
end if;return 1;EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
return 0;
END SF_CHECKIDCARD;