ORACLE转身份证号码函数

康雪儿

　　一直负责项目的数据清理工作，旧系统的身份证号码进入新系统需要转换，根据网上的资料，写了个函数。功能比一般的要强，因为不但能将15位转18位，还能将16，17位转成18位，实属无奈啊！
　　CREATE OR REPLACE FUNCTION GETID(OLD_ID IN VARCHAR2) RETURN VARCHAR2 IS
 --RESULT  VARCHAR2(18);
 ID      VARCHAR2(17);
 ID_TEMP VARCHAR2(18);
 FLAG    CHAR;
 FLAG1   INT;
 --FLAG2   INT;
BEGIN
 ID_TEMP := TRIM(OLD_ID);
 IF LENGTH(ID_TEMP) < 15 THEN
  RETURN ID_TEMP;
 END IF;
 ID := CASE LENGTH(ID_TEMP)
 --18位，去最后一位
  WHEN 18 THEN SUBSTR(ID_TEMP, 1, 17)
 --17位，不变
  WHEN 17 THEN ID_TEMP
 --16位，去掉最后一位 ，加19
  WHEN 16 THEN SUBSTR(ID_TEMP, 1, 6) || '19' || SUBSTR(ID_TEMP, 7, 9)
 --15位，加19
  WHEN 15 THEN SUBSTR(ID_TEMP, 1, 6) || '19' || SUBSTR(ID_TEMP, 7, 9) END;
　　BEGIN
  FLAG1 := TO_NUMBER(SUBSTR(ID, 1, 1)) * 7 +
       TO_NUMBER(SUBSTR(ID, 2, 1)) * 9 +
       TO_NUMBER(SUBSTR(ID, 3, 1)) * 10 +
       TO_NUMBER(SUBSTR(ID, 4, 1)) * 5 +
       TO_NUMBER(SUBSTR(ID, 5, 1)) * 8 +
       TO_NUMBER(SUBSTR(ID, 6, 1)) * 4 +
       TO_NUMBER(SUBSTR(ID, 7, 1)) * 2 +
       TO_NUMBER(SUBSTR(ID, 8, 1)) * 1 +
       TO_NUMBER(SUBSTR(ID, 9, 1)) * 6 +
       TO_NUMBER(SUBSTR(ID, 10, 1)) * 3 +
       TO_NUMBER(SUBSTR(ID, 11, 1)) * 7 +
       TO_NUMBER(SUBSTR(ID, 12, 1)) * 9 +
       TO_NUMBER(SUBSTR(ID, 13, 1)) * 10 +
       TO_NUMBER(SUBSTR(ID, 14, 1)) * 5 +
       TO_NUMBER(SUBSTR(ID, 15, 1)) * 8 +
       TO_NUMBER(SUBSTR(ID, 16, 1)) * 4 +
       TO_NUMBER(SUBSTR(ID, 17, 1)) * 2;
 EXCEPTION
  WHEN OTHERS THEN
   RETURN ID_TEMP;
 END;
 FLAG1 := MOD(FLAG1, 11);
 FLAG  := CASE FLAG1 WHEN 0 THEN '1' WHEN 1 THEN '0' WHEN 2 THEN 'X' WHEN 3 THEN '9' WHEN 4 THEN '8' WHEN 5 THEN '7' WHEN 6 THEN '6' WHEN 7 THEN '5' WHEN 8 THEN '4' WHEN 9 THEN '3' WHEN 10 THEN '2' END;
　　RETURN ID || FLAG;
END GETID;
/