An SQL retrieval operation returns a result set, and there is no way to get the first row, the next row, or the first 10 rows if you simply use a SELECT statement. Sometimes, you need to move one or more rows forward or backward in the retrieved row, and that’s where the cursor comes in. A cursor is a database query stored on a DBMS server. It is not a SELECT statement, but a result set retrieved by the statement. Once the cursor is stored, the application can scroll through or browse the data as needed.

Using the cursor

Cursor use steps:

  • Before using a cursor, you must declare (define) it. This procedure doesn’t actually retrieve the data, it just defines what to useSELECTStatement and cursor options.
  • Once declared, the cursor must be opened for use. The process is defined earlierSELECTThe statement actually retrieves the data.
  • For cursors filled with data, fetch (retrieve) rows as needed.
  • When you end a cursor use, you must close the cursor or, if possible, release it.

After the cursor is declared, it can be opened or closed as often as necessary. While the cursor is open, fetches can be performed as often as needed.

Note: Unlike most DBMSS, MySQL cursors can only be used for stored procedures (and functions).


Create a cursor

DECLEAR is used to create the cursor, DECLARE names the cursor, and define the corresponding SELECT statement with WHERE and other clauses as needed.

The following statement defines a cursor named OrderNumbers, using a SELECT statement that retrieves all orders.

Information from Order table:

DROP PROCEDURE IF EXISTS processorder;
CREATE PROCEDURE processorder()
BEGIN
	Define the cursor
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
END;
Copy the code

In this stored procedure, the DECLARE statement is used to define and name cursors. After the stored procedure processing is complete, the cursor disappears (because it is limited to the stored procedure).


Use cursor data

The OPEN statement is used to OPEN the cursor, the CLOSE statement is used to CLOSE the cursor, and after a cursor is opened, each row of it can be accessed separately using the FETCH statement. FETCH specifies the data to retrieve (required columns), and the location where the data is stored (defined variables). It also moves the internal row pointer in the cursor forward so that the next FETCH statement retrieves the next row (not reading the same row twice).

DROP PROCEDURE IF EXISTS processorder;
CREATE PROCEDURE processorder()
BEGIN
    Define local variables
    DECLARE num INT;
    Define the cursor
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
    -- Open the cursor
    OPEN ordernumbers;
    Get the first row of data
    FETCH ordernumbers INTO num;
    -- Query result
    SELECT num;
    -- Close the cursor
    CLOSE ordernumbers;
END;
CALL processorder();
Copy the code

Where FETCH is used to retrieve the order_num column of the current row (which will automatically start from the first row) into a local variable named num, and to query the result of num. Since only the first row is retrieved, the value of num is ‘20005’.

Next, the data is retrieved in a loop, from the first row to the last.

DROP PROCEDURE IF EXISTS processorder;
CREATE PROCEDURE processorder()
BEGIN
	Define local variables
	DECLARE done BOOLEAN DEFAULT false;
	DECLARE num INT;
	Define the cursor
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
	-- Define the CONTINUE HANDLER
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=true;
	-- Open the cursor
	OPEN ordernumbers;
	Loop through all rows
	REPEAT
		Get the first row of data
		FETCH ordernumbers INTO num;
	-- End loop
	UNTIL done END REPEAT;
	-- Query result
	SELECT num;
	-- Close the cursor
	CLOSE ordernumbers;
END;
CALL processorder();
Copy the code

We loop through all rows of the result set, so the value of num is the last row of data.

The difference from the previous example is that the FETCH in this example is inside the REPEAT, so it executes repeatedly until done is true.

DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET done=true;

This statement defines a CONTINUE HANDLER, which is the code to execute when a condition occurs. Here, it says SET done=true when SQLSTATE ‘02000’ appears. SQLSTATE ‘02000’ is an unfound condition that occurs when the REPEAT cannot continue because there are no more rows to loop through.


Advantages and disadvantages of cursors

Advantages: Cursors are a bridge between collective-oriented and row-oriented design ideas. Because cursors operate on rows, they can perform separate, independent operations on each row obtained from a SELECT query in the database, a separate idea. A particular operation can be performed on a result row. Such as the ability to add, delete, change and check based on cursor position.

Disadvantages:

  • The slower
  • Deadlocks can occur
  • Memory is big

For those who are new to Python or want to get started with Python, you can follow the public account “Python New Horizons” to communicate and learn with others. They are all beginners. Sometimes a simple question is stuck for a long time, but others may suddenly realize it with a little help. There are also nearly 1,000 resume templates and hundreds of e-books waiting for you to collect!