Oracle数据库全球化
2016-02-03 15:38 AlfredZhao 阅读(985) 评论(0) 编辑 收藏 举报一、自定义数据库和各个会话的语言相关行为
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;
PARAMETER VALUE
------------------------------ --------------------------------------------------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET ZHS16GBK
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
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_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 11.2.0.4.0
20 rows selected.
1.2.4 实例级别的全球化
实例级别的全球化设置，实例参数设置会重写数据库设置。
--查询nls_instance_parameters视图
select * from nls_instance_parameters
PARAMETER VALUE
------------------------------ ------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_SORT
NLS_DATE_LANGUAGE
NLS_DATE_FORMAT
NLS_CURRENCY
NLS_NUMERIC_CHARACTERS
NLS_ISO_CURRENCY
NLS_CALENDAR
NLS_TIME_FORMAT
NLS_TIMESTAMP_FORMAT
NLS_TIME_TZ_FORMAT
NLS_TIMESTAMP_TZ_FORMAT
NLS_DUAL_CURRENCY
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
17 rows selected.
1.2.5 客户端环境设置
一般我们项目常用的在客户端环境变量中配置的两个参数：
NLS_LANG
NLS_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-03
SQL> 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;
PARAMETER VALUE
------------------------------ ------------------------------
NLS_LANGUAGE SIMPLIFIED CHINESE
NLS_TERRITORY CHINA
NLS_CURRENCY ￥
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
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_session_parameters，没有字符集信息
SQL> select * from nls_session_parameters;
PARAMETER VALUE
------------------------------ ------------------------------
NLS_LANGUAGE SIMPLIFIED CHINESE
NLS_TERRITORY CHINA
NLS_CURRENCY ￥
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_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
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_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
17 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",
2 to_char(HIREDATE,'Day dd, Month YYYY','NLS_DATE_LANGUAGE=''SIMPLIFIED CHINESE''') "Chinese",
3 to_char(HIREDATE,'Day dd, Month YYYY','NLS_DATE_LANGUAGE=JAPANESE') "Japanese"
4 from scott.emp;
English Chinese Japanese
------------------------------------ ------------------------- -------------------------
Wednesday 17, December 1980 星期三 17, 12月 1980 水曜日 17, 12月 1980
Friday 20, February 1981 星期五 20, 2月 1981 金曜日 20, 2月 1981
Sunday 22, February 1981 星期日 22, 2月 1981 日曜日 22, 2月 1981
Thursday 02, April 1981 星期四 02, 4月 1981 木曜日 02, 4月 1981
Monday 28, September 1981 星期一 28, 9月 1981 月曜日 28, 9月 1981
Friday 01, May 1981 星期五 01, 5月 1981 金曜日 01, 5月 1981
Tuesday 09, June 1981 星期二 09, 6月 1981 火曜日 09, 6月 1981
Sunday 19, April 1987 星期日 19, 4月 1987 日曜日 19, 4月 1987
Tuesday 17, November 1981 星期二 17, 11月 1981 火曜日 17, 11月 1981
Tuesday 08, September 1981 星期二 08, 9月 1981 火曜日 08, 9月 1981
Saturday 23, May 1987 星期六 23, 5月 1987 土曜日 23, 5月 1987
Thursday 03, December 1981 星期四 03, 12月 1981 木曜日 03, 12月 1981
Thursday 03, December 1981 星期四 03, 12月 1981 木曜日 03, 12月 1981
Saturday 23, January 1982 星期六 23, 1月 1982 土曜日 23, 1月 1982
14 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_STD DATE_TZ DATE_LTZ
------------------- -------------------------------------------------- ---------------------------------------------------------------------------
2016-02-03 15:00:00 2016-02-03 15:00:00 HST 2016-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_STD DATE_TZ DATE_LTZ
------------------- -------------------------------------------------- ---------------------------------------------------------------------------
2016-02-03 15:00:00 2016-02-03 15:00:00 HST 2016-02-04 01:00:00
Reference
- OCP 认证考试指南 (1Z0-053)[M]. 清华大学出版社, 2010.
AlfredZhao©版权所有「从Oracle起航，领略精彩的IT技术。」
