mysql - 使用过程和触发器更新表

Question

我有一个数据库，存储一些有关电视剧的信息。

此数据库有一个名为 Stigione 的表，其中包含 Anno (YEAR) 列。

我需要为 Stigione 的每一行声明一个变量 differenza 来存储 Anno 和 YEAR( GETDATE())，并且如果 differenza 的值为 < 10:

UPDATE Stagione.Prezzo = Stagione.Prezzo * 0.9

我不知道如何结合触发器和过程来实现这一点，这可能吗？

我使用 phpmyadmin 10.1.13-MariaDB

编辑，现在工作:

DELIMITER |
CREATE PROCEDURE Promoz (IN an YEAR)
BEGIN
DECLARE differenza YEAR;
UPDATE stagione
SET Prezzo = prezzo*0.9
WHERE (an-Anno > 0) AND (an-Anno < 10);
UPDATE stagione
SET Prezzo = 0
WHERE (an-Anno > 10);
END |
DELIMITER ;

Answer 1

如果您现在更新记录，您将获得所需的结果，但只是现在。因为如果三年后查询相同的数据，您仍然会找到 Prezzo 乘以 0.9 的记录，但同时 Anno 大于 10几年前。

相反，您应该保留数据原样，并使用在查询时进行计算的数据库 View 。像这样的事情:

CREATE OR REPLACE VIEW Stagione_view AS
SELECT Anno, 
       YEAR(GETDATE()) - Anno AS differenza,
       (CASE WHEN YEAR(GETDATE()) - Anno < 10 
            THEN 0.9 ELSE 1 
       END) * Prezzo AS Prezzo
FROM   Stagione

现在，只需确保您始终查询 Stigione_view 而不是 Stagione，您就会获得所需的效果。

代码说明

CREATE OR REPLACE VIEW:这会在数据库中创建一个新对象: View 。就像表格一样，它有行和列。不同之处在于，它的数据是使用 SELECT 语句从其他表动态检索的。因此，当您查询 View 时，数据库实际上会在后台执行该 SELECT 语句。

YEAR(GETDATE()) - Anno AS Differencenza:这是年份差异的计算。因为它是在 View 中完成的，所以使用 GETDATE() 的当前值，这始终是准确的。

然后是神奇的部分:

CASE WHEN YEAR(GETDATE()) - Anno < 10 
     THEN 0.9 ELSE 1 
END

这将检查年份差异是否小于 10。如果是，则取值 0.9，否则取值 1。然后在更大的表达式中使用该结果:

       (CASE WHEN YEAR(GETDATE()) - Anno < 10 
            THEN 0.9 ELSE 1 
       END) * Prezzo AS Prezzo

...0.9 或 1 乘以 Prezzo。当然，如果第一个值为 1，那么这只会产生与 Prezzo 已有的值相同的值，但除此之外，您正在执行 0.9 * Prezzo。无论是哪一个，它都会在 View 中被赋予名称 Prezzo。因此，当您查询 View 时，您将看到调整后的 Prezzo 值:

SELECT * FROM Stagione_view;

改写问题后

every first day of the year, check the difference between Stagione.Anno and the current year, if this difference is lower than 10 and upper than 0 update Prezzo=Prezzo*0.9 else set Prezzo = 0

在这种情况下，在一年的第一天发出以下update语句:

UPDATE Stagiane
SET    Prezzo = CASE WHEN YEAR(GETDATE()) - Anno < 10 
            THEN 0.9 ELSE 0 
       END) * Prezzo
WHERE  YEAR(GETDATE()) > Anno

您可以安排此语句使用 CREATE EVENT statement 执行。 .

例如:

CREATE EVENT adjust_prezzo
ON SCHEDULE EVERY 1 YEAR
STARTS '2017-01-01 08:00'
DO 
    UPDATE Stagiane
    SET    Prezzo = CASE WHEN YEAR(GETDATE()) - Anno < 10 
                THEN 0.9 ELSE 0 
           END) * Prezzo
    WHERE  YEAR(GETDATE()) > Anno;