MySQL 游标不适用于存储过程

Question

我在mysql中写了存储过程。跟着这个网站的步骤http://www.mysqltutorial.org/mysql-cursor/但它不起作用。这是代码

---

DELIMITER $$

USE `hr`$$

DROP PROCEDURE IF EXISTS `at_getShift`$$

CREATE DEFINER=`root`@`%` PROCEDURE `at_getShift`()

BEGIN

    DECLARE finished BOOLEAN DEFAULT FALSE;
    DECLARE employeeID VARCHAR(255);-- Default "";

    -- declare cursor for employee email
    DECLARE hrEmployee CURSOR FOR SELECT EmployeeID FROM h_employees WHERE EmployeeID IN ('100013', '100014');

    -- declare NOT FOUND handler
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = TRUE;

    DROP TABLE IF EXISTS temp;
    CREATE TABLE IF NOT EXISTS temp(
      `Code` VARCHAR(255)
    );  

    OPEN hrEmployee;

    get_employee: LOOP
    FETCH hrEmployee INTO employeeID;
    INSERT INTO temp(`Code`) VALUE (employeeID);
    -- If no any row, leave loop
    IF finished THEN 
        INSERT INTO temp(`Code`) VALUE ("112");
        CLOSE hrEmployee;
        LEAVE get_employee;
    END IF;

    -- insert temp
    INSERT INTO temp(`Code`) VALUE ("111");
    END LOOP get_employee;

    SELECT * FROM temp;
END$$

DELIMITER ;

---

执行:CALL at_getShift();

结果是:

2 rows in temp table ( 1 null, 1 is 112)

请帮我解决这个问题。

Answer 1

在 MySQL 存储程序的 SQL 语句中，对过程变量的引用优先于对列的引用。

即当SQL语句中的标识符与过程变量匹配时，SQL语句引用过程变量。

使用表名或表别名引用列的限定引用，即使存在同名的过程变量也是如此。

演示:

 CREATE TABLE emp (id INT);
 INSERT INTO emp (id) VALUES (101),(102);

 DELIMITER $$


 CREATE PROCEDURE foo()
 BEGIN
   DECLARE id INT DEFAULT 3;

   -- this query returns 3 for all rows in emp
   -- because "id" is a reference to the procedure variable
   SELECT id FROM emp WHERE id = 3;

   -- this query returns no rows
   -- because "id" is a reference to the procedure variable
   SELECT id FROM emp WHERE id = 101;

   -- this query references columns in the table because
   -- references to "id" are qualified
   SELECT t.id FROM emp t WHERE t.id = 101;

 END$$

 DELIMITER ;


 CALL foo;

第一个查询返回 emp 中所有行的过程变量值

    id
 -----
     3
     3

第二个查询没有返回任何行

    id
 -----

第三个查询返回表中的引用“id”列:

    id
 -----
   101 

---

要点是两个“最佳实践”:

• 在过程中限定 SQL 语句中的所有列引用

和

• 过程变量名应该不同于列名，通常的模式是在变量上使用独特的前缀。举个简单的例子:v_id、v_name 等。

这两种做法都使人类读者更容易破译程序。

过程变量的独特命名确实减少了冲突的可能性，但不会使在 SQL 语句中限定所有列引用的“最佳实践”无效。这两者都有助于使人类读者更加清楚作者的意图。

---

编辑:

我试图回答我认为你在问的问题......“为什么我的程序没有按照我的预期去做？”。

除了您所问问题的答案之外...您的过程似乎正在执行的操作(用一组行填充临时表)该操作可以更快地执行更多高效 通过将行作为一个集合来处理，而不是为每一行发出痛苦低效的单独插入语句。在性能方面，游标循环处理 RBAR(逐行痛苦)会让你吃午饭。还有你的饭盒。

DELIMITER $$

CREATE PROCEDURE `at_getShift_faster`()
BEGIN
   -- ignore warning message when dropping a table that does not exist
   DECLARE CONTINUE HANDLER FOR 1305 BEGIN END;
   DROP TABLE IF EXISTS temp;
   CREATE TABLE IF NOT EXISTS temp(`Code` VARCHAR(255));  

   INSERT INTO temp (`Code`)  
     SELECT h.EmployeeID 
       FROM h_employees h
      WHERE h.EmployeeID IN ('100013', '100014')
   ;

   SELECT * FROM temp;
END$$

DELIMITER ;