了解Web应用程序中的国际邮政规则

在建立起了一个商业关系之后，你几乎会自动地向客户索取其邮件地址。应用程序设计者典型地会选择他们最熟悉的，其主要客户使用最多的一种地址格式。

但是如果那些分类不能应用到某个客户的地址，可能就要强迫一个用户输入一个邮政编码或者一个州名或者省名。而且，检查邮政信息是否有效也能够保证你将信件发到正确的目的地。

一个国家的邮政地址中的最重要的信息是“城市地址”。不同的国家之间这个地址的格式差别很大，没有什么信息是强制性的。然而，（在大多情况下）依然有可能提供足够的信息来产生合理的准确的城市地址。

下面是一个例子，该例子使用字符串同时正确地产生几个国家的城市地址：

create table postal_countries
(
    country char(3) not null,
    name varchar2(100) not null,
    fmt varchar2(100)
);
insert into postal_countries values('BWA','Botswana','town');
insert into postal_countries values('CAF','Central African Republic','town');
insert into postal_countries values('CAN','Canada','town rg postcode');
insert into postal_countries values('CHE','Switzerland','postcode town');
insert into postal_countries values('CHL','Chile','postcode town')

create table postal_regions
(
    country char(3) not null,
    code    varchar2(5) not null,
    name    varchar2(100) not null
);

insert into postal_regions values ('CAN','AB','Alberta');
insert into postal_regions values ('CAN','BC','British Columbia');
insert into postal_regions values ('CAN','MB','Manitoba');
insert into postal_regions values ('CAN','NB','New Brunswick');
insert into postal_regions values ('CAN','NL','Newfoundland and Labrador');
insert into postal_regions values ('CAN','NS','Nova Scotia');
insert into postal_regions values ('CAN','NT','Northwest Territories');
insert into postal_regions values ('CAN','NU','Nunavut');
insert into postal_regions values ('CAN','ON','Ontario');
insert into postal_regions values ('CAN','PE','Prince Edward Island');
insert into postal_regions values ('CAN','QC','Quebec');
insert into postal_regions values ('CAN','SK','Saskatchewan');
insert into postal_regions values ('CAN','YT','Yukon');

create table postal_addresses
(
    country     char(3) not null,
    town        varchar2(100),
    region      varchar2(5),
    postcode    varchar2(50)
);
insert into postal_addresses values('BWA','Gaborone',null,null);
insert into postal_addresses values('CAF','Bangui',null,null);
insert into postal_addresses values('CAN','Moose Jaw','SK','S6H 2X1');
insert into postal_addresses values('CHE','Zurich',null,'8037');
insert into postal_addresses values('CHL','Santiago',null,'6500709');

create or replace view postal_view
as
  select replace(replace(replace(replace(pc.fmt,'town',pa.town),
    'postcode',pa.postcode),
    'rg',pr.code),
    'region',pr.name) cityline
  from postal_addresses pa,
       postal_countries pc,
       postal_regions pr
 where pa.country = pc.country
   and pa.country = pr.country (+)
   and pa.region = pr.code (+);

select * from postal_view;

create or replace view postal_view_9i
as
  select replace(replace(replace(replace(pc.fmt,'town',pa.town),
    'postcode',pa.postcode),'rg',pr.code),'region',pr.name) cityline
  from (postal_addresses pa inner join postal_countries pc
    on pa.country = pc.country)
   left outer join postal_regions pr
   on pa.country = pr.country and pa.region = pr.code;

select * from postal_view_9i;

第一个视图产生格式与大多Oracle版本兼容的数据；第二个版本采用Oracle9i的内/外连接语法。两个查询的结果相同，如下所示：

CITYLINE
-----------------------------------------------------------------
--
Moose JawSKS6H 2X1
8037 Zurich
Gaborone
Bangui

清楚全面的各个国家的邮政规则可以从Frank's Compulsive Guide to Postal Addresses得到。这个站点描述世界上每个国家的正确的邮政地址格式。（站点的作者将这作为一个自由的公共服务。）另外还有几个商业公司推出了检查数据库中地址格式是否正确的软件。