DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`CursorProc` $$
CREATE PROCEDURE `test`.`CursorProc` ()
BEGIN
DECLARE no_more_products, quantity_in_stock INT DEFAULT 0;
DECLARE prd_code VARCHAR(255);
DECLARE cur_product CURSOR FOR SELECT code FROM products; /*First: Delcare a cursor,首先这里对游标进行定义*/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_products = 1; /*when "not found" occur,just continue,这个是个条件处理,针对NOT FOUND的条件*/
/* for loggging information 创建个临时表格来保持*/
CREATE TEMPORARY TABLE infologs (
Id int(11) NOT NULL AUTO_INCREMENT,
Msg varchar(255) NOT NULL,
PRIMARY KEY (Id)
);
OPEN cur_product; /*Second: Open the cursor 接着使用OPEN打开游标*/
FETCH cur_product INTO prd_code; /*Third: now you can Fetch the row 把第一行数据写入变量中,游标也随之指向了记录的第一行*/
REPEAT
SELECT quantity INTO quantity_in_stock
FROM products
WHERE code = prd_code;
IF quantity_in_stock < 100 THEN
INSERT INTO infologs(msg)
VALUES (prd_code);
END IF;
FETCH cur_product INTO prd_code;
UNTIL no_more_products = 1
END REPEAT;
CLOSE cur_product; /*Finally: cursor need be closed 用完后记得用CLOSE把资源释放掉*/
SELECT * FROM infologs;
DROP TABLE infologs;
END $$
DELIMITER ;
