在 MySQL 中循环结果集

Question

我正在尝试在 MySQL 中编写一个存储过程，它将执行一个简单的选择查询，然后循环结果以决定是否执行其他查询、数据转换或完全丢弃数据。实际上，我想实现这个：

$result = mysql_query("SELECT something FROM somewhere WHERE some stuff");
while ($row = mysql_fetch_assoc($result)) {
    // check values of certain fields, decide to perform more queries, or not
    // tack it all into the returning result set
}

只是，我只希望它在 MySQL 中，所以它可以作为过程调用。我知道对于触发器，有 FOR EACH ROW ... 语法，但我找不到在 CREATE TRIGGER ... 之外使用类似的内容。代码>语法。我已经阅读了 MySQL 中的一些循环机制，但到目前为止我所能想象的就是我将实现这样的东西：

SET @S = 1;
LOOP
    SELECT * FROM somewhere WHERE some_conditions LIMIT @S, 1
    -- IF NO RESULTS THEN
    LEAVE
    -- DO SOMETHING
    SET @S = @S + 1;
END LOOP

尽管这在我的脑海中有些模糊。

作为参考，虽然我认为这不一定相关，但初始查询将把四个表连接在一起以形成层次结构权限模型，然后根据特定权限在链上的高度，它将检索有关的附加信息应继承该许可的子女。

Answer 1

像这样的事情应该可以解决问题（但是，请阅读代码片段以获取更多信息）

CREATE PROCEDURE GetFilteredData()
BEGIN
  DECLARE bDone INT;

  DECLARE var1 CHAR(16);    -- or approriate type
  DECLARE var2 INT;
  DECLARE var3 VARCHAR(50);
  
  DECLARE curs CURSOR FOR  SELECT something FROM somewhere WHERE some stuff;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;

  DROP TEMPORARY TABLE IF EXISTS tblResults;
  CREATE TEMPORARY TABLE IF NOT EXISTS tblResults  (
    --Fld1 type,
    --Fld2 type,
    --...
  );

  OPEN curs;

  SET bDone = 0;
  REPEAT
    FETCH curs INTO var1, var2, var3;

    IF whatever_filtering_desired
       -- here for whatever_transformation_may_be_desired
       INSERT INTO tblResults VALUES (var1, var2, var3);
    END IF;
  UNTIL bDone END REPEAT;

  CLOSE curs;
  SELECT * FROM tblResults;
END

需要考虑的一些事情...

关于上面的代码片段：

• 可能希望将部分查询传递给存储过程，也许特别是搜索条件，以使其更加通用。
• 如果此方法要由多个会话调用，则可能需要传递某种会话 ID 来创建唯一的临时表名称（实际上不必要的关注，因为不同的会话不共享相同的临时文件命名空间；请参阅下面 Gruber 的评论）
• 需要正确指定一些部分，例如变量声明、SELECT 查询等。

更一般地说：尝试避免需要游标。

我特意将游标变量命名为 curs[e]，因为游标是喜忧参半的。它们可以帮助我们实现复杂的业务规则，这些规则可能很难用 SQL 的声明式形式来表达，但它随后让我们使用 SQL 的过程式（命令式）形式，这是 SQL 的一个普遍特征，但它既不是很友好/富有表现力、编程方面，但性能方面往往效率较低。

也许您可以考虑在“普通”（声明性）SQL 查询的上下文中表达所需的转换和过滤。

Answer 2

使用光标。

在阅读文档时，可以将光标视为缓冲阅读器。如果您将每一行视为文档中的一行，那么您将读取下一行，执行操作，然后前进光标。

Answer 3

在存储过程中使用游标。
准备 SQL 查询

SELECT id FROM employee where department_id = 1;

创建游标，用于保存 SQL 查询返回的结果集。

DECLARE BonusDistributionCursor CURSOR FOR SELECT id FROM employee where department_id = 1;

要在从游标获取行时不返回任何结果时安全退出，请声明一个名为 NOT FOUND 的处理程序，并将值设置为已声明的变量。

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

在从游标获取下一行之前打开游标。

OPEN BonusDistributionCursor;

获取光标指向的下一行，然后将光标移动到下一行。

FETCH BonusDistributionCursor INTO employeeId;

根据所需的用例运行所需的业务逻辑。

DELIMITER $
CREATE PROCEDURE distributeYearlyBonus (IN departmentId VARCHAR(2))
BEGIN
DECLARE finished INTEGER DEFAULT 0;
DECLARE empId VARCHAR(TEXT) DEFAULT "";
DECLARE BonusDistributionCursor CURSOR FOR SELECT id FROM employee where department_id = departmentId;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
OPEN BonusDistributionCursor;
addBonus: LOOP
   FETCH BonusDistributionCursor INTO empId;
   IF finished = 1 THEN 
      LEAVE addBonus;
   END IF;
INSERT INTO `bonus_paid_details` (`employee_id`, `year`, `datetime`) VALUES (empId, YEAR(CURDATE());, now());
END LOOP addBonus;
CLOSE BonusDistributionCursor;
END$
DELIMITER ;

执行上面的脚本，你会发现创建了一个新的存储过程。

通过输入将接收奖金金额的部门 ID 来调用或调用存储过程。

CALL BonusDistributionCursor(1);

希望这能解释“如何使用存储过程中使用的游标进行迭代”