Oracle 中的NLS

NLS： Nation Language Session

NLS_LANG: 語言別以及字符集

NLS_DATE_FORMAT: 日期的顯示格式

NLS_DATE_LANGUAGE: 日期的語言別

-- 查看當前SESSION NLS_LANG
SYS@HUIYI>select * from nls_session_parameters where parameter='NLS_LANGUAGE';

PARAMETER            VALUE
-------------------- ------------------------------
NLS_LANGUAGE         TRADITIONAL CHINESE

-- 查看數据庫NLS_LANG
SYS@HUIYI>select * from nls_database_parameters
  2  where parameter in('NLS_LANGUAGE','NLS_CHARACTERSET');

PARAMETER            VALUE
-------------------- ------------------------------
NLS_LANGUAGE         AMERICAN
NLS_CHARACTERSET     ZHT16MSWIN950

-- TRADITIONAL CHINESE
SYS@HUIYI>select '測試' col from dual;

COL
--------
測試

SYS@HUIYI>d
SP2-0042: 未知的命令 "d" － 此行的剩餘部份被略過不予處理

-- 修改提示語言為英文
-- 英文提示，中文正常顯示
E:>set nls_lang=american_america.zht16mswin950

E:>sqlplus "/ as sysdba"

SYS@HUIYI>d
SP2-0042: unknown command "d" - rest of line ignored.

SYS@HUIYI>select '測試' col from dual;

COL
----
測試

-- 設置成其它字符集(和數据庫不一致)
-- 文字將不能正常顯示
E:>set nls_lang=AMERICAN_AMERICA.ZHS16GBK

E:>sqlplus "/ as sysdba"

SYS@HUIYI>select '測試' col from dual;

COL
--------
測?

SYS@HUIYI>select sysdate from dual;

SYSDATE
----------
06-3月 -07

SYS@HUIYI>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

已更改階段作業.

SYS@HUIYI>select sysdate from dual;

SYSDATE
-------------------
2007-03-06 17:08:37

SYS@HUIYI>select to_char(sysdate,'Day') col from dual;

COL
------------
星期二

SYS@HUIYI>select to_char(sysdate,'Day','nls_date_language=american') col from dual;

COL
------------------
Tuesday

SYS@HUIYI>alter session set nls_date_language=american;

已更改階段作業.

SYS@HUIYI>select to_char(sysdate,'Day') col from dual;

COL
------------------
Tuesday

SYS@HUIYI>alter session set nls_date_language='TRADITIONAL CHINESE';

已更改階段作業.

SYS@HUIYI>select to_char(sysdate,'Day') col from dual;

COL
------------
星期二

 -- 格式化指定字符

  
  

SYS@HUIYI>select to_char(sysdate,'yyyy"(year)"mm"(month)"dd"(day)"') col from dual;

COL
----------------------------------------------------
2007(year)03(month)14(day)

  
  


 -- interval

  
  

SYS@HUIYI>select trunc(sysdate)+1/24/60 col from dual;

COL
------------------
14-Mar-07 00:01:00

SYS@HUIYI>select trunc(sysdate)+(interval '1' minute) col from dual;

COL
------------------
14-Mar-07 00:01:00

SYS@HUIYI>select trunc(sysdate) col1,trunc(sysdate)+(interval '01:02:03' hour to second) col2 from dual;

COL1               COL2
------------------ ------------------
14-Mar-07 00:00:00 14-Mar-07 01:02:03

SYS@HUIYI>select trunc(sysdate) col1,trunc(sysdate)+(interval '01:02' hour to minute) col2 from dual;

COL1               COL2
------------------ ------------------