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 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;
		

	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,' ');

		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
		
	end while;

    close c_dt;

end;