Using Cursor in MySQL stored procedure

[Pat Bremkamp]

This morning, I wrote my first ever MySQL stored procedure, and it almost works! Not as hard as I expected, but I'm having trouble with the cursor.

My client has a number of sites and each site has one or more tanks. What I'm trying to do is input a site id, a start date and an end date and insert a daily record for each tank for each day. I'm using a cursor to iterate through the tanks, and the problem is, if the site has tanks A,B,C, I'm getting the records for B and C, but it is skipping A.

I'm going to show the whole procedure because I think it will help others to see how reasonable it is to write a stored procedure. I think the trouble is with the "FETCH"???

Any SQL experts out there see the problem?
The site id is p_site, start date is p_start and end date is p_end

 
BEGIN
DECLARE v_tkgpid CHAR(1);
DECLARE v_site_count INT UNSIGNED DEFAULT 0;
DECLARE v_date DATE;
DECLARE v_alltksdone TINYINT DEFAULT 0;
DECLARE tg_cursor CURSOR FOR SELECT tk_gp_id FROM tkgroups WHERE SITE_ID = p_site;  
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET v_alltksdone = 1;
IF p_site>0 THEN
 SELECT COUNT(site_id) FROM site WHERE site_id = p_site INTO v_site_count;
 IF v_site_count > 0 THEN
 OPEN tg_cursor;
 REPEAT 
  FETCH tg_cursor INTO v_tkgpid;
  IF NOT v_alltksdone THEN
   WHILE v_date <= p_end DO
    INSERT IGNORE INTO readings ( rd_date, rd_site, rd_tankgroup, rd_released ) 
    VALUES ( v_date, p_site, v_tkgpid, 0);
    SET v_date = DATE_ADD(v_date, INTERVAL 1 DAY);
   END WHILE;
  END IF;
  SET v_date = p_start;
 UNTIL v_alltksdone END REPEAT; 
 CLOSE tg_cursor;
 END IF;
END IF;
END

Thanks,
Pat

[frankbicknell]

Re: Using Cursor in MySQL stored procedure

Pat here is an alternative way of doing it. Maybe it will solve your problem. SQL procedures are great I wish I had not waited so long to dive in. I see a night and day difference with speed on grids with only a couple of thousand records. It's amazing.

BEGIN
DECLARE v_tkgpid CHAR(1);
DECLARE v_site_count INT UNSIGNED DEFAULT 0;
DECLARE v_date DATE;
DECLARE v_alltksdone TINYINT DEFAULT 0;
DECLARE tg_cursor CURSOR FOR SELECT tk_gp_id FROM tkgroups WHERE SITE_ID = p_site;  
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET v_alltksdone = 1;
OPEN tg_cursor;
  Input_Site_ID: LOOP
  FETCH tg_cursor INTO v_tkgpid;

  if v_alltksdone then
    LEAVE Input_Site_ID;
   end if;

  IF NOT v_alltksdone THEN
    WHILE v_date <= p_end DO
      INSERT IGNORE INTO readings ( rd_date, rd_site, rd_tankgroup, rd_released ) 
      VALUES ( v_date, p_site, v_tkgpid, 0);
      SET v_date = DATE_ADD(v_date, INTERVAL 1 DAY);
    END WHILE;
  END IF;

END LOOP Input_Site_ID;

 CLOSE tg_cursor;
END

[Pat Bremkamp]

Re: Using Cursor in MySQL stored procedure

Thanks Frank. I'll give it a try.

Yes, stored procedures are the way to go. With a different procedure, I updated 76,000 records in about 2 seconds!

Pat

[Pat Bremkamp]

Re: Using Cursor in MySQL stored procedure

Frank,

That did the trick. It's working now.

Thanks,
Pat