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.
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 ;
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.
I remember deciding to pursue my first IT certification, the CompTIA A+. I had signed…
Key takeaways The transformer architecture has proved to be revolutionary in outperforming the classical RNN…
Once we learn how to deploy an Ubuntu server, how to manage users, and how…
Key-takeaways: Clean code isn’t just a nice thing to have or a luxury in software projects; it's a necessity. If we…
While developing a web application, or setting dynamic pages and meta tags we need to deal with…
Software architecture is one of the most discussed topics in the software industry today, and…