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

本博客若无特殊说明则由 full-stack-trip 原创发布
转载请点名出处:全栈之旅 > mysql - 存储过程 从入门到放弃
本文地址:https://www.kpromise.top/mysql-stored-procedure-from-beginner-to-master/

发表评论

电子邮件地址不会被公开。 必填项已用*标注