MySQL 存储过程返回多个记录集
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20629558/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Stored Procedure returning multiple record sets
提问by Manish Sapkal
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 个结果集。2 个结果来自 sp_Name2，第 3 个来自 sp_Name1 中的更新语句，第 4 个来自 select 语句，也在 sp_Name1 中。在这里，我只是在寻找最后一个结果集。有时结果序列以预期的顺序出现，这意味着结果可能会像 Result 1, Result 2, Result 4, Result 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() 时，我必须在我的编码中声明一些变量来检索值。
回答by Praveen Prasannan
If you don't want those result sets, don't select them.
如果您不想要这些结果集，请不要选择它们。
回答by deramko
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
当您在存储过程中执行选择时，结果集将返回给客户端。 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
回答by Mike Lischke
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）。但是，如果您不想要结果，我不明白为什么要首先运行选择。
回答by Bohemian
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 0if there are no previous inserts.
仅供参考，如果没有以前的插入，则LAST_INSERT_ID()返回0。
Stored procedures are the leastpreferred way to implement SQL, particularly because they are the least portableway (there are other good reasonstoo); 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 相似的功能和特性。
回答by user3056839
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()，您需要为其分配一个输出变量或考虑使用标量函数。
回答by HSP
Return multiple values like
返回多个值，如
CREATE PROCEDURE `sp_ReturnValue`(
p_Id int(11), -- Input param
OUT r_status1 INT(11) -- output param
OUT r_status2 VARCHAR(11) -- output param
OUT r_status3 INT(11) -- output param
)
BEGIN
SELECT Status FROM tblUsers WHERE tblUsers_ID = p_Id; // use of input param
SET r_status1 = 2; // use of output param
SET r_status2 = "ABCD"; // use of string output param
SET r_status3 = 2; // use of output param
END
And READ Like
和阅读喜欢
CREATE PROCEDURE `sp_ReadReturnValue`()
BEGIN
SELECT @ret_value1 AS ret_value1,
@ret_value2 AS ret_value2,
@ret_value3 AS ret_value3;
END
Parameter in node for Out param
Out 参数的节点中的参数
con.query("CALL sp_ReturnValue(?, @ret_value1, @ret_value2, @ret_value3); CALL sp_ReadReturnValue;", [id], (err, rows) => {
console.log("Print return value \n ");
console.log(rows[1][0].ret_value1 "\n");
console.log(rows[1][0].ret_value2 "\n");
console.log(rows[1][0].ret_value3 );
})
