MySQL Forums
Forum List  »  InnoDB

cursor iterating twice over last row
Posted by: Ian Flitman
Date: May 26, 2007 11:41AM

Dear all,

Trying to write a SP that gives me the number of entries for each alternative_id within a given cut_id (this is for an database cinema project). Some cuts have
just one set of alternatives so the cursor should just count how many there are in that one, and then exit with the number. Others have more than one, so the SP gives a cumulative total for all alternative groups found to belong to a cut_id. The trouble is the while loop (it is the same with a repeat loop) repeats the last row each time thus adding the last alternative_id number total twice! Most annoying. Anyone know why? There is a nasty line at the end that 'fixes' it, (it just subtracts the last addition found in altCount from altNumCount i.e. the cumulative total) but I can't see why that should be needed. Anyone any ideas as to why it repeats the last row twice through the loop?

DELIMITER $$

DROP PROCEDURE IF EXISTS `jane`.`getAltNumCount`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `getAltNumCount`(IN alt INT,OUT altNumCount INT)
BEGIN
declare done BOOLEAN DEFAULT 0;
declare altNumCount INT DEFAULT 0;
declare cutNum INT;
declare alternative INT;
declare alt_type varchar(16);
declare altCount INT;


DECLARE more_rows BOOLEAN DEFAULT TRUE;

declare altList CURSOR
FOR
select distinct alternative_id from alternatives where cut_id=cutNum;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET more_rows = FALSE;

select cut_id from alternatives where alternative_id=alt limit 1 into cutNum ;
select cutNum as cutResult;

OPEN altList;


while more_rows do
FETCH altList INTO alternative;
select alternative_type from alternatives where alternative_id=alternative limit 1 into alt_type;
select count(*)into altCount from alternatives where
alternative_id=alternative;

/*ignore the case statement if you like it just makes some number adjustments*/
CASE alt_type
when 'COMPOUND' then
set altCount=altCount-1;
when 'PARENT' then
set altCount=0;
when 'MIDCOMP' then
set altCount=altCount-1;
when 'PAIRCOMP' then
set altCount=altCount-1;
else
set altCount=altCount;
END CASE;

set altNumCount=altNumCount+altCount;

end while;

CLOSE altList;
/*this line is just as a workround for the extra iteration the SP is doing
- really shouldn't be necessary*/
set altNumCount=altNumCount-altCount;
select altNumCount as altNumCountEndMsg;
END$$

DELIMITER ;

Options: ReplyQuote


Subject
Views
Written By
Posted
cursor iterating twice over last row
10295
May 26, 2007 11:41AM


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.