mysql advanced (3) cursor simple tutorial

Mysql Cursor Simple Tutorial

Starting from MySQL V5.5, a big change has been made, which is to use InnoDB as the default storage engine. InnoDB supports transactions and has related RDBMS features: ACID transaction support, data integrity (supports foreign keys), disaster recovery capabilities, etc.

Now briefly summarize the knowledge of cursors.

(1) Understand the cursor

The cursor is simply the queried data index. Through the operation of the cursor (the first position, the last position, the previous position, The next position) can be traversed out of the data.

One of the main reasons for using a cursor is to convert collection operations into single record processing. After retrieving data from the database using SQL language, the result is placed in an area of ​​memory, and the result is often a collection containing multiple records. The cursor mechanism allows users to access these records row by row in SQL server and display and process these records according to the user's own wishes.

In the database, cursor is a very important concept. Cursors provide a flexible means of operating on data retrieved from a table. In essence, cursors are actually a mechanism that can extract one record at a time from a result set that includes multiple data records. A cursor is always associated with a SQL select statement because a cursor consists of a result set (which can be zero, one, or multiple records retrieved by the associated select statement) and a cursor position in the result set that points to a specific record. When you decide to process a result set, you must declare a cursor pointing to the result set. If you have ever written a program that processes files in C language, then the cursor is like the file handle you get when you open the file. As long as the file is opened successfully, the file handle can represent the file. For cursors, the logic is the same. It can be seen that the cursor can process the result set from the basic table in a manner similar to that of a traditional program reading a flat file, thereby presenting the data in the table to the program in the form of a flat file.

We know that relational database management systems are essentially set-oriented. In MS SQL SERVER, there is no expression form to describe a single record in a table, unless a where clause is used to limit only one record to be selected. Therefore, we must use cursors to perform data processing for single records. It can be seen that the cursor allows the application to perform the same or different operations on each row in the row result set returned by the query statement select, instead of performing the same operation on the entire result set at once; it also provides the ability to perform the same operation on the data in the table based on the cursor position. The ability to delete or update; moreover, it is the cursor that connects the collection-oriented database management system and row-oriented programming, allowing the two data processing methods to communicate.

During the database development process, when the data you retrieve is only one record, the transaction statement code you write often uses the SELECT INSERT statement. But we often encounter situations where we read records one by one from a certain result set. So how to solve this problem? Cursors provide us with an extremely excellent solution - that is, using the cursor

is a readable identifier used to identify where the data has been retrieved.

(2) Cursor characteristics

1, read-only

2, non-scrolling

3, insensitive

(three ) Using cursors

It should be emphasized that the cursor must be defined before the handler is defined, but the variable must be defined before the cursor is defined. The order is variable definition-cursor definition-handler.

1. Define a cursor

DECLARE cursor_name CURSOR FOR select_statement

This statement declares a cursor. Multiple cursors can also be defined in a subroutine. Each cursor in a block must be named uniquely. After declaring the cursor, it 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 (if there is a next line) with the specified open cursor, and advances the cursor pointer to The bank.

4. 游标CLOSE

CLOSE cursor_name

这个语句关闭先前打开的游标，注意，用完后必须关闭。

（四）示例

下面是一个存储过程，里面用到游标，逐条更新数据（批量更新数据）

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;  /*首先这里对游标进行定义*/
 DECLARE  CONTINUE HANDLER FOR NOT FOUND  SET  no_more_record = 1; /*这个是个条件处理,针对NOT FOUND的条件,当没有记录时赋值为1*/
 
 
 OPEN  cur_record; /*接着使用OPEN打开游标*/
 FETCH  cur_record INTO pID, pValue; /*把第一行数据写入变量中,游标也随之指向了记录的第一行*/
 
 
 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;  /*用完后记得用CLOSE把资源释放掉*/
END

以上就是 mysql进阶(三)游标简易教程的内容，更多相关内容请关注PHP中文网（www.php.cn）！