mysql存储过程无法检索临时表

Question

我正在创建一个存储过程，如果检索到数据，我将检索该存储过程。我将第一个查询结果存储在一个临时表中，但是当我在临时表中检索数据时，它返回错误 #1064 - 你的 SQL 语法有错误；查看与您的 MariaDB 服务器版本对应的手册，了解在 'SELECT * FROM tempt 附近使用的正确语法
END' 在第 40 行。但是当我尝试查询时它工作正常。这是写的命令

CREATE PROCEDURE test()
BEGIN
CREATE TEMPORARY TABLE tempt
SELECT userInput.ap_id,
       b.accountName,
       b.accountNumber,
       userInput.date,
       p.payeeName,
       t.taxType,
       t.value businessTax,
       userInput.grossAmount,
       userInput.taxableBase,
       CAST(userInput.taxableBase * t.value as decimal(11,2)) as taxAmount,
       userInput.grossAmount - (CAST(userInput.taxableBase * t.value as decimal(11,2))) as netOfVAT,
       particulars,
       accounts,
       NOW(),
       1,
       e.type,
       e.value ewtTax,
       ewtBase
FROM
(
    SELECT CONCAT('AP-',YEAR(NOW()),MONTH(NOW()),DAY(NOW()),'-',(COUNT(*) + 1)) as ap_id,
           paccountNumber as accountNumber,
           pDate as date,
           ppayee as payee,
           pgross as grossAmount,
           ptaxBase as taxableBase,
           pparticulars as particulars,
           paccounts as accounts,
           pewtBase as ewtBase
    FROM ap_entry
) userInput
INNER JOIN bnks b ON userInput.accountNumber = b.accountNumber
INNER JOIN pyee p ON userInput.payee = p.payeeName
INNER JOIN txtype t ON p.taxType = t.taxType
INNER JOIN ewt e ON p.ewt = e.id

SELECT COUNT(*) FROM tempt
END

Answer 1

在您的查询中缺少分号。

DELIMITER $$

CREATE PROCEDURE test()
BEGIN
CREATE TEMPORARY TABLE tempt
SELECT userInput.ap_id,
       b.accountName,
       b.accountNumber,
       userInput.date,
       p.payeeName,
       t.taxType,
       t.value businessTax,
       userInput.grossAmount,
       userInput.taxableBase,
       CAST(userInput.taxableBase * t.value as decimal(11,2)) as taxAmount,
       userInput.grossAmount - (CAST(userInput.taxableBase * t.value as decimal(11,2))) as netOfVAT,
       particulars,
       accounts,
       NOW(),
       1,
       e.type,
       e.value ewtTax,
       ewtBase
FROM
(
    SELECT CONCAT('AP-',YEAR(NOW()),MONTH(NOW()),DAY(NOW()),'-',(COUNT(*) + 1)) as ap_id,
           paccountNumber as accountNumber,
           pDate as date,
           ppayee as payee,
           pgross as grossAmount,
           ptaxBase as taxableBase,
           pparticulars as particulars,
           paccounts as accounts,
           pewtBase as ewtBase
    FROM ap_entry
) userInput
INNER JOIN bnks b ON userInput.accountNumber = b.accountNumber
INNER JOIN pyee p ON userInput.payee = p.payeeName
INNER JOIN txtype t ON p.taxType = t.taxType
INNER JOIN ewt e ON p.ewt = e.id; -- semi colon missing

SELECT COUNT(*) FROM tempt; -- semi colon missing
END
$$
DELIMITER ; -- changed back to default