主页 / dba / 问题 / 13248
Accepted
Mark D
Asked: 2012-02-17 11:00:38 +0800 CST

何时打开 MySQL 游标

  • 772

一个相对简单的问题，但从我的所有研究中我一直无法找到一个像样的答案。

哪个在技术上更正确，在定义处理程序之后或实际使用光标之前打开光标？

例如，

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;

在大多数示例中，上述陈述似乎是标准的，但是可以做到吗

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;
mysql cursors

1 个回答

  1. Best Answer

    我可以看到你的第二个代码在做什么。您只想在满足某些条件时打开 2 个游标，但如果不满足则只打开 1 个游标。恕我直言，应该没问题。

    事实上，《MySQL 存储过程编程》这本书在第 110-111 页的示例 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;

    BEGIN...END请注意代码中光标在块内的打开和关闭。你的代码应该没问题。如果 mysql 抱怨，请将您的 THEN 代码和 ELSE 代码放在BEGIN...END块中。

    • 4

相关问题