Example
Cursors enable you to itterate results of query one by line. DECLARE
command is used to init cursor and associate it with a specific SQL query:
DECLARE student CURSOR FOR SELECT name FROM studend;
Let's say we sell products of some types. We want to count how many products of each type are exists.
Our data:
CREATE TABLE product
(
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
type VARCHAR(50) NOT NULL,
name VARCHAR(255) NOT NULL
);
CREATE TABLE product_type
(
name VARCHAR(50) NOT NULL PRIMARY KEY
);
CREATE TABLE product_type_count
(
type VARCHAR(50) NOT NULL PRIMARY KEY,
count INT(10) UNSIGNED NOT NULL DEFAULT 0
);
INSERT INTO product_type (name) VALUES
('dress'),
('food');
INSERT INTO product (type, name) VALUES
('dress', 'T-shirt'),
('dress', 'Trousers'),
('food', 'Apple'),
('food', 'Tomatoes'),
('food', 'Meat');
We may achieve the goal using stored procedure with using cursor:
DELIMITER //
DROP PROCEDURE IF EXISTS product_count;
CREATE PROCEDURE product_count()
BEGIN
DECLARE p_type VARCHAR(255);
DECLARE p_count INT(10) UNSIGNED;
DECLARE done INT DEFAULT 0;
DECLARE product CURSOR FOR
SELECT
type,
COUNT(*)
FROM product
GROUP BY type;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
TRUNCATE product_type;
OPEN product;
REPEAT
FETCH product
INTO p_type, p_count;
IF NOT done
THEN
INSERT INTO product_type_count
SET
type = p_type,
count = p_count;
END IF;
UNTIL done
END REPEAT;
CLOSE product;
END //
DELIMITER ;
When you may call procedure with:
CALL product_count();
Result would be in product_type_count table:
type | count
----------------
dress | 2
food | 3
While that is a good example of a CURSOR, notice how the entire body of the procedure can be replaced by just
INSERT INTO product_type_count
(type, count)
SELECT type, COUNT(*)
FROM product
GROUP BY type;
This will run a lot faster.
