Tutorial on creating and opening and closing cursors in MySQL

mysql creates a cursor

The cursor is created using the DECLARE statement. DECLARE names the cursor and defines the corresponding SELECT statement, with WHERE and other clauses as needed. For example, the following statement defines a cursor named ordernumbers, using a SELECT statement that retrieves all orders.

Input:

create procedure processorders()
begin
declare ordernumbers cursor for select order_num from orders;
end;

Analysis: This stored procedure does not do much. The DECLARE statement is used to define and name the cursor, here it is ordernumbers. After the stored procedure processing is completed, the cursor disappears (because it is limited to the stored procedure). After defining the cursor, you can open it.

mysql opens and closes cursors

The cursor is opened with the OPEN CURSOR statement:

Input:

open ordernumbers;

Analysis: In processing The query is executed when the OPEN statement is used, and the retrieved data is stored for browsing and scrolling.

After the cursor processing is completed, the following statement should be used to close the cursor:

Input:

close ordernumbers;

Analysis: CLOSE releases all internal memory and resources used by the cursor, so in each Cursors 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.

Implicit closing If you do not close the cursor explicitly, MySQL will automatically close it when the END statement is reached.

The following is a modified version of the previous example:

Input:

create procedure processorders()
BEGIN
-- declare the cursor
declare ordernumbers cursor for
select order_num from orders;
-- Open the cursor
Open ordernumbers;
-- close the cursor
close ordernumbers;
END;

Analysis: This stored procedure declares, opens and closes a cursor. But nothing is done with the retrieved data.

The above is the detailed content of Tutorial on creating and opening and closing cursors in MySQL. For more information, please follow other related articles on the PHP Chinese website!