本文介绍了如何检查变量是否为 NULL,然后使用 MySQL 存储过程设置它?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!
问题描述
我有一个 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
是一个 9.4.用户定义的变量和last_run_time datetime
一个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 存储过程设置它?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!
本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!