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.