正则表达式写法
CREATE OR REPLACE FUNCTION Func_check_id_card( p_idCard IN VARCHAR2) 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
IF INSTRB( v_areaCode, SUBSTR( p_idCard, 1 , 2 ) || ',' ) = 0
THEN RETURN 0 ;
END IF ;
IF MOD ( TO_NUMBER( SUBSTRB( p_idCard, 7 , 2 ) ) + 1900 , 400 ) = 0 OR
( MOD ( TO_NUMBER( SUBSTRB( p_idCard, 7 , 2 ) ) + 1900 , 100 ) <> 0 AND MOD ( TO_NUMBER( SUBSTRB( p_idCard, 7 , 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
IF INSTRB( v_areaCode, SUBSTRB( p_idCard, 1 , 2 ) || ',' ) = 0
THEN RETURN 0 ;
END IF ;
IF MOD ( TO_NUMBER( SUBSTRB( p_idCard, 7 , 4 ) ) , 400 ) = 0 OR
( MOD ( TO_NUMBER( SUBSTRB( p_idCard, 7 , 4 ) ) , 100 ) <> 0 AND MOD ( TO_NUMBER( SUBSTRB( p_idCard, 7 , 4 ) ) , 4 ) = 0 )
THEN
v_regStr := '^[1-9][0-9]{5}(19|20)[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|20)[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 = upper( 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 Func_check_id_card;
/
非正则表达式写法
Create Or Replace Function Func_checkIdcard( p_idcard in varchar2) Return Number Is
v_sum Number;
v_mod Number;
v_length Number;
v_date Varchar2( 10 ) ;
v_isDate Boolean ;
v_isNumber Boolean ;
v_isNumber_17 Boolean ;
v_checkbit CHAR ( 1 ) ;
v_checkcode CHAR ( 11 ) := '10X98765432' ;
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,' ;
Function isNumber( p_string in varchar2) Return Boolean Is
i number;
k number;
flag boolean ;
v_length number;
Begin
flag := True ;
select length( p_string) into v_length from dual;
for i in 1. . v_length loop
k := ascii( substr( p_string, i, 1 ) ) ;
if k < 48 or k > 57
then
flag := False ;
Exit ;
end if ;
end loop ;
Return flag;
End isNumber;
Function isDate( p_date in varchar2) Return Boolean Is
v_flag boolean ;
v_year number;
v_month number;
v_day number;
v_isLeapYear boolean ;
Begin
v_flag := True ;
v_year := to_number( substr( p_date, 1 , 4 ) ) ;
v_month := to_number( substr( p_date, 5 , 2 ) ) ;
v_day := to_number( substr( p_date, 7 , 2 ) ) ;
if ( mod ( v_year, 400 ) = 0 ) Or ( mod ( v_year, 100 ) <> 0 And mod ( v_year, 4 ) = 0 ) then v_isLeapYear := True ; else v_isLeapYear := False ; end if ;
if v_month < 1 Or v_month > 12
then
v_flag := False ;
Return v_flag;
end if ;
if v_month in ( 1 , 3 , 5 , 7 , 8 , 10 , 12 ) and ( v_day < 1 or v_day > 31 ) then v_flag := False ; end if ;
if v_month in ( 4 , 6 , 9 , 11 ) and ( v_day < 1 or v_day > 30 ) then v_flag := False ; end if ;
if v_month in ( 2 )
then
if ( v_isLeapYear)
then
if ( v_day < 1 or v_day > 29 ) then v_flag := False ; end if ;
else
if ( v_day < 1 or v_day > 28 ) then v_flag := False ; end if ;
end if ;
end if ;
Return v_flag;
End isDate;
Begin
if p_idcard is null then return - 1 ; end if ;
select lengthb( p_idcard) into v_length from dual;
if v_length not in ( 15 , 18 ) then return - 1 ; end if ;
if instrb( v_areacode, substr( p_idcard, 1 , 2 ) || ',' ) = 0 then return - 5 ; end if ;
if v_length = 15
then
v_isNumber := isNumber( p_idcard) ;
if not ( v_isNumber) then return - 3 ; end if ;
elsif v_length = 18
then
v_isNumber := isNumber( p_idcard) ;
v_isNumber_17 := isNumber( substr( p_idcard, 1 , 17 ) ) ;
if not ( ( v_isNumber) or ( v_isNumber_17 and upper( substr( p_idcard, 18 , 1 ) ) = 'X' ) ) then return - 3 ; end if ;
end if ;
if v_length = 15
then
select '19' || substr( p_idcard, 7 , 6 ) into v_date from dual;
elsif v_length = 18
then
select substr( p_idcard, 7 , 8 ) into v_date from dual;
end if ;
v_isDate := isDate( v_date) ;
if not ( v_isDate) then return - 2 ; end if ;
if v_length = 18
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 = upper( substrb( p_idcard, 18 , 1 ) ) then return 1 ; else return - 4 ; end if ;
else return 1 ;
end if ;
End Func_checkIdcard;
/