前言
Oracle 對於多國語系的支援是相當強大的，可以隨時根據需求改變使用不同的語系、時間格式、貨幣格式、文字編碼、設數字表示法 … 等等。
在 Oracle 中是透過一群參數來控制多國語系的設定，這一群參數稱為「NLS(Native Language Support) parameter」。
檢視並運用 NSL paramter
NSL parameter 可以透過 Oracle 所提供的 SQL Developer 工具進行檢視，選擇「Report –> Data Dictionary Reports –> About Your Database –> National Language Support Parameters」，畫面如下：
另外，可以透過以下語法，暫時修改目前連線 session 的語系環境：
ALTER SESSION SET parameter-name = ”value”;
以下以範例作說明：
--修改語言
ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN';
但需要注意的是，透過 ALTER SESSION 所修改的參數，在重新連線後都會重新變回原本的預設值。
建立支援多國語系的環境
以下分段說明如何自訂多國語系的環境：
設定語言(Language)與地區(Territory)參數
在此部份的參數與影響的部份為：
1、NLS_LANGUAGE
- server 訊息顯示時所用的語言
- 日期與其縮寫的表示方式
- 資料排序時的依據(不同的語系會根據不同的字元標準進行排序)
以下為 NLS_LANGUAGE 的使用範例：
--修改語言
ALTER SESSION SET NLS_LANGUAGE = 'TRADITIONAL CHINESE';
--顯示 : 23-12月-08
SELECT SYSDATE FROM DUAL;
--修改語言
ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN';
--顯示 : 23-DEC-08
SELECT SYSDATE FROM DUAL;
2、NLS_TERRITORY
- 日期格式
- 十進位字元與分隔符號
- 本地貨幣符號
- ISO 貨幣符號
以下為 NLS_TERRITORY 的使用範例：
--結果如下：
-- NT$24,000.00
-- NT$17,000.00
-- NT$17,000.00
SELECT TO_CHAR(salary, 'L99G999D99') salary
FROM employees
WHERE employee_id IN (100, 101, 102);
--改變 territory 為德國
ALTER SESSION SET NLS_TERRITORY = 'GERMANY'
--結果如下：
-- €24.000,00
-- €17.000,00
-- €17.000,00
SELECT TO_CHAR(salary, 'L99G999D99') salary
FROM employees
WHERE employee_id IN (100, 101, 102);
設定日期(Date)與時間(Time)參數
不同的地區都會有自己地區表示時間的格式，但如果不適合用，當然要修改預設值也是沒有問題的，關於日期與時間的 NLS parameter，有以下幾個：
1、NLS_DATE_FORMAT
除了 NLS_TERRITORY 參數可以改變預設的日期格式外，若要進行客製化的話，就必須透過 NLS_DATE_FORMAT 這個參數了，以下用範例說明： (官方文件說明)
--執行結果 : 24.12.08
SELECT SYSDATE FROM DUAL;
--改變日期格式
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY';
--執行結果 : 12/24/2008
SELECT SYSDATE FROM DUAL;
--改變日期格式
ALTER SESSION SET NLS_DATE_FORMAT = '"DATE: "MM/DD/YYYY';
--執行結果 : DATE: 12/24/2008
SELECT SYSDATE FROM DUAL;
2、NLS_DATE_LANGUAGE
透過此參數可以改變顯示日期的語言，不過僅會在使用 TO_CHAR 與 TO_DATE 兩個 function 時有效，以下是範例：(官方文件說明)
Created with colorer-take5 library. Type 'sql'
--執行結果: ���期三:24 12月 2008
SELECT TO_CHAR(SYSDATE, 'Day:Dd Month yyyy') FROM DUAL;
--改變表示 DATE 語言
ALTER SESSION SET NLS_DATE_LANGUAGE = 'FRENCH';
--執行結果: Mercredi:24 Decembre 2008
SELECT TO_CHAR(SYSDATE, 'Day:Dd Month yyyy') FROM DUAL;
--改變表示 DATE 語言
ALTER SESSION SET NLS_DATE_LANGUAGE = 'AMERICAN';
--執行結果: Wednesday:24 December 2008
SELECT TO_CHAR(SYSDATE, 'Day:Dd Month yyyy') FROM DUAL;
3、NLS_TIMESTAMP_FORMAT & NLS_TIMESTAMP_TZ_FORMAT
這兩個其實是一起的，只是一個會改變 select 出來的 timestamp 顯示(NLS_TIMESTAMP_FORMAT)，一個會改變由 TO_CHAR() function 所呈現的 timestamp 顯示(NLS_TIMESTAMP_TZ_FORMAT)。
以下為範例：(官方文件說明)
--執行結果: 24-12月-08 02.22.38.284000000 下午 ASIA/TAIPEI
SELECT CURRENT_TIMESTAMP FROM DUAL;
--改變 timestamp 顯示格式
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH:MI:SS:FF TZH:TZM';
--執行結果: 2008-12-24 02:22:38:334000000 +08:00
SELECT CURRENT_TIMESTAMP FROM DUAL;
數字表示法的設定
數字的部份，可以透過「NLS_NUMERIC_CHARACTERS」參數設定千、百萬的分隔字元，以及表示小數點的分隔字元；以下用範例說明：
--執行結果: 4,000.00
SELECT TO_CHAR(4000, '9G999D99') FROM DUAL;
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ',.';
--執行結果: 4.000,00
SELECT TO_CHAR(4000, '9G999D99') FROM DUAL;
貨幣格式的設定
貨幣格式的部份可由以下三個參數來設定：
- NLS_CURRENCY
- NLS_ISO_CURRENCY
- NLS_DUAL_CURRENCY
以下用範例說明使用方式：
--執行結果:
-- TWD009.000,00
-- TWD006.000,00
-- TWD004.800,00
-- TWD004.800,00
-- TWD004.200,00
SELECT TO_CHAR(salary, 'C099G999D99') salary FROM employees where department_id = 60;
--將貨幣表示更改為法國
ALTER SESSION SET NLS_ISO_CURRENCY = 'FRENCH';
--執行結果:
-- EUR009.000,00
-- EUR006.000,00
-- EUR004.800,00
-- EUR004.800,00
-- EUR004.200,00
SELECT TO_CHAR(salary, 'C099G999D99') salary FROM employees where department_id = 60;
排序與搜尋方式的設定
不同的語言在資料排序上不盡然相同，有些以字母順序為依據，有些以發音為依據、有些以單字的字母數量為依據；而 Oracle 當然也支援根據不同的情況進行改變。
而影響排序搜尋的 NLS parameter 有兩個，分別是「NLS_SORT」與「NLS_COMP」 以下有兩個參數會影響到系統的運作：
以下用範例來說明：
--修改排序方式
ALTER SESSION SET NLS_SORT = 'BINARY';
--執行結果:
-- Cabrio
-- Cambrault
-- Cambrault
-- Chen
-- Chung
-- Colmenares
SELECT last_name FROM employees WHERE last_name LIKE 'C%'
ORDER BY last_name;
--修改排序方式
ALTER SESSION SET NLS_SORT = 'spanish_m';
--執行結果:
-- Cabrio
-- Cambrault
-- Cambrault
-- Colmenares
-- Chen
-- Chung
SELECT last_name FROM employees WHERE last_name LIKE 'C%'
ORDER BY last_name;
此外，若是搜尋時大小寫有別，可以透過類似「NLS_SORT = ‘BINARY_CI’」(Case-Insensitive) 的方式進行設定。
設定資料長度的判斷基準
由於 Oracle 支援多國語言，其中英文是屬於 single-byte 的字元集，其他許多國家的語言則屬於 multi-byte 的字元集；而在 Oracle 中預設是以 byte 為基準，但實際上有時候還是必須以 char 為基準才是比較正確的。
因此在 Oracle 中可以透過以下語法進行設定：
--將計算基準改為 char
ALTER SESSION SET NLS_LENGTH_SEMANTICS = 'CHAR';
--將計算基準改為 byte
ALTER SESSION SET NLS_LENGTH_SEMANTICS = 'BYTE';
設計支援多語系的應用程式
官方網站中有文件說明，指導開發者如何開發 unicode 的應用程式
要儲存 unicode 的資料到 Oracle 有兩種方式：
- 建立一個 unicode 資料庫。(指定資料庫使用的編碼)
- 指定特定欄位支援 multi-bytes 以儲存 unicode 的資料，在 Oracle 為 NCHAR 資料型態。 (基本上，設定 NCHAR 型態的話，不論資料庫使用的編碼為何，都可以儲存 unicode 的資料)
使用 Unicode 資料型態
在 Oracle 有兩種儲存 unicode 資料的型態，分別為：
- NCHAR
- NVARCHAR2
首先說明 NCHAR 使用方式，宣告方式如下：
CREATE TABLE table1 (column1 NCHAR(30));
而 NCHAR 的使用長度上有兩個限制：
- 當字元編碼為 UTF-8 時，NCHAR 最多可以儲存 2000 的字元；若是改為 AL16UTF16，就僅能儲存 1000 個字元。
- 實際儲存長度最大為 32768 bytes。
根據使用者的宣告，資料長度若不足宣告的欄位長度，則會補足空白字元。
另外一種資料型態則為 NVARCHAR2，宣告方式如下：
CREATE TABLE tables (column2 NVARCHAR2(2000));
而 NVARCHAR2 在使用長度上也是有兩個限制：
- 當字元編碼為 UTF-8 時，NCHAR 最多可以儲存 4000 的字元；若是改為 AL16UTF16，就僅能儲存 2000 個字元。
- 實際儲存長度最大為 32768 bytes。
使用與處理 Unicode 字串
在 SQL 處理中，若要指定使用 unicode 字串，有以下幾個方式：
- 在單引號旁邊加上大寫「N」。
- 使用 NCHR(n) 函式，可以將字元轉為 unicode 編碼，提高程式的可攜帶性。
- 使用 UNISTR(‘string’) 函式，將字串轉換為 unicode 編碼。
NCHAR 的轉換
在開發多國語系的應用程式時，要考慮的部份除了 server 的編碼外，還包含 client 的編碼，當兩者編碼不同時，資料在傳輸時可能就會因為轉換而造成資料不正確，即使使用 N(‘string’) 的方式也是相同。
此時可以在 DB client 的地方增加一個名稱為「ORA_NCHAR_LITERAL_REPLACE」的環境變數，並將值設定為 TRUE，如此一來使用 N(‘string’) 時，系統就會自動轉換為 unicode 來傳輸並儲存囉!
在 Function 中使用 NLS parameter
在 Oracle 中有些 function 會因為語系、地區的不同而造成使用效果也相對不同(例如：TO_CHAR'、TO_DATE、TO_NUMBER、NLS_UPPER、NLS_LOWER、NLS_INITCAP、NLSSORT … 等等)，因此若是有特殊需求的話，可以直接將 NLS parameter 指定在 function 中，讓操作環境暫時改變。為了讓 function 開發時可以容易些，有時候必須暫時轉變語系進行處理，否則程式光是要考慮語系的部份可能就太過於複雜了，以下用個範例說明：
Created with colorer-take5 library. Type 'sql'
--這樣搜尋是不被允許的，因為日期格式並非如此表示
SELECT last_name FROM employees WHERE hire_date > '01-Jan-1999';
--所以改成美國的表示法
ALTER SESSION SET NLS_DATE_LANGUAGE = 'American';
--如此一來，不論原本的語系為何，都可以正確顯示搜尋結果囉!
SELECT last_name FROM employees WHERE hire_date > '01-Jan-1999';
透過在 function 中指定有個好處，就是不會改變該連線 session 時的 NLS 設定，而只有在該 DML 操作時有效而已，以下用個範例說明：
--直接在 TO_CHAR 函式中設定 NLS parameter
SELECT last_name
FROM employees
WHERE hire_date > TO_DATE('01-Jan-1999', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = American');
因此若是要避免修改到 NLS 設定而影響其他操作，就將 NLS parameter 放進 function 中。
而有哪些 function 可以接受 NLS parameter ? 而這些 function 可以接受的 NLS parameter 又是哪些呢?
以下為列表：- TO_DATE
NLS_DATE_LANGUAGE, NLS_CALENDAR - TO_NUMBER
NLS_NUMERIC_CHARACTERS, NLS_CURRENCY, NLS_ISO_CURRENCY, NLS_DUAL_CURRENCY - TO_CHAR
NLS_DATE_LANGUAGE, NLS_NUMERIC_CHARACTERS, NLS_CURRENCY,NLS_ISO_CURRENCY, NLS_DUAL_CURRENCY, NLS_CALENDAR - TO_NCHAR
NLS_SORT - NLS_UPPER
NLS_SORT - NLS_LOWER
NLS_SORT - NLS_INITCAP
NLS_SORT - NLSSORT
NLS_SORT
接著為使用範例：
-- 21/JUIN /1999
-- 13/JANV./2000
-- 17/SEPT./1987
SELECT TO_CHAR(hire_date, 'DD/MON/YYYY', 'NLS_DATE_LANGUAGE = French') "Hire Date" FROM employees;
-- 25/12月/2008
SELECT TO_CHAR(SYSDATE, 'DD/MON/YYYY', 'NLS_DATE_LANGUAGE=''Traditional Chinese'' ') "System Date" FROM DUAL;
-- 13.000,00
SELECT TO_CHAR(13000, '99G999D99', 'NLS_NUMERIC_CHARACTERS = '',.''') "13K" FROM DUAL;
-- 2.600,00
-- 2.600,00
-- 4.400,00
SELECT TO_CHAR(salary, '99G999D99', 'NLS_NUMERIC_CHARACTERS='',.'' NLS_CURRENCY=''EUR''') salary FROM employees;
-- ABEL
-- ANDE
-- ATKINSON
SELECT NLS_UPPER(last_name, 'NLS_SORT = Swiss') "Last Name" FROM employees;
-- Abel
-- Ande
-- Atkinson
-- Austin
SELECT last_name FROM employees ORDER BY NLSSORT(last_name, 'NLS_SORT = German');
為了符合多國語系，在開發應用程式時，許多細節就必須注意到，未來才不需要因為語系或是編碼的問題傷透腦筋囉!
沒有留言:
張貼留言