mysql如何用函数实现组织机构代码校验

Question

mysql如何用函数实现组织机构代码校验，要求使用函数实现，不用过程。

Answer 1

DELIMITER //

CREATE FUNCTION code(code VARCHAR(20)) RETURNS BOOLEAN
BEGIN
    DECLARE len, i, sum, mod, weight INT;
    DECLARE digit, checkCode CHAR(1);
    DECLARE codeArray CHAR(20);
    
    SET len = LENGTH(code);
   
    IF len <> 9 THEN
        RETURN FALSE;
    END IF;
 
    IF code REGEXP '^[0-9A-Z]{8}[0-9X]$' = 0 THEN
        RETURN FALSE;
    END IF;
    
    SET codeArray = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    SET weight = 3;
    SET sum = 0;
    
   
    FOR i = 1 TO 8 DO
        SET digit = SUBSTRING(code, i, 1);
        SET mod = INSTR(codeArray, digit) - 1;
        SET sum = sum + mod * weight;
        SET weight = IF(weight = 1, 3, weight - 1);
    END FOR;
    
    SET mod = (11 - (sum % 11)) % 11;
    SET checkCode = SUBSTRING(code, 9, 1);
    

    IF mod = 10 THEN
        RETURN checkCode = 'X';
    ELSE
        RETURN checkCode = CAST(mod AS CHAR);
    END IF;
    
END //

DELIMITER ;

SELECT code('这里填接收参数，最后返回的是一个bool值');