创建存储过程:
#判断存储过程是否存在,存在删除
DROP PROCEDURE IF EXISTS toCard;
CREATE DEFINER=`root`@`%` PROCEDURE `toCard`()
BEGIN
#声明结束标识
DECLARE end_flag int DEFAULT 0;
#变量
DECLARE userId,cardThird bigint;
DECLARE cardHex,leftCard,rightCard,ecardNum VARCHAR(10);
#声明游标 user_curosr,将sql结果集赋值到游标中
DECLARE user_curosr CURSOR FOR SELECT id,ecardNumThird FROM u_user WHERE ecardNumThird IS NOT NULL AND ecardNumThird!='';
#设置终止标识
DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_flag=1;
#打开游标
OPEN user_curosr;
#遍历游标
REPEAT
#获取当前游标指针记录,取出值赋给自定义的变量,注:变量名不要与sql返回的列名相同,变量顺序要和sql结果列的顺序一致
FETCH user_curosr INTO userId,cardThird;
SET cardHex=CONV(LEFT(cardThird, 8), 10, 16);
SET leftCard= CONV(LEFT(LPAD(cardHex,8,0) , 4), 16, 10);
SET rightCard= CONV(RIGHT(LPAD(cardHex,8,0) , 4), 16, 10);
SET ecardNum=LPAD(CONCAT(leftCard,rightCard),8,0);
#输出结果
#SELECT userId,cardThird,cardHex,leftCard,rightCard,ecardNum;
#利用取到的值进行数据库的操作
UPDATE u_user SET ecardNum=ecardNum WHERE id=userId;
# 根据 end_flag 判断是否结束
UNTIL end_flag END REPEAT;
#关闭游标
close user_curosr;
END
执行存储过程:
call toCard();
内包含wiegand26卡号转换协议