ITPub博客

首页 > Linux操作系统 > Linux操作系统 > NLS_LANG

NLS_LANG

原创 Linux操作系统 作者:ylw66 时间:2009-09-17 17:27:08 0 删除 编辑

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

这是我刚开始不解的地方:为什么languageterritory是中文的,但是字符集是英文字符集呢。问题就在languageterritory有什么用?

 

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博客 ” ,链接:http://blog.itpub.net/22111412/viewspace-614919/,如需转载,请注明出处,否则将追究法律责任。

下一篇: LOB (1)
请登录后发表评论 登录
全部评论

注册时间:2009-07-29

  • 博文量
    57
  • 访问量
    94458