A temporary table is a short-lived table for data needed during a connection or operation. Think of it as a scratch spreadsheet: copy a useful data set into it, work with that copy, and let it disappear when the database connection closes. section 22.3.2
Temporary tables are useful when you need to:
- Reduce blocking by giving a cursor or WHILE loop a connection-only copy of the rows it must process. section 22.3.2 section 22.3.1
- Reuse a data set several times in a SQL script. section 22.3.2
- Break a complicated query into smaller queries, or improve performance when testing shows that a temporary-table join reads less data. section 22.3.2 section 23.2.4
Copy order IDs for a stored procedure
In MySQL, add TEMPORARY between CREATE and TABLE: CREATE TEMPORARY TABLE orderheadertemp (OrderID int); INSERT orderheadertemp (OrderID) SELECT OrderID FROM orderheader; A WHILE loop can then select the smallest remaining OrderID from orderheadertemp and continue until no ID remains. The table is available to queries in the current connection and is dropped when that connection closes. section 22.3.2
A temporary table is not automatically the best choice. For small data sets, a subquery may be sufficient; for larger data sets, testing can show whether materializing intermediate results and joining them improves performance. Temporary-table syntax also differs among relational database systems, so check the product documentation. section 23.2.4 section 22.3.2
DROP PROCEDURE GetTitleTotalQuantitySoldListPrice;
DELIMITER //
CREATE PROCEDURE GetTitleTotalQuantitySoldListPrice(
OUT _TotalQuantitySold int
)
BEGIN
DECLARE _OrderID int;
SET _TotalQuantitySold = 0;
CREATE TEMPORARY TABLE orderheadertemp (OrderID int);
INSERT orderheadertemp (OrderID)
SELECT OrderID
FROM orderheader;
SET _OrderID = (SELECT MIN(OrderID) FROM orderheadertemp);
WHILE _OrderID IS NOT NULL DO
SET _TotalQuantitySold = _TotalQuantitySold +
(SELECT COALESCE(SUM(Quantity),0)
FROM title t
INNER JOIN orderitem oi
ON t.TitleID = oi.TitleID
AND t.Price = oi.ItemPrice
WHERE oi.OrderID = _OrderID
);
SET _OrderID =
(SELECT MIN(OrderID)
FROM orderheadertemp
WHERE OrderID > _OrderID);
END WHILE;
END //
DELIMITER ;
Browse a complete MySQL stored procedure that creates orderheadertemp, fills it from orderheader, and iterates through the temporary table. section 22.3.2
