时间间隔函数INTERVAL如何传参数

显示全部楼层 · 2009-9-27 10:21:21

我要求某个时间提前某个小时后的时间，过程如下：
PROCEDURE GET_DATE(P_ASM_CUR_START_DATE DATE,

P_ADJUST_TIMENUMBER,

P_NEW_DATE OUT DATE) IS
V_NEW_DATE DATE;
BEGIN
SELECT P_ASM_CUR_START_DATE - INTERVAL '&P_ADJUST_TIME' HOUR INTO P_NEW_DATE
FROM DUAL;
END;
可是P_ADJUST_TIME这个参数怎么写？

千问 · 2009-9-27 10:21:21

过程中不能用替换变量
sql语句是可以的
SQL> select sysdate - INTERVAL '&P_ADJUST_TIME' HOUR from dual;
Enter value for p_adjust_time: 24
old 1: select sysdate - INTERVAL '&P_ADJUST_TIME' HOUR from dual
new 1: select sysdate - INTERVAL '24' HOUR from dual
SYSDATE-INTERV
--------------
25-9月 -09
Elapsed: 00:00:00.01

千问 · 2009-9-27 10:21:21

SQL> var x number;
SQL> exec :x:=24;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.40

SQL> select sysdate - numtodsinterval(:x ,'HOUR') from dual;
SYSDATE-NUMTOD
--------------
25-9月 -09
Elapsed: 00:00:00.01

千问 · 2009-9-27 10:21:21

如果只是DATE类型，用P_ASM_CUR_START_DATE - P_ADJUST_TIME/24 就行了.