mysql存储过程中遍历json数据

一、今天在开发中遇到mysql中遍历json数据的需求,查了些资料,然后特此记录,

二、在mysql5.7中是有一个方法用来取json数据的,JSON_EXTRACT(JSON.Val,key),详细代码如下

三、代码如下:

CREATE DEFINER=`root`@`localhost` PROCEDURE `fun_sys_user_local_mobileReg`(pUserId int,pIP varchar(95),params varchar(5000),out pRlt varchar(800))
BEGIN
-- BS用户移动版启用--本地存储过程
-- {"funName":"sys_user_mobileReg","uuid":"c39a37r98e06t40o49p30","localFunName":"sys_user_local_mobileReg","userNo":{"userNo0":"admin","userNo1":"test","userNo2":"hyc"},"userNoLen":3}
declare vUserNo,vUserNoTmp,vUserNoTmpKey varchar(200);
declare vUserNoLen int;
declare i int default 0;
set vUserNo = params->>'$.userNo';
set vUserNoLen = params->>'$.userNoLen';

while i<vUserNoLen do
set vUserNoTmpKey = concat('$.userNo',i);
select JSON_EXTRACT(vUserNo, vUserNoTmpKey) into vUserNoTmp;
set vUserNoTmp = replace(vUserNoTmp,'\"','');
update user set mobileActive = 1 where userNo = vUserNoTmp;
set i = i+1;
end while;
set pRlt=concat('{"success":true,"msg":""}');
end

 

 

CREATE DEFINER=`jovie`@`%` PROCEDURE `fun_api_uploadEmpAttInfo`(pUserId int,pIP varchar(95),params varchar(5000),out pRlt varchar(800))
BEGIN
    -- 考勤记录处理
    -- {token:"4fb7edb9-8f21-4295-83ad-8552590deb0f",methodName:"uploadEmpAttInfo",clockNo:"006",
        -- recData:[{"clockNo":"006","cardNo":"10066","cardTime":"2018-7-13 13:58:21"},{"clockNo":"006","cardNo":"10066","cardTime":"2018-7-13 13:58:23"}]}
    declare vClockNo,vCardNo,vCardTime,vOriginal,vKeyTmp varchar(45);
    declare vRecData,recTmp varchar(5000);
    declare vEmpId,i,vRecLen int default 0;
    declare vCardDate date;
    
    set vRecData = params->>'$.recData';
    set vRecLen = JSON_LENGTH(vRecData);

    while i < vRecLen do 
        set vKeyTmp = concat('$[',i,']'); 
        select JSON_EXTRACT(vRecData, vKeyTmp) into recTmp;
        set vClockNo = JSON_EXTRACT(recTmp, '$.clockNo');
        set vClockNo = replace(vClockNo,'\"','');
        
        set vCardNo = JSON_EXTRACT(recTmp, '$.cardNo');
        set vCardNo = replace(vCardNo,'\"','');
        
        set vCardDate = JSON_EXTRACT(recTmp, '$.cardDate');
        set vCardDate = replace(vCardDate,'\"','');
        
        set vCardTime = JSON_EXTRACT(recTmp, '$.cardTime');
        set vCardTime = replace(vCardTime,'\"','');
        
        set vCardDate = cast(vCardTime as date);
        
        set vOriginal = concat(vClockNo,'_',vCardNo,'_',vCardTime);
        
        select empId into vEmpId from kqcard where cardNo = vCardNo;   
        set i = i + 1;
        if not exists(select original from epcardoriginalrec where original = vOriginal) then
                insert into epcardrec (clockNo,cardNo,empId,cardDate,cardTime,original)
            values (vClockNo,vCardNo,vEmpId,vCardDate,vCardTime,vOriginal);       
        end if;        
    end while;    
    set pRlt= concat('{"success":true,"total":1,"msg":"考勤数据插入成功!"}');
end

阅读更多
个人分类: 数据库
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭