What is the cursor of mysql stored procedure?

In the mysql stored procedure, the cursor is also called the cursor. It is a database query stored on the DBMS server. It is a set of results returned by the retrieval operation. It is generally used to move forward or backward the retrieved data. operate.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

In MySQL, queries in stored procedures or functions sometimes return multiple records, and using a simple SELECT statement, there is no way to get the first row, next row, or first ten rows of data. In this case, you can Use a cursor to read records from the query result set one by one. Cursors are also called cursors in some materials.

Cursor Introduction

1. Cursor: Also called cursor, it is a database query stored on the DBMS server. It is not a select statement, but the result set retrieved by the statement.

2. Purpose: To perform forward or backward operations on the retrieved data, mainly used for interactive applications, such as users scrolling data on the screen

3. Features:

• Be able to mark the cursor as read-only, so that the data can be read, but cannot be updated and deleted.
• Can control the directional operations that can be performed (forward, backward, first, last, absolute position, Relative position, etc.)
• Can mark some columns as editable and some as non-editable
• Specify the range so that the cursor can respond to the specific request that created it (such as a stored procedure) or Accessible to all requests
• It is just that the DBMS copies the retrieved data (rather than pointing out the active data in the table) so that the data does not change during the cursor opening and access period

4 , DBMS that supports cursors: DB2, MariaDB, MySQL 5, SQL Server, SQLite, Oracle and PostgreSQL, while Microsoft Access does not support

5. Cursors are not very useful for Web-based applications (ASP, ASP.NET , ColdFusion, PHP, Python, Ruby, JSP, etc.), most web application developers do not use cursors

6. Use:

• Declare the cursor: DECLARE cursor_name CURSOR FOR SELECT * FROM table_name; // No data has been retrieved yet

-- MySQL游标的声明
DECLARE cursor_name CURSOR FOR select_statement

-- SQL Server游标的声明
DECLARE cursor_name CURSOR FOR select_statement [FOR [READ ONLY | UPDATE {[co lumn_list]}]]

-- Oracle游标的声明
DECLARE CORSOR cursor_name IS {select_statement}

• Open cursor: OPEN cursor_name; // Start retrieving data, that is, the SELECT statement of the specified cursor is executed, and the query result set is saved specific area in memory.

-- MySQL打开游标
OPEN cursor_name


-- SQL Server打开游标
OPEN cursor_name


-- Oracle打开游标
OPEN cursor_name [param1 [, param2]]

• Get data: FETCH cursor_name into var1, var2,...,varn; // After the cursor cursor_name retrieves the data, the end will only be triggered after the next fetch. Flag

-- MySQL游标获取数据
FETCH cursor_name INTO var1_name [, var2_name] ...


-- SQL Server游标获取数据
FETCH NEXT FROM cursor_name [INTO fetch_list]


-- Oracle游标获取数据
FETCH cursor_name  {INTO : host_var1 [[INDICATOR] : indicator_var1] [, : host_var2 [[INDICATOR] : indicator_var2]] | USING DESCRIPTOR DESCRIPTOR}

• Close cursor: CLOSE cursor_name;

-- MySQL关闭游标，会主动释放资源，所以不需要DEALLOCATE语句
CLOSE cursor_name


-- SQL Server关闭游标和释放资源
CLOSE cursor_name
DEALLOCATE cursor_name


-- Oracle关闭游标，会主动释放资源，所以不需要DEALLOCATE语句
CLOSE cursor_name

[Related recommendations: mysql video tutorial]

The above is the detailed content of What is the cursor of mysql stored procedure?. For more information, please follow other related articles on the PHP Chinese website!