MySQL Cursor example mysql cursors easy tutorial

Starting with MySQL V5.5, a big change was made to InnoDB as the default storage engine. InnoDB supports transactions and has the relevant RDBMS features: ACID transaction support, data integrity (support for foreign keys), disaster recovery capabilities, and more.

Now briefly summarize the cursor knowledge.
(i), Understanding cursors (cursor)

is a readable identifier used to identify where the data is taken.

(ii), cursor characteristics

1, read-only
2, do not scroll
3, non-sensitive

(iii), using cursors

It is important to emphasize that the cursor must be defined before the handler is defined, but the variable must be defined before the cursor is defined, in the order of the variable definition-cursor definition-handler.

1. Defining cursors

DECLARE cursor_name cursor for select_statement
This statement declares a cursor. You can also define multiple cursors in a subroutine, and each cursor in a block must be named unique. Declaring a cursor is also a single operation.

2. Cursor Open

OPEN cursor_name
This statement opens a previously declared cursor.

3. Cursor Fetch

FETCH cursor_name into Var_name [, Var_name] ...
This statement reads the next line with the specified open cursor (if there is a next line), and advances the cursor pointer to the row.

4. Cursor Close

CLOSE cursor_name
This statement closes the previously opened cursor, which must be closed after it is exhausted.

(d) example, here is a stored procedure that uses cursors to update data (batch update data)

BEGIN DECLARE  No_more_record INT DEFAULT 0; DECLARE  PID BIGINT (20); DECLARE  pValue DECIMAL (15,5); DECLARE  Cur_record CURSOR   for SELECT ColA, ColB from TABLEABC;  /* First define the cursor here */DECLARE  CONTINUE HANDLER for not FOUND  SET  no_more_record = 1;/* This is a conditional processing, for the condition of not FOUND, When no record is assigned the value is 1*/open  Cur_record;/followed by open cursor */FETCH  Cur_record into PID, pValue;/*third:now can FETCH the R Ow writes the first row of data to the variable, and the cursor points to the first line of the record */REPEAT INSERT into  testtable (ID, Value) VALUES  (PID, PValue); FETCH  Cur_record into PID, PValue; UNTIL  No_more_record = 1 END REPEAT; CLOSE  Cur_record;  /Remember to use close to release the resources when you're done with */end

MySQL Cursor example mysql cursors easy tutorial