正文 

当前位置： 首页 > 源码 >

MySQL存储过程，处理多个游标和查询结果

     2023-05-09     41

关键词：

【中文标题】MySQL存储过程，处理多个游标和查询结果【英文标题】：MySQL stored procedure, handling multiple cursors and query results 【发布时间】：2010-01-03 14:55:50 【问题描述】：

如何在同一个例程中使用两个游标？如果我删除第二个游标声明并获取循环一切正常。该例程用于在我的 webapp 中添加朋友。它获取当前用户的 id 和我们想要添加为朋友的朋友的电子邮件，然后检查电子邮件是否具有相应的用户 id，如果不存在朋友关系，它将创建一个。除此以外的任何其他常规解决方案也都很棒。

DROP PROCEDURE IF EXISTS addNewFriend;
DELIMITER //
CREATE PROCEDURE addNewFriend(IN inUserId INT UNSIGNED, IN inFriendEmail VARCHAR(80))
BEGIN
    DECLARE tempFriendId INT UNSIGNED DEFAULT 0;
    DECLARE tempId INT UNSIGNED DEFAULT 0;
    DECLARE done INT DEFAULT 0;

    DECLARE cur CURSOR FOR
        SELECT id FROM users WHERE email = inFriendEmail;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;
    REPEAT
        FETCH cur INTO tempFriendId;
    UNTIL done  = 1 END REPEAT;
    CLOSE cur;

    DECLARE cur CURSOR FOR 
        SELECT user_id FROM users_friends WHERE user_id = tempFriendId OR friend_id = tempFriendId;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;
    REPEAT
        FETCH cur INTO tempId;
    UNTIL done  = 1 END REPEAT;
    CLOSE cur;

    IF tempFriendId != 0 AND tempId != 0 THEN
        INSERT INTO users_friends (user_id, friend_id) VALUES(inUserId, tempFriendId);
    END IF;
    SELECT tempFriendId as friendId;
END //
DELIMITER ;

【问题讨论】：

【参考方案1】：

下面是一个简单的例子，说明如何在同一个例程中使用两个游标：

DELIMITER $$

CREATE PROCEDURE `books_routine`()
BEGIN
  DECLARE rowCountDescription INT DEFAULT 0;
  DECLARE rowCountTitle INT DEFAULT 0;
  DECLARE updateDescription CURSOR FOR
    SELECT id FROM books WHERE description IS NULL OR CHAR_LENGTH(description) < 10;
  DECLARE updateTitle CURSOR FOR
    SELECT id FROM books WHERE title IS NULL OR CHAR_LENGTH(title) <= 10;

  OPEN updateDescription;
  BEGIN
      DECLARE exit_flag INT DEFAULT 0;
      DECLARE book_id INT(10);
      DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET exit_flag = 1;

      updateDescriptionLoop: LOOP
        FETCH updateDescription INTO book_id;
            IF exit_flag THEN LEAVE updateDescriptionLoop; 
            END IF;
            UPDATE books SET description = 'Lorem ipsum dolor sit amet, consectetur adipiscing elit.' WHERE books.id = book_id;
        SET rowCountDescription = rowCountDescription + 1;
      END LOOP;
  END;
  CLOSE updateDescription;

  OPEN updateTitle;
  BEGIN
      DECLARE exit_flag INT DEFAULT 0;
      DECLARE book_id INT(10);
      DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET exit_flag = 1;

      updateTitleLoop: LOOP
        FETCH updateTitle INTO book_id;
            IF exit_flag THEN LEAVE updateTitleLoop; 
            END IF;
            UPDATE books SET title = 'Lorem ipsum dolor sit amet' WHERE books.id = book_id;
        SET rowCountTitle = rowCountTitle + 1;
      END LOOP;
  END;
  CLOSE updateTitle;

  SELECT 'number of titles updated =', rowCountTitle, 'number of descriptions updated =', rowCountDescription;
END

【讨论】：

【参考方案2】：

我知道您找到了更好的解决方案，但我相信您最初问题的答案是您需要 SET Done=0;在两个游标之间，否则第二个游标在退出循环之前只会获取一条记录，因为前一个处理程序的 Done=1。

【讨论】：

是的，我现在可以看到了，你是对的。我认为我最终以更好的方式解决了这个问题，没有游标和循环。【参考方案3】：

我终于写了一个不同的函数来做同样的事情：

DROP PROCEDURE IF EXISTS addNewFriend;
DELIMITER //
CREATE PROCEDURE addNewFriend(IN inUserId INT UNSIGNED, IN inFriendEmail VARCHAR(80))
BEGIN
 SET @tempFriendId = (SELECT id FROM users WHERE email = inFriendEmail);
 SET @tempUsersFriendsUserId = (SELECT user_id FROM users_friends WHERE user_id = inUserId AND friend_id = @tempFriendId);
 IF @tempFriendId IS NOT NULL AND @tempUsersFriendsUserId IS NULL THEN
  INSERT INTO users_friends (user_id, friend_id) VALUES(inUserId, @tempFriendId);
 END IF;
 SELECT @tempFriendId as friendId;
END //
DELIMITER ;

我希望这是一个更好的解决方案，无论如何它都可以正常工作。感谢您告诉我在不需要时不要使用游标。

【讨论】：

【参考方案4】：

你可以在 WHERE 子句中使用 EXISTS 子句，而不是使用游标来检查记录是否存在：

INSERT INTO users_friends 
  (user_id, friend_id) 
VALUES
  (inUserId, tempFriendId)
WHERE EXISTS(SELECT NULL 
               FROM users 
              WHERE email = inFriendEmail)
  AND NOT EXISTS(SELECT NULL 
                   FROM users_friends 
                  WHERE user_id = tempFriendId 
                    AND friend_id = tempFriendId);

在阅读 Paul 的关于第二个查询的 cmets 后，我进行了更改，并颠倒了逻辑，因此插入不会添加重复项。理想情况下，这应该作为复合键（包括两列或更多列）的主键来处理，这样就不需要签入代码。

【讨论】：

【参考方案5】：

哇，我不知道该说什么，请去阅读并学习一下 sql，无意冒犯，但这是我见过的最糟糕的 SQL 之一。

SQL 是一种基于集合的语言，游标通常很糟糕，在某些情况下它们很有用，但它们相当少见。您在这里使用游标是完全不合适的。

您在第二个光标中的逻辑也有缺陷，因为它会选择包含朋友的任何记录，而不仅仅是所需的友谊。

如果您想修复它，可以尝试为第二个光标指定一个不同的名称，但最好重新开始。

在 users_friends 上设置复合 PK 或唯一约束，然后您不必担心检查关系，然后尝试这样的操作。

INSERT INTO users_friends 
SELECT 
    @inUserId, 
    users.user_id
FROM 
    users
WHERE
    email = @inFriendEmail

【讨论】：

感谢您的指导，我是存储过程的新手，并尝试使用以前获得帮助的常规 (***.com/questions/1903189/…)，我将尝试找到不同的方法来编写此过程. 是的，你的逻辑是对的，应该是： SELECT user_id FROM users_friends WHERE (user_id = tempFriendId ANDfriend_id = inUserId) OR (friend_id = tempFriendId AND user_id = inUserId);但正如我所说，我会尝试另一种方法。

mysql之游标

...游标：CREATEFUNCTION函数名称（参数）RETURNS数据类型程序体存储过程中使用游标的4个步骤：定义游标、打开游标、读取游标数据和关闭游标。定义游标：DECLARE游标名CURSORFOR查询语句打开游标：OPEN游标名称；读取游标数据：FETCH游... 查看详情

mysql存储过程使用游标实现两张表数据同步数据(代码片段)

一 存储过程中的游标1.1游标的功能概述游标就是类似java中集合遍历的迭代器，MySQL中的游标只能用在存储过程和函数中，在存储过程和函数中可以使用游标对结果集进行循环的处理，可以遍历返回的多行结果，... 查看详情

mysql游标(代码片段)

...。游标也是一种面向过程的sql编程方法，所以一般在存储过程、函数、触发器、循环处理中使用。游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。游标的作用游标相当于一... 查看详情

创建游标的查询语句直接在mysql中可以运行,但是用存储过程的方式后我的游标查询不到数据(附图)

...标的查询语句单独执行,结果为有数据;第二行命令为调用存储过程结果为空并显示一行警告,第三行命令为显示上一行命令产生的警告的详情,结果翻译为:游标遍历了空集(也就是创建游标的查询语句没有查询到数据)!参考技术A不如... 查看详情

如何通过mybatis获取mysql存储过程返回的不确定个数的多个结果集

参考技术A如何通过MyBatis获取mysql存储过程返回的不确定个数的多个结果集如果对select返回的结果行都需要处理,使用游标。如果只想取得返回多行中的一行,使用limit。 查看详情

oracle如何返回多条记录

Oracle的存储过程和函数并不能直接返回查询结果集（并非不能，“没有做不到，只有想不到”，呵）。也就是说在Oracle存储过程或函数中直接写查询返回结果集是非法的（在SQLServer是可以的）。与SQLServer的存储过程和函数均可以... 查看详情

在 MySQL 中使用动态 SQL 创建游标

...布时间】：2021-11-2214:02:46【问题描述】：我正在编写一个存储过程，它打开一个表的游标，然后遍历所有记录。在迭代过程中，我根据第一个游标的结果创建了一个动态查询。我需要在动态SQL上打开游标，但MySQL不允许我这样做... 查看详情

MySQL游标存储过程条件显示重复记录

】MySQL游标存储过程条件显示重复记录【英文标题】：MySQLcursorstoredprocedureconditionshowingduplicaterecords【发布时间】：2019-04-2409:44:40【问题描述】：如屏幕截图所示，结果中返回了存储过程和表中的重复行。如表中只有一条记录匹配... 查看详情

Java 和数据库：关于使用多个结果集和游标

...述】：我正在运行一个java方法来调用Oracle11.2数据库中的存储过程。我正在使用JDBC连接调用带有OUT参数的存储过程，以将数据库游标作为结果集返回给java方法。一切正常。现在我想将第二个结果集返回给SAME存 查看详情

游标的作用是啥?

...t_SQL游标由DECLARECURSOR语法定义、主要用在Transact_SQL脚本、存储过程和触发器中。Transact_SQL游标主要用在服务器上，由从客户端发送给服务器的Transact_SQL语句或是批处理、存储过程、触发器中的Transact_SQL进行管理。2、API游标API游... 查看详情

mysql从入门到精通50讲（二十一）-cursor游标

游标游标概念游标（CURSOR）是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。游标主要用于交互式应用，其中... 查看详情

mysql从入门到精通50讲（二十一）-cursor游标

游标游标概念游标（CURSOR）是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。游标主要用于交互式应用，其中... 查看详情

mysql数据库的存储过程能返回游标么?

如果能,应该怎么写?谢谢.不知道mysql这个跟oracle里面，是不是一致的，下面是我学习的时候总结的如果一个过程要返回一个结果集，那么要引用游标来处理这个结果集。createorreplaceprocedureTest(varEmpNameemp.ename%type)Asbegin------会报错.... 查看详情

视图、游标是啥？

...。视图是从一个或几个基本表导出的表。视图本身不独立存储在数据库中，是一个虚表。游标：是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结... 查看详情

21使用游标(代码片段)

21.1游标在存储过程中使用游标可以对一个结果集进行移动遍历。游标主要用于交互式应用，其中用户需要对数据集中的任意行进行浏览和修改。21.2使用游标使用游标的四个步骤：声明游标，这个过程没有实际检索出数据，它只... 查看详情

mysql存储过程和游标的使用(代码片段)

这里存储过程和游标的定义和作用就不介绍了，网上挺多的，只通过简单的介绍，然后用个案例让大家快速了解。实例中会具体说明变量的定义，赋值，游标的使用，控制语句，循环语句的介绍。1.创... 查看详情

mysql游标(代码片段)

...写一个函数，计算test1表中a、b字段所有的和测试游标过程解析定义游标（Cursor）是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次一行遍历数据的能力。游标也是一种面... 查看详情

mysql存储过程demo(代码片段)

从没写过mysql存储过程，靠着百度和以前写oracle存储过程的经验写了一个，还算顺利，留个例子吧CREATEDEFINER=`west_brain`@`%`PROCEDURE`man_tree_area`()BEGIN--存储树状结果处理sql变量DECLAREvar_codeVARCHAR(1000);DECLAREvar_pcodeVARCHAR(1000);DECLAREvar_nameVA... 查看详情