Cursors in mysql

Cursors in mysql

A cursor is used to manipulate  set of result-sets returned by mysql query  one by one. With the use of cursor we can perform operations on set of result set on each returned row. So with the use of cursor you can loops through the results.Cursor is convenient to use when you are performing on a complex result-set.Cursor can be used inside the stored procedures, functions and triggers.

Main purpose of a cursor is when you want to perform operation in multiple tables for each row with the results of a query operations.Another reason to use cursor is to use when there is some steps in process are optional and you want to perform those steps on certain rows of query. so with cursor you can fetch the result set and then perform the additional processing only on the rows that require it.Latest Version of MySQL 5 and greater are support cursor.

LEAVE statement at any time can be used to stop the processing. You may be looking for only one or a limited number of candidate records in the result set, or you may have detected some other condition suggesting that further processing is unnecessary. 

Cursor statements must occur in the sequence OPEN-FETCH-CLOSE. Any variation on
this sequence will result in runtime errors.

Defining a Cursor

Define a cursor with the DECLARE statement, which has the following syntax:
DECLARE cursor_name CURSOR FOR SELECT_statement; 

Cursor declarations must occur after all of our variable declarations. Declaring a cursor before declaring our variables generates error 

DECLARE cursor1 CURSOR FOR
SELECT customer_name, contact_surname,contact_firstname
FROM customers;

Cursor definition including a stored procedure variable 

CREATE PROCEDURE cursor_demo (in_customer_id INT)
BEGIN
DECLARE v_customer_id INT;
DECLARE v_customer_name VARCHAR(30);
DECLARE c1 CURSOR FOR 

SELECT in_customer_id,customer_name
FROM customers
WHERE customer_id=in_customer_id;

Cursor Statements

The MySQL stored program language supports three statements for performing
operations on cursors: 

OPEN 
Initializes the result set for the cursor. We must open a cursor before fetching
any rows from that cursor. The syntax for the OPEN statement is very simple:
OPEN cursor_name; 

FETCH 
Retrieves the next row from the cursor and moves the cursor “pointer” to the
following row in the result set. It has the following syntax:
FETCH cursor_name INTO variable list; 

The variable list must contain one variable of a compatible data type for each
column returned by the SELECT statement contained in the cursor declaration.
We’ll discuss FETCH in more detail later in this chapter. 

CLOSE 
Deactivates the cursor and releases the memory associated with that cursor. The
syntax for this statement is:
CLOSE cursor_name; 

We should close a cursor when we have finished fetching from it, or when we
need to open that cursor again after changing a variable that affects the cursor’s
result set.

Cursor can be created inside the stored procedures, functions and triggers.

Cursors have these properties:
Asensitive: The server may or may not make a copy of its result table
Read only: Not updatable
Nonscrollable: Can be traversed only in one direction and cannot skip rows 

DELIMITER $
CREATE DEFINER=`root`@`localhost` PROCEDURE `cursor_sample`()
READS SQL DATA
BEGIN
DECLARE last_row INT DEFAULT 0;
DECLARE depth_first_name VARCHAR(40);
DECLARE depth_shopper_id INT;
DECLARE c_depth CURSOR FOR
SELECT shopper_id, first_name from shopper_table;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET last_row =1;
OPEN c_depth;
cursor_loop:LOOP
FETCH c_depth INTO depth_shopper_id , depth_first_name;

IF (last_row=1) THEN
LEAVE cursor_loop;
END IF;
IF (depth_shopper_id >5) THEN
SELECT depth_shopper_id , depth_first_name ;
END IF;

END LOOP cursor_loop;
CLOSE c_depth;
END


CALL cursor_sample(); 

To handle a SELECT statement that returns more than one row, we must create and
then manipulate a cursor. A cursor is an object that provides programmatic access to
the result set returned by your SELECT statement. Use a cursor to iterate through the
rows in the result set and take action for each row individually.
Currently, MySQL only allows us to fetch each row in the result set from first to last
as determined by the SELECT statement. We cannot fetch from the last to first row,
and cannot jump directly to a specific row in the result set. 
----------------------------------------------------------------------------------------



CREATE PROCEDURE curdemo( )
BEGIN
DECLARE done INT DEFAULT ( ) ;
DECLARE a CHAR(16);
DECLARE b,c INT;
DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur1;
OPEN cur2;

REPEAT

FETCH cur1 INTO a, b;
FETCH cur2 INTO c;
IF NOT done THEN
IF b < c THEN
INSERT INTO test.t3 VALUES (a,b);
ELSE
INSERT INTO test.t3 VALUES (a,c);
END IF;
END IF;

UNTIL done END REPEAT;

CLOSE cur1;
CLOSE cur2;

END

-----------------------------------------------------------------------------------------------

delimiter $
 DROP PROCEDURE IF EXISTS sp_populate$
 CREATE PROCEDURE sp_populate()

 BEGIN

DECLARE iUserId INTEGER (11) UNSIGNED;
DECLARE iLastXp INTEGER (11) UNSIGNED;
DECLARE iLastRank INTEGER (11) UNSIGNED;
DECLARE iInitXp INTEGER (11) UNSIGNED;
DECLARE iInitRank INTEGER (11) UNSIGNED;
DECLARE iDone INTEGER (11) UNSIGNED;

DECLARE iVal01 INTEGER (11) UNSIGNED;
DECLARE iVal02 INTEGER (11) UNSIGNED;

-- this cursor returns all user ids that do not have a valid entry for their profile foreign key.

DECLARE cUserIterator CURSOR FOR
SELECT userId FROM `User`;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET iDone = 1;
 -- Step 01: Creating empty profile sets for users that do not yet have a profile

SET iDone = 0;
OPEN cUserIterator;

UserIterator: LOOP

FETCH cUserIterator INTO iUserId;
IF 1 = iDone THEN
LEAVE lUserIterator;

END IF;

SELECT rank, xp INTO iLastRank, iLastXp FROM UserTotalStat WHERE updateDate = '2010-01-02' AND userId = iUserId;

SELECT rank, xp INTO iInitRank, iInitXp FROM UserTotalStat WHERE updateDate = '2010-01-01' AND userId = iUserId;

SET iVal01 = ABS(iLastRank - iInitRank);
SET iVal02 = ABS(iLastXp - iInitXp);
IF iVal02 IS NOT NULL THEN
INSERT INTO `UserWeekStat` (`userId`, `rank` , `xp`) VALUES (iUserId, iVal01, iVal02);
END IF;

END LOOP lUserIterator;
CLOSE cUserIterator;

END$
delimiter ;

call sp_populate();
----------------------------------------------------------------------------------------

USE `hyppoaddb`;
DROP procedure IF EXISTS `get_adclicks`;

DELIMITER $
USE `hyppoaddb`$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_adclicks`(IN _id_city int(10),IN lmt tinyint(3))

BEGIN
DECLARE last_row INT DEFAULT 0;
DECLARE _idclicks bigint(20);
DECLARE _orgid bigint(20);
DECLARE _org_code VARCHAR(45);
DECLARE _adimg VARCHAR(100);
DECLARE _link_adrs VARCHAR(100);
DECLARE _detail VARCHAR(150);

DECLARE c_depth CURSOR FOR

SELECT idclicks ,orgid,org_code,adimg,link_adrs,detail from hy_clicks
where id_city=_id_city and isactive=1 and sdate<= now() and edate>= now()
order by no_views
limit lmt;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET last_row =1;

CREATE TEMPORARY TABLE temp_table (_tidclicks bigint(20),_torgid bigint(20),_torg_code VARCHAR(45),_tadimg VARCHAR(100),_tlink_adrs VARCHAR(100),_tdetail VARCHAR(150));

OPEN c_depth;
cursor_loop:LOOP
FETCH c_depth INTO _idclicks ,_orgid,_org_code,_adimg,_link_adrs,_detail ;

IF (last_row=1) THEN
LEAVE cursor_loop;
END IF;

insert into temp_table
SELECT _idclicks ,_orgid,_org_code,_adimg,_link_adrs,_detail ;
call update_no_adviews(_idclicks);
END LOOP cursor_loop;

CLOSE c_depth;
select * from temp_table;
drop table temp_table;
END$

DELIMITER ;