Can cursors be used in mysql functions?

Cursors can be used in mysql functions. In MySQL, cursors can only be used for stored procedures and functions; 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 , then you can use a cursor to read the records in the query result set one by one. Cursors can be used to perform forward or backward operations on retrieved data. They are mainly used in interactive applications, such as users scrolling data on the screen.

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

Cursors can be used in mysql functions.

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.

Use of cursor: forward or backward operations on retrieved data, mainly used for interactive applications, such as users scrolling data on the screen

Characteristics of cursor:

• Be able to mark the cursor as read-only, so that 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 uneditable

• Specify scope to make the cursor accessible to the specific request that created it (such as a stored procedure) or to all requests

• Only the DBMS will access the retrieved data (rather than pointing to the table Active data in the cursor) is copied so that the data does not change during the cursor opening and access period

In MySQL, cursors can only be used for stored procedures and functions.

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. At this time, you can use a cursor to read the records in the query result set one by one. Cursors are also called cursors in some materials.

The relational database management system is essentially set-oriented. In MySQL, there is no expression form to describe a single record in the table, unless a WHERE clause is used to limit only one record to be selected. So sometimes we must use cursors to process data on a single record.

1. Declare the cursor

Use the DECLARE keyword in MySQL to declare the cursor, define the corresponding SELECT statement, and add WHERE and other clauses as needed. The basic form of its syntax is as follows:

DECLARE cursor_name CURSOR FOR select_statement;

Among them, cursor_name represents the name of the cursor; select_statement represents the SELECT statement, which can return one or more rows of data.

Example 1

The following declares a cursor named nameCursor. The code is as follows:

mysql> DELIMITER //
mysql> CREATE PROCEDURE processnames()
    -> BEGIN
    -> DECLARE nameCursor CURSOR
    -> FOR
    -> SELECT name FROM tb_student;
    -> END//
Query OK, 0 rows affected (0.07 sec)

The above statement defines the nameCursor cursor. The cursor is only limited to stored procedures. Stored procedures Once processing is complete, the cursor disappears.

2. Open the cursor

After declaring the cursor, if you want to extract data from the cursor, you must first open the cursor. In MySQL, opening a cursor is achieved through the OPEN keyword, and its syntax is as follows:

OPEN cursor_name;

where cursor_name represents the name of the cursor to be opened. It should be noted that when a cursor is opened, the cursor does not point to the first record, but to the front of the first record.

In the program, a cursor can be opened multiple times. After the user opens the cursor, other users or programs may be updating the data table, so sometimes the results displayed are different every time the user opens the cursor.

3. Using a cursor

After the cursor is successfully opened, you can use the FETCH...INTO statement to read data. The syntax is as follows:

FETCH cursor_name INTO var_name [,var_name]...

In the above statement, save the execution result of the SELECT statement in the cursor cursor_name to the variable parameter var_name. The variable parameter var_name must be defined before the cursor can be used. Using a cursor is similar to array traversal in high-level languages. When the cursor is used for the first time, the cursor points to the first record of the result set.

MySQL's cursor is read-only, that is to say, you can only read the result set sequentially from the beginning to the back, not from the back to the front, nor can you jump directly to the middle record.

4. Close the cursor

After the cursor is used, it must be closed in time. In MySQL, use the CLOSE keyword to close the cursor. The syntax format is as follows:

CLOSE cursor_name;

CLOSE releases all internal memory and resources used by the cursor, so each cursor should be closed when no longer needed.

After a cursor is closed, it cannot be used without reopening it. However, there is no need to declare it again to use a declared cursor, just open it with the OPEN statement.

If you do not close the cursor explicitly, MySQL will automatically close it when the END statement is reached. After the cursor is closed, it cannot be used using FETCH.

[Related recommendations: mysql video tutorial]

The above is the detailed content of Can cursors be used in mysql functions?. For more information, please follow other related articles on the PHP Chinese website!