今天科比离去，今天肺炎病毒持续肆虐。。。
意识到生命的脆弱，今天我继续前行，比以往更加坚定和紧迫，这辈子不活好自己就算白来一趟。
最近需要用到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;
转载：https://blog.csdn.net/IndexMan/article/details/104095784
查看评论