选择中的 MySQL 过程？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8500091/
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 Procedure within a Select?
提问by Fuginator
I have a procedure that works like this:
我有一个像这样工作的程序：
mysql> call Ticket_FiscalTotals(100307);
+---------+--------+----------+------------+------------+
| Service | Items | SalesTax | eTaxAmount | GrandTotal |
+---------+--------+----------+------------+------------+
| 75.00 | 325.00 | 25.19 | 8.00 | 433.19 |
+---------+--------+----------+------------+------------+
1 row in set (0.08 sec)
I would like to call this procedure from within a select, like so:
我想从选择中调用此过程，如下所示：
SELECT Ticket.TicketID as `Ticket`,
Ticket.DtCheckOut as `Checkout Date / Time`,
CONCAT(Customer.FirstName, ' ', Customer.LastName) as `Full Name`,
Customer.PrimaryPhone as `Phone`,
(CALL Ticket_FiscalTotals(Ticket.TicketID)).Service as `Service`
FROM Ticket
INNER JOIN Customer ON Ticket.CustomerID = Customer.CustomerID
ORDER BY Ticket.SiteHomeLocation, Ticket.TicketID
However I know that this is painfully wrong. Can someone please point me in the proper direction? I will need access to all of the columns from the procedure to be (joined?) in the final Select. The SQL code within that procedure is rather painful, hence the reason for it in the first place!
但是我知道这是非常错误的。有人可以指出我正确的方向吗？我需要访问程序中的所有列，以便（加入？）在最终选择中。该过程中的 SQL 代码相当痛苦，因此首先是它的原因！
回答by Devart
The Ticket_FiscalTotals procedure returns a data set with some fields, but you need just one of them - Service. Rewrite your procedure to stored function - Get_Ticket_FiscalTotals_Service.
Ticket_FiscalTotals 过程返回包含一些字段的数据集，但您只需要其中之一 - Service。将您的过程重写为存储函数 - Get_Ticket_FiscalTotals_Service.
Another way is to create and fill temporary table in the procedure, and add this temporary to a query, e.g.:
另一种方法是在过程中创建和填充临时表，并将这个临时表添加到查询中，例如：
DELIMITER $$
CREATE PROCEDURE Ticket_FiscalTotals()
BEGIN
DROP TEMPORARY TABLE IF EXISTS temp1;
CREATE TEMPORARY TABLE temp1(
Service FLOAT(10.2),
Items FLOAT(10.2),
SalesTax FLOAT(10.2),
eTaxAmount FLOAT(10.2),
GrandTotal FLOAT(10.2)
);
INSERT INTO temp1 VALUES (75.0, 325.0, 25.19, 8.0, 433.19);
END
$$
DELIMITER ;
-- Usage
CALL Ticket_FiscalTotals();
SELECT t.*, tmp.service FROM Ticket t, temp1 tmp;
回答by Micha? Powaga
You can't join directly to stored procedure. You can join to temporary table that this stored procedure fills:
您不能直接加入存储过程。您可以加入此存储过程填充的临时表：
- create temporary table,
- execute SP that fills data in your temp table,
- join to temp table in your query,
- drop temp table.
- 创建临时表，
- 执行在临时表中填充数据的 SP，
- 加入查询中的临时表，
- 删除临时表。
Of course it is not one line solution.
当然，这不是单行解决方案。
The other way (worse in my opinion) I think of is to have as many UDF as columns in SP result set, this might look like fallowing code:
另一种方式（在我看来更糟）我想到的 UDF 与 SP 结果集中的列一样多，这可能看起来像闲置代码：
SELECT
Ticket.TicketID as `Ticket`,
Ticket.DtCheckOut as `Checkout Date / Time`,
CONCAT(Customer.FirstName, ' ', Customer.LastName) as `Full Name`,
Customer.PrimaryPhone as `Phone`,
Ticket_FiscalTotals_Service(Ticket.TicketID) as `Service`,
Ticket_FiscalTotals_Items(Ticket.TicketID) as `Items`,
Ticket_FiscalTotals_SalesTax(Ticket.TicketID) as `SalesTax`,
Ticket_FiscalTotals_eTaxAmount(Ticket.TicketID) as `eTaxAmount`,
Ticket_FiscalTotals_GrandTotal(Ticket.TicketID) as `GrandTotal`
FROM Ticket
INNER JOIN Customer ON Ticket.CustomerID = Customer.CustomerID
ORDER BY Ticket.SiteHomeLocation, Ticket.TicketID
