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