56,677
社区成员
发帖
与我相关
我的任务
分享
DELIMITER $$
create procedure test()
BEGIN
DECLARE l_start_dt1 datetime;
DECLARE l_end_dt1 datetime;
DECLARE l_date datetime;
DECLARE l_str varchar(30);
DECLARE l_seq int;
DECLARE l_start_dt datetime;
DECLARE l_end_dt datetime;
DECLARE done INT DEFAULT FALSE;
DECLARE C1 CURSOR FOR SELECT SEQ,str_to_date(concat(begindate,' ',begintime),'%Y-%c-%e %k:%i') START_DT,str_to_date(concat(enddate,' ',endtime),'%Y-%c-%e %k:%i') END_DT from test;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN C1;
l_loop: LOOP
FETCH C1 into l_seq,l_start_dt,l_end_dt;
IF done THEN
LEAVE l_loop;
END IF;
set l_start_dt1 = l_start_dt;
loop_label: LOOP
set l_str = concat(date_format(l_start_dt1,'%Y-%m-%d %H'),':','59');
set l_end_dt1 = str_to_date(l_str,'%Y-%m-%d %H:%i');
if l_end_dt1>=l_end_dt then
select l_seq,l_start_dt1,l_end_dt;
leave loop_label;
else
select l_seq,l_start_dt1,l_end_dt1;
end if;
set l_start_dt1 = DATE_ADD(l_end_dt1,INTERVAL 1 MINUTE);
END LOOP loop_label;
END LOOP l_loop;
CLOSE C1;
END$$
DELIMITER ;
--调用
call test();
[/quote]
DELIMITER $$
create procedure test()
BEGIN
DECLARE l_start_dt1 datetime;
DECLARE l_end_dt1 datetime;
DECLARE l_date datetime;
DECLARE l_str varchar(30);
DECLARE l_seq int;
DECLARE l_start_dt datetime;
DECLARE l_end_dt datetime;
DECLARE done INT DEFAULT FALSE;
DECLARE C1 CURSOR FOR SELECT SEQ,str_to_date(concat(begindate,' ',begintime),'%Y-%c-%e %k:%i') START_DT,str_to_date(concat(enddate,' ',endtime),'%Y-%c-%e %k:%i') END_DT from test;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN C1;
l_loop: LOOP
FETCH C1 into l_seq,l_start_dt,l_end_dt;
IF done THEN
LEAVE l_loop;
END IF;
set l_start_dt1 = l_start_dt;
loop_label: LOOP
set l_str = concat(date_format(l_start_dt1,'%Y-%m-%d %H'),':','59');
set l_end_dt1 = str_to_date(l_str,'%Y-%m-%d %H:%i');
if l_end_dt1>=l_end_dt then
select l_seq,l_start_dt1,l_end_dt;
leave loop_label;
else
select l_seq,l_start_dt1,l_end_dt1;
end if;
set l_start_dt1 = DATE_ADD(l_end_dt1,INTERVAL 1 MINUTE);
END LOOP loop_label;
END LOOP l_loop;
CLOSE C1;
END$$
DELIMITER ;
--调用
call test();
DELIMITER //
create procedure test(in pi_begin_time datetime,in pi_end_time datetime)
begin
DECLARE l_start_dt datetime;
DECLARE l_end_dt datetime;
DECLARE l_date datetime;
DECLARE l_str varchar(30);
set l_start_dt = pi_begin_time;
loop_label: LOOP
set l_str = concat(date_format(l_start_dt,'%Y-%m-%d %H'),':','59');
set l_end_dt = str_to_date(l_str,'%Y-%m-%d %H:%i');
if l_end_dt>=pi_end_time then
select l_start_dt,pi_end_time;
leave loop_label;
end if;
select l_start_dt,l_end_dt;
set l_start_dt = DATE_ADD(l_end_dt,INTERVAL 1 MINUTE);
END LOOP;
END;
//
[/quote]
具体怎么执行呢??
DELIMITER //
create procedure test(in pi_begin_time datetime,in pi_end_time datetime)
begin
DECLARE l_start_dt datetime;
DECLARE l_end_dt datetime;
DECLARE l_date datetime;
DECLARE l_str varchar(30);
set l_start_dt = pi_begin_time;
loop_label: LOOP
set l_str = concat(date_format(l_start_dt,'%Y-%m-%d %H'),':','59');
set l_end_dt = str_to_date(l_str,'%Y-%m-%d %H:%i');
if l_end_dt>=pi_end_time then
select l_start_dt,pi_end_time;
leave loop_label;
end if;
select l_start_dt,l_end_dt;
set l_start_dt = DATE_ADD(l_end_dt,INTERVAL 1 MINUTE);
END LOOP;
END;
//