login

Subscribe to MySQL Tutorial website RSS Feed
 » MySQL Stored Procedure » SQL Cursor in Stored Procedures

SQL Cursor in Stored Procedures

MySQL supported cursor in stored procedures, functions and triggers. Cursor is used to iterate through a set of rows, which returned by a query, and process individual row. Currently with all versions greater 5.x, MySQL cursor has following properties:

  • Read only: it means you cannot update the cursor.
  • Non-scrollable: it only can traverse in one direction and cannot skip, move back or forth in result set.
  • Asensitive: you should avoid update table while open a cursor on that table otherwise you may get unexpected results.

MySQL supports following statements for working with cursor.

First you have to declare a cursor using DECLARE statement:

 DECLARE cursor_name CURSOR FOR SELECT_statement;

Second you have to open the cursor using OPEN statement. You must open cursor before fetching rows from it.

 OPEN cursor_name;

Next you can retrieve next row from cursor and move the cursor to the following row in a result set by using FETCH statement.

 FETCH cursor_name INTO variable list;

And finally, you must close the cursor to deactivate it and release the memory associated with that cursor. To close the cursor you use CLOSE statement:

CLOSE cursor_name;

One of the most important point when working with cursor is you should use a NOT FOUND handler to avoid raising a fatal “no data to fetch” condition.
We use a stored procedure example below to demonstrate cursor.

 

DELIMITER $$
 DROP PROCEDURE IF EXISTS CursorProc$$
 CREATE PROCEDURE CursorProc()
 BEGIN
 DECLARE  no_more_products, quantity_in_stock INT DEFAULT 0;
 DECLARE  prd_code VARCHAR(255);
             DECLARE  cur_product CURSOR FOR 
 SELECT  productCode FROM products;
   DECLARE  CONTINUE HANDLER FOR NOT FOUND 
 SET  no_more_products = 1;
 
 /* for  loggging information */
 CREATE  TABLE infologs (
 Id int(11) NOT NULL AUTO_INCREMENT,
 Msg varchar(255) NOT NULL,
 PRIMARY KEY (Id)
 );
 OPEN  cur_product;
 
 FETCH  cur_product INTO prd_code;
 REPEAT 
 SELECT  quantityInStock INTO quantity_in_stock
 FROM  products
 WHERE  productCode = 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;
 SELECT *  FROM infologs;
 DROP TABLE  infologs;
 END$$
 DELIMITER;

The stored procedure is very simple and can archive the same result by SQL query. We use it only for demonstrating how cursors work.

We use a cursor for products table and loop though the products result set. If the quantity in stock of a product is less than 100, we log it into to a temporary table and after the loop we select all products to print it on screen.

Remember you must declare cursor first and then declare a NOT FOUND handler; otherwise you will get an error.