MySQL：在SELECT和FROM中使用存储过程

I have a question related to stored procedure. I have a table called as account with a field named agent. I have a simple SQL like this:

我有一个与存储过程有关的问题。我有一个名为account的表，其中包含一个名为agent的字段。我有一个像这样的简单SQL：

-- First SQL --
SELECT      account.agent
FROM        account
GROUP BY    account.agent
HAVING      account.agent > 0

If I insert this above SQL to a table called as agent_structure, I simply can JOIN this table with another table (e.g. customer) by using SQL like this:

如果我将上面的SQL插入到一个名为agent_structure的表中，我可以使用SQL将此表与另一个表（例如customer）连接起来，如下所示：

-- Second SQL --
SELECT      agent_structure.agent,
            customer.name, 
            customer.age, 
            customer.country
FROM        agent_structure
INNER JOIN  customer ON agent_structure.agent = customer.id

The problem is, I don't want to add another table only for saving one field. So, I try to use stored procedure. So, I put the first SQL to procedure like this:

问题是，我不想仅为保存一个字段添加另一个表。所以，我尝试使用存储过程。所以，我把第一个SQL放到这样的过程：

-- FIRST SQL put into Procedure --
CREATE PROCEDURE agent_structure() 
SELECT      account.agent
FROM        account
GROUP BY    account.agent
HAVING      account.agent > 0

This looks very well, since when I write 'CALL agent_structure();', the SQL output the single field that I want. However, I don't know how to use this result like in second SQL. I try this dummy way after give out parameter to the procedure, but it doesn't work:

这看起来非常好，因为当我编写'CALL agent_structure（）;'时，SQL输出我想要的单个字段。但是，我不知道如何在第二个SQL中使用此结果。在给出过程参数后我尝试这种虚拟方式，但它不起作用：

-- Second SQL but use stored procedure --
CALL agent_structure(@a);
SELECT      @a,
            customer.name, 
            customer.age, 
            customer.country
FROM        @a
INNER JOIN  customer ON @a.agent = customer.id

The goal is like using a script to another script. I don't want to put the first script directly to the second one since my actual script is larger and have multiple layers. Anyone can help me give the solution for this?

目标就像将脚本用于另一个脚本。我不想将第一个脚本直接放到第二个脚本，因为我的实际脚本更大并且有多个层。有人可以帮我解决这个问题吗？

1 个解决方案

#1

As I can see you can't include Stored Procedure inside SELECT. In this case you need to use View instead Stored Procedure.

我可以看到你不能在SELECT中包含存储过程。在这种情况下，您需要使用View而不是Stored Procedure。

    CREATE VIEW agent_structure AS
    SELECT      account.agent
    FROM        account
    GROUP BY    account.agent
    HAVING      account.agent > 0

    SELECT      customer.name, 
                customer.age, 
                customer.country
    FROM        agent_structure AS a
    INNER JOIN  customer AS c ON a.agent = c.id