0

I have a procedure which consists of a Cursor within another cursor, but It is giving some error while importing into DB.

    create procedure genData()
    BEGIN
    DECLARE PORT,I,LID INT DEFAULT 0;
    DECLARE temp INT DEFAULT 0;
    declare done,done1 BOOL default FALSE;

    DECLARE cur1 cursor for select distinct LinkID from MAIN_TBL;
    DECLARE cur2 cursor for select Port from TEMP_LINK_TOPPORT;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    open cur1;
    rl: LOOP
            fetch cur1 INTO LID;
            IF done=TRUE THEN
                    LEAVE rl;
            END IF;
            drop table if exists TEMP_LINK_TOPPORT;
            create table if not exists TEMP_LINK_TOPPORT(NoOfBytes INT,Port INT);
            INSERT INTO TEMP_LINK_TOPPORT
                    select sum(T2.NoOfBytes),T1.Port  as P from (select Port,LinkID from LINK_APP_TBL where LinkID=LID) as T1,MAIN_TBL T2 where T1.LinkID=T2.LinkID and (T1.Port=T2.SourcePort or T1.Port=T2.DestPort ) and time_to_sec(timediff(current_timestamp,insertTime))<=240*60*60 group by 2 order by 1 desc limit 5;
            DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = TRUE;
            open cur2;
            rl2:LOOP
                    fetch cur2 INTO PORT;
                    IF done1=TRUE THEN
                            LEAVE rl2;
                    END IF;
                    call temp_scale(LID,PORT);
            END LOOP;
            close cur2;
    END LOOP;
    close cur1;
    END$$

I am getting error for the second continue handler. I have just learnt cursors from web so don't know them clearly.

tshepang
  • 12,111
  • 21
  • 91
  • 136
unbesiegbar
  • 471
  • 2
  • 7
  • 19
  • ERROR 1064 (42000) at line 37: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = TRUE; open cur2; rl2:LOOP' at line 19 – unbesiegbar Aug 08 '13 at 18:23
  • 1
    general tip: if you get an error message, TELL US what that message is. don't make us guess. – Marc B Aug 08 '13 at 18:23
  • Sorry Guys, I have given the error in above comment. – unbesiegbar Aug 08 '13 at 18:24
  • Guys, Please ignore the line numbers because the file have one more procedure above it. If you want the actual line numbers the let me know. I will segregate them and tell you. – unbesiegbar Aug 08 '13 at 18:43
  • 1
    [Refer this](http://stackoverflow.com/questions/9699896/nested-cursors-in-mysql) possible duplication – Akhil Aug 08 '13 at 18:43
  • 1
    @Akil, I added BLOCK:BEGIN and END which worked perfectly. Thanks !! – unbesiegbar Aug 08 '13 at 18:50

0 Answers0