oracle身份证15转18

函数1（超）：

create or replace function ID15TO18(prm_ID15 in varchar2) return varchar2 is
  Result varchar2(18);

      type ww is varray(18) of number(2);
      type aa is varray(11) of varchar2(1);
      i     number:=1;
      str   varchar2(18);
      v_sum number(10);
      ai    number(2);
      prm_ID18 varchar2(18);
      w ww:= ww(7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2,1);
      a aa:= aa('1','0','X','9','8','7','6','5','4','3','2');
begin
      IF length(prm_ID15)=15 then
         str:=substr(prm_ID15,1,6)||'19'||substr(prm_ID15,7,9);
         v_sum:=0;
         FOR i IN 1..17 LOOP
            v_sum:=v_sum+to_number(substr(str,i,1))*w(i);
         END LOOP;
         ai := MOD(v_sum,11);
         prm_ID18 :=str||a(ai+1);
      ELSIF LENGTH(prm_ID15) = 18 THEN
         --prm_ID18 := prm_ID15;
         str:=substr(prm_ID15,1,6)||substr(prm_ID15,9,9);
         str:=substr(str,1,6)||'19'||substr(str,7,9);
         v_sum:=0;
         FOR i IN 1..17 LOOP
            v_sum:=v_sum+to_number(substr(str,i,1))*w(i);
         END LOOP;
         ai := MOD(v_sum,11);
         prm_ID18 :=str||a(ai+1);
         RETURN(prm_ID18);
      ELSE
         prm_ID18 := prm_ID15;
         RETURN(prm_ID18);
      END IF;
  return(prm_ID18);
  exception
  when OTHERS then
  begin
  return -100;
  end;
end ID15TO18;

 

 

或函数二：

 

CREATE OR REPLACE FUNCTION ConvertIDCard(oldCardNumber in varchar2)
return varchar2 is vResult varchar2(18);

   TYPE char_type IS TABLE OF VARCHAR2(5) INDEX BY BINARY_INTEGER;--定义字符串数组
   TYPE int_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;--定义整型数组

   strJiaoYan  char_type ;--初始化
   intQuan int_type;

--临时变量
strTemp varchar2(25);
intTemp number;
i number;
n varchar(2);

Begin

   strJiaoYan(1) := '1';
   strJiaoYan(2) := '0';
   strJiaoYan(3) := 'X';
   strJiaoYan(4) := '9';
   strJiaoYan(5) := '8';
   strJiaoYan(6) := '7';
   strJiaoYan(7) := '6';
   strJiaoYan(8) := '5';
   strJiaoYan(9) := '4';
   strJiaoYan(10) := '3';
   strJiaoYan(11) := '2';

   intQuan(1) := 7;
   intQuan(2) := 9;
   intQuan(3) := 10;
   intQuan(4) := 5;
   intQuan(5) := 8;
   intQuan(6) := 4;
   intQuan(7) := 2;
   intQuan(8) := 1;
   intQuan(9) := 6;
   intQuan(10) := 3;
   intQuan(11) := 7;
   intQuan(12) := 9;
   intQuan(13) := 10;
   intQuan(14) := 5;
   intQuan(15) := 8;
   intQuan(16) := 4;
   intQuan(17) := 2;
   intQuan(18) := 1;

   --身份证年份前加19
   n  :=  '19';
   strTemp := SUBSTR(oldCardNumber,1,6) || n || SUBSTR(oldCardNumber,7,9);
   -- DBMS_OUTPUT.Put_line(strTemp);

    i:= 1;
    intTemp := 0;
   While i<18 loop
      intTemp := intTemp + TO_NUMBER(SUBSTR(strTemp,i,1)) * intQuan(i);
      --DBMS_OUTPUT.Put_line(intTemp);
      i := i+1;
   End Loop;
   intTemp := MOD(intTemp,11);

   --DBMS_OUTPUT.Put_line(intTemp+1);

   vResult := strTemp || strJiaoYan(intTemp+1);

   return vResult;
End ConvertIDCard;