Oracle数据库全球化

一、自定义数据库和各个会话的语言相关行为

1.1 全球化功能

字符集
Oracle数据库软件有自己的字符集，不依赖于主机操作系统的字符集。
Oracle支持数百种字符集，我们在建立数据库时应该充分考虑实际业务需求，从而选择合适的字符集。
比如，我们确定数据库只是中国人来用，那么就可以选择ZHS16GBK字符集。语言支持
查询v$nls_valid_values视图:--查询数据库语言支持情况select * from v$nls_valid_values where parameter="LANGUAGE";可以看到结果中支持很多语言，比如SIMPLIFIED CHINESE，TRADITIONAL CHINESE，JAPANESE等。Oracle默认排序规则是BINARY，同时Oracle还提供了很多排序规则供用户选择：--查询数据库排序支持情况select * from v$nls_valid_values where parameter="SORT";地区支持
查询数据库支持的地区：--查询数据库地区支持情况select * from v$nls_valid_values where parameter="TERRITORY";结果可以看到很多支持的地区，比如CHINA，CANADA，FRANCH等。选中某个地区可以默认设置许多全球化特性。

1.2 使用全球化支持功能

可以在任意级别上指定全球化（优先级由低->高）：数据库、实例、客户端环境、会话、语句。

1.2.1 选择字符集

前面说过，如果我们确定数据库只是中国人来用，那么就可以选择ZHS16GBK字符集。
同样，如果要存储法语和西班牙语，就可以选择Western European字符集；如果要存储俄语或者捷克语数据，就应该选择Eastern European字符集。数据库字符集用于存储varchar2、clob、char、long数据类型列中的所有数据。
8.0版本后，Oracle引入了National Character Set，即数据库的第二个字符集，它用于存储nvarchar2, nclob, nchar数据类型的数据。
9i版本后，Oracle的National Character Set只能为Unicode字符集，有两种选择AL16UTF16和UTF8.选择哪个要考虑nvarchar2数据类型中实际要存储的数据类型，如果大部分数据可以表示为一个字节，选择UTF8；反之选择AL16UTF16。如果实际应用表中用不到nvarchar的数据类型，这个选择没有意义。

1.2.2 改变数据库字符集

在9i版本之前，无法改变数据库字符集，从9i版本开始，虽然可以改字符集，但无法保证这种方法有效。
可以使用csscan和lcsscan工具来帮助决定字符集是否能够修改（必须首先运行csminst.sql脚本）。
确定可以无损改变数据库字符集后，就可以通过执行alter database character set..命令来完成字符集的修改。
命令本身要求目标字符集必须是原始字符集的一个超集，但不能保证不存在问题。

1.2.3 数据库中的全球化

数据库级别的全球化设置。

--查询NLS_DATABASE_PARAMETERS视图select * from nls_database_parameters;PARAMETERVALUE------------------------------ --------------------------------------------------------------------------------NLS_LANGUAGE AMERICANNLS_TERRITORYAMERICANLS_CURRENCY $NLS_ISO_CURRENCY AMERICANLS_NUMERIC_CHARACTERS .,NLS_CHARACTERSET ZHS16GBKNLS_CALENDAR GREGORIANNLS_DATE_FORMATDD-MON-RRNLS_DATE_LANGUAGEAMERICANNLS_SORT BINARYNLS_TIME_FORMATHH.MI.SSXFF AMNLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AMNLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZRNLS_TIMESTAMP_TZ_FORMATDD-MON-RR HH.MI.SSXFF AM TZRNLS_DUAL_CURRENCY$NLS_COMP BINARYNLS_LENGTH_SEMANTICS BYTENLS_NCHAR_CONV_EXCPFALSENLS_NCHAR_CHARACTERSET AL16UTF16NLS_RDBMS_VERSION11.2.0.4.020 rows selected.

1.2.4 实例级别的全球化

实例级别的全球化设置，实例参数设置会重写数据库设置。

--查询nls_instance_parameters视图select * from nls_instance_parametersPARAMETERVALUE------------------------------ ------------------------------NLS_LANGUAGE AMERICANNLS_TERRITORYAMERICANLS_SORTNLS_DATE_LANGUAGENLS_DATE_FORMATNLS_CURRENCYNLS_NUMERIC_CHARACTERSNLS_ISO_CURRENCYNLS_CALENDARNLS_TIME_FORMATNLS_TIMESTAMP_FORMATNLS_TIME_TZ_FORMATNLS_TIMESTAMP_TZ_FORMATNLS_DUAL_CURRENCYNLS_COMP BINARYNLS_LENGTH_SEMANTICS BYTENLS_NCHAR_CONV_EXCPFALSE17 rows selected.

1.2.5 客户端环境设置

一般我们项目常用的在客户端环境变量中配置的两个参数：NLS_LANGNLS_DATE_FORMAT/bin/bash环境：export NLS_LANG="simplified chinese_china.ZHS16GBK"export NLS_DATE_FORMAT="YYYY-MM-DD HH24:Mi:SS"/bin/csh环境：setenv NLS_LANG "simplified chinese_china.ZHS16GBK"setenv NLS_DATE_FORMAT "YYYY-MM-DD HH24:Mi:SS"注意：DBA不能控制终端用户对NLS_LANG环境变量的设置。如果应用程序容易受到全球化的影响，那么开发人员就应该考虑到这个问题，同时在会话内控制全球化设置。
服务器端与客户端全球化设置之间的转换由Oracle Net完成。如果客户端与服务器使用不同的字符集，那么就可能无法完成数据的转换。

1.2.6 会话级别的全球化设置

会话级别的规范优先于服务器端数据库与实例级别的设置，而且会重写用户使用环境变量配置其会话的各种尝试。
设定nls_date_format

--第一种方法alter session set nls_date_format="YYYY-MM-DD HH24:Mi:SS";--第二种方法exec dbms_session.set_nls（"nls_date_format","""dd.mm.yyyy"""）;--实验验证：SQL> alter session set nls_date_format="YYYY-MM-DD";Session altered.SQL> select sysdate from dual;SYSDATE----------2016-02-03SQL> exec dbms_session.set_nls（"nls_date_format","""dd.mm.yyyy"""）;PL/SQL procedure successfully completed.SQL>select sysdate from dual;SYSDATE----------03.02.2016

查看v$nls_parameters或者nls_session_parameters视图查看当前作用于会话的全球化设置：

select * from v$nls_parameters;select * from nls_session_parameters;SQL> select * from v$nls_parameters;PARAMETERVALUE------------------------------ ------------------------------NLS_LANGUAGE SIMPLIFIED CHINESENLS_TERRITORYCHINANLS_CURRENCY ￥NLS_ISO_CURRENCY CHINANLS_NUMERIC_CHARACTERS .,NLS_CALENDAR GREGORIANNLS_DATE_FORMATYYYY-MM-DD HH24:Mi:SSNLS_DATE_LANGUAGESIMPLIFIED CHINESENLS_CHARACTERSET ZHS16GBKNLS_SORT BINARYNLS_TIME_FORMATHH.MI.SSXFF AMNLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AMNLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZRNLS_TIMESTAMP_TZ_FORMATDD-MON-RR HH.MI.SSXFF AM TZRNLS_DUAL_CURRENCY￥NLS_NCHAR_CHARACTERSET AL16UTF16NLS_COMP BINARYNLS_LENGTH_SEMANTICS BYTENLS_NCHAR_CONV_EXCPFALSE19 rows selected.--查询nls_session_parameters，没有字符集信息SQL> select * from nls_session_parameters;PARAMETERVALUE------------------------------ ------------------------------NLS_LANGUAGE SIMPLIFIED CHINESENLS_TERRITORYCHINANLS_CURRENCY ￥NLS_ISO_CURRENCY CHINANLS_NUMERIC_CHARACTERS .,NLS_CALENDAR GREGORIANNLS_DATE_FORMATYYYY-MM-DD HH24:Mi:SSNLS_DATE_LANGUAGESIMPLIFIED CHINESENLS_SORT BINARYNLS_TIME_FORMATHH.MI.SSXFF AMNLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AMNLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZRNLS_TIMESTAMP_TZ_FORMATDD-MON-RR HH.MI.SSXFF AM TZRNLS_DUAL_CURRENCY￥NLS_COMP BINARYNLS_LENGTH_SEMANTICS BYTENLS_NCHAR_CONV_EXCPFALSE17 rows selected.

1.2.7 语句级别的全球化设置

全球化设置的最优控制级别是在每个SQL语句内进行编程控制，此时必然会在SQL函数中使用NLS参数。
比如：

--分别用英语，简体中文，日语显示职员的入职日期及星期：select to_char（HIREDATE,"Day dd, Month YYYY","NLS_DATE_LANGUAGE=AMERICAN"） "English",to_char（HIREDATE,"Day dd, Month YYYY","NLS_DATE_LANGUAGE=""SIMPLIFIED CHINESE"""） "Chinese",to_char（HIREDATE,"Day dd, Month YYYY","NLS_DATE_LANGUAGE=JAPANESE"） "Japanese"from scott.emp;SQL> select to_char（HIREDATE,"Day dd, Month YYYY","NLS_DATE_LANGUAGE=AMERICAN"） "English",2to_char（HIREDATE,"Day dd, Month YYYY","NLS_DATE_LANGUAGE=""SIMPLIFIED CHINESE"""） "Chinese",3to_char（HIREDATE,"Day dd, Month YYYY","NLS_DATE_LANGUAGE=JAPANESE"） "Japanese"4from scott.emp;EnglishChinese Japanese------------------------------------ ------------------------- -------------------------Wednesday 17, December1980 星期三 17, 12月 1980水曜日 17, 12月 1980Friday20, February1981 星期五 20, 2月1981金曜日 20, 2月1981Sunday22, February1981 星期日 22, 2月1981日曜日 22, 2月1981Thursday02, April 1981 星期四 02, 4月1981木曜日 02, 4月1981Monday28, September 1981 星期一 28, 9月1981月曜日 28, 9月1981Friday01, May 1981 星期五 01, 5月1981金曜日 01, 5月1981Tuesday 09, June1981 星期二 09, 6月1981火曜日 09, 6月1981Sunday19, April 1987 星期日 19, 4月1987日曜日 19, 4月1987Tuesday 17, November1981 星期二 17, 11月 1981火曜日 17, 11月 1981Tuesday 08, September 1981 星期二 08, 9月1981火曜日 08, 9月1981Saturday23, May 1987 星期六 23, 5月1987土曜日 23, 5月1987Thursday03, December1981 星期四 03, 12月 1981木曜日 03, 12月 1981Thursday03, December1981 星期四 03, 12月 1981木曜日 03, 12月 1981Saturday23, January 1982 星期六 23, 1月1982土曜日 23, 1月198214 rows selected.

二、使用数据库和NLS字符集

2.1 语言排序与选择

Oracle默认使用二进制排序，要排序的字符串从左到右读取，并且每个字符都被简化为他的ASCII数字。这种排序方法适用于美国英语，对于其他语言，则有可能不正确。

2.2 Locale Builder

Locale Builder是个图形化的工具，他能通过为语言、地区、字符集以及语言排序生成定义来创建自定义的全球化环境。

2.3 使用时区

为了实现业务必须在多个时区之间正常运转的功能，需要指定数据库所运作的时区以及使用TIMESTAMP WITH TIME ZONE与TIMESTAMP WITH LOCAL TIME ZONE数据类型。
实验：调整时区

--1.sqlplus连接到实例--2.确定数据库时区select property_value from database_properties where property_name="DBTIMEZONE";PROPERTY_VALUE------------------------------------------------------------------------------------------------------------------------00:00--3.创建表create table times（date_std date,date_tz timestamp with time zone,date_ltz timestamp with local time zone）;--4.查看支持的所有时区：select * from v$timezone_names;--5.将会话时区调整为某个不同于数据库时区的时区--将会话时区设定为美国夏威夷：alter session set time_zone="US/Hawaii";--6.调整会话的时区/时间格式alter session set nls_timestamp_tz_format="YYYY-MM-DD HH24:MI:SS TZD";alter session set nls_timestamp_format="YYYY-MM-DD HH24:MI:SS";alter session set nls_date_format="YYYY-MM-DD HH24:MI:SS";--7.表中插入一条记录insert into times values（"2016-02-03 15:00:00","2016-02-03 15:00:00","2016-02-03 15:00:00"）;commit;--8.查看时间select * from times;SQL> select * from times;DATE_STDDATE_TZDATE_LTZ------------------- -------------------------------------------------- ---------------------------------------------------------------------------2016-02-03 15:00:00 2016-02-03 15:00:00 HST2016-02-03 15:00:00--9.会话切换至数据库时区：alter session set time_zone=DBTIMEZONE;--10.再次查看时间，发现timestamp with local time zone数据类型对应的时间自动调整了select * from times;SQL> select * from times;DATE_STDDATE_TZDATE_LTZ------------------- -------------------------------------------------- ---------------------------------------------------------------------------2016-02-03 15:00:00 2016-02-03 15:00:00 HST2016-02-04 01:00:00

更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址