Navicate和DataGrip中执行的创建函数语句
CREATE FUNCTION TimeSpenTODay
(TimeString VARCHAR(20),unit VARCHAR(20))
RETURNS FLOAT
BEGIN
DECLARE hours float;
DECLARE mins float;
DECLARE seconds float;
DECLARE result FLOAT;
set unit='DAY';
SET hours=convert((LEFT(TimeString,3)),float);
SET mins=convert((SUBSTRING(TimeString,instr(':',TimeString)+1,2)),float);
SET seconds=convert((RIGHT(TimeString,2)),float);
IF UPPER(unit)='DAY' THEN
SET result= hours/24+mins/1440+seconds/86400;
ELSEIF UPPER(unit)='HOUR' THEN
SET result= hours+mins/60+seconds/3600;
ELSE
SET result= hours+mins/60+seconds/3600;
END IF;
RETURN ROUND(result,4);
END
Workbench执行语句如下
delimiter $$
CREATE FUNCTION TimeSpenTODay
(TimeString VARCHAR(20),unit VARCHAR(20))
RETURNS FLOAT
BEGIN
DECLARE hours float;
DECLARE mins float;
DECLARE seconds float;
DECLARE result FLOAT;
set unit='DAY';
SET hours=convert((LEFT(TimeString,3)),float);
SET mins=convert((SUBSTRING(TimeString,instr(':',TimeString)+1,2)),float);
SET seconds=convert((RIGHT(TimeString,2)),float);
IF UPPER(unit)='DAY' THEN
SET result= hours/24+mins/1440+seconds/86400;
ELSEIF UPPER(unit)='HOUR' THEN
SET result= hours+mins/60+seconds/3600;
ELSE
SET result= hours+mins/60+seconds/3600;
END IF;
RETURN ROUND(result,4);
END
$$
delimiter ;
**总结:
workerbench执行要在开头加delimiter $$
结尾加$$ delimiter ;