sql校验组织机构代码

来源：互联网发布：淘宝我的提问看不到编辑：程序博客网时间：2024/04/26 06:02

第一种方法：

理解为： 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?
create or replace function jgid_verify(pid varchar2) return varchar2 is  
  result varchar2(58);  
  type char_tabletype is table of varchar2(1) not null index by binary_integer;  
  type num_tabletype is table of number not null index by binary_integer;  
  tab_a char_tabletype;  
  tab_w num_tabletype;  
  tab_i char_tabletype;  
  i     number(2) := 0;  
  len   number(2) := length(rtrim(ltrim(pid)));  
  sigma number(4) := 0;  
begin  
  
  if len = 9 then  
    -- c9 = 11 - mod ( ∑ci * wi ，11) … (2)  
    for i in 1 .. 8 loop  
      tab_w(i) := mod(power(2, ((10 - i) - 1)), 11);  
      --tab_w(i) := mod(power(2, ((10 - i) - 1)), 11); 就是加权算法值  
      tab_a(i) := substr(pid, i, 1);  
      sigma := sigma + tab_w(i) * tab_a(i);  
    end loop;  
    if mod(sigma, 11) = 1 then  
      result := substr(pid, 1, 8) || 'x';  
    else  
      result := substr(pid, 1, 8) || to_char(11 - mod(sigma, 11));  
    end if;  
  end if;  
  if result = pid then  
    return('正确');  
  else  
    return('错误,应该是：' || result);  
  end if;  
exception  
  when others then  
    begin  
      return('错误');  
      dbms_output.put_line('发生了异常的错误');  
    end;  
end jgid_verify;  
/  

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

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

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

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

针对10位组织机构代码：

[Java] view plain copy
 print?
--输入 10位的组织机构代码（含有-） 格式：12345678-9  
--输出 是否验证通过，0 未通过，1 通过  
create or replace function organizationCode10Verify(organizationCode varchar2)  
  return smallint is  
  len       number(3); --组织机构代码长度  
  I         int;         
  J         int;  
  K         int;  
  N         int;  
  C         char(1);  
  valid     number(1); --是否验证通过，0 未通过，1 通过  
begin  
  valid := 0;  
  len:= Length(trim(organizationCode));  
    
  IF len != 10 then  
    return valid;  
  END IF;  
  IF substr(organizationCode, 9, 1)!='-' then  
    return valid;  
  END IF;  
  I := 1;  
  J := 1;  
  N := 0;  
  while(I <= 8) loop  
    K := ASCII(substr(organizationCode, 9-I, 1)) - 48;  
    J := trunc(mod((J * 2), 11));  
    N := N + J * K;  
    I := I + 1;  
  END loop;  
  N := 11-trunc(mod(N, 11));  
  C := substr(organizationCode, 10, 1);  
  K := ASCII(C) - 48;  
  if N=11 then   
  N:=0;  
  END IF;  
  IF C='x' OR C='X' THEN   
   K:=10;  
  END IF;  
  IF K != N then  
    return valid;  
  END IF;  
  valid := 1;  
  return valid;  
end organizationCode10Verify;  

针对9位组织机构代码：

[java] view plain copy
 print?
--输入 9位的组织机构代码（不含-） 格式：123456789  
--输出 是否验证通过，0 未通过，1 通过  
create or replace function organizationCode9Verify(organizationCode varchar2)  
  return smallint is  
  len       number(3); --组织机构代码长度  
  I         int;         
  J         int;  
  K         int;  
  N         int;  
  C         char(1);  
  valid     number(1); --是否验证通过，0 未通过，1 通过  
begin  
  valid := 0;  
  len:= Length(trim(organizationCode));  
  
  IF len != 9 then  
    return valid;  
  END IF;  
  I := 1;  
  J := 1;  
  N := 0;  
  while(I <= 8) loop  
    K := ASCII(substr(organizationCode,9-I, 1)) - 48;  
    J := trunc(mod((J * 2), 11));  
    N := N + J * K;  
    I := I + 1;  
  END loop;  
  N := 11-trunc(mod(N, 11));  
  C := substr(organizationCode, 9, 1);  
  K := ASCII(C) - 48;  
  if N=11 then   
    N:=0;  
  END IF;  
  IF C='x' OR C='X' THEN   
  K:=10;  
  END IF;  
  IF K != N then  
    return valid;  
  END IF;  
  valid := 1;  
  return valid;  
end organizationCode9Verify;  

原文地址：
http://blog.csdn.net/fengyifei11228/article/details/6256097

第二种方法：

[sql] view plain copy
CREATE OR REPLACE FUNCTION CHECKORGCODE(organizationCode VARCHAR2)  
/*  
    功能：验证组织机构代码，成功返回1，失败返回0  
    organizationCode：要验证的组织机构代码  
    相关资料：  
    http://baike.baidu.com/view/238601.htm  
  */  
 RETURN NUMBER AS  
  codeSum NUMBER(10) := 0;  
  code    VARCHAR(100);  
  code_9  varchar(1);  
  C9      NUMBER(2);  
  /*字符与字符的值，每个字符后两位为该字符的字符数值*/  
  Ci CHAR(250) := '000101202303404505606707808909A10B11C12D13E14F15G16H17I18J19K20L21M22N23O24P25Q26R27S28T29U30V31W32X33Y34Z35';  
  /*前8位字符的加权因子*/  
  type v_ar is varray(10) of number;  
  Wi v_ar := v_ar(3, 7, 9, 10, 5, 8, 4, 2);  
BEGIN  
  /*判断是否为null*/  
  IF (organizationCode is NULL) THEN  
    BEGIN  
      RETURN 0;  
    END;  
  END IF;  
  
  code := RTRIM(LTRIM(REPLACE(organizationCode, '-', ''))); /*把-,前后空格去掉*/  
  
  /*验证长度是否正确*/  
  /*验证机构代码是由数字和大写字母组成*/  
  IF (LENGTH(code) != 9 or NOT REGEXP_LIKE(code, '^[A-Z0-9]+$')) THEN  
    BEGIN  
      RETURN 0;  
    END;  
  END IF;  
  
  /*前8位字符的字符数值分别乘于该位的加权因子，然后求和*/  
  for i in 1 .. Wi.count loop  
    codeSum := codeSum +  
               to_Number(substr(Ci, INSTR(Ci, substr(code, i, 1)) + 1, 2)) *  
               Wi(i);  
  end loop;  
  
  /* 计算校验码C9*/  
  C9     := 11 - (codeSum MOD 11);  
  code_9 := substr(code, 9, 1);  
  
  /*验证校验码C9*/  
  /*当C9的值为10时，校验码应是拉丁字母X */  
  /*当C9的值为11时校验码应是0*/  
  /*验证第9位是否等于计算出的校验结果*/  
  IF ((C9 = 10 and code_9 = 'X') or (C9 = 11 and code_9 = '0') or  
     (code_9 = to_char(C9))) THEN  
    BEGIN  
      return 1;  
    END;  
  END IF;  
  
  RETURN 0;  
EXCEPTION  
  WHEN OTHERS THEN  
    raise;  
END;  

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

阅读全文

1 0