最近有个报表的需求，于是乎用了存储过程，如果你正在寻找存储过程相关的资料，那么，恭喜你找对了地方。无论你的需求是什么，我都坚信本文能帮到你，甚至，我以为，存储过程有这一篇文章就足够了。那么，我们开始今天的主题吧。
首先，我们说几个坑：
1、DECLARE 语句一定要放在最前面，否则报错：SQL 错误 [1064] 比如
DROP PROCEDURE IF EXISTS json_to_select;
CREATE PROCEDURE json_to_select (in jsonArray JSON)
BEGIN
DECLARE i INT DEFAULT 0;
select JSON_LENGTH(jsonArray) into arrayLen;
DECLARE tmpSql2 varchar(1000) character set utf8;
end;
这里，我故意在 select JSON_LENGTH(jsonArray) into arrayLen; 后面加了一行 DECLARE tmpSql2 varchar(1000) character set utf8; 此时会报错的，如果一定要声明，则需要提到 select 前面。
那么，问题来了，我需要先获取到 用户编码，然后声明一个游标，类似下面的 sql
DECLARE orderList CURSOR FOR SELECT order_code FROM ex_order where user_code = userCode;
按照 java 的逻辑，sql 应该这么写：
DECLARE userCode varchar(20) character set utf8;
select user_code into userCode;
DECLARE orderList CURSOR FOR SELECT order_code FROM ex_order where hosp_code = hospCode and user_code = userCode and beactive = 1 order by id desc limit limitCount;
而按照 DECLARE 语句必须写到最前面，这不就冲突了？其实，存储过程中，你完全可以这么写：
DECLARE userCode varchar(20) character set utf8;
DECLARE orderList CURSOR FOR SELECT order_code FROM ex_order where hosp_code = hospCode and user_code = userCode and beactive = 1 order by id desc limit limitCount;
select user_code into userCode;
另外，into 的意思是赋值，比如 select user_code into userCode; 或者 select user_code, user_name into userCode, userName; 含义是查询 user_code 赋值给 userCode，查询 user_name 赋值给 userName
另外，这里 character set utf8 也同样很重要，如果没有它，中文会乱码。
2、存储过程加工数据后，输出数据集
DROP PROCEDURE IF EXISTS json_to_select;
CREATE PROCEDURE json_to_select (in jsonArray JSON)
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE j INT DEFAULT 0;
DECLARE arrayLen INT DEFAULT 0;
DECLARE keyLen INT DEFAULT 0;
DECLARE jsonObject varchar(500) character set utf8;
DECLARE jsonKeys varchar(500) character set utf8;
DECLARE jsonKey varchar(500) character set utf8;
DECLARE jsonValue varchar(500) character set utf8;
DECLARE sqlValue varchar(5000) character set utf8;
DECLARE tmpSql varchar(1000) character set utf8;
select JSON_LENGTH(jsonArray) into arrayLen;
WHILE (i < arrayLen) DO
SELECT JSON_EXTRACT(jsonArray, CONCAT('$[',i,']')) into jsonObject;
SELECT JSON_KEYS(jsonObject) into jsonKeys;
select 0 into j;
select 'select' into tmpSql;
select JSON_LENGTH(jsonKeys) into keyLen;
WHILE (j < keyLen) DO
SELECT JSON_UNQUOTE(JSON_EXTRACT(jsonKeys, CONCAT('$[',j,']'))) into jsonKey;
SELECT JSON_VALUE(jsonObject, CONCAT('$.',jsonKey,'')) into jsonValue;
SELECT CONCAT(tmpSql, ' ', '"', jsonValue , '"', ' ', jsonKey) into tmpSql;
SELECT j + 1 INTO j;
if(j < keyLen) then
SELECT CONCAT(tmpSql, ', ') into tmpSql;
END if;
END WHILE;
if(i < 1) then
select tmpSql into sqlValue;
end if;
if(i > 0) then
select CONCAT(sqlValue, ' UNION ALL ', tmpSql) into sqlValue;
end if;
SELECT i + 1 INTO i;
END WHILE;
set @sql = sqlValue;
PREPARE stmt FROM @sql;
EXECUTE stmt;
deallocate prepare stmt;
end;
call json_to_select('[
{
"id": 4233,
"hospCode": "tdyy",
"orderCode": "2020070700018",
"departCode": "100037",
"departName": "一般检查"
},
{
"id": 4234,
"hospCode": "tdyy",
"orderCode": "2020070700019",
"departCode": "100038",
"departName": "一般检查2"
},
{
"id": 4235,
"hospCode": "tdyy",
"orderCode": "2020070700020",
"departCode": "100039",
"departName": "一般检查3"
}
]');
这个存储过程则是把输入的 json 输出为 mysql 的查询集，这在存储过程中非常有用。你可以将数据查询出来后转为 json object 或者 json array，新版 mysql 已经支持这个。然后操作 json，最后输出。完美！注意：dbeaver 社区版不支持 存储过程，直接报错（可以参考：
Unable create stored procedure for MySQL）但是 navicat 正常。
评论