mysql逐条取出使用_mysql 存储过程，逐条判断，逐条删除记录

CREATEDEFINER=`root`@`%`PROCEDURE`e_test`()BEGIN--变量声明DECLARES_dateINT(11);DECLARES_hourINT(11);DECLARES_minINT(11);DECLARES_countchar(14);DECLARES_idINT(11);DECLARES...

CREATE DEFINER=`root`@`%` PROCEDURE `e_test`()

BEGIN

-- 变量声明

DECLARE S_date INT(11);

DECLARE S_hour INT(11);

DECLARE S_min INT(11);

DECLARE S_count char(14);

DECLARE S_id INT(11);

DECLARE S_diff_sec int(11);

DECLARE flag INT DEFAULT 0;

DECLARE diff_curtime_tstime CURSOR FOR SELECT id,day,hour,min_index FROM ts_record;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET flag = 1 ;

OPEN diff_curtime_tstime;

FETCH NEXT FROM diff_curtime_tstime into S_id,S_date,S_hour,S_min;

REPEAT

SELECT CAST(S_date as CHAR(8));

SELECT CAST(S_hour as CHAR(2));

Set S_min =S_min*10;

SELECT S_min;

SELECT CAST(S_min as CHAR(2));

if length(S_hour)=1 then

SET S_count= CONCAT(S_date,'0',S_hour,S_min,'00');

else

SET S_count= CONCAT(S_date,S_hour,S_min,'00');

end if;

SELECT UNIX_TIMESTAMP(S_count);

SELECT UNIX_TIMESTAMP();

set S_diff_sec =UNIX_TIMESTAMP()-UNIX_TIMESTAMP(S_count);

SELECT S_diff_sec;

delete from ts_record where S_diff_sec >605400 ;

FETCH NEXT FROM diff_curtime_tstime into S_id,S_date,S_hour,S_min;

UNTIL flag END REPEAT;

close diff_curtime_tstime;

END;

展开