mysql存储过程游标，计算仪器近期维保时间

2023-08-03 13:50:16

mysql存储过程游标，计算仪器近期维保时间

（1）mysql游标简单入门
（2）主题！根据仪器维保时间于维保规则创建近期维保工单。运用了些mysql函数这里说一下。

最近工作遇到的问题，本来打算写一个java api在外面计算维保时间然后添加到工单表中，后来发现数据量大的时候太慢了，程序会一直访问数据库。所以干脆在内部写，顺便记录下。

（1）mysql游标简单入门

概念就是在存储过程中遍历结果集。以前我用分页也实现过，现在看来，真是太蠢了。
游标的使用方式：
1）：创建游标
declare <游标名>CURSOR FOR select * from <表名>;
2）：开启游标：
open <游标名> ;
3):属性赋值：
FETCH <游标名字> into <变量名>；
例子：变量要在创建游标前创建。
declare vUid int(11) default ‘0’;
declare <游标名>CURSOR FOR select uid from <表名>;
FETCH <游标名字> into vUid ；
这样就可以赋值了。
4）：关闭游标
close mycursor;

（2）主题！根据仪器维保时间于维保规则创建近期维保工单。运用了些mysql函数这里说一下。

now（）：获取当天时间。
DATE_FORMAT(now() ,’%Y-%m-%d %H:%i:%s’)：时间规则。
round（）：取整。
ADDDATE(‘时间’,interval <需添加天数>day)；时间加法
to_days（）：时间转换成天数，这个函数我老大说比较慢，数据量大的时候不建议用。我还没找到替代的函数。
if exists（select * from where）then ：查询通过判断表中数据来判断是否满足if条件。


begin
	
-- 声明所需赋值变量，这里有个点需要说一下：声明变量于声明游标之间不能对变量进行赋值。好像区域块的原因具体我也不太清楚。
declare vUid int(11) default '0';
declare vDone int(11) default '0';
declare vDevcode varchar(100) default '';
declare vStartTime varchar(100) default '';
declare vEndTime varchar(100) default '';
declare vCreateTime varchar(100) default '';
declare vFirstDay int(11) default '0';
declare vRule varchar(100) default '';
declare vWeekPeriods varchar(100) default '';
declare vCustomCode varchar(100) default '';
declare vDefenId int(11) default '0';

declare vOverdueData varchar(100) default '';
declare vCon int(11) default '0';

-- 声明游标左联出需要的数据。

declare mycursor CURSOR FOR select 
a.id ,
a.dev_number,
a.start_time ,
a.end_time ,
a.create_time ,
a.first_day ,
b.rule,
b.week_periods,
c.owner_customer_number 
from biosys_device_defenddate a
left join biosys_device_defend b on a.defcode_id = b.id 
left join bio_device c on a.dev_number = c.device_number ;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET vDone=1;


-- 开启游标
open mycursor;
-- 这里需要注意的点 into后面的需要赋值的值要与select查出的数量顺序一致 不然无法进入循环体。
	FETCH  mycursor into vDefenId ,vDevcode ,vStartTime ,vEndTime ,vCreateTime ,vFirstDay ,vRule ,vWeekPeriods ,vCustomCode;
-- 下面这些的就是生成维保工单的逻辑算法vFirstDay是数据表中取出维保日期偏移数值不能大于一周，所以这里判断取模。
	if vFirstDay>7 then
		set vFirstDay = 7%vFirstDay;
	end if ;
-- 进去游标循环体这里会把数据集循环出来。	
	while vDone<> 1 do
-- 判断当天是否小于维保最后维保日期
		if DATE_FORMAT(now() ,'%Y-%m-%d %H:%i:%s')<DATE_FORMAT(vEndTime,'%Y-%m-%d %H:%i:%s')then
-- 开启跳出循环 相当于java的break；要点必须包住while循环体
		outer_label:  BEGIN
-- 这里根据维保规则计算还需维保几次，结束时间减第一次维保时间除以维保周期，
--这要就可以算出需要维保的时间了，计算出的 vStartTime+偏移+维保周期 = 近期维保时间。
			while vCon < round((to_days(DATE_FORMAT(vEndTime,'%Y-%m-%d'))-to_days(DATE_FORMAT(vStartTime,'%Y-%m-%d')))/ (vWeekPeriods*7)) do
				set vOverdueData = date_format(ADDDATE(ADDDATE(date_format(vStartTime,'%Y-%m-%d'),interval vFirstDay day),interval vWeekPeriods*7 day),'%Y-%m-%d');
				if ROUND((to_days(DATE_FORMAT(vOverdueData,'%Y-%m-%d'))-to_days(DATE_FORMAT(now(),'%Y-%m-%d'))))<=7 then
				
					if exists(select a.pk_id from bio_device a where a.device_number = vDevcode)then 
							insert into biosys_workorder(`workorder_type_id`
								,`desc`,`memb_id`,`custom_id`
								,`title`,`code`,`create_date`
								,`exp_date`,`workerorder_bugtype_id`
								,`level`
								,`device_number`,`inspection`,`src_id`,`expected_date`,`ref_code`)
							values(5,'',1,vCustomCode
								,'设备维保'
								,concat('BIO',_nextval('WO_ID'))
								,DATE_FORMAT(now() ,'%Y-%m-%d %H:%i:%s')
								,DATE_FORMAT( DATE_ADD(now(),INTERVAL 3 DAY)  ,'%Y-%m-%d')
								,1,1,vDevcode,'',4, DATE_FORMAT(vOverdueData ,'%Y-%m-%d %H:%i:%s')
								,0);
					end if ;
				else
					LEAVE  outer_label;
				end if ;
				set vCon = vCon+1;
				set vStartTime = vOverdueData;
			end while ;
		END outer_label; 
		end if;
	FETCH  mycursor into vDefenId,vDevcode ,vStartTime ,vEndTime ,vCreateTime ,vFirstDay ,vRule ,vWeekPeriods ,vCustomCode;
	end while ;
close mycursor;
	
END

到这里就结束了。

码农公寓