In this template, there is no loop control variable (like "done") and no label (like "MyLabel:"). For the "my cursor traversal code is not looking good" type of freaks :)

Note : Template is the convertion of the sample given in "Cursors" topic of MySQL manual,

Beware :

1. There is a secondary BEGIN .. END envelop between OPEN cursor and CLOSE cursor.

2. NOT FOUND handler moved to the inner BEGIN .. END and its action is EXIT !

CREATE PROCEDURE curdemo()
BEGIN
  DECLARE a CHAR(16);
  DECLARE b,c INT;
  DECLARE cur1 CURSOR FOR SELECT id,DATA FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
 
  OPEN cur1;
  OPEN cur2;
 
  BEGIN
    DECLARE EXIT HANDLER FOR NOT FOUND BEGIN END;
    LOOP
      FETCH cur1 INTO a, b;
      FETCH cur2 INTO c;
      IF b < c THEN
        INSERT INTO test.t3 VALUES (a,b);
      ELSE
        INSERT INTO test.t3 VALUES (a,c);
      END IF;
    END LOOP;
  END;
 
  CLOSE cur1;
  CLOSE cur2;
END
 

You must be logged in to tag this tool

No Comments yet