Oracle 全球化特性与字符集

最新推荐文章于 2022-08-19 19:39:08 发布

最新推荐文章于 2022-08-19 19:39:08 发布 · 70 阅读

文章标签：

#数据库 #操作系统

本文详细介绍了Oracle数据库的全球化特性，包括语言支持、地域支持、字符集支持等内容，并深入探讨了字符集的选择与转换机制。

--==========================

-- 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(7bit)，WE8ISO8859P1(8bit)，WE8DEC(8bit)

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

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

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)

-------------

SQL> select lengthb(ename) from tb_c; --计算长度(字节数)

LENGTHB(ENAME)

--------------

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)

Oracle 控制文件(CONTROLFILE)

Oracle 表空间与数据文件

Oracle 归档日志