在另一个存储过程中使用mysql存储过程的结果集

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

我有一个MYSQL存储过程SP1()，它返回一个结果集。

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

我想在SP2()中调用SP1()，并通过SP1()的结果集进行循环，以完成一些额外的工作。

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

我不想包含SP1()中的逻辑，因为这会使SP2()变得太复杂。

Any suggestions?

有什么建议吗?

Thanks.

谢谢。

2 个解决方案

#1

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:

你想做的听起来不是特别好，也许你应该重新设计这两个步骤。但是，您可以这样做:

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.

让您的sp2 sproc将它的中间结果写到临时表中，然后您可以在sp1中访问/进程。然后，在sp1返回时，可以删除在sp2中创建的临时表。

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

不是很优雅，但应该这样做

#2

Cursors would help solve the issue.

游标将有助于解决这个问题。

I am not sure if this is possible but make a cursor for select call for SP1() and iterate over them as usual cursor.

我不确定这是否可行，但为SP1()创建一个选择调用的游标，并像往常一样对它们进行迭代。

#1