MySQL存储过程返回多个记录集 [英] MySQL Stored Procedure returning multiple record sets

问题描述

我已经在数据库(MySQL)中创建了一些存储过程，如下所示.

I have created some stored procedures in my database (MySQL) as follows.

存储过程1

CREATE PROCEDURE sp_Name1(
    param1,
    param2,
    ...... 
)
BEGIN
   .....
   some code
   IF cond THEN 
       call sp_Name2  //Calling 2nd procedure from here.

       Update SomeTable .....

       SELECT '1' As Result;
   END IF
END

存储过程2

CREATE PROCEDURE sp_Name2(
    param1,
    param2,
    ...... 
)
BEGIN
   .....
   some code
   IF cond THEN 
       SELECT '2' As Result;

       SELECT '3' As Result;
   END IF
END

现在，我正在按以下方式调用我的第一个存储过程:

Now I am calling my first stored procedure as follows:

Call sp_Name1(param1, param2, ... );

在这里，我在MySQL Workbench中得到4个结果集. sp_Name2的结果为2，sp_Name1中的update语句为3，sp_Name1中的select语句为4.在这里，我只是在寻找最后一个结果集.有时结果序列按预期顺序显示，这意味着结果可能像结果1，结果2，结果4，结果3一样出现(在这种情况下，我无法判断哪个结果集对我有用，因为最后一个结果集可能对我有用.被更改).

Here I am getting 4 result sets in MySQL Workbench. 2 results from sp_Name2, 3rd for the update statement in sp_Name1 and 4th from the select statement, also in sp_Name1. Here I am looking for just the last result set. Sometimes the result sequence appears in the expected order, which means the results may come in like Result 1, Result 2, Result 4, Result 3 (In this case I can not judge which result set is useful to me, because last result set may be changed).

如何抑制不需要的结果集?

How do I suppress unwanted result sets?

我有用例，供您更好地理解.

EDIT : I have use case for your better understanding.

CREATE PROCEDURE sp_LoginUser( IN Username  varchar(50) , IN password varchar(50) )
BEGIN
    IF EXISTS( SELECT 1 FROM Users where name = UserName and Pwd = password)
       SET userid = 0;
       SET loginid = 0;
       SELECT userid INTO userid
       FROM users
       WHERE name = UserName and Pwd = password;
       IF userid > 0 THEN
           CALL sp_Login(userid);
           SET loginid = LAST_INSERT_ID();         
       END IF;
       //only this result i am expecting.
       IF loginid > 0 THEN
           SELECT userid as userid, loginid AS loginid;
       ELSE
           SELECT 0 userid, 0 loginid;
       END IF;
    END IF;
END

CREATE PROCEDURE sp_Login( IN Userid int )
BEGIN
    INSERT Logins ( userid, datetime )
    VALUES ( Userid, now() );

    SELECT LAST_INSERT_ID() AS loginid;
END

因此，现在，当我的用户请求登录并在登录页面上输入其用户名和密码时，我已经在服务器上调用了sp_LoginUser().在许多情况下，我必须分别调用sp_Login().

So, Now when my user requesting for login and enter his/her username with password on my login page, then I have call sp_LoginUser() on my server. In many cases I have to call sp_Login() separately.

在上述情况下，我可以在sp_Login()过程中将一个参数(例如loginid)设置为IN INOUT，为其分配LAST_INSERT_ID()，删除SELECT语句并在sp_LoginUser()中进行检索. 但是，当我需要分别调用sp_Login()时，必须在编码中声明一些变量以获取值.

In above case I can set one parameter (eg. loginid) AS INOUT in sp_Login() procedure, assign LAST_INSERT_ID() to it, remove SELECT statement and retrieve in sp_LoginUser(). But when I need to call sp_Login() separately, i must have to declare some variable in my coding to retrieve value.

推荐答案

如果您不想要这些结果集，请不要选择它们.

If you don't want those result sets, don't select them.

这篇关于MySQL存储过程返回多个记录集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！