sql - MySQL Version 5.5.37: Dropping tables older than 30 days using CURSOR -

i've been researching trying find way fetch rows query result , process them individually. i've written script thought work apparently not.

the script:

declare @name char(20);  declare c1 cursor read_only select table_name information_schema.tables table_schema = 'puslogger' , update_time < (now() - interval 30 day)  open c1;   fetch next c1 @table_name  while @@fetch_status = 0 begin  prepare stmt "concat('drop table if exists `', @table_name,'`;')" execute stmt dealloctate stmt  fetch next c1 @table_name  end  close c1 deallocate c1 

the script intended drop tables older 30 days. although doesn't seem work mysql version 5.5.37.

i'm new mysql , i'm running server mysql windows (xp). perhaps syntax cursors isn't correct corresponding server version? i'm not sure i'd happy if me out.

edit:

this error message returned when try execute script sql command line:

error 1064 (42000): have error in sql syntax;

check manual corresponds mysql server version right syntax use near 'declare @name char(20)' @ line 1

error 1064 (42000): have error in sql syntax;

check manual corresponds mysql server version right syntax use near 'declare c1 cursor read_only select table_name information_schema.tables' @ line 1

error 1064 (42000): have error in sql syntax; check manual corresponds mysql server version right syntax use near 'fetch next c1 @table_name

while @@fetch_status = 0 begin

prepare st' @ line 1

update:

i tried script (note: intend create event executed daily remove tables older 30 days.):

delimiter |  create event clean_logger     on schedule every 1 day         begin         declare @name char(20);     declare c1 cursor read_only         select table_name     information_schema.tables table_schema = 'puslogger' , update_time < (now() - interval 30 day);      open c1;       fetch next c1     @table_name;      while @@fetch_status = 0     begin      prepare stmt "concat('drop table if exists `', @table_name,'`;')";     execute stmt;     dealloctate stmt;      fetch next c1     @table_name;      end;  close c1; deallocate c1; end |  delimiter ; 

running script in sql command line returns:

error 1064 (42000): have error in sql syntax;

check manual corresponds mysql server version right syntax use near '@name char(20); declare c1 cursor read_only select table_name infor' @ line 5

try below procedure:

delimiter $$  use `test`$$  drop procedure if exists `sp_drop_table`$$  create definer=`root`@`localhost` procedure `sp_drop_table`() begin         declare done int(1) default 0;         declare _tblname varchar(20) default '';          declare cur1 cursor select table_name information_schema.tables table_schema = 'puslogger' , update_time < (now() - interval 30 day);         declare continue handler sqlstate '02000' set done=1; -- open cursor1 open cur1; begin  repeat fetch cur1 _tblname; if _tblname = ''     set done = 1; end if;               if (done<>1)     set @str1=concat("drop table if exists ",_tblname);                  prepare stmt1 @str1;                  execute stmt1;                  deallocate prepare stmt1;              end if;  until done end repeat; end;      close cur1; -- close cursor1            select 'done';  end$$  delimiter ;