MYSQL统一社会信用代码校验函数

CREATE DEFINER=`tm` FUNCTION `func_check_credit_code`(v_card varchar(32)) RETURNS varchar(32) CHARSET utf8mb3
    DETERMINISTIC
BEGIN
-- 此函数用于校验明文统一社会信用代码
-- 校验通过返回 1
-- 校验不通过返回 0 
	DECLARE v_flag varchar(32) DEFAULT '';  -- 是否有效标志
	DECLARE v_sum INT DEFAULT 0;   -- 校验第一步求和
	DECLARE v_mod varchar(32) DEFAULT '';   -- 校验第二步取余
	DECLARE i_flag varchar(32) DEFAULT '';  -- 校验第三步计算校验位
	DECLARE v_cstr varchar(200) DEFAULT '';  
	DECLARE v_wstr varchar(200) DEFAULT '';  -- 加权因子字符串
	
-- 每一位对应的数字
	SET v_cstr = '0123456789ABCDEFGHJKLMNPQRTUWXY';
-- 对应数字的加权因子
	SET v_wstr = '1, 3, 9, 27, 19, 26, 16, 17, 20, 29, 25, 13, 8, 24, 10, 30, 28';
	
-- 长度不等于18为空
	IF LENGTH(v_card) <> 18 THEN
	RETURN 0;
	END IF;
	
-- 判断第一位登记管理部门代码
	IF SUBSTRING(v_card,1,1) NOT IN (1,2,3,4,5,6,7,8,9,'A','N','Y') THEN
	RETURN 0;
	END IF;
	
-- 判断第二位机构类别代码
-- 判断机构编制
	IF SUBSTRING(v_card,1,1) = 1 THEN
		IF SUBSTRING(v_card,2,1) NOT IN (1,2,3,9) THEN
		RETURN 0;
		END IF;
	END IF;
-- 判断外交
	IF SUBSTRING(v_card,1,1) = 2 THEN
		IF SUBSTRING(v_card,2,1) NOT IN (1,9) THEN
		RETURN 0;
		END IF;
	END IF;
-- 判断司法行政
	IF SUBSTRING(v_card,1,1) = 3 THEN
		IF SUBSTRING(v_card,2,1) NOT IN (1,2,3,4,5,9) THEN
		RETURN 0;
		END IF;
	END IF;
-- 判断文化	
	IF SUBSTRING(v_card,1,1) = 4 THEN
		IF SUBSTRING(v_card,2,1) NOT IN (1,9) THEN
		RETURN 0;
		END IF;
	END IF;
-- 判断民政
	IF SUBSTRING(v_card,1,1) = 5 THEN
		IF SUBSTRING(v_card,2,1) NOT IN (1,2,3,9) THEN
		RETURN 0;
		END IF;
	END IF;
-- 判断旅游	
	IF SUBSTRING(v_card,1,1) = 6 THEN
		IF SUBSTRING(v_card,2,1) NOT IN (1,2,9) THEN
		RETURN 0;
		END IF;
	END IF;
-- 判断宗教
	IF SUBSTRING(v_card,1,1) = 7 THEN
		IF SUBSTRING(v_card,2,1) NOT IN (1,2,9) THEN
		RETURN 0;
		END IF;
	END IF;
-- 判断工会	
	IF SUBSTRING(v_card,1,1) = 8 THEN
		IF SUBSTRING(v_card,2,1) NOT IN (1,9) THEN
		RETURN 0;
		END IF;
	END IF;
-- 判断工商	
	IF SUBSTRING(v_card,1,1) = 9 THEN
		IF SUBSTRING(v_card,2,1) NOT IN (1,2,3) THEN
		RETURN 0;
		END IF;
	END IF;
-- 判断中央军委改革和编制办公室
	IF SUBSTRING(v_card,1,1) = 'A' THEN
		IF SUBSTRING(v_card,2,1) NOT IN (1,9) THEN
		RETURN 0;
		END IF;
	END IF;
-- 判断农业
	IF SUBSTRING(v_card,1,1) = 'N' THEN
		IF SUBSTRING(v_card,2,1) NOT IN (1,2,3,9) THEN
		RETURN 0;
		END IF;
	END IF;
-- 判断其他
	IF SUBSTRING(v_card,1,1) = 'Y' THEN
		IF SUBSTRING(v_card,2,1) <> 1 THEN
		RETURN 0;
		END IF;
	END IF;

-- 判断区划代码前两位
	IF SUBSTRING(v_card,3,2) NOT IN (11,12,13,14,15,21,22,23,31,32,33,34,35,36,37,41,42,43,44,45,46,50,51,52,53,54,61,62,63,64,65,71,81,83) THEN
	RETURN 0;
	END IF;
	
-- 求和
SET v_sum =  (INSTR(v_cstr, SUBSTR(v_card, 1, 1)) - 1)*REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(v_wstr,',',1)),',',1))+
(INSTR(v_cstr, SUBSTR(v_card, 2, 1)) - 1)*REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(v_wstr,',',2)),',',1))+
(INSTR(v_cstr, SUBSTR(v_card, 3, 1)) - 1)*REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(v_wstr,',',3)),',',1))+
(INSTR(v_cstr, SUBSTR(v_card, 4, 1)) - 1)*REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(v_wstr,',',4)),',',1))+
(INSTR(v_cstr, SUBSTR(v_card, 5, 1)) - 1)*REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(v_wstr,',',5)),',',1))+
(INSTR(v_cstr, SUBSTR(v_card, 6, 1)) - 1)*REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(v_wstr,',',6)),',',1))+
(INSTR(v_cstr, SUBSTR(v_card, 7, 1)) - 1)*REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(v_wstr,',',7)),',',1))+
(INSTR(v_cstr, SUBSTR(v_card, 8, 1)) - 1)*REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(v_wstr,',',8)),',',1))+
(INSTR(v_cstr, SUBSTR(v_card, 9, 1)) - 1)*REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(v_wstr,',',9)),',',1))+
(INSTR(v_cstr, SUBSTR(v_card, 10, 1)) - 1)*REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(v_wstr,',',10)),',',1))+
(INSTR(v_cstr, SUBSTR(v_card, 11, 1)) - 1)*REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(v_wstr,',',11)),',',1))+
(INSTR(v_cstr, SUBSTR(v_card, 12, 1)) - 1)*REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(v_wstr,',',12)),',',1))+
(INSTR(v_cstr, SUBSTR(v_card, 13, 1)) - 1)*REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(v_wstr,',',13)),',',1))+
(INSTR(v_cstr, SUBSTR(v_card, 14, 1)) - 1)*REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(v_wstr,',',14)),',',1))+
(INSTR(v_cstr, SUBSTR(v_card, 15, 1)) - 1)*REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(v_wstr,',',15)),',',1))+
(INSTR(v_cstr, SUBSTR(v_card, 16, 1)) - 1)*REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(v_wstr,',',16)),',',1))+
(INSTR(v_cstr, SUBSTR(v_card, 17, 1)) - 1)*REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(v_wstr,',',17)),',',1));
	
-- 取余得到加权因子
SET v_mod = 31 - v_sum%31;
IF v_mod = 31 THEN 
	SET v_mod = 0 ;
END IF;

-- 判断校验位
SET i_flag = SUBSTR(v_cstr,v_mod+1,1);
IF i_flag = SUBSTRING(v_card,18,1) THEN
RETURN 1;
		ELSE
		RETURN 0;
	END IF;

END