CREATE DEFINER=`root`@`%` PROCEDURE `find_time`(IN orgId VARCHAR(64), out time TIMESTAMP)
BEGIN
    
    declare i int default 0;
    declare cachtime TIMESTAMP;
    declare done int default 0;
    # 1、游标的定义
    declare cache_timecursor for select  created_time  from analysis_events_1 WHERE cameraid = orgId  order by created_time desc LIMIT 10;
     # 捕获系统抛出的 not found 错误，如果捕获到，将 done 设置为 1  相当于try异常
    declare continue handler for not found set done=1;
    
        # 2、打开游标
    open cache_time;

    www:loop
      # 3、使用游标
      fetch cache_timeinto time;
      # 如果发生异常
        if done = 1 then
          leave www;
        end if ;
                
            set i = i +1;

    end loop ;
    # 4、关闭游标
    close cache_time;
    
END

CREATE DEFINER=`root`@`%` PROCEDURE `clear_data`()
BEGIN

declare cachtime TIMESTAMP;
DECLARE camid VARCHAR(100) DEFAULT '';
DECLARE count INTEGER DEFAULT 0;
SELECT cameraid , count(`cameraid`) AS`count` FROM analysis_events_1  GROUP BY cameraid  ORDER BY count DESC LIMIT 1 INTO camid, count;

IF(count >10) THEN    

#测试
#SELECT count; 
#SELECT camid; 

CALL find_time(camid, cachtime);

#测试
#SELECT cachtime; 

DELETE FROM analysis_events_1 WHERE cameraid = camid and created_time < cachtime;

END IF ;

END