为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】:V8.1
【操作系统】:CENTOS
【CPU】:32
【问题描述】*:从ORACLE迁移到DM8上的一个函数编译报错,感觉像是
INTERVAL DAY TO SECOND 类型报错报错,虽然达梦手册明确支持这个数据类型。SQL如下
Create or Replace FUNCTION "ZFS_SYS"."GET_TIME_DIFF_MIN" (tmstmp_fm in TIMESTAMP, tmstmp_to in TIMESTAMP, prec in NUMBER)
RETURN NUMBER
IS
v_inteval interval day(9) to second(6);
min_diff NUMBER := 0;
BEGIN
v_inteval := tmstmp_to - tmstmp_fm;
SELECT
extract(day from v_inteval)*24*60+
extract(hour from v_inteval)*60+
extract(minute from v_inteval)+
round(extract(second from v_inteval)/60,prec) into min_diff
from dual;
RETURN min_diff;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;
可以用如下方式来实现:
Create or Replace FUNCTION "GET_TIME_DIFF_MIN" (tmstmp_fm in TIMESTAMP, tmstmp_to in TIMESTAMP, prec in NUMBER)
RETURN NUMBER
IS
v_inteval number;
min_diff NUMBER := 0;
BEGIN
v_inteval := tmstmp_to - tmstmp_fm;
select ROUND(TO_NUMBER(tmstmp_to-tmstmp_fm) * 24 * 60,prec)
into min_diff from dual;
RETURN min_diff;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;