Usage notes for MySQL cursors

The following articles mainly introduce the notes for using MySQL cursors, which can be used in SQL statements of stored procedures. The main types are as follows, I believe that if you master this technology, it will be of great help in your future studies or work.

1. No return result statements, such as INSERT, UPDATE, DROP, and DELETE.

2. The select statement returns a single-row variable and can be passed to the local variable (select ..)

3. Return the select statement of the multi-row result set and can be processed cyclically using the MySQL cursor.

Note: The multi-row result set returned by a stored procedure can be received by a client program such as php, but it is impossible to receive the result set of another stored procedure in one stored procedure, the general solution is to store a temporary table for sharing in other processes.

4. prepare statement

The following describes the cursor and prepare

Cursor

Definition

 
 

  
  
DECLARE cursor_name CURSOR FOR SELECT_statement; 
 
 

Cursor operation

OPEN cursor

 
 

  
  
OPEN cursor_name; 
 
 

FETCH: obtains the record of the current pointer of the cursor and sends it to the specified Variable list. Note that the number of variables must be the same as the number of fields returned by the MySQL cursor. To obtain multiple rows of data, run the FETCH statement in a loop.

 
 

  
  
FETCH cursor_name INTO variable list; 
 
 

CLOSE cursor

 
 

  
  
CLOSE cursor_name ; 
 
 

Note: The MySQL cursor is read-only, that is, you can only read the result set sequentially from the beginning to the end, not from the back to the front, or directly jump to the middle of the record.

A complete example:

Define local variables

 
 

  
  
DECLARE o varchar(128); 
 
 

Define a cursor

 
 

  
  
DECLARE ordernumbers CURSOR  
  
  
FOR  
  
  
SELECT callee_name FROM account_tbl where acct_timeduration=10800;  
  
  
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_departments=1;  
  
  
SET no_more_departments=0;  
 
 

Open cursor

 
 

  
  
OPEN ordernumbers; 
 
 

Loop all rows

 
 

  
  
REPEAT  
  
  
-- Get order number  
  
  
FETCH ordernumbers INTO o;  
  
  
update account set allMoneyallMoney=allMoney+72,lastMonthConsumelastMonthConsume=lastMonthConsume-72 where NumTg=@o;  
 
 

Loop ends

 
 

  
  
UNTIL no_more_departments  
  
  
END REPEAT; 
 
 

Close cursor

 
 

  
  
CLOSE ordernumbers; 
 
 

The above content is an introduction to the notes for using MySQL cursors. I hope you will have some gains.