MySQL 游标
#删除创建存储过程DROP PROCEDURE IF EXISTS FountTable;
DELIMITER $$CREATE PROCEDURE FountTable()BEGIN
DECLARE TableName varchar(64);
#声明游标 DECLARE cur_FountTable CURSOR FOR SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA='front' AND TABLE_NAME LIKE 'student%'; DECLARE EXIT HANDLER FOR not found CLOSE cur_FountTable;
#打开游标 OPEN cur_FountTable;
REPEAT FETCH cur_FountTable INTO TableName;
#定义预处理 SET @SQLSTR1 = CONCAT('create index Flag on ','`',TableName,'`',' (Flag); ');
SET @SQLSTR2 = CONCAT('create index State on ','`',TableName,'`',' (State); ');
SET @SQLSTR3 = CONCAT('create index upload on ','`',TableName,'`',' (upload); ');
SET @SQLSTR4 = CONCAT('create index ccFlag on ','`',TableName,'`',' (lockFlag); ');
SET @SQLSTR5 = CONCAT('create index comes on ','`',TableName,'`',' (comes); ');
###SET @SQLSTR=CONCAT(@SQLSTR1,@SQLSTR2,@SQLSTR3,@SQLSTR4,@SQLSTR5 ); PREPARE STMT1 FROM @SQLSTR1;
PREPARE STMT2 FROM @SQLSTR2;
PREPARE STMT3 FROM @SQLSTR3;
PREPARE STMT4 FROM @SQLSTR4;
PREPARE STMT5 FROM @SQLSTR5;
EXECUTE STMT1;
EXECUTE STMT2;
EXECUTE STMT3;
EXECUTE STMT4;
EXECUTE STMT5;
DEALLOCATE PREPARE STMT1; DEALLOCATE PREPARE STMT2; DEALLOCATE PREPARE STMT3; DEALLOCATE PREPARE STMT4; DEALLOCATE PREPARE STMT5;
# SELECT @SQLSTR;
UNTIL 0 END REPEAT;
#关闭游标 CLOSE cur_FountTable;END $$
DELIMITER ;
CALL FountTable();
页:
[1]