使用觸發器和視圖來實現表格的多語言輸出

來源:互聯網
上載者:User

沒有有關的標準化過程,程式開發人員們想要開發一個能在多種語言環境下使用的應用程式是非常地困難的。如果可能的話,這些翻譯轉換過程能在資料庫中完成,那麼就給開發人員們帶來了很多便利,使他們能專註於開發本身的事情了。

 
例如,假設我們現在有一個國家的列表。其中國家名是依據終端使用者的語言不同而不同。在當時沒有翻譯器的條件下,我們需要根據使用者佈建的語言來輸出國家名,或是用預設的語言—英語來進行輸出。

要達到這個目的,我們首先建立一個表格,用我們的基礎語言來儲存有關這些國家的資訊,建立過程如下:

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

現在,我們建立了一個轉化表格。通常情況下,這個轉化表格只需要三個元素,他們是:表的主碼,語言代碼,以及譯文。如果有多列需要轉化的話,我們還可以加一個標識符來記載當前列位置。這也需要我們進行對基礎資料表的引用,可參照以下代碼:

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

然後,我們可以用以下方法在基礎資料表和轉換表上用當前指定的語言代碼來建立視圖,其中的語言代碼是在USERENV('LANG')中設定的,建立方法如下:

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的外串連及文法來建立視圖,方法如下:

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

  

由於這個視圖是基於外部串連建立的,而且沒有得到與其基表相同的主鍵,所以它也就不存在保護鍵了。這就意味著我們必須建立“instead-of”觸發器來完成對視圖的插入/更新/刪除的操作。就我們上面所講的這個例子而言,我們可以假設如果當前的使用者語言環境是英語,那麼他們就對基礎資料表進行操作,否則他們就對轉換表進行操作。所以,在實際運用中,你就需要檢查是否存在非英語使用者在對基礎資料表進行更新或提出異議。那麼如何設計此觸發器呢?你可參看以下方法:

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;
 
 這裡我們給出了一些示範資料,我們只取那些國家名以“A”字母開頭的國家:

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;
戶語言來對視圖進行查詢:

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從一個文字檔中取得轉換過的資料,並將其存入資料庫中,SQL*Loader實際上是調用了DELETE(屬於REPLACE)和INSERT這兩個觸發器。如果我們在運行SQL*Loader之前設定了NLS_LANG參數,那麼插入的資料將自動地以當前設定的語言代碼插入到轉換表中。如下面這個例子:

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




相關文章

Beyond APAC's No.1 Cloud

19.6% IaaS Market Share in Asia Pacific - Gartner IT Service report, 2018

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。