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如何调用？

<select id="updateWorkDt" parameterType="map" useCache="false" statementType="CALLABLE">
    <![CDATA[
      call proc_update_work_dt(#{year,mode=IN,jdbcType=VARCHAR},#{month,mode=IN,jdbcType=VARCHAR});
    ]]>
    </select>

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

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;