Comparing Cursor and Set Approaches in Processing Relational Data

0
106
4 min read

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.

 

 

LEAVE A REPLY

Please enter your comment!
Please enter your name here