沒有有關(guān)的標(biāo)準(zhǔn)化過程，程序開發(fā)者們想要開發(fā)一個(gè)能在多種語言環(huán)境下使用的應(yīng)用程序是非常地困難的。如果可能的話，這些翻譯轉(zhuǎn)換過程能在數(shù)據(jù)庫(kù)中完成，那么就給開發(fā)者們帶來了很多便利，使他們能專注于開發(fā)本身的事情了。

 
例如，假設(shè)我們現(xiàn)在有一個(gè)國(guó)家的列表。其中國(guó)家名是依據(jù)最終用戶的語言不同而不同。在當(dāng)時(shí)沒有翻譯器的條件下，我們需要根據(jù)用戶設(shè)置的語言來輸出國(guó)家名，或是用默認(rèn)的語言—英語來進(jìn)行輸出。

要達(dá)到這個(gè)目的，我們首先創(chuàng)建一個(gè)表格，用我們的基礎(chǔ)語言來存儲(chǔ)有關(guān)這些國(guó)家的信息，創(chuàng)建過程如下：

create table base_countries
(
    a2      char(2) not null,
    a3      char(3) not null,
    num     char(3) not null,
    name    varchar2(60) not null,
    constraint base_countries_pk primary key (a2)
);

現(xiàn)在，我們創(chuàng)建了一個(gè)轉(zhuǎn)化表格。通常情況下，這個(gè)轉(zhuǎn)化表格只需要三個(gè)元素，他們是：表的主碼，語言代碼，以及譯文。如果有多列需要轉(zhuǎn)化的話，我們還可以加一個(gè)標(biāo)識(shí)符來記載當(dāng)前列位置。這也需要我們進(jìn)行對(duì)基礎(chǔ)表的引用，可參照以下代碼：

create table tran_countries
(
    a2      char(2) not null,
    lang    varchar2(2) not null,
    name    varchar2(60) not null,
    constraint tran_countries_pk primary key (a2,lang),
    constraint tran_countries_fk foreign key (a2) references base_countries (a2)
);

然后，我們可以用以下方法在基礎(chǔ)表和轉(zhuǎn)換表上用當(dāng)前指定的語言代碼來創(chuàng)建視圖，其中的語言代碼是在userenv('lang')中設(shè)置的，創(chuàng)建方法如下：

create or replace view nls_countries as
      select base_countries.a2,
           base_countries.a3,
           base_countries.num,
           nvl(tran_countries.name,base_countries.name) name
      from base_countries,tran_countries
     where base_countries.a2 = tran_countries.a2 (+)
       and userenv('lang') = tran_countries.lang (+);

或者，我們還可以用oracle9i的外連接及語法來創(chuàng)建視圖，方法如下：

create or replace view nls_countries as
      select base_countries.a2,
           base_countries.a3,
           base_countries.num,
           nvl(tran_countries.name,base_countries.name) name
      from base_countries left outer join tran_countries
        on base_countries.a2 = tran_countries.a2
       and tran_countries.lang = userenv('lang');

由于這個(gè)視圖是基于外部連接創(chuàng)建的，而且沒有得到與其基表相同的主鍵，所以它也就不存在保護(hù)鍵了。這就意味著我們必須創(chuàng)建“instead-of”觸發(fā)器來完成對(duì)視圖的插入/更新/刪除的操作。就我們上面所講的這個(gè)例子而言，我們可以假設(shè)如果當(dāng)前的用戶語言環(huán)境是英語，那么他們就對(duì)基礎(chǔ)表進(jìn)行操作，否則他們就對(duì)轉(zhuǎn)換表進(jìn)行操作。所以，在實(shí)際運(yùn)用中，你就需要檢查是否存在非英語用戶在對(duì)基礎(chǔ)表進(jìn)行更新或提出異議。那么如何設(shè)計(jì)此觸發(fā)器呢？你可參看以下方法：

create or replace trigger nls_countries_instrg
    instead of insert on nls_countries
    for each row
begin
    if userenv('lang') in ('us','gb') then
        insert into base_countries values (:new.a2,:new.a3,:new.num,:new.name);
    else
        -- this will fail if base_countries doesn't have matching row
        insert into tran_countries values (:new.a2,userenv('lang'),:new.name);
    end if;
end;
/
show errors;

create or replace trigger nls_countries_updtrg
    instead of update on nls_countries
    for each row
begin
    if userenv('lang') in ('us','gb') then
        update base_countries
           set a2 = :new.a2,
               a3 = :new.a3,
               num = :new.num,
               name = :new.name
         where a2 = :old.a2;
    else
        update tran_countries
            set a2 = :new.a2,
                name = :new.name
          where a2 = :old.a2
            and lang = userenv('lang');
    end if;
end;
/
show errors;

create or replace trigger nls_countries_deltrg
    instead of delete on nls_countries
    for each row
begin
    if userenv('lang') in ('us','gb') then
        delete from base_countries where a2 = :old.a2;
    else
        delete from tran_countries
         where a2 = :old.a2 and lang = userenv('lang');
    end if;
end;
/
show errors;
 
 這里我們給出了一些示范數(shù)據(jù)，我們只取那些國(guó)家名以“a”字母開頭的國(guó)家：

insert into base_countries values ('af','afg','004','afghanistan');
insert into base_countries values ('al','alb','008','albania');
insert into base_countries values ('dz','dza','012','algeria');
insert into base_countries values ('as','asm','016','american samoa');
insert into base_countries values ('ad','and','020','andorra');
insert into base_countries values ('ao','ago','024','angola');
insert into base_countries values ('ai','aia','660','anguilla');
insert into base_countries values ('aq','ata','010','antarctica');
insert into base_countries values ('ag','atg','028','antigua and barbuda');
insert into base_countries values ('ar','arg','032','argentina');
insert into base_countries values ('am','arm','051','armenia');
insert into base_countries values ('aw','abw','533','aruba');
insert into base_countries values ('au','aus','036','australia');
insert into base_countries values ('at','aut','040','austria');
insert into base_countries values ('az','aze','031','azerbaijan');

insert into tran_countries values ('af','e','afghanistan');
insert into tran_countries values ('al','cs','albanie');
insert into tran_countries values ('al','d','albanien');
insert into tran_countries values ('al','f','albanie');
insert into tran_countries values ('dz','cs','alzir');
insert into tran_countries values ('dz','d','algerien');
insert into tran_countries values ('dz','e','argelia');
insert into tran_countries values ('dz','f','algerie');
insert into tran_countries values ('as','cs','americka samoa');
insert into tran_countries values ('as','d','amerikanisch-samoa');
insert into tran_countries values ('as','e','samoa americanes');
insert into tran_countries values ('as','f','samoa americana');
insert into tran_countries values ('ad','f','andorre');
insert into tran_countries values ('aq','cs','antarktis');
insert into tran_countries values ('aq','d','antarktis');
insert into tran_countries values ('aq','f','antarctique');
insert into tran_countries values ('ag','cs','antigua a barbuda');
insert into tran_countries values ('ag','d','antigua und barbuda');
insert into tran_countries values ('ag','e','antigua y barbuda');
insert into tran_countries values ('ag','f','antigua-et-barbuda');
insert into tran_countries values ('ar','d','argentinien');
insert into tran_countries values ('ar','f','argentine');
insert into tran_countries values ('am','cs','armanie');
insert into tran_countries values ('am','d','armenien');
insert into tran_countries values ('am','f','armenie');
insert into tran_countries values ('au','cs','australie');
insert into tran_countries values ('au','d','australien');
insert into tran_countries values ('au','f','australie');
insert into tran_countries values ('at','cs','rakousko');
insert into tran_countries values ('at','d','?sterreich');
insert into tran_countries values ('at','f','austriche');
insert into tran_countries values ('az','cs','azerbajdzan');
insert into tran_countries values ('az','d','aserbaidschan');
insert into tran_countries values ('az','e','azerbaijan');
insert into tran_countries values ('az','f','azerba?djan');
commit;
戶語言來對(duì)視圖進(jìn)行查詢：

sql> select * from nls_countries;

a2 a3  num name
-- --- --- ----------------------
af afg 004 afghanistan
al alb 008 albania
dz dza 012 algeria
as asm 016 american samoa
ad and 020 andorra
ao ago 024 angola
ai aia 660 anguilla
aq ata 010 antarctica
ag atg 028 antigua and barbuda
ar arg 032 argentina
am arm 051 armenia
aw abw 533 aruba
au aus 036 australia
at aut 040 austria
az aze 031 azerbaijan

15 rows selected.

sql> alter session set nls_language = 'french';

session modifiee.

sql> select * from nls_countries;

a2 a3  num name
-- --- --- ------------------------------------
af afg 004 afghanistan
al alb 008 albanie
dz dza 012 algerie
as asm 016 samoa americana
ad and 020 andorre
ao ago 024 angola
ai aia 660 anguilla
aq ata 010 antarctique
ag atg 028 antigua-et-barbuda
ar arg 032 argentine
am arm 051 armenie
aw abw 533 aruba
au aus 036 australie
at aut 040 austriche
az aze 031 azerba?djan

15 ligne(s) selectionnee(s).
我們還可以用sql*loader從一個(gè)文本文件中取得轉(zhuǎn)換過的數(shù)據(jù)，并將其存入數(shù)據(jù)庫(kù)中，sql*loader實(shí)際上是調(diào)用了delete（屬于replace）和insert這兩個(gè)觸發(fā)器。如果我們?cè)谶\(yùn)行sql*loader之前設(shè)定了nls_lang參數(shù)，那么插入的數(shù)據(jù)將自動(dòng)地以當(dāng)前設(shè)定的語言代碼插入到轉(zhuǎn)換表中。如下面這個(gè)例子：

load data characterset we8iso8859p1
infile *
replace
into table nls_countries
fields terminated by ',' optionally enclosed by '"'
(a2,name)
begindata
al,"albanie"
dz,"algerie"
as,"samoa americana"
ad,"andorre"
aq,"antarctique"
ag,"antigua-et-barbuda"
ar,"argentine"
am,"armenie"
au,"australie"
at,"austriche"
az,"azerba?djan"

set nls_lang=france_french.we8iso8859p1
sqlldr userid=scott/tiger control=nlsview.ctl

sql*loader: release 8.1.7.0.0 - production on di mar 30 20:26:40 2003
(c) copyright 2000 oracle corporation. all rights reserved.
point de validation (commit) atteint - nombre d'enregis. logiques 11