MySQL Cursor example mysql cursors easy tutorial

Starting with mysqlV5.5, a big change has been 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, not scrolling 3, insensitive

(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, note that it must be closed after it is exhausted.

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

1234567891011121314151617181920

BEGIN DECLAREno_more_record INT DEFAULT 0; DECLAREpID BIGINT(20); DECLAREpValue DECIMAL(15,5); DECLAREcur_record CURSOR FORSELECT colA, colB from tableABC;  /*首先这里对游标进行定义*/ DECLARECONTINUE HANDLER FOR NOT FOUND  SETno_more_record = 1; /*这个是个条件处理,针对NOT FOUND的条件,当没有记录时赋值为1*/ OPENcur_record; /*接着使用OPEN打开游标*/ FETCHcur_record INTO pID, pValue; /*把第一行数据写入变量中,游标也随之指向了记录的第一行*/ WHILE no_more_record != 1 DO INSERTINTO testTable(ID, Value) VALUES(pID, pValue); FETCHcur_record INTO pID, pValue; END WHILE; CLOSEcur_record;  /*用完后记得用CLOSE把资源释放掉*/END

MySQL Cursor example mysql cursors easy tutorial