选择一个MySQL过程? [英] MySQL Procedure within a Select?

问题描述

我有一个这样的程序:

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

但是我知道这是很痛苦的错误.有人可以指出我正确的方向吗?我将需要访问过程的所有列，以便在最终选择"中(加入?).该过程中的SQL代码非常痛苦，因此首先要这样做！

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!

推荐答案

Ticket_FiscalTotals过程返回包含一些字段的数据集，但您只需要其中一个-Service.将您的过程重写为存储函数-Get_Ticket_FiscalTotals_Service.

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.

另一种方法是在过程中创建并填充临时表，并将此临时表添加到查询中，例如:

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;

这篇关于选择一个MySQL过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！