delimiter $$
CREATE FUNCTION calcTaskMonitoringDesignate
(in_orderStatus int(4),
in_delayTime VARCHAR(20),
in_lineRequireDate datetime,
in_createTime datetime,
in_lineRequireType varchar(2),
in_editType varchar(2))
RETURNS bigint(16)
BEGIN
DECLARE p_result bigint(16);
DECLARE p_date bigint(16);
DECLARE p_type bigint(1);
DECLARE p_distanceDay bigint(20);
if in_orderStatus !=0 THEN
if in_orderStatus is null then
return(0);
end if;
return (in_delayTime);
end if;
if in_lineRequireDate is null then
return(0);
end if;
if in_lineRequireType ='1' then
if (in_editType = '1' or in_editType = '2' or in_editType = '5') THEN
set p_type = 1;
else
set p_type = 3;
end if;
else
set p_type = 2;
end if;
if p_type=1 THEN
set p_date = (unix_timestamp(concat(DATE_FORMAT(date_sub(in_lineRequireDate,interval 1 day),'%Y-%m-%d'),' 09:00:00')));
end if;
if p_type=2 or p_type=3 then
set p_distanceDay = to_days(DATE_FORMAT(in_lineRequireDate,'%Y%m%d')) - to_days(DATE_FORMAT(in_createTime,'%Y%m%d'));
if p_distanceDay = 0 THEN
set p_date = unix_timestamp(in_createTime) + (30*60);
end if;
if p_distanceDay = 1 THEN
IF (in_createTime > DATE_FORMAT(DATE_sub(NOW(),INTERVAL 1 DAY),'%Y-%m-%d 16:00:00')) THEN
return (0);
ELSE
set p_date = (unix_timestamp(concat(DATE_FORMAT(date_sub(in_lineRequireDate,interval 1 day),'%Y-%m-%d'),' 18:00:00')));
end if;
end if;
end if;
set p_result = (unix_timestamp(now()) - p_date)*1000;
if p_result <0 THEN
set p_result = NULL;
end if;
RETURN(p_result);
END$$
1.DELIMITER $$的含义
MySQL默认的行分隔符是分号,遇到分号,就执行前面的一段语句。脚本中有多行,我们希望不按照分号来一句一句执行,希望一段代码作为一个整体执行。在脚本最前面加上DELIMITER$$执行成功。
2.运行函数
select calcTaskMonitoringDesignate(0,NULL,NOW(),o.order_create_tm,2,3 )