oracle下interval类型的用法总结

什么都不说，先上例子：
select date '2010-01-01' + interval '21' year from dual
select date '2010-01-01' + interval '123-2' year(4) to month from dual
select date '2010-01-01' + interval '100' month from dual
select date '2010-01-01' + interval '100' day(2) from dual
select date '2010-01-01' + interval '1 2' day to hour from dual
select date '2010-01-01' + interval '1 2:3' day to minute from dual
select date '2010-01-01' + interval '1 2:3:4' day to second from dual
select date '2010-01-01' + interval '1:2:3' hour from dual
select date '2010-01-01' + interval '1:2' hour to minute from dual
select date '2010-01-01' + interval '1:2:3' hour to second from dual
select date '2010-01-01' + interval '1:2' minute to second from dual

转换函数：
select numtoyminterval(13, 'month'), numtodsinterval(100, 'hour') from dual

PLSQL中的定义及调用：
declare
    v_dts interval day to second := interval '1 2:3:4' day to second;
    v_dd date := date '2010-01-01';
begin
    execute immediate 'alter session set nls_date_format = ''yyyy-mm-dd hh24:mi:ss''';
    select v_dd + v_dts into v_dd from dual;
    dbms_output.put_line(v_dd);
end;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12932950/viewspace-624507/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12932950/viewspace-624507/