Stored procedure using cursor in mySql.
- by RAVI
I wrote a stored procedure using cursor in mysql but that procedure is taking 10 second to fetch the result while that result set have only 450 records so, I want to know that why that proedure is taking that much time to fetch tha record.
procedure as below:
DELIMITER //
DROP PROCEDURE IF EXISTS curdemo123//
CREATE PROCEDURE curdemo123(IN Branchcode int,IN vYear int,IN vMonth int)
BEGIN
DECLARE EndOfData,tempamount INT DEFAULT 0;
DECLARE tempagent_code,tempplantype,tempsaledate CHAR(12);
DECLARE tempspot_rate DOUBLE;
DECLARE var1,totalrow INT DEFAULT 1;
DECLARE cur1 CURSOR FOR select SQL_CALC_FOUND_ROWS ad.agentCode,ad.planType,ad.amount,ad.date from adplan_detailstbl ad where ad.branchCode=Branchcode and (ad.date between '2009-12-1' and '2009-12-31')order by ad.NUM_ID asc;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET EndOfData = 1;
DROP TEMPORARY TABLE IF EXISTS temptable;
CREATE TEMPORARY TABLE temptable (agent_code varchar(15), plan_type char(12),sale double,spot_rate double default '0.0', dATE DATE);
OPEN cur1;
SET totalrow=FOUND_ROWS();
while var1 <= totalrow DO
fetch cur1 into tempagent_code,tempplantype,tempamount,tempsaledate;
IF((tempplantype='Unit Plan' OR tempplantype='MIP') OR tempplantype='STUP') then
select spotRate into tempspot_rate from spot_amount where ((monthCode=vMonth and year=vYear) and ((agentCode=tempagent_code and branchCode=Branchcode) and (planType=tempplantype)));
INSERT INTO temptable VALUES(tempagent_code,tempplantype,tempamount,tempspot_rate,tempsaledate);
else
INSERT INTO temptable(agent_code,plan_type,sale,dATE) VALUES(tempagent_code,tempplantype,tempamount,tempsaledate);
END IF;
SET var1=var1+1;
END WHILE;
CLOSE cur1;
select * from temptable;
DROP TABLE temptable;
END //
DELIMITER ;
