存储过程中的游标

Question

drop procedure if exists pDropTable12;CREATE  PROCEDURE pDropTable12
(
dbname varchar(255),
daynum int
)
BEGIN
declare db_name varchar(255);
declare day_num int;
declare table_name varchar(255);
declare cur_1 cursor for  select table_name from information_schema.tables where table_schema=db_name and (instr(table_name, YEAR(sysdate())-1) OR instr(table_name, YEAR(sysdate()))) and create_time < date_sub(current_date interval day_num day);
 set db_name=dbname; 
 set day_num=daynum;
 open cur_1;  
 repeat  
 fetch cur_1 into table_name; 
 drop table table_name;
 end repeat;
 close cur_1;
END;
各位看下我的游标定义是不是有什么问题啊,顺便给我写下游标的用法,谢谢!

Answer 1

你的db_name,day_num是不是应该先赋值,然后再搜索呢.?

Answer 2

需要改的地方不少。mysql> delimiter //
mysql> drop procedure if exists pDropTable12//
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql>
mysql> CREATE  PROCEDURE pDropTable12(
    ->  dbname varchar(255),
    ->  daynum int
    -> )
    -> BEGIN
    ->  DECLARE done INT DEFAULT 0;
    ->  declare vtable_name varchar(255);
    ->
    ->  DECLARE cur_1 CURSOR FOR
    ->          select table_name from information_schema.tables
    ->                  where table_schema=dbname
    ->                  and (instr(table_name, YEAR(sysdate())-1)
    ->                          OR instr(table_name, YEAR(sysdate())))
    ->                  and create_time < date_sub(current_date ,interval daynum day);
    ->  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    ->
    ->  open cur_1;
    ->  fetch cur_1 into vtable_name;
    ->  while done=0 do
    ->          set @sTempSQL = concat('drop table ',vtable_name);
    ->          PREPARE stmt1 FROM @sTempSQL;
    ->          EXECUTE stmt1;
    ->          DEALLOCATE PREPARE stmt1;
    ->          fetch cur_1 into vtable_name;
    ->  end while;
    ->  close cur_1;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;
mysql>
mysql> create table t200901(id int);
Query OK, 0 rows affected (0.09 sec)mysql> create table t200902(id int);
Query OK, 0 rows affected (0.06 sec)mysql> create table t200903(id int);
Query OK, 0 rows affected (0.11 sec)mysql> show tables like 't2009%';
+-------------------------+
| Tables_in_test (t2009%) |
+-------------------------+
| t200901                 |
| t200902                 |
| t200903                 |
+-------------------------+
3 rows in set (0.00 sec)mysql>
mysql> call pDropTable12('test',-1);
Query OK, 0 rows affected (0.06 sec)mysql> show tables like 't2009%';
Empty set (0.00 sec)mysql>

Answer 3

1、动态生成SQL语句：
 set @SQL = concat('drop table ',vtable_name);
 PREPARE stmt1 FROM @SQL;
 EXECUTE stmt1;
2、游标部份改动比较多，建议看看MYSQL HELP：
declare db_name varchar(255);
declare day_num int;
declare table_name varchar(255);
declare cur_1 cursor for  select table_name from information_schema.tables where table_schema=db_name and (instr(table_name, YEAR(sysdate())-1) OR instr(table_name, YEAR(sysdate()))) and create_time < date_sub(current_date interval day_num day);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    open cur_1;
    fetch cur_1 into vtN;
    while done=0 do
             set @SQL = concat('drop table ',vtN);
             PREPARE stmt1 FROM @SQL;
              EXECUTE stmt1;
              DEALLOCATE PREPARE stmt1;
              fetch cur_1 into vtN;
      end while;
      close cur_1;