MySQL introduced cursor in its release 5. You create a cursor to define a set of rows from one or more table that you want to access row-by-row sequentially. If your need is really to access the rows one at a time then use cursor. But a lot of data processing is actually set processing, and set processing is what relational database is best for.

This article by Djoni Darmawikarta compares cursor approach and set approach using various examples.

To give you an idea about cursor, the following DECLARE statement creates a cursor (named zero_bal_crs), which gives you access to the rows of a payment table, rows that have 100 or less balances.

DECLARE zero_bal_crs CURSOR FOR
  SELECT * FROM payment WHERE payment_bal < 100

You then fetch and process each of the rows sequentially; the process can be, for example, summing up rows by product group and loading the sums into a summary table. You develop the process outside of the cursor, for example, in a stored procedure. (You’ll see in the examples that the cursor-based process is procedural).

Instead of processing row-by-row sequentially, you can process relational data set-by-set, without a cursor. For example, to sum all payment rows with 100 or less balances and load it into a table (named payment_100orless_bal in the following SQL statement), you can use the following SQL statement. This single SQL statement completely processes the rows that meet the condition, all at once, as a set.

INSERT INTO payment_100orless_bal
  SELECT SUM(payment_bal) FROM payment WHERE payment_bal < 100

The following three examples, which are among those I most frequently encountered, further compare cursor-based processing with set processing.

Example 1: Sequential Loop cf. One SQL Statement

Our process in the first example is to summarize sales transactions by product. Listing 1 shows the DDLs for creating the sales transaction table and sales product table that stores the summary.

Listing 1.1: DDLs of the Example 1 Tables

CREATE TABLE sales_transactions
( product_code INT
, sales_amount DEC(8,2)
, discount INT);

CREATE TABLE sales_product
(product_code INT
, sales_amount DEC(8,2));

Listing 1.2 shows a cursor-based stored procedure that implements the process. In this example, what the cursor (sales_crs) does is simply putting all rows in ascending order by their product codes. As you might have expected, this stored procedure applies loop with if-then-else programming construct to process the data row-by-row.

Listing 1.2 Cursor Procedural solution

DELIMITER $$

DROP PROCEDURE IF EXISTS ex1_cursor $$
USE sales $$
CREATE PROCEDURE ex1_cursor()
BEGIN

DECLARE p INT DEFAULT 0;
DECLARE s DECIMAL(8,2) DEFAULT 0;
DECLARE d INT DEFAULT 0;
DECLARE done INT DEFAULT 0;
DECLARE first_row INT DEFAULT 0;
DECLARE px INT DEFAULT 0;
DECLARE sx DECIMAL(8,2) DEFAULT 0;

DECLARE sales_crs CURSOR FOR
  SELECT * FROM sales_transactions ORDER BY product_code;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN sales_crs;

  REPEAT

          FETCH sales_crs INTO p, s, d;

IF first_row = 0 THEN
          		SET first_row = 1;
          		SET px = p;
         		SET sx = s * (100 - d)/100;
  
ELSEIF NOT done THEN

    		IF p <> px THEN
          			INSERT INTO sales_product VALUES(px, sx);
         	 		SET sx = s * (100 - d)/100;
         	 		SET px = p;

    		ELSE
         			 SET sx = sx + s * (100 - d)/100;
   		
END IF;

  	ELSE INSERT INTO sales_product VALUES(px, sx);

  	END IF;

  UNTIL done END REPEAT;

CLOSE sales_crs;

END $$

DELIMITER ;

The stored procedure in Listing 1.3 implements a set, processing to accomplish the same purpose as its foregoing cursor-based processing. You can see that this stored procedure is simpler than its cursor-based counterpart.

Listing 1.3 Set Operation solution

DELIMITER //

USE sales //

DROP PROCEDURE IF EXISTS ex1_sql //
CREATE PROCEDURE ex1_sql ()
BEGIN

      INSERT INTO sales_product
        	SELECT  product_code
             		 , SUM(sales_amount * (100 - discount)/100)
        	FROM sales_transactions
        	GROUP BY product_code;

END //

DELIMITER ;

Example 2: Nested Cursor cf. Join

Our 2nd example is to consolidate order by customer. While example 1 has one table, example 2 has two: order and item. The DDLs of the two tables are shown in Listing 2.

To process two tables, our cursor implementation uses a nested loop (Listing 2.2) which makes it even more complex than example 1.

Listing 2.1: DDL’s of the Example 2 Tables

CREATE TABLE  order (
  , order_number INT
  , order_date DATE
  , customer_number INT);

CREATE TABLE  item (
  , order_number INT
  , product_code INT
  , quantity INT
  , unit_price DEC(8,2));

Listing 2.2: Nested Cursor

DELIMITER $$

DROP PROCEDURE IF EXISTS ex2_cursor $$
CREATE PROCEDURE  ex2_cursor()
BEGIN

DECLARE so INT;
DECLARE sc INT;
DECLARE io INT;
DECLARE iq INT;
DECLARE iu DEC(10,2);

DECLARE done1 VARCHAR(5) DEFAULT 'START' ;
DECLARE done2 VARCHAR(5) DEFAULT 'START' ;

DECLARE sales_crs CURSOR FOR
 	SELECT customer_number, order_number
  	FROM sales_order
  	ORDER BY customer_number, order_number;

DECLARE order_crs CURSOR FOR
 	 SELECT order_number, quantity, unit_price
 	 FROM item
  	WHERE order_number = so
  	ORDER BY order_number;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = 'END';

OPEN sales_crs;

  WHILE done1 <> 'END' DO
 	FETCH sales_crs INTO sc, so;
  		IF done1 <> 'END' THEN

/* inner cursor									*/
    			OPEN oorder_crs;
      			SET done2 = done1;

       		WHILE done1 <> 'END' DO
          		FETCH order_crs INTO io, iq, iu;
           		IF done1 <> 'END' THEN
          		INSERT INTO customer_order VALUES (sc, iq * iu);
         				
END IF;
END WHILE;

    			CLOSE order_crs;
    		SET done1 = done2;
 		END IF;
  END WHILE;
CLOSE sales_crs;
END $$
DELIMITER ;

Listing 2.3 is the set solution. We apply join in this 2nd example which makes it just a single SQL statement. You can see that this set stored procedure is again simpler than its cursor-based equivalent.