可以确认游标内是可以传递参数的。下面是我写的一个清理测试数据的一个存储过程,里面就用到游标,传递数据库名称进去的。CREATE PROCEDURE Clear_DB( DB_NAME varchar(50) # 数据库名称 ) BEGIN DECLARE done INT DEFAULT 0; #游标的标志位 DECLARE a varchar(20); DECLARE b varchar(20); DECLARE cur1 CURSOR FOR SELECT table_name FROM information_schema.TABLES WHERE table_schema=DB_NAME ; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1; REPEAT FETCH cur1 INTO a; IF NOT done THEN set b=concat('Delete from ',DB_NAME,'.',a); # 拼删除命令 # set b=concat('TRUNCATE from ',DB_NAME,'.',a); # 拼删除命令 SET @E=b; PREPARE stmt1 FROM @E; EXECUTE stmt1; # 执行命令 DEALLOCATE PREPARE stmt1; #释放对象 END IF; UNTIL done END REPEAT; CLOSE cur1; END;
DB_NAME varchar(50) # 数据库名称
)
BEGIN
DECLARE done INT DEFAULT 0; #游标的标志位
DECLARE a varchar(20);
DECLARE b varchar(20);
DECLARE cur1 CURSOR FOR SELECT table_name FROM information_schema.TABLES WHERE table_schema=DB_NAME ;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO a;
IF NOT done THEN
set b=concat('Delete from ',DB_NAME,'.',a); # 拼删除命令
# set b=concat('TRUNCATE from ',DB_NAME,'.',a); # 拼删除命令
SET @E=b;
PREPARE stmt1 FROM @E;
EXECUTE stmt1; # 执行命令
DEALLOCATE PREPARE stmt1; #释放对象
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
END;
select @a;@a是OUT类型的参数,存储过程的返回值,需要再SELECT出来。