--==========================
-- Oracle 全球化特性与字符集
--==========================
数据库的全球化特性是数据库发展的必然结果，位于不同地区、不同国家、不用语言而使用同一数据库越来越普遍。 Oracle 数据库提供
了对全球化数据库的支持，消除不同文字、语言环境、历法货币等所带来的差异、使得更容易、更方便来使用数据库。
一、 Oracle 全球化特性
Oracle 全球化特性包括
Language support
Territory support
Character set support
Linguistic sorting
Message support
Date and time formats
Numeric formats
Monetary formats
在 Oracle 全球化特性中最重要的则是字符集，即使用何种字符集将数据存储在数据库中
二、什么是字符集
1. 定义
主要是讨论两个问题，一是字符如何存储，二是如何字符显示
简单的说就是输入的数据、字符、符号等如何存储到到计算机并将其翻译出来，而此处则是存储到数据库系统内。
比如英文的个英文字符、个阿拉伯数据字， # 、 $ 等。美国 ANSI 则使用了一个单字节 ( 7 位 ) 来表示了使用的标准字符集
由于世界各国和各个地区使用的符号的多样性，而单字节仅仅能表示个字符，因此就有使用到了多字节来表示各自不同的字符
正是由于上述原因，因此产生了不同的字符集的概念 , 如美国使用的为 US7ASCII , 西欧则使用的是 WE8ISO8859P1 , 中国则是 ZHS16GBK
同时，为了统一世界各国字符编码，统一编码字符集的概念应运而生，这就是 Unicode 。
在 Oracle 中，几种常用的 Unicode 为 UTF - 8 ， AL16UTF8 ， AL32UTF8
2.Oracle 所支持的字符集及分类
Oracle 支持两百多种字符集，包含了单字节、可变字节以及通用字符集等。
字符集通常根据说使用的字节数来分类 , 通常分为以下几类
a . 单字节字符集，如 US7ASCII ( 7 bit ) ， WE8ISO8859P1 ( 8 bit ) ， WE8DEC ( 8 bit )
b . 可变长度字符集，如 JA16SJIS ， ZHT16HKSCS
c . 通用字符集，如 AL32UTF8
3.UTF - 8 、 UTF - 16 、 UTF8 、 AL32UTF8 、 AL16UTF8
上述是几个经常容易混淆的概念，在此有必要做一下说明
UTF - 8 、 UTF - 16 、 UCS - 2 是标准的 Unicode 字符集，即是使用 UTF - 8 或 UTF - 16 或 UCS - 2 来实现编码
UTF8 指的是 Oracle 中的字符集，使用 UTF - 8 来编码
AL32UTF8 、 AL16UTF8 是 Oracle 数据库中使用基于标准 Unicode 字符集编码而定义的 Unicode 字符集
下面主要说明一下 AL32UTF8 、 AL16UTF8
AL32UTF8
是一个 bit 的 Unicode 字符编码，使用 UTF - 8 来实现编码
支持可变长编码，是 ASCII 码的严格超集，即 ASCII 字符集中表示字符码值与 AL32UTF8 中完全相同
该字符集支持 - 4 字节可变长度编码 , 对欧洲使用的字符通常用 - 2 个字节 , 对于亚洲则使用个字节 , 即一个汉字使用个字节表示
对于增补的特殊符号，则使用个字节来表示
AL16UTF8
是一个 bit 的 Unicode 字符编码，使用 UTF - 16 来实现编码
使用固定长度来表示字符，通常使用个字节来实现
该字符集通常适用于欧洲和亚洲国家，因为个字节足以满足这些字符的码点需求
对于增补的特殊符号， AL16UTF8 则使用个字节来实现
对于 AL16UTF8 中的字符，要么使用个字节，要么是个字节来表示
Oracle 数据库支持的 Unicode 字符集
Character Set Unicode Encoding Database Character Set National Character Set
UTF8 UTF - 8 Yes Yes ( Oracle 9i and 10g only )
AL32UTF8 UTF - 8 Yes No
AL16UTF16 UTF - 16 No Yes
4. 字符集影响的数据类型
对于二进制数据类型，字符集的设置不影响该类型数据的存储，如视频、音频等
影响的数据类型为： char , varchar2 , nchar , nvarchar2 , blob , clob , long , nclob
三、相关 NLS 参数的设定
1. 查看 NLS 参数
a . 本次会话中设定及使用的 NLS 参数 ( nls_session_parameters )
select * from nls_session_parameters ;
SQL > col parameter format a28
SQL > col value format a35
SQL > select * from nls_session_parameters ;
PARAMETER VALUE
---------------------------- -----------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
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
PARAMETER VALUE
---------------------------- -----------------------------------
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
b . 查看数据库中设定的 NLS 参数
select * from nls_database_parameters ;
c . 查看基于实例设定的 NLS 参数
select * from nls_instance_parameters ;
2. 设定 nls_language 参数
设定 nls_language 影响的内容
NLS_LANGUAGE AMERICAN -- 只要设定该参数则下面的参数即可确定
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
-- 下面查看 nls_language 参数以及设定不同的 nls_language 所带来的影响
SQL > select * from nls_session_parameters where parameter = 'NLS_LANGUAGE' ;
PARAMETER VALUE
---------------------------- -----------------------------------
NLS_LANGUAGE AMERICAN
SQL > select empno , ename , round ( sal * 1.35 , 2 ) sal , hiredate from emp where sal < 1200 ;
EMPNO ENAME SAL HIREDATE
---------- ---------- ---------- ---------
7369 SMITH 1080 17 - DEC - 80
7876 ADAMS 1485 23 - MAY - 87
7900 JAMES 1282.5 03 - DEC - 81
SQL > alter session set nls_language = 'ITALIAN' ; -- 将 nls_language 设定为 ITALIAN
Session altered .
-- 再次执行上面的查询语句可以看出日期月份的表示发生了变化
SQL > select empno , ename , round ( sal * 1.35 , 2 ) sal , hiredate from emp where sal < 1200 ;
EMPNO ENAME SAL HIREDATE
---------- ---------- ---------- ---------
7369 SMITH 1080 17 - DIC - 80
7876 ADAMS 1485 23 - MAG - 87
7900 JAMES 1282.5 03 - DIC - 81
3. 设定 nls_territory 参数
设定 nls_territory 影响的内容
NLS_TERRITORY AMERICA -- 只要设定该参数则下面的参数即可确定
NLS_CURRENCY $
NLS_DUAL_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_DATE_FORMAT DD - MON - RR
NLS_TIMESTAMP_FORMAT DD - MON - RR HH . MI . SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT DD - MON - RR HH . MI . SSXFF AM TZR
SQL > alter session set nls_language = 'ENGLISH' ; -- 将 nls_language 设置为英语
Session altered .
-- 在设定为英语后执行查询可以看出查询结果同美语是一样的
SQL > select empno , ename , round ( sal * 1.35 , 2 ) sal , hiredate from emp where sal < 1200 ;
EMPNO ENAME SAL HIREDATE
---------- ---------- ---------- ---------
7369 SMITH 1080 17 - DEC - 80
7876 ADAMS 1485 23 - MAY - 87
7900 JAMES 1282.5 03 - DEC - 81
SQL > alter session set nls_territory = 'CANADA' ; -- 将 nls_territory 设置为加拿大
Session altered .
-- 将 nls_territroy 设置为加拿大后，日期发生了变化，小数位符号也发生了变化
SQL > select empno , ename , round ( sal * 1.35 , 2 ) sal , hiredate from emp where sal < 1200 ;
EMPNO ENAME SAL HIREDATE
---------- ---------- ---------- --------
7369 SMITH 1080 80 - 12 - 17
7876 ADAMS 1485 87 - 05 - 23
7900 JAMES 1282 , 5 81 - 12 - 03
SQL > alter session set nls_territory = 'AUSTRALIA' ; -- 保留 nls_language 不变，即为英语，将地区改为 AUSTRALIA
Session altered .
-- 从下面的查询可以看到日期部分发生了变化
SQL > select empno , ename , round ( sal * 1.35 , 2 ) sal , hiredate from emp where sal < 1200 ;
EMPNO ENAME SAL HIREDATE
---------- ---------- ---------- ---------
7369 SMITH 1080 17 / DEC / 80
7876 ADAMS 1485 23 / MAY / 87
7900 JAMES 1282.5 03 / DEC / 81
关于 nls_language 和 nls_territory 参数的总结
为 nls_language 设定了不同的值，则影响了日期的表示方法排序等
通常，在 nls_language 设定后，应为 nls_territory 设定合理的值，假如语言设定为简体中文，地区设定为澳大利亚则不太合理
对于使用同样的语言不同国家或地区 , 比如英语 , 澳大利亚和英国 , 则 nls_territory 设定不同 , 则同样影响相关参数如 currency 等
两个参数所影响的具体值请参考前面部分的描述及演示
4.NLS_LANG 参数的设定
NLS_LANG 为一个总控参数，控制了前面描述的 nls_language 和 nls_territory 的行为
该参数可以用于设定服务器和客户端的 language 和 territory 值，也可设置客户端输入数据和显示的字符集
只要设定了该参数，则其它参数就确定了。当然也可以只设定其中的一部分， NLS_LANG 只能在环境变量中设定。
该参数的格式为： NLS_LANG = language_territory . charset 如： NLS_LANG = French_France . UTF8
NLS_LANG 设定后，在客户端应用程序启动时生效。当连接到服务器端时，客户端使用 NLS_LANG 指定的信息与
Oracle 服务器段来通信，该设置通常对 Oracle 提供的客户端有效，如 SQLPlus 等
在参数 NLS_LANG = French_France . AL32UTF8 ，该参数分为个部分来设定
第一部分为 language ，如示例中为 French 。
第二部分为 territory ，如示例中为 France 。一二部分必须用下划线连接
第三部分为 character set ，如示例中为 AL32UTF8 二三部分必须用小数点连接
不正确的设定方法
NLS_LANG = JAPANESE_JAPAN . WE8ISO8859P1
西文 WE8ISO8859P1 不能完全支持日文字符 , 因此应该将字符集改为 JA16EUC , 完整形式为 : NLS_LANG = JAPANESE_JAPAN . JA16EUC
[oracle@oradb ~]$ export NLS_LANG = French_France . WE8ISO8859P1 -- 将环境变量设定为 French_France.WE8ISO8859P1
[oracle@oradb ~]$ sqlplus / nolog -- 启动 SQLPlus
SQL * Plus : Release 10.2.0.1.0 - Production on Dim . Sept . 26 12 : 04 : 37 2010
Copyright ( c ) 1982 , 2005 , Oracle . All rights reserved .
SQL > conn scott / tiger ;
Connected .
SQL > col parameter format a28
SQL > col value format a35
SQL > select * from nls_session_parameters ; -- 查看会话中的参数已经被设定为 FRENCH 和 FRANCE
PARAMETER VALUE
---------------------------- -----------------------------------
NLS_LANGUAGE FRENCH
NLS_TERRITORY FRANCE
NLS_CURRENCY ?
NLS_ISO_CURRENCY FRANCE
NLS_NUMERIC_CHARACTERS ,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD / MM / RR
NLS_DATE_LANGUAGE FRENCH
NLS_SORT FRENCH
NLS_TIME_FORMAT HH24 : MI : SSXFF
NLS_TIMESTAMP_FORMAT DD / MM / RR HH24 : MI : SSXFF
PARAMETER VALUE
---------------------------- -----------------------------------
NLS_TIME_TZ_FORMAT HH24 : MI : SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD / MM / RR HH24 : MI : SSXFF TZR
NLS_DUAL_CURRENCY ?
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
SQL > select empno , ename , round ( sal * 1.35 , 2 ) sal , hiredate from emp where sal < 1200 ;
EMPNO ENAME SAL HIREDATE
---------- ---------- ---------- --------
7369 SMITH 1080 17 / 12 / 80
7876 ADAMS 1485 23 / 05 / 87
7900 JAMES 1282 , 5 03 / 12 / 81
在 French_France . WE8ISO8859P1 环境变量下修改 nls_date_format
SQL > alter session set nls_date_format = 'yyyy-mm-dd' ;
Session altered .
-- 修改后，仅仅是日期显示发生了变化
SQL > select empno , ename , round ( sal * 1.35 , 2 ) sal , hiredate from emp where sal < 1200 ;
EMPNO ENAME SAL HIREDATE
---------- ---------- ---------- ----------
7369 SMITH 1080 1980 - 12 - 17
7876 ADAMS 1485 1987 - 05 - 23
7900 JAMES 1282 , 5 1981 - 12 - 03
由上可知，基于会话级别的设置优先于环境变量的设置
alter session set nls_date_format = 'yyyy-mm-dd' ;
dbms_session . sest_nls ( 'NLS_DATE_FORMAT' , '''yyyy-mm-dd''' )
基于 SQL 函数来设定 NLS 参数
select to_char ( hire_date , 'DD.Mon.YYYY' , 'NLS_DATE_LANGUAGE=FRENCH' )
from employees
where hire_date > '01-JAN-2000' ;
TO_CHAR ( HIRE_DATE , 'DD.MON.YY'
----------------------------
13.Janv . .2000
08.Mars .2000
06.Fevr . .2000
基于 window 客户端设置 NLS_LANG
在 Window 平台，使用注册表来设定 Oracle 的 NLS_LANG 参数
a . 打开注册表 ( 运行中输入 regedit 后确定 )
b . 逐个键展开 ( COMPUTER/HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE/KEY_OraClient10g_hom1 )
c . 找到 NLS_LANG 子项，如为该子项输入值 SIMPLIFIED CHINESE_CHINA . ZHS16GBK
从 Window 客户端使用 NLS_LANG 来访问服务器，客户端使用的 NLS_LANG 为 SIMPLIFIED CHINESE_CHINA . ZHS16GBK
-- 首先查看一下服务器段的 language 、 territory 、 character set
-- 可以看出服务器的设置与 Window 客户端的设置是不相同的
SQL > select * from nls_database_parameters ;
PARAMETER VALUE
--------------------------------------------- -----------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET AL32UTF8
C : / > sqlplus scott / tiger@list2 -- 从客户端使用 tnsnams.ora 登陆到服务器
SQL * Plus : Release 10.2.0.1.0 - Production on 星期六 9 月 25 12 : 51 : 52 2010
Copyright ( c ) 1982 , 2005 , Oracle . All rights reserved .
Connected to :
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning , OLAP and Data Mining options
SQL > desc tb ;
Name Null ? Type
----------------------------------------- -------- ----------------------
ID NUMBER ( 38 )
ENAME VARCHAR2 ( 40 )
SQL > insert into tb select 1 , ' 鲁滨逊 ' from dual ; -- 插入中文记录
1 row created .
SQL > commit ;
Commit complete .
SQL > select * from tb ; -- 可以正常显示
ID ENAME
---------- --------------------
1 鲁滨逊
总结：尽管服务器端 NLS_LANGUAGE 设置为 AMERICAN ， NLS_TERRITORY 设置为 AMERICA ，但并不影响客户端中文的使用
因为服务器端使用了 AL32UTF8 Unicode 字符集 . 尽管客户端使用的字符集为 ZHS16GBK . 其间的转换工作则由 OracleNet 完成
5.NLS_COMP 参数
能够被设定为 ANSI ， BINARY ( 缺省值 ) ， LINGUISTIC 用于设定 SQL 操作时的比较行为
6.NLS_LENGTH_SEMANTICS 参数
该参数的参数值为 byte 或 char ，作用域为环境变量，初始化参数，会话级别 ( alter session )
用于设定以何种方式来统计字符串的长度，缺省的为按字节来统计。当指定一个列为 char ( 20 ) 则表示使用个字节来存储数据
当使用单字节字符集时，则字符数与字节数是相等的，使用多字节字符集时，则不相等，可能一个字符使用到了多个字节
NLS_LENGTH_SEMANTICS 允许创建 char , varchar2 , long 类型列使用基于字节、也可以使用基于字符来统计长度
而对于 nchar , nvarchar2 , clob , nclob 则总是使用基于字符来统计字节的长度
NLS_LENGTH_SEMANTICS 不影响 SYS 与 SYSTEM 用户创建的表，即两者使用的是以字节统计长度
如果 NLS_LENGTH_SEMANTICS 参数在客户端未设置，则该参数使用服务器端的缺省设置
SQL > select * from nls_session_parameters where parameter like 'NLS_LENG%' ;
PARAMETER VALUE
---------------------------- -----------------------------------
NLS_LENGTH_SEMANTICS BYTE
SQL > show user ;
USER is "SCOTT"
SQL > create table tb_b ( id number , ename varchar2 ( 10 )); -- 列 ename 使用缺省值字节来作为存储长度
Table created .
SQL > create table tb_b_2 ( id number , ename varchar2 ( 10 byte )); -- 列 ename 使用显示值字节来作为存储长度
Table created .
SQL > create table tb_c ( id number , ename varchar2 ( 10 char )); -- 列 ename 使用显示值字符来作为存储长度
Table created .
SQL > insert into tb_b select 1 , ' 中华人民共和国 ' from dual ; -- 超出长度
insert into tb_b select 1 , ' 中华人民共和国 ' from dual
*
ERROR at line 1 :
ORA - 12899 : value too large for column "SCOTT" . "TB_B" . "ENAME" ( actual : 21 ,
maximum : 10 )
SQL > insert into tb_c select 1 , ' 中华人民共和国 ' from dual ; -- 该表 ename 列使用字符作为存储长度，成功插入记录
1 row created .
SQL > select length ( ename ) from tb_c ; -- 计算长度 ( 字符数 )
LENGTH ( ENAME )
-------------
7
SQL > select lengthb ( ename ) from tb_c ; -- 计算长度 ( 字节数 )
LENGTHB ( ENAME )
--------------
21
SQL > select substr ( ename , 1 , 4 ) from tb_c ; -- 截取子串 ( 字符数 )
SUBSTR ( E
--------
中华人民
SQL > select substrb ( ename , 1 , 9 ) from tb_c ; -- 截取子串 ( 字节数 )
SUBSTRB ( ENAME , 1 , 9 )
------------------
中华人
SQL > select table_name , column_name , data_type , data_length from user_tab_columns
2 where table_name like 'TB_%' and column_name like 'ENAME' ;
TABLE_NAME COLUMN_NAME DATA_TYPE DATA_LENGTH
--------------- --------------- --------------- -----------
TB_B ENAME VARCHAR2 10
TB_B_2 ENAME VARCHAR2 10
TB_C ENAME VARCHAR2 40
7.NLS 参数设定的不同级别及优先顺序 ( 下面的描述中级别由低到高，即 SQL 函数级别的 NLS 参数优先级最高 )
a . 在初始化参数中设定 NLS 参数及使用缺省的 NLS 参数，这些参数在服务器端使用，不影响客户端
b . 在客户端使用环境变量来设定 NLS 参数，该参数高于服务器端的 NLS 设置
c . 使用 alter session 来修改 NLS 参数
d . 使用 SQL 函数来设定 NLS 参数 ( 具有最高优先级别 )
四、 Oracle 字符集转换
正如前面所描述，客户端可以使用不同于服务器端的字符集、语言环境，这些不同字符集的转换工作则由 OracleNet 来负责
Oracle Net 会比较客户端和服务器之间的字符集，在需要的时候 Oracle Net 则实现透明、自动转换字符集
一般情况下，要求服务器端的字符集是客户端的超集或相等，即使用相同的字符集
1. 未正确设置 NLS_LANG 时容易导致数据不转换或转换错误
假定客户端使用的是 Windows 系统， Windows 客户端使用的代码页
服务器端的字符集为 AL32UTF8 , 如 Window 客户端设置为 AL32UTF8 , OracleNet 认为两者一致 , 则不执行转换 , 无效的数据将输入到数据库
应该设置正确的 NLS_LANG = AMERICAN_AMERICA . WE8MSWIN1252
2. 不当设置引起导入导出时的错误
假定服务器的字符集设置为 US7ASCII ， National Character Set 为 UTF8
客户端使用的 NLS_LANG 为 NLS_LANG = SIMPLIFIED CHINESE_HONGKONG . US7ASCII
此种情况可以存储多字节汉字到单字节数据中
对于非 ASCII 码字符在导入导出到另一个数据库时产生数据丢失
3. 数据导入导出时发生的字符集转换
在将数据从一个 Oracle 数据库导出之后再导入到另外一个 Oracle 数据库，如果两者字符集不同，则发生转换
当导出 DDL 时，导出文件使用 NLS_LANG 指定的字符集，当指定的字符集不同于数据库字符集时，转换将发生
当导入数据到数据库时，如果源数据库字符集不同于目标数据库字符集，则源数据库字符集向目标数据库字符集转换
外部表使用服务器端的 NLS 设置来决定使用的字符集
SQL * Loader 中
对于传统路径方式，数据基于会话指定的 NLS 设置来转换
对于直接路径方式，数据基于客户端 NLS 设置来转换
五、 Character Set 与 National Character Set
Character Set ：前面已经描述过，主要是用作描述字符如何保存。
National Character Set ：主要是用于辅助 Character Set 。因为早期的数据库中很多使用了单字节字符集，但随着业务的需要及发展，
需要使用到诸如 nchar , nvarchar 等 Unicode 字符或者需要扩展到世界各地存储不同的字符，因此辅助字符集应运而生。
两者的比较：
Character Set National Character Set
---------------------- -----------------------
创建时被定义 创建时被定义
创建见后不能被改变，仅有很少列外 可以被改变
可以存储列的类型为 char , varchar2 , clob , long 可以存储的类型为 NCHAR , NVARCHAR2 , NCLOB
能够存储可变长度字符集 能够存储 Unicode 字符集，要么使用 AL16UTF8 或 UTF8
查看数据使用的字符集
SQL > select parameter , value from nls_database_parameters
2 where parameter like '%CHARACTERSET' ;
PARAMETER VALUE
---------------------------- -----------------------------------
NLS_CHARACTERSET AL32UTF8
NLS_NCHAR_CHARACTERSET AL16UTF16
六、字符集的几个常用视图
nls_session_parameters
nls_instance_parameters
nls_database_parameters
v$nls_parameters
v$nls_valid_values
七、更多参考
Oracle 联机重做日志文件(ONLINE LOG FILE)
