MySQL Stored Procedure returning multiple record sets [MySQL存储过程返回多个记录集] - 问题-字节技术
MySQL Stored Procedure returning multiple record sets
MySQL存储过程返回多个记录集
问题 (Question)
I have created some stored procedures in my database (MySQL) as follows.
Stored Procedure 1
CREATE PROCEDURE sp_Name1(
IF cond THEN
call sp_Name2
//Calling 2nd procedure from here.
Update SomeTable .....
SELECT '1' As R
Stored Procedure 2
CREATE PROCEDURE sp_Name2(
IF cond THEN
SELECT '2' As R
SELECT '3' As R
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).
How do I suppress unwanted result sets?
I have use case for your better understanding.
CREATE PROCEDURE sp_LoginUser( IN Username
varchar(50) , IN password varchar(50) )
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 =
IF userid & 0 THEN
CALL sp_Login(userid);
SET loginid = LAST_INSERT_ID();
//only this result i am expecting.
IF loginid & 0 THEN
SELECT userid as userid, loginid AS
SELECT 0 userid, 0
CREATE PROCEDURE sp_Login( IN Userid int )
INSERT Logins ( userid, datetime )
VALUES ( Userid, now() );
SELECT LAST_INSERT_ID() AS
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.
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.
我创造了一些存储过程在我的数据库（MySQL）如下。存储过程1CREATE PROCEDURE sp_Name1(
IF cond THEN
call sp_Name2
//Calling 2nd procedure from here.
Update SomeTable .....
SELECT '1' As R
存储过程2CREATE PROCEDURE sp_Name2(
IF cond THEN
SELECT '2' As R
SELECT '3' As R
我现在打电话给我的第一个存储过程如下：Call sp_Name1(param1, param2, ... );
我在MySQL的工作台，得到4的结果集。从sp_name2 2的结果，第三和第四在sp_name1 SELECT语句的语句，也sp_name1。在这里，我找的只是最后的结果集。有时候结果序列出现在预期的订单，这意味着结果可能会喜欢的结果1，结果2，结果4，结果3（在这种情况下，我不能判断其结果集对我有用，因为最后的结果集可能改变）。我如何抑制有害的结果集？编辑：我为你更好的使用。CREATE PROCEDURE sp_LoginUser( IN Username
varchar(50) , IN password varchar(50) )
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 =
IF userid & 0 THEN
CALL sp_Login(userid);
SET loginid = LAST_INSERT_ID();
//only this result i am expecting.
IF loginid & 0 THEN
SELECT userid as userid, loginid AS
SELECT 0 userid, 0
CREATE PROCEDURE sp_Login( IN Userid int )
INSERT Logins ( userid, datetime )
VALUES ( Userid, now() );
SELECT LAST_INSERT_ID() AS
所以，现在，当我的用户请求的登录和输入他/她对我的登录密码的用户名，然后我打电话给sp_loginuser()在我的服务器。在许多情况下，我要打电话给sp_login()分开。在上述情况下我可以设置一个参数（如loginID）在sp_login()程序输入，指定last_insert_id()它，删除语句检索sp_loginuser() 。但当我需要打电话给sp_login()分开，我必须在我的编码检索变量声明一些。
最佳答案 (Best Answer)
If you don't want those result sets, don't select them.
如果你不想要这些结果集，不选他们。
答案 (Answer) 2
I'm not sure why you are selecting LAST_INSERT_ID() in sp_Login and again in 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()又在sp_loginuser？如果你需要返回last_insert_id()从sp_login你需要指定一个输出变量或考虑而使用标量函数。
答案 (Answer) 3
When you perform a select inside a stored procedure the resultset is returned to the client.
CREATE PROCEDURE sp_LoginUser( IN Username
varchar(50) , IN password varchar(50) )
--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 doesUserE
IF doesUserExist
SET userid = 0;
SET loginid = 0;
SELECT userid INTO userid
FROM users
WHERE name = UserName and Pwd =
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);
//only this result i am expecting.
IF loginid & 0 THEN
SELECT userid as userid, loginid AS
SELECT 0 userid, 0
-- this is now a function so it can return what you need
CREATE FUNCTION sp_Login(Userid int)
RETURNS INTEGER
INSERT Logins ( userid, datetime )
VALUES ( Userid, now() );
SET loginid = LAST_INSERT_ID();
当你执行一个选择在存储过程的结果集返回到客户端。CREATE PROCEDURE sp_LoginUser( IN Username
varchar(50) , IN password varchar(50) )
--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 doesUserE
IF doesUserExist
SET userid = 0;
SET loginid = 0;
SELECT userid INTO userid
FROM users
WHERE name = UserName and Pwd =
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);
//only this result i am expecting.
IF loginid & 0 THEN
SELECT userid as userid, loginid AS
SELECT 0 userid, 0
-- this is now a function so it can return what you need
CREATE FUNCTION sp_Login(Userid int)
RETURNS INTEGER
INSERT Logins ( userid, datetime )
VALUES ( Userid, now() );
SET loginid = LAST_INSERT_ID();
答案 (Answer) 4
Use DO SELECT.. if you don't want to return a result set for a select (). However, I don't understand why you run the selects in the first place if you don't want the results.
使用DO SELECT..如果你不想返回一个结果集的选择（）。然而，我不明白为什么你运行首先如果你不想要的结果。
答案 (Answer) 5
Why the stored procs? You can do it with normal SQL:
SET @previous := LAST_INSERT_ID();
INSERT INTO Logins ( userid, datetime )
SELECT Userid, now()
FROM users
WHERE name = UserName
FROM Logins
WHERE ID = LAST_INSERT_ID()
AND ID != @
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.
Stored procedures are the least preferred way to implement SQL, particularly because they are the least portable way (there are
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做正常：SET @previous := LAST_INSERT_ID();
INSERT INTO Logins ( userid, datetime )
SELECT Userid, now()
FROM users
WHERE name = UserName
FROM Logins
WHERE ID = LAST_INSERT_ID()
AND ID != @
如果用户名和密码是正确的，你的行集将有登录-行你能加入到用户表也得到了所有用户数据。如果用户名/密码不正确，你会有一个空的行集。仅供参考，LAST_INSERT_ID()退货0如果没有以前的插入。存储过程是至少执行SQL的首选方式，特别是因为他们是最便携式（有路太多）；如果你能实现普通SQL是一个更好的选择。虽然这个SQL不是完全可移植的，它可以被转换为大多数数据库相当容易有类似的功能和特点，对MySQL的。
本文翻译自StackoverFlow，英语好的童鞋可直接参考原文：python 可迭代对象 Python中对象迭代与反迭代的技巧总结-Python教程
当前位置：&>&&>& &
Python中对象迭代与反迭代的技巧总结
python 可迭代对象 Python中对象迭代与反迭代的技巧总结
|　来源:网络 | 关键字：
一、如何实现可迭代对象和迭代器对象？实际案例某软件要求从网络抓取各个城市气味信息，并其次显示：北京： 15 ~ 20 天津： 17 ~ 22 长春： 12 ~ 18 ......如果一次抓取所有城市天
一、如何实现可迭代对象和迭代器对象？实际案例某软件要求从网络抓取各个城市气味信息，并其次显示：北京： 15 ~ 20 天津： 17 ~ 22 长春： 12 ~ 18 ......如果一次抓取所有城市天气再显示，显示第一个城市气温时，有很高的延时，并且浪费存储空间，我们期望以用时访问的策略，并且把所有城市气温封装到一个对象里，可用for语句进行迭代，如何解决？解决方案实现一个迭代器对象Weatherlterator,next方法每次返回一个城市气温，实现一个可迭代对象Weatherlterable,――――iter__方法返回一个迭代器对象import requests from collections import Iterable, Iterator # 气温迭代器 class WeatherIterator(Iterator): def __init__(self, cities): self.cities = cities self.index = 0 def getWeather(self, city): r = requests.get('/weather_mini?city=' + city) data = r.json()['data']['forecast'][0] return '%s：%s , %s' % (city, data['low'], data['high']) def __next__(self): if self.index == len(self.cities): raise StopIteration city = self.cities[self.index] self.index += 1 return self.getWeather(city) # 可迭代对象 class WeatherIterable(Iterable): def __init__(self, cities): self.cities = cities def __iter__(self): return WeatherIterator(self.cities) for x in WeatherIterable(['北京', '上海', '广州', '深圳']): print(x)执行结果如下：C:PythonPython35python.exe E:/python-intensive-training/s2.py 北京：低温 21℃ , 高温 30℃ 上海：低温 23℃ , 高温 26℃ 广州：低温 26℃ , 高温 34℃ 深圳：低温 27℃ , 高温 33℃ Process finished with exit code 0二、如何使用生成器函数实现可迭代对象？实际案例实现一个可迭代对象的类，它能迭代出给定范围内所有素数：python pn = PrimeNumbers(1, 30) for k in pn: print(k) `` 输出结果text2 3 5 7 11 13 17 19 23 29“`解决方案-将该类的__iter__方法实现生成器函数，每次yield返回一个素数class PrimeNumbers: def __init__(self, start, stop): self.start = start self.stop = stop def isPrimeNum(self, k): if k & 2: return False for i in range(2, k): if k % i == 0: return False return True def __iter__(self): for k in range(self.start, self.stop + 1): if self.isPrimeNum(k): yield k for x in PrimeNumbers(1, 20): print(x)运行结果C:PythonPython35python.exe E:/python-intensive-training/s3.py 2 3 5 7 11 13 17 19 Process finished with exit code 0三、如何进行反向迭代以及如何实现反向迭代？实际案例实现一个连续浮点数生成器FloatRange(和rrange类似)，根据给定范围(start, stop)和步进值(step)产生一些列连续浮点数，如迭代FloatRange(3.0,4.0,0.2)可产生序列：正向：3.0 & 3.2 & 3.4 & 3.6 & 3.8 & 4.0 反向：4.0 & 3.8 & 3.6 & 3.4 & 3.2 & 3.0解决方案实现反向迭代协议的__reversed__方法，它返回一个反向迭代器class FloatRange: def __init__(self, start, stop, step=0.1): self.start = start self.stop = stop self.step = step def __iter__(self): t = self.start while t &= self.stop: yield t t += self.step def __reversed__(self): t = self.sto(.cn)p while t &= self.start: yield t t -= self.step print("正相迭代-----") for n in FloatRange(1.0, 4.0, 0.5): print(n) print("反迭代-----") for x in reversed(FloatRange(1.0, 4.0, 0.5)): print(x)输出结果C:PythonPython35python.exe E:/python-intensive-training/s4.py 正相迭代----- 1.0 1.5 2.0 2.5 3.0 3.5 4.0 反迭代----- 4.0 3.5 3.0 2.5 2.0 1.5 1.0 Process finished with exit code 0四、如何对迭代器做切片操作？实际案例有某个文本文件，我们想都去其中某范围的内容，如100~300行之间的内容，python中文本文件是可迭代对象，我们是否可以使用类似列表切片的方式得到一个100~300行文件内容的生成器？解决方案使用标准库中的itertools.islice，它能返回一个迭代器对象切片的生成器from itertools import islice f = open('access.log') # # 前500行 # islice(f, 500) # # 100行以后的 # islice(f, 100, None) for line in islice(f,100,300): print(line)islice每次训话都会消耗之前的迭代对象l = range(20) t = iter(l) for x in islice(t, 5, 10): print(x) print('第二次迭代') for x in t: print(x)输出结果C:PythonPython35python.exe E:/python-intensive-training/s5.py 5 6 7 8 9 第二次迭代 10 11 12 13 14 15 16 17 18 19 Process finished with exit code 0五、如何在一个for语句中迭代多个可迭代对象？实际案例1、某班学生期末考试成绩，语文、数学、英语分别存储再3个列表中，同时迭代三个列表，计算每个学生的总分(并行)2、某年纪有四个班，某次考试没班英语成绩分别存储在四个列表中，依次迭代每个列表，统计全学年成绩高于90分人数(串行)解决方案并行：使用内置函数zip，它能将多个可迭代对象合并，每次迭代返回一个元组from random import randint # 申城语文成绩，# 40人，分数再60-100之间 chinese = [randint(60, 100) for _ in range(40)] math = [randint(60, 100) for _ in range(40)] # 数学 english = [randint(60, 100) for _ in range(40)] # 英语 total = [] for c, m, e in zip(chinese, math, english): total.append(c + m + e) print(total)执行结果如下：C:PythonPython35python.exe E:/python-intensive-training/s6.py [232, 234, 259, 248, 241, 236, 245, 253, 275, 238, 240, 239, 283, 256, 232, 224, 201, 255, 206, 239, 254, 216, 287, 268, 235, 223, 289, 221, 266, 222, 231, 240, 226, 235, 255, 232, 235, 250, 241, 225] Process finished with exit code 0串行：使用标准库中的itertools.chain，它能将多个可迭代对象连接from random import randint from itertools import chain # 生成四个班的随机成绩 e1 = [randint(60, 100) for _ in range(40)] e2 = [randint(60, 100) for _ in range(42)] e3 = [randint(60, 100) for _ in range(45)] e4 = [randint(60, 100) for _ in range(50)] # 默认人数=1 count = 0 for s in chain(e1, e2, e3, e4): # 如果当前分数大于90，就让count+1 if s & 90: count += 1 print(count)输出结果C:PythonPython35python.exe E:/python-intensive-training/s6.py 48 Process finished with exit code 0总结以上就是这篇文章的全部内容，希望对大家的学习或者工作带来一定的帮助，如果有疑问大家可以留言交流。
网友评论仅供其表达个人看法，并不表明网易立场。