MYSQL應(yīng)用
DECLARE cursor_name CURSOR FOR select_statement

MYSQL應(yīng)用
DECLARE cursor_name CURSOR FOR select_statement

MYSQL應(yīng)用
FETCH cursor_name INTO var_name [, var_name] ...

MYSQL應(yīng)用
CLOSE cursor_name

MYSQL應(yīng)用
#刪除創(chuàng)建存儲過程
DROP PROCEDURE IF EXISTS FountTable;
DELIMITER $$
CREATE PROCEDURE FountTable()
BEGIN
  DECLARE TableName varchar(64); 
  #聲明游標(biāo)
  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;
  #打開游標(biāo)
  OPEN cur_FountTable;
  REPEAT
     FETCH cur_FountTable INTO TableName;
     #定義預(yù)處理
     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;
  #關(guān)閉游標(biāo)
  CLOSE cur_FountTable;
END $$
DELIMITER ;
CALL FountTable();