Oracle数据库多语言支持

Oracle数据库多语言支持

一、NLS Parameter

Oracle called globalization support National Language Support (NLS),

可以从Session, Instance, Database三个级别来对NLS参数进行设置。
Session级别的设置覆盖Instance级别的设置，Instance级别的设置覆盖Database级别的设置。
相应地，Oracle提供NLS_SESSION_PARAMETERS，NLS_INSTANCE_PARAMETERS，NLS_DATABASE_PARAMETERS这3个视图来查看NLS参数。
此外，还可以在SQL函数中指定NLS参数，这些函数称作locale-dependent。

A) NLS_SESSION_PARAMETERS
此视图中的参数可以通过"ALTER SESSION "语句来进行设置。
ALTER SESSION SET parameter_name=parameter_value [ parameter_name=parameter_value ]... ;
如果没有通过"ALTER SESSION "语句设置过，那么使用NLS_LANG环境变量的定义值。(NLS_LANG环境变量下面详细说明)
如果NLS_LANG也没有被定义，那么沿用NLS_INSTANCE_PARAMETERS中的设定值。
这些参数影响到客户端的错误信息显示语言；日期，数字，货币等的显示格式；排序结果等。

B) NLS_INSTANCE_PARAMETERS
此视图中中的参数设定是数据库启动时参照init.ora中的设定而来。 可以通过"ALTER SYSTEM"语句更改。
NLS_LANGUAGE设定决定了alert.log和trace files中的Server错误信息的语言。

C) NLS_DATABASE_PARAMETERS
此视图中的参数是创建数据库时决定的。数据库创建好以后不能改动，不要通过修改系统表格来修改设定。
如果INSTANCE和SESSION参数没有设定，那么默认使用这里的参数。
NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET 参数是创建数据库时决定的，不能被INSTANCE和SESSION参数覆盖，不要在创建完数据库后试图修改它们，这种尝试会损坏数据库。(关于这2个参数下面有详细说明)

注1：NLS_DATABASE_PARAMETERS视图等价于SELECT name,value$ from sys.props$ where name like '%NLS%'。
注2：V$NLS_PARAMETERS视图给出了当前的SESSION参数以及NLS_DATABASE_PARAMETERS视图中的2个CHARACTERSET参数。
注3：select userenv ('language') from dual; select sys_context('userenv','language') from dual;这2个语句执行结果等同于
          <当前Session的Language>_<当前Session的Territory> .<数据库的字符集NLS_CHARACTERSET>，
          这个结果貌似NLS_LANG，但是实际上不是，NLS_LANG定义的是客户端的字符集。
注4：select userenv ('lang') from dual; 返回的是Session参数NLS_LANGUAGE的简写。
注5：V$NLS_VALID_VALUES视图返回NLS_LANGUAGE, NLS_SORT, NLS_TERRITORY, NLS_CHARACTERSET的有效取值。

D) Locale-dependent SQL Function
SQL functions whose behavior depends on the values of NLS parameters are called locale-dependent.
依赖于NLS参数的SQL函数包括：
?TO_CHAR
?TO_DATE
?TO_NUMBER
?NLS_UPPER
?NLS_LOWER
?NLS_INITCAP
?NLSSORT
In all of the preceding functions, you can specify these NLS parameters:以上函数中可以指定的参数包括：
?NLS_DATE_FORMAT
?NLS_DATE_LANGUAGE
?NLS_NUMERIC_CHARACTERS
?NLS_CURRENCY
?NLS_ISO_CURRENCY
?NLS_DUAL_CURRENCY
?NLS_CALENDAR
?NLS_SORT
此外对于NLSSORT函数，还可以指定以下参数。
?NLS_LANGUAGE
?NLS_TERRITORY
按照'parameter=value' ['parameter=value']...的格式来指定参数。
例如，
select to_char(sysdate,'DD-MON-YYYY','NLS_DATE_LANGUAGE=''SIMPLIFIED CHINESE''') from dual;结果返回 "04-8月 -2011"
select * from employees order by nlssort(name,'NLS_SORT=SCHINESE_PINYIN_M');

E) NLS_LANG
NLS_LANG这个参数比较特殊，它是通过环境变量(UNIX)或者注册表(WINDOWS)定义的。它分三部分，格式如下：
    NLS_LANG = <Language>_<Territory>.<Charset>
<Language>部分设定了SESSION的NLS_LANGUAGE参数；
_<Territory>部分设定了SESSION的NLS_TERRITORY参数；
.<Charset>部分设定了CLIENT的字符集。它会影响到如何转换客户端的字符集到数据库字符集。
这三部分可以只定义任意一个/几个部分。
如果只有_<Territory>部分，<Language>默认等于AMERICAN；
如果只有<Language>部分，_<Territory>的值根据<Language>的值来决定；
如果既没有_<Territory>部分也没有<Language>部分，这2部分认等于AMERICAN_AMERICA；
如果没有定义NLS_LANG，那么默认值<Language>_<Territory>.US7ASCII，其中<Language>_<Territory>部分根据NLS_INSTANCE_PARAMETERS中的值来决定。

NLS_LANG参数不能通过ALTER SESSION或者ALTER SYSTEM来更改(但是可以更改NLS_LANGUAGE和NLS_TERRITORY)，也不能定义在init.ora中。
数据库创建的时候定义此参数也不影响数据库的设定，包括不会影响数据库的NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET 参数。
NLS_LANG参数的客户端字符集定义部分<Charset>不能通过查询任何系统表格或视图得到。
(感觉实际上这个参数应该只会影响到客户端，也就是NLS_SESSION_PARAMETERS)

NLS_LANG直接设定了NLS_LANGUAGE和NLS_TERRITORY参数，还间接设定了其他的NLS参数。
NLS_DATE_LANGUAGE 和 NLS_SORT 根据NLS_LANGUAGE决定取值；
NLS_CURRENCY, NLS_DUAL_CURRENCY, NLS_ISO_CURRENCY, NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, NLS_TIMESTAMP_TZ_FORMAT, NLS_NUMERIC_CHARACTERS 根据NLS_TERRITORY决定取值。

注1：NLS_LANG在WINDOWS注册表中的位置：HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_xxx

There are 3 levels at which you can set NLS parameters: Database, Instance and Session.
If a parameter is defined at more than one level then the rules on which one takes precedence are quite straightforward:
1. NLS database settings are superseded by NLS instance settings
2. NLS database & NLS instance settings are superseded by NLS session settings

Applications can check the session, instance, and database NLS parameters by querying the following data dictionary views:
A) NLS_SESSION_PARAMETERS
NLS_SESSION_PARAMETERS shows the NLS parameters and their values for the session that is querying the view.
It does not show information about the character set.
The values of NLS parameters set by "ALTER SESSION ". If there is no explicit "ALTER SESSION " statement done then it reflects the setting of the corresponding NLS parameter on the client derived from the NLS_LANG variable.

The NLS_LANG parameter has three components: language, territory, and character set. Specify it in the following format, including the punctuation:
NLS_LANG = <Language>_<Territory>.<Charset>
If NLS_LANG is specified with only the <Territory> part then AMERICAN is used as default <Language>.
If NLS_LANG is specified with only the <Language> part then the <Territory> defaults to a setting based on <Language>.
If NLS_LANG is specified without the <Language>_<Territory> part then the <Language>_<Territory> part defaults to AMERICAN_AMERICA.
If the NLS_LANG is not set at all, then it defaults to <Language>_<Territory>.US7ASCII and the values for the <Language>_<Territory> part used are the ones found in
NLS_INSTANCE_PARAMETERS.

The language and territory components of the NLS_LANG parameter determine the default values for other detailed NLS parameters, such as date format, numeric characters, and linguistic sorting.
If NLS_DATE_LANGUAGE or NLS_SORT are not set then they are derived from NLS_LANGUAGE.
If NLS_CURRENCY, NLS_DUAL_CURRENCY, NLS_ISO_CURRENCY, NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, NLS_TIMESTAMP_TZ_FORMAT, NLS_NUMERIC_CHARACTERS are not set then they are derived from NLS_TERRITORY

* NLS_LANG cannot be changed by ALTER SESSION, NLS_LANGUAGE and NLS_TERRITORY can.
* The <clients characterset> part of NLS_LANG is NOT shown in any system table or view.
* On Windows you have two possible options, normally the NLS_LANG is set in the registry, but it can also be set in the environment, however this is not often done and generally not recommended to do so. The value in the environment takes precedence over the value in the registry and is used for ALL Oracle_Homes on the server if defined as a system environment variable.
* Oracle strongly recommends that you set the NLS_LANG on the client at least to NLS_LANG=.<clients characterset>

B) NLS_INSTANCE_PARAMETERS
NLS_INSTANCE_PARAMETERS shows the current NLS instance parameters that have been explicitly set and the values of the NLS instance parameters.
These are the settings in the init.ora of the database at the moment that the database was started or set through ALTER SYSTEM.
If the parameter is not explicitly set in the init.ora or defined by ALTER SYSTEM then its value is NOT derived from a "higher" parameter (we are talking about parameters like NLS_SORT that derive a default from NLS_LANGUAGE in NLS_SESSION_PARAMETERS, this is NOT the case for NLS_INSTANCE_PARAMETERS)
* NLS_LANG is not an init.ora parameter; NLS_LANGUAGE and NLS_TERRITORY are so you need to set NLS_LANGUAGE and NLS_TERRITORY separately.
* You cannot define the <clients characterset> or NLS_LANG in the init.ora.The client characterset is defined by the NLS_LANG on the client OS (see above).
* You cannot define the database characterset in the init.ora. The database characterset is defined by the "Create Database" command.
* These settings take precedence above the NLS_DATABASE_PARAMETERS.
* These values are used for the NLS_SESSION_PARAMETERS if the client the NLS_LANG is NOT set.
* The NLS_LANGUAGE in the instance parameters also declares the language for the server error messages in alert.log and in trace files.

C) NLS_DATABASE_PARAMETERS
NLS_DATABASE_PARAMETERS shows the values of the NLS parameters for the database. The values are stored in the database.
Defaults to AMERICAN_AMERICA if there are no parameters explicitly set in the init.ora during database creation time. If there is parameters set in the init.ora during database creation you see them here. There is no way to change these after the database creation. Do NOT attempt to update system tables to bypass these settings! These settings are used to give the database a default if the INSTANCE and SESSION parameters are not set.
The database (national) character set NLS_(NCHAR)_CHARACTERSET) is defined by the "Create Database" command.
* The NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET parameters cannot be overridden by instance or session parameters.
They are defined by the value specified in the "CREATE DATABASE command and are not intended to be changed afterwards dynamically. Do NOT update system tables to change the character set. This can corrupt your database and potentially make it impossible to open the database again.
* Setting the NLS_LANG during the creation of the database does not influence the NLS_DATABASE_PARAMETERS.
* The NLS_LANG set during the database creation has NO impact on the database National Characterset.