MySQL Forums
Forum List  »  Stored Procedures

iteration over resultset rows
Posted by: Sranya Jeerangsapasook
Date: January 29, 2013 01:43PM

I'm newbie for mysql stored procedure writer. This is first stored procedure. And I have a problem is over insert records from fetch cursor. Please recommend me how to fix this issue. Thank you very much.


DELIMITER //
CREATE PROCEDURE setagent(IN startlasthours datetime ,IN endlasthours datetime)
BEGIN
DECLARE vdate date;
DECLARE vstarttime time;
DECLARE vuserfk int;
DECLARE vcountid int;
DECLARE vcounttime int;
DECLARE done INT DEFAULT FALSE;

DECLARE cur CURSOR FOR
SELECT IFNULL(agentfk,0) ,IFNULL(countid,0) ,IFNULL(counttime,0) FROM
FROM logtable
WHERE ((starttime >= startlasthours) AND (starttime <= endlasthours))
GROUP BY userfk
ORDER BY userfk DESC;

# exit_loop to true, if there are no more rows to iterate
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

SET vdate = DATE(SUBSTRING(startlasthours,1,10));
SET vstarttime = TIME(SUBSTRING(startlasthours,13,8));

OPEN cur;
read_loop: LOOP
FETCH cur INTO vuserfk ,vcountid ,vcounttime;

INSERT INTO rp_hagent(date ,starttime ,userfk ,countid ,counttime)
VALUES (vdate ,vstarttime ,vuserfk ,vcountid ,SEC_TO_TIME(vcounttime));
IF done THEN
LEAVE read_loop;
END IF;
END LOOP;
CLOSE cur;


END //
DELIMITER ;

Options: ReplyQuote


Subject
Views
Written By
Posted
iteration over resultset rows
6318
January 29, 2013 01:43PM
1298
January 29, 2013 06:54PM
1483
January 29, 2013 09:46PM
1178
January 29, 2013 10:57PM
1624
January 30, 2013 06:58AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.