mysql存储过程游标的创建_MySQL 存储过程,游标,临时表创建

-- --------------------------------------------------------------------------------

-- Routine DDL

-- Note: comments before and after the routine body will not be stored by the server

-- --------------------------------------------------------------------------------

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `filter_record_time`(startTime timestamp, endTime timestamp, pointIndex int)

BEGIN

declare _recordtime timestamp;

declare _pointIndex smallint;

declare _value double;

declare _result text;

declare _previoustime timestamp;

DECLARE done INT DEFAULT FALSE;

declare fetchSeqCursor cursor for select distinct RecordTime,PointIndex,Value

from flow_record

where recordtime >= startTime

and recordtime < endTime

and pointIndex = pointIndex

and recordtime is not null

order by recordtime;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

create table if not exists filter_record_time_temp(timeFrom timestamp, timeTo timestamp, times int);

open fetchSeqCursor;

seq_loop:loop

fetch fetchSeqCursor into _recordtime, _pointIndex, _value;

IF done THEN

LEAVE seq_loop;

END IF;

if _previoustime is null then

if _recordtime <> startTime then

insert into filter_record_time_temp select startTime timefrom, _recordtime timeto,

cast((unix_timestamp(_recordtime) - unix_timestamp(startTime) - 120) / 120 as signed) times;

end if;

else

if unix_timestamp(_previoustime) + 120 < unix_timestamp(_recordtime) then

insert into filter_record_time_temp select _previoustime timefrom, _recordtime timeto,

cast((unix_timestamp(_recordtime) - unix_timestamp(_previoustime) - 120) / 120 as signed) times;

end if;

end if;

set _previoustime = _recordtime;

end loop;

close fetchSeqCursor;

if unix_timestamp(_previoustime) + 120 < unix_timestamp(endTime) then

insert into filter_record_time_temp select _previoustime timefrom, endTime timeto,

cast((unix_timestamp(endTime) - unix_timestamp(_previoustime) - 120) / 120 as signed) times;

end if;

select * from filter_record_time_temp;

drop table if exists filter_record_time_temp;

END

真尼玛烦人,各个数据库sql语法都不一致,写一点东西查半天资料,耽误时间.