mysql - 存储过程 从入门到放弃

最近有个报表的需求，于是乎用了存储过程，如果你正在寻找存储过程相关的资料，那么，恭喜你找对了地方。无论你的需求是什么，我都坚信本文能帮到你，甚至，我以为，存储过程有这一篇文章就足够了。那么，我们开始今天的主题吧。

首先，我们说几个坑：

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 正常。