mysql使用函数生成模拟组织机构代码

  -- 组织机构代码校验位
CREATE FUNCTION CalculateOCCheckDigit(input_string varchar(8)) RETURNS varchar(1)  
BEGIN  
    DECLARE digit1 INT DEFAULT 0;  
    DECLARE digit2 INT DEFAULT 0;  
    DECLARE digit3 INT DEFAULT 0;  
    DECLARE digit4 INT DEFAULT 0;  
    DECLARE digit5 INT DEFAULT 0;  
    DECLARE digit6 INT DEFAULT 0;  
    DECLARE digit7 INT DEFAULT 0;  
    DECLARE digit8 INT DEFAULT 0;  
    DECLARE sum_of_weights INT DEFAULT 0;  
    DECLARE check_digit CHAR(1);  
  
    -- 解析每个字符并计算其对应的值  
    SET digit1 = CASE  
        WHEN SUBSTRING(input_string, 1, 1) BETWEEN '0' AND '9' THEN CAST(SUBSTRING(input_string, 1, 1) AS UNSIGNED)  
        WHEN SUBSTRING(input_string, 1, 1) BETWEEN 'A' AND 'Z' THEN ASCII(SUBSTRING(input_string, 1, 1)) - ASCII('A') + 10  
        ELSE 0 -- 非法字符处理（这里简化为0，但你可能需要抛出错误）  
    END;  
  
    SET digit2 = CASE  
        WHEN SUBSTRING(input_string, 2, 1) BETWEEN '0' AND '9' THEN CAST(SUBSTRING(input_string, 2, 1) AS UNSIGNED)  
        WHEN SUBSTRING(input_string, 2, 1) BETWEEN 'A' AND 'Z' THEN ASCII(SUBSTRING(input_string, 2, 1)) - ASCII('A') + 10  
        ELSE 0  
    END;  
		SET digit3 = CASE  
        WHEN SUBSTRING(input_string, 3, 1) BETWEEN '0' AND '9' THEN CAST(SUBSTRING(input_string, 3, 1) AS UNSIGNED)  
        WHEN SUBSTRING(input_string, 3, 1) BETWEEN 'A' AND 'Z' THEN ASCII(SUBSTRING(input_string, 3, 1)) - ASCII('A') + 10  
        ELSE 0  
    END;  
		
		SET digit4 = CASE  
        WHEN SUBSTRING(input_string, 4, 1) BETWEEN '0' AND '9' THEN CAST(SUBSTRING(input_string, 4, 1) AS UNSIGNED)  
        WHEN SUBSTRING(input_string, 4, 1) BETWEEN 'A' AND 'Z' THEN ASCII(SUBSTRING(input_string, 4, 1)) - ASCII('A') + 10  
        ELSE 0  
    END;  
		
		SET digit5 = CASE  
        WHEN SUBSTRING(input_string, 5, 1) BETWEEN '0' AND '9' THEN CAST(SUBSTRING(input_string, 5, 1) AS UNSIGNED)  
        WHEN SUBSTRING(input_string, 5, 1) BETWEEN 'A' AND 'Z' THEN ASCII(SUBSTRING(input_string, 5, 1)) - ASCII('A') + 10  
        ELSE 0  
    END;  
		
		SET digit6 = CASE  
        WHEN SUBSTRING(input_string, 6, 1) BETWEEN '0' AND '9' THEN CAST(SUBSTRING(input_string, 6, 1) AS UNSIGNED)  
        WHEN SUBSTRING(input_string, 6, 1) BETWEEN 'A' AND 'Z' THEN ASCII(SUBSTRING(input_string, 6, 1)) - ASCII('A') + 10  
        ELSE 0  
    END;  
		
		SET digit7 = CASE  
        WHEN SUBSTRING(input_string, 7, 1) BETWEEN '0' AND '9' THEN CAST(SUBSTRING(input_string, 7, 1) AS UNSIGNED)  
        WHEN SUBSTRING(input_string, 7, 1) BETWEEN 'A' AND 'Z' THEN ASCII(SUBSTRING(input_string, 7, 1)) - ASCII('A') + 10  
        ELSE 0  
    END;  
		
		SET digit8 = CASE  
        WHEN SUBSTRING(input_string, 8, 1) BETWEEN '0' AND '9' THEN CAST(SUBSTRING(input_string, 8, 1) AS UNSIGNED)  
        WHEN SUBSTRING(input_string, 8, 1) BETWEEN 'A' AND 'Z' THEN ASCII(SUBSTRING(input_string, 8, 1)) - ASCII('A') + 10  
        ELSE 0  
    END;  
  
    -- ... 重复上面的CASE语句来处理剩余的字符（digit3到digit8）  
  
    -- 假设权数是固定的，例如7, 9, 10, 5, 8, 4, 2（注意：这些只是示例权数）  
    SET sum_of_weights = (digit1 * 3) + (digit2 * 7) + (digit3 * 9) + (digit4 *10) + (digit5 * 5) + (digit6 * 8) + (digit7 * 4) + (digit8 * 2);  
  
    -- 计算校验位  
    SET check_digit = CASE  
        WHEN 11 - sum_of_weights % 11 = 11 THEN '0'  
        WHEN 11 - sum_of_weights % 11 = 10 THEN 'X'  
        WHEN 11 - sum_of_weights % 11 = 9 THEN '9'  
        WHEN 11 - sum_of_weights % 11 = 8 THEN '8'  
        WHEN 11 - sum_of_weights % 11 = 7 THEN '7'  
        WHEN 11 - sum_of_weights % 11 = 6 THEN '6'  
        WHEN 11 - sum_of_weights % 11 = 5 THEN '5'  
        WHEN 11 - sum_of_weights % 11 = 4 THEN '4'  
        WHEN 11 - sum_of_weights % 11 = 3 THEN '3'  
        WHEN 11 - sum_of_weights % 11 = 2 THEN '2'  
        WHEN 11 - sum_of_weights % 11 = 1 THEN '1' -- 注意：在USCC中，10通常映射为'X'  
        ELSE 'Error' -- 不应该发生的情况，但作为一个防御性编程措施  
    END;  
  
    RETURN check_digit;  
END   
  

	-- 生成模拟组织机构代码，需要传参作为登记管理机关行政区划码
CREATE FUNCTION SimulateOrgCode(orgid varchar(100)) RETURNS CHAR(18)  
BEGIN  
    DECLARE org_code CHAR(18) DEFAULT '';  
    DECLARE random_int INT;  
    DECLARE random_char CHAR(1);  
  
    -- 登记管理部门代码（假设为1，代表工商部门）  
    SET org_code = CONCAT(org_code, '1');  
  
    -- 机构类别代码（随机生成1位数字）  
    SET random_int = FLOOR(RAND() * 9) + 1; -- 生成1-9之间的随机数  
    SET org_code = CONCAT(org_code, CAST(random_int AS CHAR));  
  
    -- 登记管理机关行政区划码（随机生成6位数字，这里简化为随机数）  
    REPEAT  
        SET random_int = left(orgid ,6); -- 生成0-999999之间的随机数  
        SET org_code = CONCAT(org_code, LPAD(CAST(random_int AS CHAR), 6, '0')); -- 左
    UNTIL LENGTH(org_code) = 8 END REPEAT;  


  
    -- 校验码（这里简化处理，只添加固定值'X'作为示例）  
    -- 真实情况下，校验码需要通过一定的算法计算得出  
    SET org_code = CONCAT(org_code, CalculateOCCheckDigit(org_code));  
  
    RETURN org_code;  
END