MYQL存储过程给银行账号每四位增加一个空格(请勿直接复制执行,可以参考 思路写法)

--  修改银行账号存储过程 (蓝色的代表 某一张表 绿色的代表银行账号这个字段) 
DROP PROCEDURE IF EXISTS update_bankNumber;

DELIMITER;;
CREATE PROCEDURE update_bankNumber()
BEGIN
DECLARE id varchar(64);
DECLARE oldNuber varchar(64);
DECLARE newNuber varchar(64);

DECLARE i int;
DECLARE icount int;

DECLARE t int;
DECLARE tcount int;

DECLARE lin int;
DECLARE linNum int;

-- 定义 循环变量
SET i = 0;
SET t = 0;
SET lin = 0;
SET linNum = 0;
-- 获取总数
SET icount = (SELECT COUNT(*) from nk_business.base_pay_accounts);
-- 获取表的条数
WHILE i < icount DO
    SET id  = "";
    SET newNuber  = ",";
    -- 获取每条数据的ID并赋值
    SET id = (SELECT f.ID FROM nk_business.base_pay_accounts f LIMIT i,1);
    SET  oldNuber = (SELECT f.CARD_CODE FROM nk_business.base_pay_accounts f WHERE f.ID = id);

    -- 去掉所有的空格
    -- UPDATE  base_pay_accounts bs SET bs.CARD_CODE = replace(oldNuber,' ','') where bs.ID = id;
    SET oldNuber = replace(oldNuber,' ','');
    IF oldNuber != ""  THEN
        SET @updateBankNubmerSql = CONCAT("update nk_business.base_pay_accounts set CARD_CODE ='",oldNuber,"' where ID='",id,"'");
        PREPARE updateBankNumber from @updateBankNubmerSql;
        EXECUTE updateBankNumber;
        deallocate prepare updateBankNumber;
    END IF;
    -- 获取并设置这一条数据的银行账号的长度
    SET tcount = (SELECT length(f.CARD_CODE) FROM nk_business.base_pay_accounts f LIMIT i,1);
    WHILE t < tcount DO
        -- SET lin = t + 4;
        IF t + 4 <= tcount THEN 
            SET newNuber  =  CONCAT(newNuber,(substring(oldNuber,t+1,4)),"-");
        END IF;
        IF t + 4 > tcount THEN
            SET linNum = tcount - t;
            SET newNuber  =  CONCAT(newNuber,(substring(oldNuber,t+1,linNum)),"-");
        END IF;
        SET t = t + 4;
        -- 结束循环
    END WHILE;
    SET newNuber =  replace(newNuber,'-',' ');
    SET newNuber =  replace(newNuber,',','');
    SET newNuber =  substring(newNuber,1,length(newNuber)-1);
    IF newNuber != ""  THEN
        SET @updateBankNubmerSql = CONCAT("update nk_business.base_pay_accounts set CARD_CODE ='",newNuber,"' where ID='",id,"'");
        PREPARE updateBankNumber from @updateBankNubmerSql;
        EXECUTE updateBankNumber;
        deallocate prepare updateBankNumber;
    END IF;
    SET t = 0;
    -- 执行sql语句
    SET i = i + 1;
-- 结束循环
END WHILE;
COMMIT;
END
;;
DELIMITER ;

CALL update_bankNumber();