Mysql cursor example mysql cursor simple tutorial, mysql cursor example tutorial

Mysql cursor example mysql cursor simple tutorial, mysql cursor example tutorial

From mysql V5.5, a major change was made, that is, InnoDB was used as the default storage engine. InnoDB supports transactions and has the relevant RDBMS features: ACID transaction support, data integrity (supports foreign keys), and disaster recovery capabilities.

Now, let's briefly summarize the cursor knowledge.
(1) know cursor)

It is a readable identifier used to identify where data is obtained.

(2). cursor features

1. Read-Only
2. Do not scroll
3. insensitive

(3) Use a cursor

It should be emphasized that the cursor must be defined before the definition of the processing program, but the variable must be defined before the definition of the cursor. The sequence is variable definition-cursor definition-processing program.

1. Define a cursor

DECLARE cursor_name cursor for select_statement
This statement declares a cursor. You can also define multiple cursors in a subroutine. Each cursor in a block must be named Unique. After the cursor is declared, it is also a single operation.

2. OPEN cursor

OPEN cursor_name
This statement opens the previously declared cursor.

3. cursor FETCH

FETCH cursor_name INTO var_name [, var_name]...
This statement uses the specified open cursor to read the next row (if there is a next row) and forward the cursor to this row.

4. CLOSE the cursor

CLOSE cursor_name
This statement closes the previously opened cursor. Note that it must be closed after use.

(4) For example, the following is a stored procedure in which the cursor is used to update data one by one (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 */declare continue handler for not found set no_more_record = 1;/* this is a condition processing for not found, if no record exists, the value is 1 */OPEN cur_record;/* then OPEN the cursor with OPEN */FETCH cur_record INTO pID, pValue;/* write the first row of data INTO the variable, the cursor also points to the first line of the record */WHILE no_more_record! = 1 do insert into testTable (ID, Value) VALUES (pID, pValue); FETCH cur_record INTO pID, pValue; end while; CLOSE cur_record; /* Remember to use CLOSE to release the resource after it is used up */END