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;
各位看下我的游标定义是不是有什么问题啊,顺便给我写下游标的用法,谢谢!
解决方案 »
- mysql 更新问题
- 求助:netbeans连接mysql出错
- 插入数据时把自增ID附加到数据尾部,急!
- mysql存储过程 数组参数的处理
- linux mysql 服务器配置
- 关于mysql插入空值的处理
- mysql从一个表字段赋值给另一个表字段,在线等待。
- 为什么会出错:mysql="select * from Bbs_User where User_Name="& request.form("Name")谢谢!
- mysql 远程登录成功了 但是本地连接却找不到数据库
- 学习MySQL官方文档,是看5.6还是5.5呢?
- 一个表里删除一行之后如何对rowNum列进行重排序
- mysql建表: 出现 error 1604(42000)的错误,建表的源代码如正文(是在数据库“test”中建立这个表的)
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>
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;