在另一个存储过程中使用mysql存储过程的结果集 [英] use result set of mysql stored procedure in another stored procedure

问题描述

我有一个返回结果集的MYSQL存储过程SP1().

I have a MYSQL stored procedure SP1() that returns a result set.

我想在SP2()内部调用SP1()并遍历SP1()的结果集以做一些额外的工作.

I want to call SP1() inside of SP2() and loop through the result set of SP1() to do some additional work.

我不想从SP1()中包括我的逻辑，因为这会使SP2()变得太复杂了.

I don't want to include my logic from SP1() because it would make SP2() too complicated.

有什么建议吗?

谢谢.

推荐答案

您想做的事情听起来并不特别好，也许您应该考虑重新设计这两个过程.但是，您可以执行以下操作来快速解决此问题:

What you want to do doesnt sound particularly good and maybe you should think about re-designing those 2 procs. However, you could do something like this as a quick fix:

让您的sp2 sproc将其中间结果写入临时表，然后可以在sp1内部进行访问/处理.一旦sp1返回，您就可以删除在sp2中创建的临时表.

get your sp2 sproc to write it's intermediate results to a temporary table which you can then access/process inside of sp1. You can then drop the temporary table which you created in sp2 once sp1 returns.

http://pastie.org/883881

delimiter ;
drop procedure if exists foo;
delimiter #

create procedure foo()
begin

  create temporary table tmp_users select * from users;

  -- do stuff with tmp_users

  call bar();

  drop temporary table if exists tmp_users;

end #

delimiter ;

drop procedure if exists bar;

delimiter #

create procedure bar()
begin
  -- do more stuff with tmp_users
  select * from tmp_users;
end #

delimiter ;

call foo();

不是很优雅，但应该可以解决问题

not very elegant but should do the trick

这篇关于在另一个存储过程中使用mysql存储过程的结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！