Shulou(Shulou.com)06/01 Report--

Cursor declaration cursor DECLARE cursor_name CURSOR FOR select_statement

This statement declares a cursor. You can also define multiple cursors in a subroutine, but each cursor in a block must have a unique name.

Note: SELECT statements cannot have INTO clauses.

Open the cursor OPEN cursor_name

This statement opens the previously declared cursor.

Forward cursor FETCH cursor_name INTO var_name [, var_name]...

This statement reads the next line (if any) with the specified open cursor and advances the cursor pointer.

Close the cursor CLOSE cursor_name

This statement closes the previously opened cursor.

Add index in batch

Share a cursor that adds indexes in batches, and batch operations become easier when there are hundreds of tables with the same table structure but different names in a library.

# delete the stored procedure DROP PROCEDURE IF EXISTS FountTable;DELIMITER $$CREATE PROCEDURE FountTable () BEGIN DECLARE TableName varchar (64); # declare the cursor 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 the cursor OPEN cur_FountTable; REPEAT FETCH cur_FountTable INTO TableName # define preprocessing 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; # closes the cursor CLOSE cur_FountTable;END $$DELIMITER; CALL FountTable ()

Here are a few details:

Remember to modify the conditions you need to query when declaring cursors

In the preprocessing, you also need to change it to the corresponding field.

When defining condition variables, I use EXIT here, that is, I break when I encounter an error, of course, I can also use CONTINUE.

Note: since mysql cannot directly use the queried variable name as a table name in a stored procedure, the method of dynamic splicing SQL is used here, but the usual SET CONCAT method does not work, so PREPARE is used for precompilation.

Before defining cursors, all definitions can only be defined through decalre, not SET!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.