sql校验组织机构代码

时间：2021-10-09 16:33:33

第一种方法：

理解为： c9 = 11 - mod ( ∑ci * wi ，11) … (2)     i=1
    其中：mod —— 表示求余函数；
    i —— 表示代码字符从左到右位置序号；
    ci —— 表示第i位置上的代码字符的值，采用附录a“代码字符集”所列字符；
    c9 —— 表示校验码；
    wi —— 表示第i位置上的加权因子，其数值如下表：
    i 1 2 3 4 5 6 7 8
    wi 3 7 9 10 5 8 4 2
    当mod函数值为1（即 c9 = 10）时，校验码用字母x表示。
[vb] view plain copy print?
  1. create or replace function jgid_verify(pid varchar2) return varchar2 is  
  2.   result varchar2(58);  
  3.   type char_tabletype is table of varchar2(1) not null index by binary_integer;  
  4.   type num_tabletype is table of number not null index by binary_integer;  
  5.   tab_a char_tabletype;  
  6.   tab_w num_tabletype;  
  7.   tab_i char_tabletype;  
  8.   i     number(2) := 0;  
  9.   len   number(2) := length(rtrim(ltrim(pid)));  
  10.   sigma number(4) := 0;  
  11. begin  
  12.   
  13.   if len = 9 then  
  14.     -- c9 = 11 - mod ( ∑ci * wi ，11) … (2)  
  15.     for i in 1 .. 8 loop  
  16.       tab_w(i) := mod(power(2, ((10 - i) - 1)), 11);  
  17.       --tab_w(i) := mod(power(2, ((10 - i) - 1)), 11); 就是加权算法值  
  18.       tab_a(i) := substr(pid, i, 1);  
  19.       sigma := sigma + tab_w(i) * tab_a(i);  
  20.     end loop;  
  21.     if mod(sigma, 11) = 1 then  
  22.       result := substr(pid, 1, 8) || 'x';  
  23.     else  
  24.       result := substr(pid, 1, 8) || to_char(11 - mod(sigma, 11));  
  25.     end if;  
  26.   end if;  
  27.   if result = pid then  
  28.     return('正确');  
  29.   else  
  30.     return('错误,应该是：' || result);  
  31.   end if;  
  32. exception  
  33.   when others then  
  34.     begin  
  35.       return('错误');  
  36.       dbms_output.put_line('发生了异常的错误');  
  37.     end;  
  38. end jgid_verify;  
  39. /  

上边这个函数，经过验证，发现对于大部分组织机构代码，都是可以使用的，但是对于一小部分，却会出问题。

SELECT lower('766618250'),jgid_verify(lower('766618250')) FROM dual;

返回值是：错误,应该是：7666182511  --这个返回值是错误的，因为 7666182511 10位，明显有问题。

 

下面我提供两个另外的函数，分别针对10位组织机构代码和9为组织机构代码进行验证

针对10位组织机构代码：

[Java] view plain copy print?
  1. --输入 10位的组织机构代码（含有-） 格式：12345678-9  
  2. --输出 是否验证通过，0 未通过，1 通过  
  3. create or replace function organizationCode10Verify(organizationCode varchar2)  
  4.   return smallint is  
  5.   len       number(3); --组织机构代码长度  
  6.   I         int;         
  7.   J         int;  
  8.   K         int;  
  9.   N         int;  
  10.   C         char(1);  
  11.   valid     number(1); --是否验证通过，0 未通过，1 通过  
  12. begin  
  13.   valid := 0;  
  14.   len:= Length(trim(organizationCode));  
  15.     
  16.   IF len != 10 then  
  17.     return valid;  
  18.   END IF;  
  19.   IF substr(organizationCode, 9, 1)!='-' then  
  20.     return valid;  
  21.   END IF;  
  22.   I := 1;  
  23.   J := 1;  
  24.   N := 0;  
  25.   while(I <= 8) loop  
  26.     K := ASCII(substr(organizationCode, 9-I, 1)) - 48;  
  27.     J := trunc(mod((J * 2), 11));  
  28.     N := N + J * K;  
  29.     I := I + 1;  
  30.   END loop;  
  31.   N := 11-trunc(mod(N, 11));  
  32.   C := substr(organizationCode, 10, 1);  
  33.   K := ASCII(C) - 48;  
  34.   if N=11 then   
  35.   N:=0;  
  36.   END IF;  
  37.   IF C='x' OR C='X' THEN   
  38.    K:=10;  
  39.   END IF;  
  40.   IF K != N then  
  41.     return valid;  
  42.   END IF;  
  43.   valid := 1;  
  44.   return valid;  
  45. end organizationCode10Verify;  
  

针对9位组织机构代码：

 

[java] view plain copy print?
  1. --输入 9位的组织机构代码（不含-） 格式：123456789  
  2. --输出 是否验证通过，0 未通过，1 通过  
  3. create or replace function organizationCode9Verify(organizationCode varchar2)  
  4.   return smallint is  
  5.   len       number(3); --组织机构代码长度  
  6.   I         int;         
  7.   J         int;  
  8.   K         int;  
  9.   N         int;  
  10.   C         char(1);  
  11.   valid     number(1); --是否验证通过，0 未通过，1 通过  
  12. begin  
  13.   valid := 0;  
  14.   len:= Length(trim(organizationCode));  
  15.   
  16.   IF len != 9 then  
  17.     return valid;  
  18.   END IF;  
  19.   I := 1;  
  20.   J := 1;  
  21.   N := 0;  
  22.   while(I <= 8) loop  
  23.     K := ASCII(substr(organizationCode,9-I, 1)) - 48;  
  24.     J := trunc(mod((J * 2), 11));  
  25.     N := N + J * K;  
  26.     I := I + 1;  
  27.   END loop;  
  28.   N := 11-trunc(mod(N, 11));  
  29.   C := substr(organizationCode, 9, 1);  
  30.   K := ASCII(C) - 48;  
  31.   if N=11 then   
  32.     N:=0;  
  33.   END IF;  
  34.   IF C='x' OR C='X' THEN   
  35.   K:=10;  
  36.   END IF;  
  37.   IF K != N then  
  38.     return valid;  
  39.   END IF;  
  40.   valid := 1;  
  41.   return valid;  
  42. end organizationCode9Verify;  
原文地址：
http://blog.csdn.net/fengyifei11228/article/details/6256097

第二种方法：

[sql] view plain copy
  1. CREATE OR REPLACE FUNCTION CHECKORGCODE(organizationCode VARCHAR2)  
  2. /*  
  3.     功能：验证组织机构代码，成功返回1，失败返回0  
  4.     organizationCode：要验证的组织机构代码  
  5.     相关资料：  
  6.     http://baike.baidu.com/view/238601.htm  
  7.   */  
  8.  RETURN NUMBER AS  
  9.   codeSum NUMBER(10) := 0;  
  10.   code    VARCHAR(100);  
  11.   code_9  varchar(1);  
  12.   C9      NUMBER(2);  
  13.   /*字符与字符的值，每个字符后两位为该字符的字符数值*/  
  14.   Ci CHAR(250) := '000101202303404505606707808909A10B11C12D13E14F15G16H17I18J19K20L21M22N23O24P25Q26R27S28T29U30V31W32X33Y34Z35';  
  15.   /*前8位字符的加权因子*/  
  16.   type v_ar is varray(10) of number;  
  17.   Wi v_ar := v_ar(3, 7, 9, 10, 5, 8, 4, 2);  
  18. BEGIN  
  19.   /*判断是否为null*/  
  20.   IF (organizationCode is NULL) THEN  
  21.     BEGIN  
  22.       RETURN 0;  
  23.     END;  
  24.   END IF;  
  25.   
  26.   code := RTRIM(LTRIM(REPLACE(organizationCode, '-', ''))); /*把-,前后空格去掉*/  
  27.   
  28.   /*验证长度是否正确*/  
  29.   /*验证机构代码是由数字和大写字母组成*/  
  30.   IF (LENGTH(code) != 9 or NOT REGEXP_LIKE(code, '^[A-Z0-9]+$')) THEN  
  31.     BEGIN  
  32.       RETURN 0;  
  33.     END;  
  34.   END IF;  
  35.   
  36.   /*前8位字符的字符数值分别乘于该位的加权因子，然后求和*/  
  37.   for i in 1 .. Wi.count loop  
  38.     codeSum := codeSum +  
  39.                to_Number(substr(Ci, INSTR(Ci, substr(code, i, 1)) + 1, 2)) *  
  40.                Wi(i);  
  41.   end loop;  
  42.   
  43.   /* 计算校验码C9*/  
  44.   C9     := 11 - (codeSum MOD 11);  
  45.   code_9 := substr(code, 9, 1);  
  46.   
  47.   /*验证校验码C9*/  
  48.   /*当C9的值为10时，校验码应是拉丁字母X */  
  49.   /*当C9的值为11时校验码应是0*/  
  50.   /*验证第9位是否等于计算出的校验结果*/  
  51.   IF ((C9 = 10 and code_9 = 'X') or (C9 = 11 and code_9 = '0') or  
  52.      (code_9 = to_char(C9))) THEN  
  53.     BEGIN  
  54.       return 1;  
  55.     END;  
  56.   END IF;  
  57.   
  58.   RETURN 0;  
  59. EXCEPTION  
  60.   WHEN OTHERS THEN  
  61.     raise;  
  62. END;  

 原文地址：http://blog.csdn.net/junjieok/article/details/6963742
标签：

相关文章