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


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

  1. CREATEORREPLACEFUNCTION FUNC_AAC00215_18 (prm_aac002 varchar2)
  2. return varchar2 is
  3. ll_i number(4) := 18;
  4. ll_row number(5) := 0;
  5. var_aac002 varchar2(18);
  6. ll_sfz18 number(3);
  7. ll_sfz varchar2(3);
  8. var_sfz18 varchar(18);
  9. var_date varchar(10);
  10. dat_date date;
  11. ll_num number(2);
  12. s_sfz varchar2(15);
  13. s_sss varchar2(20):='X';
  14. v_newaac002 varchar(18);
  15. n_length number(2);
  16. V_CHAR varchar2(15);
  17. s_date varchar2(15);
  18. begin
  19. if prm_aac002 isnullthen
  20. return'';
  21. end if;
  22. if length(prm_aac002) <> 15 and length(prm_aac002) <> 18 then
  23. return'';
  24. end if;
  25. if length(prm_aac002) = 18 then
  26. n_length := length(prm_aac002);
  27. FOR n_i IN 1 .. n_length LOOP
  28. V_CHAR := SUBSTR(prm_aac002, n_i, 1);
  29. IF V_CHAR IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9') AND N_I<18 THEN
  30. v_newaac002 :=prm_aac002;
  31. ELSIF N_I=18 AND V_CHAR IN('0', '1', '2', '3', '4', '5', '6', '7', '8', '9','X') THEN
  32. v_newaac002 :=prm_aac002;
  33. else
  34. return'含义非法字符！';
  35. END IF;
  36. END LOOP;
  37. /*2. 取出生日期是否有效*/
  38. s_date := substr(prm_aac002, 7, 8);
  39. begin
  40. select to_date(s_date, 'yyyymmdd') into dat_date from dual;
  41. exception
  42. when others then
  43. return'出生日期不对！';
  44. end;
  45. if substr(prm_aac002,7, 2)>20 or substr(prm_aac002,7, 2) <19 then
  46. return'年度不对！';
  47. end if;
  48. /*3. 最后位效验*/
  49. while n_length >= 2 loop
  50. begin
  51. select to_number(substr(prm_aac002, 19 - n_length, 1))
  52. into ll_num
  53. from dual;
  54. exception
  55. when others then
  56. return'';
  57. end;
  58. ll_row := ll_row + mod(power(2, (n_length - 1)), 11) *
  59. to_number(substr(prm_aac002, 19 - n_length, 1));
  60. n_length := n_length - 1;
  61. end loop;
  62. ll_sfz18 := mod(ll_row, 11);
  63. 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')
  64. into ll_sfz
  65. from dual;
  66. if ll_sfz <> substr(prm_aac002,18, 1) then
  67. return'尾数不对！应为'||ll_sfz;
  68. end if;
  69. RETURN v_newaac002;
  70. end if;
  71. if prm_aac002 = '111111111111111111'then
  72. return'';
  73. end if;
  74. if s_sfz <>'X'then
  75. s_sss := s_sfz;
  76. else
  77. s_sss := prm_aac002;
  78. end if;
  79. if length(prm_aac002)=15 then
  80. var_date := '19' || substr(s_sss, 7, 6);
  81. begin
  82. select to_date(var_date, 'yyyymmdd') into dat_date from dual;
  83. exception
  84. when others then
  85. return'';
  86. end;
  87. var_aac002 := substr(s_sss, 0, 6) || '19' ||
  88. substr(s_sss, 7, 9);
  89. while ll_i >= 2 loop
  90. begin
  91. select to_number(substr(var_aac002, 19 - ll_i, 1))
  92. into ll_num
  93. from dual;
  94. exception
  95. when others then
  96. return'';
  97. end;
  98. ll_row := ll_row + mod(power(2, (ll_i - 1)), 11) *
  99. to_number(substr(var_aac002, 19 - ll_i, 1));
  100. ll_i := ll_i - 1;
  101. end loop;
  102. ll_sfz18 := mod(ll_row, 11);
  103. 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')
  104. into ll_sfz
  105. from dual;
  106. var_sfz18 := var_aac002 || ll_sfz;
  107. return var_sfz18;
  108. end if;
  109. end func_aac00215_18;
  110. /
  111. show errors;

使用如下:

SELECT func_aac00215_18('371102841104258') FROM dual;

相关内容