mysql存储过程实战

今天科比离去，今天肺炎病毒持续肆虐。。。
意识到生命的脆弱，今天我继续前行，比以往更加坚定和紧迫，这辈子不活好自己就算白来一趟。
最近需要用到mysql存储过程去处理一些表数据，然后利用java poi导出excel数据。
既然用了那就总结一下用到的知识点：

1.声明变量

declare

2.参数

in/out 参数名 数据类型

3.查询赋值

select into   和oracle相似。

4.结果集循环

声明游标，然后打开赋值循环，再赋值。

5.case..when语句

可以直接case

6.条件控制语句

if .. then
else
end if;

7.循环语句

对比oracle for循环，使用while

8.mybatis如何调用？

有不懂得直接看我的存储过程（省略了部分） ：

DROP PROCEDURE IF EXISTS `proc_update_work_dt`;

CREATE PROCEDURE `proc_update_work_dt`(in p_year varchar(32), in p_month varchar(32))
BEGIN
    declare v_total double DEFAULT 0;
        declare v_hours double DEFAULT 0;
        declare v_begin_dt VARCHAR(100);
        declare v_end_dt   VARCHAR(100);
        declare v_work_day VARCHAR(100);
        declare v_work_time VARCHAR(100);
    declare v_count int; /*总记录数*/
      declare i int DEFAULT 1;
    declare j int DEFAULT 0;
    declare no_more_row int default 0; 
    declare v_seqno int;
    declare v_d1 VARCHAR(100);
        declare v_d2 VARCHAR(100);
        declare v_d3 VARCHAR(100);
        ...


        declare c_dt cursor for 
        select seqno,d1,d2,d3,d4,d5,d6,d7,d8,d9,d10,d11,d12,d13,d14,d15,d16,d17,d18,d19,d20,d21,d22,d23,d24,d25,d26,d27,d28,d29,d30,d31
    from work_dt;

    declare CONTINUE HANDLER for not found
    set no_more_row=1;

    open c_dt;

    fetch c_dt into v_seqno,v_d1,v_d2,v_d3,v_d4,v_d5,v_d6,v_d7,v_d8,v_d9,v_d10,v_d11,v_d12,v_d13,v_d14,v_d15,v_d16,v_d17,v_d18,v_d19,
    v_d20,v_d21,v_d22,v_d23,v_d24,v_d25,v_d26,v_d27,v_d28,v_d29,v_d30,v_d31;
        

        while !no_more_row DO
      set v_total = 0;
      set i = 1;
            while i <= 31 DO
        set v_work_day = CONCAT(p_year,'-',p_month,'-',i,' ');
        select v_work_day;

                case i
                    when 1 then
          set v_work_time = v_d1;
        when 2 then
          set v_work_time = v_d2;
        when 3 then
          ...
                end case;


 select substr(v_work_time,1,instr(v_work_time,'-')-1),substr(v_work_time,instr(v_work_time,'-')+1, length(v_work_time))
      into v_begin_dt, v_end_dt;

      
      set v_hours = 0;
      if(v_begin_dt is not null and v_begin_dt!='' and v_end_dt is not null and v_end_dt != '') then
          set v_begin_dt = concat(v_work_day,v_begin_dt);
          set v_end_dt = concat(v_work_day,v_end_dt);
                    
          select truncate(timestampdiff(MINUTE, DATE_FORMAT(v_begin_dt,'%Y-%m-%d %H:%i'),DATE_FORMAT(v_end_dt,'%Y-%m-%d %H:%i'))/60,1)
          into v_hours;

          
          set v_total = v_total+v_hours;
          
          
          case i
            when 1 then
              update work_dt set h1 = v_hours where seqno = v_seqno;
            when 2 then
              update work_dt set h2 = v_hours where seqno = v_seqno;
            when 3 then
              update work_dt set h3 = v_hours where seqno = v_seqno;
            ...
          end case;
      else
        case i
            when 1 then
              update work_dt set h1 = null where seqno = v_seqno;
            when 2 then
              update work_dt set h2 = null where seqno = v_seqno;
            when 3 then
              ...
          end case;
      end if;
                set i=i+1;

      end WHILE;

      update work_dt
      set total = v_total
      where seqno = v_seqno;

             fetch c_dt into v_seqno,v_d1,v_d2,v_d3,v_d4,v_d5,v_d6,v_d7,v_d8,v_d9,v_d10,v_d11,v_d12,v_d13,v_d14,v_d15,v_d16,v_d17,v_d18,v_d19,
                v_d20,v_d21,v_d22,v_d23,v_d24,v_d25,v_d26,v_d27,v_d28,v_d29,v_d30,v_d31;
        
        end while;

    close c_dt;

end;