MySQL存储过程

业务是根据test表的code分区获取统计数据，然后根据test表的code查询test_new表对应code的数量，最后新增统计数据到test_statistic

CREATE  PROCEDURE `test_statistic`()
BEGIN
  -- 声明变量
  DECLARE done INT DEFAULT FALSE;
  DECLARE total INT;
  DECLARE name VARCHAR(255);
  DECLARE code VARCHAR(255);
  DECLARE new_num INT;

  -- 游标声明
  DECLARE cur CURSOR FOR
    SELECT COUNT(1) total, t_name, t_code FROM test GROUP BY code;

  -- 游标完成标志
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  -- 业务上需要清除所有数据再添加新数据
  DELETE FROM test_statistic;

  -- 打开游标
  OPEN cur;
  -- 游标读取循环
  read_loop: LOOP
    FETCH cur INTO total, name, code;
    IF done THEN
      LEAVE read_loop;
    END IF;

        -- 最新数量赋值给new_num
        SELECT count(1) FROM test_new WHERE tn_code = code INTO new_num;

        -- 插入数据
        INSERT INTO test_statistic (total, ts_name, ts_code, new_num) VALUES (total, name, code, new_num);

  END LOOP;

  -- 关闭游标
  CLOSE cur;

END