Oracle 全球化特性與字符集

分類： Oracle 其它特性 2010-11-23 18:06 334人閱讀評論(0) 舉報

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

-- 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)

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

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 全球化特性與字符集

相關文章