MySQL写存储过程的坑点

本身需求很简单，但是因为之前都没有写过存储过程。所以很多坑点没踩过。

另外：阿里巴巴开发手册（三）MYSQL 7.【强制】禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。

当业务主导时，就当没说。

大致需求是：传入几个参数当做查询条件，查询满足条件的 User，然后为 User 生成一批业务数据，主要是得到 User的主键 id。参数中有起始截止时间，在该区间内以天为单位，每天为每一位 User生成一条业务数据。最终语句是这样：
内容已做脱敏处理

show code

/*
 创建存储过程 AutoCreateData
 company_id   公司id
 dept_id      部门id
 start_day    起始时间
 end_day      截止时间
 ATTENTIONS:
 1.调用该过程，会覆盖式插入 t_business （user_id在该时间区间内的bizdata）
 */
 DELIMITER $$
 DROP PROCEDURE IF EXISTS AutoCreateData;
 CREATE PROCEDURE AutoCreateData(IN company_id VARCHAR(32),IN dept_id VARCHAR(32),IN start_day VARCHAR(10),IN end_day VARCHAR(10))
 BEGIN
     DECLARE user_id VARCHAR(32);
     DECLARE _STOP BOOLEAN DEFAULT FALSE;
     DECLARE start_day_date date;
     DECLARE end_day_date date;
      DECLARE user_cursor CURSOR FOR SELECT t.id FROM user t where t.company_id=company_id and t.dept_id=dept_id and t.status=0;
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET _STOP=TRUE;
     SET start_day_date=DATE_FORMAT(start_day,'%Y%m%d');
     SET end_day_date=DATE_FORMAT(end_day,'%Y%m%d');
     OPEN user_cursor;
     my_loop:LOOP
         --  每次循环为 user_id 重置start_day_date
         SET start_day_date=DATE_FORMAT(start_day,'%Y%m%d');
         FETCH user_cursor INTO user_id;
         IF _STOP  THEN 
             LEAVE my_loop;
         END IF;
         WHILE start_day_date <= end_day_date DO
             REPLACE INTO t_business
                 (id,company_id,user_id,pb_date,dept_id,create_date,create_uid)
             VALUE
                 (UUID(),
                  company_id,
                  user_id,
                  start_day_date,
                  dept_id,
                  NOW(),
                  'SYS_AUTO_INSERT_PRESSURE_TEST');
             SET start_day_date = DATE_ADD(start_day_date,INTERVAL 1 DAY);
         END WHILE;
     END LOOP my_loop;
     CLOSE user_cursor;
 END $$
 DELIMITER ;

call

CALL AutoCreateData('company_id','dept_id','2021-05-13','2021-05-23')

坑点一：创建格式

不要使用 create or replace PROCEDURE ，这种方式 Oracle是OK的。

坑点二：replace

在存储过程内部的业务代码中，insert 业务数据时，通常我们会先删掉之前的数据，另一种情况是：当业务表的某个字段有唯一索引时，后期会插入失败，必须先删除。观者可以试试这种方法：先delete再insert。保证你加班到明天。

请使用 replace into tableName（） values（） 替换。

坑点三：别名

在存储过程逻辑代码一开始，我们可能会使用某几个参数去查询一张表的数据。当在定义游标时，for之后的select语句中表名请使用别名，类似这样：

DECLARE user_cursor CURSOR FOR SELECT t.id FROM user t where t.company_id=company_id and t.dept_id=dept_id and t.status=0;

如果不用别名这种方式表示，可能会是这样的：

DECLARE user_cursor CURSOR FOR SELECT id FROM user where company_id=company_id and dept_id=dept_id and status=0;

仔细观察where条件语句，company_id=company_id，会不会懵逼，同样的mysql也可能懵逼，同时也不利于阅读。

坑点四：游标结束位置

在定义游标的结束标识 NOT FOUND SET _STOP=TRUE时，请紧跟游标的定义，中间不要插入其他的语句，否则游标取值时你会抓狂。

DECLARE user_cursor CURSOR FOR SELECT t.id FROM user t where t.company_id=company_id and t.dept_id=dept_id and t.status=0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET _STOP=TRUE;

坑点五：结束符号

DELIMITER关键字的使用，在编写存储过程时，可能会有一大段语句，每句都需要使用英文分号作为结尾，MySQL在识别时就会断句，导致执行失败。解决方法是：使用DELIMITER重新定义MySQL默认的语句结束符 ，可以自定义这个符号，比如 $$ //

在编写完存储过程后，以自定义的符号结尾， 最后再次以 DELIMITER 重新定义语句结束符为英文分号。