Oracle身份证号验证函数

Oracle身份证号验证函数

Create Or Replace Function f_Is_身份证(v_Input Varchar2) Return Number As

  ----------------------------------------------------------------

  --功能：检查本输入数据是否为身份证件

  --参数：如下

  --返回：如果位身份证返回 1,非身份证返回 -1 

  --修改:

  -----------------------------------------------------------------

  v_省份码     Varchar2(900) := '11,42,12,43,13,44,14,45,15,46,21,50,22,51,23,52,31,53,32,54,33,61,34,62,35,63,36,64,37,65,41';

  n_Is_格式    Number(2) := -1;

  n_Length     Number(2) := -1;

  n_Sum        Number(5) := 0;

  v_Finput     Varchar2(100) := Replace(To_Single_Byte(Upper(v_Input)), ' ', '');

  v_Full_Input Varchar2(20);

  n_Mod        Number(2);

  n_Sign_Mod   Number(1); --//是否在取值Mod 中

  n_End_Card   Varchar2(1); --//最后一位身份证

Begin

  -----------------------------------------------------------------

  --算法说明

 

  -----------------------------------------------------------------

  --判断身份证的长度

  Select Lengthb(v_Finput) Into n_Length From Dual;

  If n_Length = 15 Or n_Length = 18 Then

    Select Count(*) Into n_Is_格式 From Dual Where Regexp_Like(Rtrim(v_Finput, 'X'), '^[[:digit:]]+$');

    If n_Is_格式 <> 1 Then

      Return - 1;

    Else

      --15位的身份证补齐

      If n_Length = 15 Then

        Select Substr(v_Finput, 1, 6) || '19' || Substr(v_Finput, 7, 6) || Substr(v_Finput, 13, 3) Into v_Full_Input From Dual;

      Else

        v_Full_Input := v_Finput;

      End If;

      If To_Number(Substr(v_Full_Input, 7, 8)) > 1900 And To_Number(Substr(v_Full_Input, 7, 8)) <= To_Number(To_Char(Sysdate, 'YYYYMMDD')) Then

        --判断15位身份证//Begin

        If Lengthb(v_Full_Input) = 17 Then

          If Instr(v_省份码, Substr(v_Full_Input, 1, 2)) = 0 Then

            Return 1;

          End If;

        End If;

        --//判断15位身份证//End

        If Lengthb(v_Full_Input) = 18 Then

          --//系数:7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2

          n_Sum := Substr(v_Full_Input, 1, 1) * 7 + Substr(v_Full_Input, 2, 1) * 9 + Substr(v_Full_Input, 3, 1) * 10 + Substr(v_Full_Input, 4, 1) * 5 +

                   Substr(v_Full_Input, 5, 1) * 8 + Substr(v_Full_Input, 6, 1) * 4 + Substr(v_Full_Input, 7, 1) * 2 + Substr(v_Full_Input, 8, 1) * 1 +

                   Substr(v_Full_Input, 9, 1) * 6 + Substr(v_Full_Input, 10, 1) * 3 + Substr(v_Full_Input, 11, 1) * 7 +

                   Substr(v_Full_Input, 12, 1) * 9 + Substr(v_Full_Input, 13, 1) * 10 + Substr(v_Full_Input, 14, 1) * 5 +

                   Substr(v_Full_Input, 15, 1) * 8 + Substr(v_Full_Input, 16, 1) * 4 + Substr(v_Full_Input, 17, 1) * 2;

          n_Mod := Mod(n_Sum, 11);

          --//余数只可能有0 1 2 3 4 5 6 7 8 9 10这11个数字

          Select Count(*) Into n_Sign_Mod From Dual Where n_Mod In (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10);

          If n_Sign_Mod = 0 Then

            Return - 1;

          Else

            Select Substr(v_Full_Input, 18, 1) Into n_End_Card From Dual;

            --//取Mod 正常(进行对照)

            If (n_Mod = 0 And n_End_Card = '1') Or (n_Mod = 1 And n_End_Card = '0') Or (n_Mod = 2 And n_End_Card = 'X') Or

               (n_Mod = 3 And n_End_Card = '9') Or (n_Mod = 4 And n_End_Card = '8') Or (n_Mod = 5 And n_End_Card = '7') Or

               (n_Mod = 6 And n_End_Card = '6') Or (n_Mod = 7 And n_End_Card = '5') Or (n_Mod = 8 And n_End_Card = '4') Or

               (n_Mod = 9 And n_End_Card = '3') Or (n_Mod = 10 And n_End_Card = '2') Then

              Return 1;

            Else

              Return - 1;

            End If;

          End If;

        End If;

      End If;

    End If;

  Else

    Return - 1;

  End If;

Exception

  When Others Then

    Return - 1;

End f_Is_身份证;

/