MySQL存储过程返回多个记录集

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

我在我的数据库(MySQL)中创建了一些存储过程,如下所示。

Stored Procedure 1

存储过程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

Stored Procedure 2

存储过程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, ... );

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).

这里我在MySQL Workbench中获得了4个结果集。 sp_Name2中的2个结果,sp_Name1中的update语句的第3个结果和select语句中的第4个结果,也是sp_Name1中的结果。在这里,我只是寻找最后的结果集。有时候结果序列会出现在预期的顺序中,这意味着结果可能会出现在结果1,结果2,结果4,结果3中(在这种情况下,我无法判断哪个结果集对我有用,因为最后的结果集可能被改变了。

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

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_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.

在上面的例子中,我可以在sp_Login()过程中设置一个参数(例如,loginid)AS INOUT,为其分配LAST_INSERT_ID(),删除SELECT语句并在sp_LoginUser()中检索。但是当我需要单独调用sp_Login()时,我必须在编码中声明一些变量来检索值。

5 个解决方案

#1

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

如果您不想要这些结果集,请不要选择它们。

#2

When you perform a select inside a stored procedure the resultset is returned to the client. http://dev.mysql.com/doc/refman/5.5/en/faqs-stored-procs.html#qandaitem-B-4-1-14

在存储过程中执行select时,结果集将返回给客户端。 http://dev.mysql.com/doc/refman/5.5/en/faqs-stored-procs.html#qandaitem-B-4-1-14

CREATE PROCEDURE sp_LoginUser( IN Username  varchar(50) , IN password varchar(50) )
BEGIN
    --put the resultset into a variable so it don't return back
    DECLARE doesUserExist BOOL;
    SELECT EXISTS( SELECT 1 FROM Users where name = UserName and Pwd = password ) INTO doesUserExist;
    IF doesUserExist
       SET userid = 0;
       SET loginid = 0;
       SELECT userid INTO userid
       FROM users
       WHERE name = UserName and Pwd = password;
       IF userid > 0 THEN
           -- call a function instead of a procedure so you don't need to call last_insert_id again
           SET loginid = sp_Login(userid);
       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
-- this is now a function so it can return what you need
CREATE FUNCTION sp_Login(Userid int) 
RETURNS INTEGER
BEGIN
    INSERT Logins ( userid, datetime )
    VALUES ( Userid, now() );

    SET loginid = LAST_INSERT_ID();
    RETURN loginid;
END

#3

Use DO SELECT.. if you don't want to return a result set for a select (http://dev.mysql.com/doc/refman/5.6/en/do.html). However, I don't understand why you run the selects in the first place if you don't want the results.

如果您不想返回选择的结果集,请使用DO SELECT ..(http://dev.mysql.com/doc/refman/5.6/en/do.html)。但是,如果您不想要结果,我不明白为什么您首先运行选择。

#4

Why the stored procs? You can do it with normal SQL:

为什么存储过程?你可以用普通的SQL做到这一点:

SET @previous := LAST_INSERT_ID();

INSERT INTO Logins ( userid, datetime )
SELECT Userid, now()
FROM users
WHERE name = UserName
and Pwd = password;

SELECT *
FROM Logins
WHERE ID = LAST_INSERT_ID()
AND ID != @previous;

If the username/password was correct, your rowset will have the row for the login - you could join it to the user table to also get all the user data too.

如果用户名/密码正确,您的行集将具有登录行 - 您可以将其加入用户表以获取所有用户数据。

If the username/password was incorrect, you'll have an empty rowset.

如果用户名/密码不正确,您将拥有一个空行集。

FYI, LAST_INSERT_ID() returns 0 if there are no previous inserts.

如果没有先前的插入,则FYI,LAST_INSERT_ID()返回0。

---

Stored procedures are the least preferred way to implement SQL, particularly because they are the least portable way (there are other good reasons too); if you can implement in plain SQL it's a better option. Although this SQL isn't completely portable, it can be converted reasonably easily as most databases have similar functions and features to mysql's.

存储过程是实现SQL的最不优选的方法,特别是因为它们是最不可移植的方式(还有其他好的理由);如果你可以在纯SQL中实现它是一个更好的选择。虽然这个SQL不是完全可移植的,但它可以很容易地转换,因为大多数数据库都具有与mysql相似的功能和特性。

#5

I'm not sure why you are selecting LAST_INSERT_ID() in sp_Login and again in sp_LoginUser?

我不确定为什么要在sp_Login中选择LAST_INSERT_ID()并再次在sp_LoginUser中选择?

If you need to return LAST_INSERT_ID() from sp_Login you need to either assign an output variable to it or consider using a scalar function instead.

如果需要从sp_Login返回LAST_INSERT_ID(),则需要为其分配输出变量或考虑使用标量函数。