nls_lang很多人会误解，包括曾经的我在内
nls_lang其实是一个环境变量，对于unix环境来说，它是一个环境变量，对于windows中是一个注册表项
NLS_LANG = language_territory.charset
可以这样查
SQL> select * from v$nls_parameters;
PARAMETER VALUE
------------------------------ ------------------------------
NLS_LANGUAGE SIMPLIFIED CHINESE
NLS_TERRITORY CHINA
NLS_CURRENCY RMB
NLS_ISO_CURRENCY CHINA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT YYYY-MM-DD HH24:MI:SS
NLS_DATE_LANGUAGE SIMPLIFIED CHINESE
NLS_CHARACTERSET ZHS16GBK
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
PARAMETER VALUE
------------------------------ ------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY RMB
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
已选择19行。
也可以这样查：
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
看看另外一个环境
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
---------------------------------------------------------
SIMPLIFIED CHINESE_CHINA.WE8ISO8859P1
这是我刚开始不解的地方：为什么language和territory是中文的，但是字符集是英文字符集呢。问题就在language和territory有什么用？
language:定义了Oracle messages, sorting, day names, and month names等约定。比如AMERICAN, FRENCH, or GERMAN.
我们实验下：
SQL> SELECT ename, hiredate, ROUND(sal/8,2) salary FROM emp order by ename;
ENAME HIREDATE SALARY
---------- ------------------- ----------
adams 1987-05-23 00:00:00 137.5
allen 1981-02-20 00:00:00 200
blake 1981-05-01 00:00:00 356.25
clark 1981-06-09 00:00:00 306.25
ford 1981-12-03 00:00:00 375
james 1981-12-03 00:00:00 500
jones 1981-04-02 00:00:00 371.88
king 1981-11-17 00:00:00 625
martin 1981-09-28 00:00:00 156.25
miller 1982-01-23 00:00:00 162.5
scott 1987-04-19 00:00:00 375
ENAME HIREDATE SALARY
---------- ------------------- ----------
smith 1980-12-17 00:00:00 100
turner 1981-09-08 00:00:00 187.5
ward 1981-02-22 00:00:00 156.25
已选择14行。
SQL> alter session set nls_language='AMERICAN';
Session altered.
SQL> SELECT ename, hiredate, ROUND(sal/8,2) salary FROM emp order by ename;
ENAME HIREDATE SALARY
---------- ------------------- ----------
adams 1987-05-23 00:00:00 137.5
allen 1981-02-20 00:00:00 200
blake 1981-05-01 00:00:00 356.25
clark 1981-06-09 00:00:00 306.25
ford 1981-12-03 00:00:00 375
james 1981-12-03 00:00:00 500
jones 1981-04-02 00:00:00 371.88
king 1981-11-17 00:00:00 625
martin 1981-09-28 00:00:00 156.25
miller 1982-01-23 00:00:00 162.5
scott 1987-04-19 00:00:00 375
smith 1980-12-17 00:00:00 100
turner 1981-09-08 00:00:00 187.5
ward 1981-02-22 00:00:00 156.25
14 rows selected.
界面的提示信息变成英文了，sorting我理解是各中语言可能排序的依据是不一样的，比如按第一个字母
territory: :定义了default date, monetary, and numeric formats等约定,如AMERICA, FRANCE, or CANADA
再实验下：
SQL> alter session set nls_territory='AMERICA';
Session altered.
SQL> SELECT ename, hiredate, ROUND(sal/8,2) salary FROM emp;
ENAME HIREDATE SALARY
---------- --------- ----------
smith 17-DEC-80 100
allen 20-FEB-81 200
ward 22-FEB-81 156.25
jones 02-APR-81 371.88
martin 28-SEP-81 156.25
blake 01-MAY-81 356.25
clark 09-JUN-81 306.25
scott 19-APR-87 375
king 17-NOV-81 625
turner 08-SEP-81 187.5
adams 23-MAY-87 137.5
ENAME HIREDATE SALARY
---------- --------- ----------
james 03-DEC-81 500
ford 03-DEC-81 375
miller 23-JAN-82 162.5
14 rows selected.
注意日期都变了，之前是YYYY-MM-DD HH24:MI:SS，现在是DD-MON-RR，由于改变了territory，默认的日期格式相应改变了
再看：
SQL> select * from v$nls_parameters;
PARAMETER VALUE
------------------------------ ------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $ RMB变成了dollar
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_CHARACTERSET ZHS16GBK
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
PARAMETER VALUE
------------------------------ ------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
19 rows selected.
将nls_territory修改为FRANCE
SQL> alter session set nls_territory='FRANCE';
Session altered.
SQL> SELECT ename, hiredate, ROUND(sal/8,2) salary FROM emp order by ename;
ENAME HIREDATE SALARY
---------- -------- ----------
allen 20/02/81 200
blake 01/05/81 356,25 注意原分隔的点号改成了逗号
clark 09/06/81 306,25
ford 03/12/81 375
james 03/12/81 500
jones 02/04/81 371,88
king 17/11/81 625
martin 28/09/81 156,25
miller 23/01/82 162,5
scott 19/04/87 375
smith 17/12/80 100
ENAME HIREDATE SALARY
---------- -------- ----------
turner 08/09/81 187,5
ward 22/02/81 156,25
小米 23/05/87 137,5
14 rows selected.
charset：字符集相关的知识就多了
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/22111412/viewspace-614919/ ，如需转载，请注明出处，否则将追究法律责任。
