oracle如何插入timestamp数据

SQL> select * from t_timestamp;
 
A
--------------------------------------------------------------------------------
14-11月-12 10.46.24.195000 上午
 
SQL> show parameter time
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
cursor_space_for_time                boolean     FALSE
ddl_lock_timeout                     integer     0
distributed_lock_timeout             integer     60
log_checkpoint_timeout               integer     1800
nls_time_format                      string     
nls_time_tz_format                   string     
nls_timestamp_format                 string     
nls_timestamp_tz_format              string     
parallel_min_time_threshold          string      AUTO
resumable_timeout                    integer     0
timed_os_statistics                  integer     0
timed_statistics                     boolean     TRUE
 
SQL> alter session set nls_time_format='yyyy-mm-dd hh24:mi:ss.ff';
 
alter session set nls_time_format='yyyy-mm-dd hh24:mi:ss.ff'
 
ORA-01821: 日期格式无法识别

SQL> conn sys/system@orcl as sysdba
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as SYS
 
SQL> show parameter nls
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_calendar                         string     
nls_comp                             string      BINARY
nls_currency                         string     
nls_date_format                      string     
nls_date_language                    string     
nls_dual_currency                    string     
nls_iso_currency                     string     
nls_language                         string      AMERICAN
nls_length_semantics                 string      BYTE
nls_nchar_conv_excp                  string      FALSE
nls_numeric_characters               string     
nls_sort                             string     
nls_territory                        string      AMERICA
nls_time_format                      string     
nls_time_tz_format                   string     
nls_timestamp_format                 string     
nls_timestamp_tz_format              string   

SQL> alter system set nls_date_format='yyyy-mm-dd' scope=spfile;
 
System altered

sql> shutdown immediate
sql>startup

SQL> show parameter nls
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_calendar                         string     
nls_comp                             string      BINARY
nls_currency                         string     
nls_date_format                      string      yyyy-mm-dd

SQL> conn scott/system@orcl
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott

SQL> alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss.ff';
 
Session altered

SQL> select * from t_timestamp;
 
A
--------------------------------------------------------------------------------
2012-11-14 10:46:24.195000
 
SQL> insert into t_timestamp values('2012-12-01 09:21:13.1');
 
1 row inserted
 
 
 
 SQL> select * from t_timestamp;
 
A
--------------------------------------------------------------------------------
2012-11-14 10:46:24.195000
2012-12-01 09:21:13.100000
 
 
小结；1,nls_timestamp_format与nls_date_format关联，必须先配置后者
        不然会报ORA-01821: 日期格式无法识别
      2,timestamp数据可以用to_timestamp自定义格式插入数据，道理
      　和to_date一样

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

转载于:http://blog.itpub.net/9240380/viewspace-749182/