Mysql cursors are referenced in the database

I as a background development engineer in an internet company, but the company has more basic data, and more complex.

Therefore, in the development of the development of using Excel table to import the basic data, but because of the early business understanding, in the writing of the import process, the original name of the product is not added to the name '-factory ', after the actual operation found immediately after the program modification;

However, during this time the import data is not dirty data, in order to avoid duplication of operations, using the database to the right batch processing results.

The following is a cursor usage instance.

1 DROP PROCEDURE IFexiest re_name ()2 CREATE PROCEDURERe_name ()3 begin4     DECLARES_didint( One);5     DECLARES_dnameVARCHAR( the);6     DECLAREStopint DEFAULT 0;7     --DECLARE fname VARCHAR (n);8     DECLARECurcursor  for SelectDid,dname fromDb_fittingswhereDquality= 'Original'  andDname not  like '% original%' ;9     Declare CONTINUEHANDLER forSQLSTATE'02000' SETStop= NULL;Ten     OPENcur; One FETCHCur intoS_did,s_dname; A      while(Stop is  not NULL) do -     Update' Db_fittings 'Set' Dname '=CONCAT (S_dname,'-Original')WHERE' Did '=S_did; -     FETCHCur intoS_did,s_dname; the     END  while; - CLOSEcur; - End

The following explains the above code as a record:

The first line deletes the stored procedure re_name () if it exists;

Create a stored procedure re_name ();

declaring local variables;

The cursor is declared with the same use as the variable needs to be declared: DECLARE cur cursor FOR select did,dname from db_fittings where dquality = ' original ' and dname not-like '% original Plant% ';

The following declaration occurs when the query exception is stopped jumping out of the loop: Declare CONTINUE HANDLER for SQLSTATE ' 02000 ' SET stop = null;

The cursor needs to be opened before using the right: Open cur

Use fetch to tell the result of the query to assign a temporary variable: Fetch cur into s_did,s_dname

Then process the cursor query results using the while Do loop

Closes the cursor.

Settle stored procedures.

Problems encountered in the writing process

The original database field length of 32 is not long enough to cause the data too long for column ' Dname ' at row 1 error, and then the length 32 changed to 60 after the program is working properly, renamed successfully

Mysql cursors are referenced in the database