mysql存储过程实战
2015 年 9 月 8 日
今天科比离去,今天肺炎病毒持续肆虐。。。
意识到生命的脆弱,今天我继续前行,比以往更加坚定和紧迫,这辈子不活好自己就算白来一趟。
最近需要用到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;