56,687
社区成员
发帖
与我相关
我的任务
分享
DELIMITER //
DROP PROCEDURE IF EXISTS getinfo //
CREATE PROCEDURE
getinfo( jsonstr JSON )
BEGIN
//获取json数据长度
DECLARE num CURSOR FOR select JSON_LENGTH(jsonstr);
//创建临时表
create temporary table if not exists tmp_info(information_id JSON);
//初始化临时表
truncate TABLE tmpTable;
OPEN num;
while num>0 do
SET @res:=(SELECT JSON_EXTRACT(jsonstr,CONCAT('$[',num,'].information_id')));
INSERT INTO tmp_info(information_id) VALUES (@res);
set num=num-1;
end while;
SELECT information_id FROM tmp_info;
END //
DELIMITER ;
DELIMITER //
CREATE PROCEDURE
getinfo(jsonstr varchar(2048))
BEGIN
select JSON_LENGTH(jsonstr) into @num;
create temporary table if not exists tmp_info(information_id VARCHAR(2048));
SET @num=@num-1;
truncate TABLE tmp_info;
while @num>-1 do
SET @res:=(SELECT JSON_EXTRACT(jsonstr,CONCAT('$[',@num,'].information_id')));
INSERT INTO tmp_info(information_id) VALUES (@res);
SET @num=@num-1;
end while;
SELECT information_id FROM tmp_info;
END //
DELIMITER ;