Mysql – When to open your MySQL cursor

cursorsMySQL

A relatively simple question but from all my research I have been unable to find a decent answer.

Which is technically more correct, open your cursor after your handlers have been defined or before you actually use your cursor?

For example, 

BEGIN
  DECLARE done INT DEFAULT FALSE;
  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;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur1;
  OPEN cur2;

  read_loop: LOOP
    FETCH cur1 INTO a, b;
    FETCH cur2 INTO c;
    IF done THEN
      LEAVE read_loop;
    END IF;
    IF b < c THEN
      INSERT INTO test.t3 VALUES (a,b);
    ELSE
      INSERT INTO test.t3 VALUES (a,c);
    END IF;
  END LOOP;

  CLOSE cur1;
  CLOSE cur2;
END;

The above statements seem to be standard in most examples however would it be possible to do

BEGIN
  DECLARE done INT DEFAULT FALSE;
  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;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  SELECT x,y,z INTO @X, @Y, @Z FROM TableA WHERE X=1;

  IF (@X >= @Y) THEN

      OPEN cur1;
      OPEN cur2;

      read_loop: LOOP
        FETCH cur1 INTO a, b;
        FETCH cur2 INTO c;
        IF done THEN
          LEAVE read_loop;
        END IF;
        IF b < c THEN
          INSERT INTO test.t3 VALUES (a,b);
        ELSE
          INSERT INTO test.t3 VALUES (a,c);
        END IF;
      END LOOP;

      CLOSE cur1;
      CLOSE cur2;
  ELSE 
      OPEN cur1;

      read_loop: LOOP
        FETCH cur1 INTO a, b;
        IF done THEN
          LEAVE read_loop;
        END IF;
          INSERT INTO test.t3 VALUES (a,b);
      END LOOP;

      CLOSE cur1;
  END IF;
END;

Best Answer

I can see what your second code is doing. You want to open 2 cursors only if certain conditions are met, but only 1 cursor if not met. IMHO that should be fine.

In fact, the book MySQL Stored Procedure Programming has this sample code on pages 110-111 under Example 5-17

DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_customer=1;
SET l_last_customer=0;
OPEN customer_csr;
cust_loop:LOOP
    FETCH customer_crs INTO l_customer_id;
    IF l_last_customer=1 THEN LEAVE cust_loop; END IF;
    SET l_customer_count=l_customer_count+1;
    sales_block: BEGIN
        DECLARE l_last_sale INT DEFAULT 0;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_sale=1;
        OPEN sales_csr;
        sales_loop:LOOP
            FETCH sales_csr;
            IF l_last_sale=1 THEN LEAVE sales_look; END IF;
            CALL check_sale(l_sales_id);
            SET l_sales_count=l_sales_count+1
        END LOOP sales_loop;
    END sales_block;
END LOOP cust_loop;
SET l_last_customer=0;
CLOSE customer_csr;

Notice in the code the opening and closing of a cursor inside a BEGIN...END block. You should be OK with your code. If mysql complains, place your THEN code and ELSE code within BEGIN...END blocks.

Related Question