本人使用游标写了一段sql语句,创建了一个存储过程然后调用存储过程,但是执行不出来 也没有错误提示,语句如下,求帮助。。
CREATE PROCEDURE p_test()
BEGIN
DECLARE fk_name varchar(64);
DECLARE sqlcmd varchar(255);
DECLARE table_name varchar(64);
DECLARE cur_ok bool DEFAULT false;
DECLARE cur CURSOR for SELECT a.CONSTRAINT_NAME,b.TABLE_NAME from information_schema.REFERENTIAL_CONSTRAINTS a,information_schema.TABLES b WHERE a.TABLE_NAME=b.TABLE_NAME;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET cur_ok=true;
open cur;
REPEAT
FETCH cur INTO fk_name,table_name;
IF NOT cur_ok THEN
set @sqlcmd=CONCAT('alter table ', table_name, ' drop foreign key ', fk_name);
SELECT @sqlcmd;
PREPARE stmt FROM @sqlcmd;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
UNTIL cur_ok END REPEAT;
close cur;
END;
CREATE PROCEDURE p_test()
BEGIN
DECLARE fk_name varchar(64);
DECLARE sqlcmd varchar(255);
DECLARE table_name varchar(64);
DECLARE cur_ok bool DEFAULT false;
DECLARE cur CURSOR for SELECT a.CONSTRAINT_NAME,b.TABLE_NAME from information_schema.REFERENTIAL_CONSTRAINTS a,information_schema.TABLES b WHERE a.TABLE_NAME=b.TABLE_NAME;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET cur_ok=true;
open cur;
REPEAT
FETCH cur INTO fk_name,table_name;
IF NOT cur_ok THEN
set @sqlcmd=CONCAT('alter table ', table_name, ' drop foreign key ', fk_name);
SELECT @sqlcmd;
PREPARE stmt FROM @sqlcmd;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
UNTIL cur_ok END REPEAT;
close cur;
END;
解决方案 »
- 求SQL
- 急!~~高手指教 错误:1206 SQLSTATE: HY000 (ER_LOCK_TABLE_FULL)
- 10万用户的系统怎么设计?
- 连接postgresql中出现Ident authentication failed for user (求解)
- 请教一个mysql替换的查询
- 求<<MySQL核心编程:高级开发者指南>>电子版。
- 请教MySQL安装问题, 送分!
- 请教:数据库事务中的“脏读”、“不可重复的读”、“虚读”都代表什么意思,我有点搞不明白
- 了解数据库这方面的朋友进来下!有个问题想请教!
- MySQL中文和数字排序问题
- 如何优化下面的sql语句
- 这句简单的查询怎么回事?求解释
SELECT @sqlcmd;
PREPARE stmt FROM @sqlcmd;
EXECUTE stmt;
DEALLOCATE PREPARE stmt; 是否有结果输出,贴出以供分析。
SELECT a.CONSTRAINT_NAME,b.TABLE_NAME from information_schema.REFERENTIAL_CONSTRAINTS a,information_schema.TABLES b WHERE a.TABLE_NAME=b.TABLE_NAME AND b.TABLE_SCHEMA = 'db_name';set @sqlcmd=CONCAT('use db_name; alter table ', table_name, ' drop foreign key ', fk_name);