如何检查变量是否为NULL，然后使用MySQL存储过程对其进行设置? [英] How to check if a variable is NULL, then set it with a MySQL stored procedure?

问题描述

我有一个MySQL存储过程，可以从表中找到最大值.

I have a MySQL stored procedure where I find the max value from a table.

如果没有值，我想将变量设置为昨天的日期.

If there is no value I want to set the variable to yesterday's date.

DECLARE current_procedure_name CHAR(60) DEFAULT 'accounts_general';
DECLARE last_run_time datetime DEFAULT NULL;
DECLARE current_run_time datetime DEFAULT NOW();

-- Define the last run time
SET last_run_time := (SELECT MAX(runtime) 
FROM dynamo.runtimes WHERE procedure_name = @current_procedure_name);

-- if there is no last run time found then use yesterday as starting point
IF(@last_run_time IS NULL) THEN
    SET last_run_time := DATE_SUB( NOW(), INTERVAL 1 DAY);
END IF;

SELECT @last_run_time;

问题在于@last_run_time始终为NULL.

The problem is that @last_run_time is always NULL.

由于某些原因未执行以下代码

The following code is not being executed for some reason

IF(last_run_time IS NULL) THEN
    SET last_run_time := DATE_SUB( NOW(), INTERVAL 1 DAY);
END IF;

如何正确设置变量@last_run_time?

推荐答案

@last_run_time是 13.6.4.1.局部变量DECLARE语法是不同的变量.

@last_run_time is a 9.4. User-Defined Variables and last_run_time datetime one 13.6.4.1. Local Variable DECLARE Syntax, are different variables.

尝试:SELECT last_run_time;

更新

示例:

/* CODE FOR DEMONSTRATION PURPOSES */
DELIMITER $$

CREATE PROCEDURE `sp_test`()
BEGIN
    DECLARE current_procedure_name CHAR(60) DEFAULT 'accounts_general';
    DECLARE last_run_time DATETIME DEFAULT NULL;
    DECLARE current_run_time DATETIME DEFAULT NOW();

    -- Define the last run time
    SET last_run_time := (SELECT MAX(runtime) FROM dynamo.runtimes WHERE procedure_name = current_procedure_name);

    -- if there is no last run time found then use yesterday as starting point
    IF(last_run_time IS NULL) THEN
        SET last_run_time := DATE_SUB(NOW(), INTERVAL 1 DAY);
    END IF;

    SELECT last_run_time;

    -- Insert variables in table2
    INSERT INTO table2 (col0, col1, col2) VALUES (current_procedure_name, last_run_time, current_run_time);
END$$

DELIMITER ;

这篇关于如何检查变量是否为NULL，然后使用MySQL存储过程对其进行设置?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！