</p> 当前时刻减去7天的时刻9 v( T8 t8 T0 }. l# n4 M
select sysdate - interval '7' day from dual m. ^) n0 B5 z/ f
当前时刻减去7月的时刻* n/ v4 b: J. j- l* h+ F& e
select sysdate,sysdate - interval '7' month from dual+ ~. c4 L1 A9 e4 _( }0 R5 ~
当前时刻减去7年的时刻
; n5 t' t; y1 ?! A* V0 W2 S select sysdate,sysdate - interval '7' year from dual
# Y& j! u. j4 j0 R& C% J3 d0 M 侍旧肃离乘以一个数字# n$ g3 F( O& d4 p2 n' {9 o, L
select sysdate,sysdate - 8 *interval '2' hour from dual
9 w7 W% g4 r3 s+ m* i$ {: c# l 2.日期到字符操作) l0 u# g7 p3 L, H! D, |$ u
select sysdate,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual) k, ~4 h$ x! M: S
select sysdate,to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual
' A3 \- [# e4 m5 d E- l select sysdate,to_char(sysdate,'yyyy-ddd hh:mi:ss') from dual# o( A' [: Z" M) n% r* C8 A
select sysdate,to_char(sysdate,'yyyy-mm iw-d hh:mi:ss') from dual3 `; c/ ]8 G/ I( w$ {% ^; T3 _
参考oracle的相关关文档(ORACLE901DOC/SERVER.901/A90125/SQL_ELEMENTS4.HTM#48515)
* Q/ V" ~( o2 O* ]/ V7 s% { 3. 字符到日期操作5 w5 @: i6 U2 m y2 f8 j: S3 C
select to_date('2003-10-17 21:15:37','yyyy-mm-dd hh24:mi:ss') from dual; \& I: l5 i O8 o; c6 \7 E
具体用法和膳缦沔的to_char差不多。
$ g+ X4 }( N; t3 M* p4 q/ @ 4. trunk/ ROUND函数的使用8 h3 |8 R H. s2 r& N Q, R
select trunc(sysdate ,'YEAR') from dual
( C ^! F! R' U select trunc(sysdate ) from dual$ N# j! G5 [7 V8 u' l' w) i8 T
select to_char(trunc(sysdate ,'YYYY'),'YYYY') from dual- R; P2 V9 }* ?; j# u9 n# I) u
5.oracle有毫秒级的数据类型- C5 Z" S7 h U4 ]& v/ A) X6 n" ^
--返回当前时刻 年月日小时分秒毫秒. M* L; C2 R4 G" G% s
select to_char(current_timestamp(5),'DD-MON-YYYY HH24:MI:SSxFF') from dual;
1 J- d: p' U9 `7 j: y# x: a --返回当前 时刻的秒毫秒,可以指定秒后面的精度(最年夜=9)& P/ a* p' ]# J5 ^0 U$ N
select to_char(current_timestamp(9),'MI:SSxFF') from dual;
& L, h) B2 b: w 6.计较轨范运行的时刻(ms)+ ~$ O* F% A w' L
declare
9 x L/ [1 p2 o/ O# @0 c type rc is ref cursor;: \3 u+ |# l3 f" f
l_rc rc;
" g* U2 y0 M# v& ` l_dummy all_objects.object_name%type;
$ Q' ?+ @* U4 ]7 J l_start number default dbms_utility.get_time;7 D1 {4 ~4 Z3 C" u% G
begin9 \2 ~# c( E$ m$ q% k! z
for I in 1 .. 1000
0 ]6 N/ ?3 _3 A5 A8 L loop; s8 G7 E, C" L4 L
open l_rc for2 D$ i- d- W9 ?
'select object_name from all_objects '||
# \: G5 h( y, f0 q8 u 'where object_id = ' || i;3 b, _6 g5 l* A" u8 L# n
fetch l_rc into l_dummy;
3 \# D6 T; `- X7 J close l_rc;( c2 I/ H( L/ X% G' {
* M, l6 i0 C1 n d- d/ e6 f end loop; |