nls参数、环境变量以及函数起作用的顺序是:
instance级参数--->nls_lang--->session级参数--->函数。
C:>set nls_lang=SIMPLIFIED CHINESE_CHINA.ZHS16GBK
C:>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 7月 29 21:01:38 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> show parameter nls_date_format
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_date_format string yyyy/mm/dd hh24:mi:ss
--很明显实例级别参数nls_date_format没有启作用
SQL> select sysdate from dual;
SYSDATE
--------------
29-7月 -09
--==============================
--在注册表中清除nls_lang的值,这是我们发现实例级参数nls_date_format:格式
yyyy/mm/dd hh24:mi:ss开始发挥作用
SQL> exit
从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options 断开
C:>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jul 29 20:54:59 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
???:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select sysdate from dual;
SYSDATE
-------------------
2009/07/29 20:55:01
--====================================
--恢复注册表中的nls_lang设置:SIMPLIFIED CHINESE_CHINA.ZHS16GBK
SQL> exit
? Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options ??
C:>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 7月 29 20:55:21 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select sysdate from dual;
SYSDATE
--------------
29-7月 -09
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
会话已更改。
--显然session级别的nls_date_format发挥了作用
SQL> select sysdate from dual;
SYSDATE
-------------------
2009-07-29 20:55:55
--很显然函数发挥了作用
SQL> select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2009-07-29 09:10:01