存储过程-身份证校验

实现功能：

从表idcard中读取身份证号，将身份证信息和校验结果写入到表idcard_result中。

代码流程：

1. 创建存储方法validate_idcard(p_idcard)，校验身份证号p_idcard是否合法

2. 创建存储过程validateIdcard从idcard表中读取身份证数据，调用存储方法validate_idcard校验合法性，将结果写入到表idcard_result

2. 调用存储过程，完成校验

set global log_bin_trust_function_creators=1;

DELIMITER $$

/************************************************************
函数名称：validate_idcard
参数：p_idcard
返回值：身份证校验结果

备注：目前中国的身份证号码有18位和15位.
1，18位身份证号码的组成:6位地区编码＋8位出生年月日＋3位编号（奇男偶女）＋1位校验码
2，15位身份证号码的组成:6位地区编码＋6位出生年月日＋3位编号（奇男偶女）

18位身份证校验规则：
1、身份证号码前17位数分别乘以不同的系数。从第一位到第十七位的系数分别为:7、9、10、5、8、4、2、1、6、3、7、9、10、5、8、4、2 ;
2、将这17位数字和系数相乘的结果相加;用加出来和除以11，看余数是多少;
3、余数只可能有0、1、2、3、4、5、6、7、8、9、10这11个数字。其分别对应的最后一位身份证的号码为1、0、X、9、8、7、6、5、4、3、2；
*************************************************************/
CREATE FUNCTION `validate_idcard`(`p_idcard` varchar(18)) RETURNS varchar(255) CHARSET utf8
BEGIN
    DECLARE v_regstr VARCHAR ( 2000 );#正则
    DECLARE v_sum INT (11);#求和
    DECLARE v_mod INT (11);#求余
    DECLARE v_checkcode CHAR (11) DEFAULT '10X98765432';#校验码表
    DECLARE v_checkbit CHAR (1);#校验码
    #区域代码，验证身份证号是否在所在区域
    DECLARE v_areacode VARCHAR (2000) DEFAULT '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,82,91,';
    DECLARE v_idcard17 VARCHAR(20);#17位身份证号
    #身份证前17位每位加权因子{7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2};
    #判断身份证号是否为空
    IF p_idcard is null or ltrim(rtrim(p_idcard))='' THEN RETURN '身份证号为空';
    END IF;

    CASE LENGTH (p_idcard)
        #15位身份证只校验区域代码和出生日期
        WHEN 15 THEN
            IF INSTR(v_areacode,substr(p_idcard,1,2)) = 0 THEN RETURN "区域代码错误";
            END IF;
            IF(SUBSTR(p_idcard,7,4))%4 = 0 and (SUBSTR(p_idcard,7,4))%100 <> 0 THEN  #判断是否为闰年
                set v_regstr := '^[1-9][0-9]{5}[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|[1-2][0-9]))[0-9]{3}$';
                ELSE set v_regstr := '^[1-9][0-9]{5}[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|1[0-9]|2[0-8]))[0-9]{3}$';
                IF (p_idcard REGEXP v_regstr = 1 ) THEN RETURN "身份证号正确";
                    ELSE return "身份证号错误";
                END IF;
            END IF;
            return "身份证号错误";

        WHEN 18 THEN
            IF INSTR(v_areacode,substr(p_idcard,1,2)) = 0 THEN
                RETURN "区域代码错误";
            END IF;
            
            IF(SUBSTR(p_idcard,7,4))%4 = 0 and (SUBSTR(p_idcard,7,4))%100 <> 0 THEN 
                set v_regstr := '^[1-9][0-9]{5}(19|20)[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|[1-2][0-9]))[0-9]{3}[0-9Xx]$';
                ELSE 
                set v_regstr := '^[1-9][0-9]{5}(19|20)[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|1[0-9]|2[0-8]))[0-9]{3}[0-9Xx]$';
            END IF;
            #校验
            IF (`p_idcard` REGEXP v_regstr = 1 ) THEN
                    set v_sum:= ((SUBSTR(p_idcard,1,1)+SUBSTR(p_idcard,11,1)))*7+(SUBSTR(p_idcard,2,1)+SUBSTR(p_idcard,12,1))*9+(SUBSTR(p_idcard,3,1)+SUBSTR(p_idcard,13,1))*10+(SUBSTR(p_idcard,4,1)+SUBSTR(p_idcard,14,1))*5+(SUBSTR(p_idcard,5,1)+SUBSTR(p_idcard,15,1))*8+(SUBSTR(p_idcard,6,1)+SUBSTR(p_idcard,16,1))*4+(SUBSTR(p_idcard,7,1)+SUBSTR(p_idcard,17,1))*2+(SUBSTR(p_idcard,8,1))*1+(SUBSTR(p_idcard,9,1))*6+(SUBSTR(p_idcard,10,1))*3;
                    set v_mod := v_sum%11;
                    set v_checkbit := SUBSTR(v_checkcode, v_mod + 1,1);
                    IF v_checkbit = upper(substr( p_idcard, 18, 1 ) ) THEN
                            RETURN "身份证号正确";
                        ELSE RETURN "身份证号错误";
                    END IF;
            END IF;
            return "身份证号错误";

        ELSE RETURN '身份证号位数错误';
    END CASE;
END$$


#存储过程，用来调用存储函数，获得结果
DELIMITER $$
CREATE procedure validateIdcard()
BEGIN
    DECLARE id int(11);
    DECLARE id_card varchar(18);
    declare result varchar(100);
    DECLARE has_data int DEFAULT 1;
    DECLARE card_result cursor for select * from idcard;
    DECLARE exit handler for not found set has_data=0;

    open card_result;

    repeat
        fetch card_result into id, id_card;
        select validate_idcard(id_card) into result;
        insert into idcard_result VALUES (id,id_card,result);
        until has_data = 0
    end repeat;

    close card_result;
END$$

#调用存储过程
call validateIdcard();