Shulou(Shulou.com)05/31 Report--

This article mainly explains "what is the role of cursors in mysql stored procedures". The explanation in this article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what is the role of cursors in mysql stored procedures".

In the mysql stored procedure, the cursor, also known as the cursor, is a database query stored on the DBMS server, where the retrieval operation returns a set of results, which is generally used for forward or backward operations on the retrieved data.

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, but using simple SELECT statements, there is no way to get the first, next, or first ten rows of data, so cursors can be used to read the records in the query result set one by one. Cursors are also called cursors in some data.

Introduction to Vernier

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

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

3. Characteristics:

Can mark cursors as read-only so that data can be read, but cannot be updated or deleted

Can control the directional operations that can be performed (forward, backward, first, last, absolute position, relative position, etc.)

Can mark some lists as editable and some as non-editable

Specify the scope to make the cursor accessible to specific requests (such as stored procedures) that created it or to all requests

It's just that DBMS replicates the retrieved data (rather than pointing out the active data in the table) so that the data does not change during cursor opening and access

4. DBMS:DB2, MariaDB, MySQL 5, SQL Server, SQLite, Oracle and PostgreSQL are supported for cursors, but not for Microsoft Access

5. Cursors are of little use to Web-based applications (ASP, ASP.NET, ColdFusion, PHP, Python, Ruby, JSP, etc.), and most Web application developers do not use cursors

6. Use:

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

-- declaration of MySQL cursors DECLARE cursor_name CURSOR FOR select_statement [FOR [READ ONLY | UPDATE {[co lumn_list]}]]-- declaration of Oracle cursors DECLARE CORSOR cursor_name IS {select_statement}

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

-- MySQL Open cursor OPEN cursor_name-- SQL Server Open cursor OPEN cursor_name-- Oracle Open cursor OPEN cursor_name [param1 [, param2]]

Get data: FETCH cursor_name into var1,var2,...,varn; / / when the cursor cursor_name has retrieved the data, the end flag will not be triggered until the next fetch

-- MySQL cursor acquisition data FETCH cursor_name INTO var1_name [, var2_name]...-- SQL Server cursor acquisition data FETCH NEXT FROM cursor_name [INTO fetch_list]-- Oracle cursor acquisition data FETCH cursor_name {INTO: host_var1 [[INDICATOR]: indicator_var1] [,: host_var2 [[INDICATOR]: indicator_var2]] | USING DESCRIPTOR DESCRIPTOR}

Close cursor: CLOSE cursor_name

-- MySQL closes cursors and actively releases resources, so there is no need for DEALLOCATE statements CLOSE cursor_name-- SQL Server to close cursors and release resources CLOSE cursor_nameDEALLOCATE cursor_name-- Oracle closed cursors will actively release resources, so there is no need for DEALLOCATE statements CLOSE cursor_name thank you for reading, the above is the content of "what is the purpose of cursors of mysql stored procedures", after the study of this article I believe that you have a deeper understanding of the role of cursors in mysql stored procedures, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.