时间戳

首页 > 解决方案 > 在 MySQL 中执行存储过程时出现错误（错误代码：1054）

mysql - 在 MySQL 中执行存储过程时出现错误（错误代码：1054）

问题描述

+--------------------+---------------+------+-----+---------+-------+
| Field              | Type          | Null | Key | Default | Extra |
+--------------------+---------------+------+-----+---------+-------+
| asset_id           | varchar(20)   | NO   | PRI | NULL    |       |
| lifetime           | tinyint(4)    | YES  |     | NULL    |       |
| depreciationRate   | decimal(18,0) | YES  |     | NULL    |       |
| condition          | varchar(10)   | NO   |     | NULL    |       |
| manufacturer       | varchar(20)   | YES  |     | NULL    |       |
| plant              | varchar(20)   | YES  |     | NULL    |       |
| serialNumber       | varchar(20)   | YES  |     | NULL    |       |
| depreciationMethod | varchar(20)   | NO   |     | NULL    |       |
| costOfPurchase     | decimal(18,0) | NO   |     | NULL    |       |
| serviceInterval    | decimal(18,0) | YES  |     | NULL    |       |
| state              | varchar(10)   | NO   |     | NULL    |       |
| assetType          | varchar(20)   | NO   |     | NULL    |       |
| purchaseDate       | date          | YES  |     | NULL    |       |
| warrantyCode       | varchar(20)   | YES  | MUL | NULL    |       |
| serviceDue         | date          | YES  |     | NULL    |       |
+--------------------+---------------+------+-----+---------+-------+

当我尝试在 MySQL 中使用在上表中创建的存储过程时，出现以下错误：

Error Code: 1054. Unknown column 'serviceInterval' in 'field list'

我创建的存储过程如下：

CREATE PROCEDURE `UpdateNextServiceDue`(IN assetId VARCHAR(20))
BEGIN
    DECLARE serviceInt DECIMAL(5,2);
    DECLARE performedDate date;
    DECLARE purchaseDate date;
    DECLARE nextDate date;
    SELECT `serviceInterval` INTO serviceInt FROM noncurrentasset WHERE asset_id = assetId;
    IF performedDate = null THEN
        SELECT `purchaseDate` INTO performedDate FROM noncurrentasset WHERE asset_id = assetId;
    ELSE
        SELECT `serviceDue` INTO performedDate FROM noncurrentasset WHERE asset_id = assetId;
    END IF;
    SET nextDate = DATE_ADD(performedDate, INTERVAL serviceInterval YEAR);
    UPDATE `noncurrentasset` SET serviceDue = nextDate WHERE asset_id = assetId;
END

很可能，错误必须存在于存储过程中。但是，我找不到它。有人可以告诉我这里出了什么问题，我该如何纠正。

标签： mysqlsqlstored-procedures

解决方案

你的变量是serviceInt.notserviceInterval

...
 SET nextDate = DATE_ADD(performedDate, INTERVAL serviceInt YEAR);
...

推荐阅读