使用触发器和视图来实现表格的多语言输出

80酷酷网    80kuku.com

  

没有有关的标准化过程,程序开发者们想要开发一个能在多种语言环境下使用的应用程序是非常地困难的。如果可能的话,这些翻译转换过程能在数据库中完成,那么就给开发者们带来了很多便利,使他们能专注于开发本身的事情了。

 
例如,假设我们现在有一个国家的列表。其中国家名是依据最终用户的语言不同而不同。在当时没有翻译器的条件下,我们需要根据用户设置的语言来输出国家名,或是用默认的语言—英语来进行输出。

要达到这个目的,我们首先创建一个表格,用我们的基础语言来存储有关这些国家的信息,创建过程如下:

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



分享到
  • 微信分享
  • 新浪微博
  • QQ好友
  • QQ空间
点击: