[oracle@DG1 test]$ ls -R
.:
bin files

./bin:
phone.sql region_new.sh server.conf

./files:
AH.txt          CT_CQ.txt CT_HA.txt   CT_JL.txt   CT_QH.txt CT_TJ.txt GD.txt HE.txt   JX.txt   SD.txt XZ.txt
all_region.txt CT_FJ.txt CT_HB.txt   CT_JS.txt   CT_SC.txt CT_XJ.txt GS.txt HI.txt   LN.txt   SH.txt YN.txt
BJ.txt          CT_GD.txt CT_HE.txt   CT_JX.txt   CT_SD.txt CT_XZ.txt GX.txt HLJ.txt NMG.txt SN.txt ZJ.txt
CQ.txt          CT_GS.txt CT_HI.txt   CT_LN.txt   CT_SH.txt CT_YN.txt GZ.txt HN.txt   NX.txt   SX.txt
CT_AH.txt       CT_GX.txt CT_HLJ.txt CT_NMG.txt CT_SN.txt CT_ZJ.txt HA.txt JL.txt   QH.txt   TJ.txt
CT_BJ.txt       CT_GZ.txt CT_HN.txt   CT_NX.txt   CT_SX.txt FJ.txt     HB.txt JS.txt   SC.txt   XJ.txt

[oracle@DG1 bin]$ cat server.conf
NX:NINGXIA
SD:SHANDONG
SX:SHAN1XI
SH:SHANGHAI
SC:SICHUAN
TJ:TIANJIN
XZ:XIZANG
XJ:XINJIANG
YN:YUNNAN
ZJ:HANGZHOU
HLJ:HEILONGJIANG
NMG:NEIMENGGU
GX:GUANGXI
HE:HEBEI
LN:LIAONING
QH:QINGHAI
SN:SHAN3XI
CQ:CHONGQING
AH:ANHUI
BJ:BEIJING
FJ:FUZHOU
GS:GANSU
GD:GUANGZHOU
GZ:GUIZHOU
HI:HAINAN
HA:HENAN
HB:HUBEI
HN:HUNAN
JL:JILIN
JS:JIANGSU
JX:JIANGXI

[oracle@DG1 bin]$ cat region_new.sh
#!/bin/bash

. /home/oracle/.bash_profile

export BASE_HOME=/home/oracle/test
export BIN_HOME=$BASE_HOME/bin
export FILE_HOME=$BASE_HOME/files

server_list=`cat $BIN_HOME/server.conf`

for server in $server_list ;do
        l_region_cd=`echo $server | cut -d':' -f1`
        l_name=`echo $server | cut -d':' -f2`
        #echo $l_region_cd $l_name
        sqlplus -s scott/oracle @$BIN_HOME/phone.sql $FILE_HOME $l_region_cd

done

[oracle@DG1 bin]$ cat phone.sql
set pagesize 0 feedback off heading off trimspool on echo off termout off serveroutput on verify off

spool &1/&2\.txt
with v as (
select region_id , 'CN' || SYS_CONNECT_BY_PATH(region_cd, '.') || '#' as "REGION_CD"
from region
connect by prior region_id= p_region_id
start with region_type=2 and region_cd='&&2')
select p.range_id || '=' || v.region_cd || p.brand_id from v, phone_range p
where p.region_id=v.region_id and p.telecom=1;

spool off

spool &1/CT_&2\.txt
with v as (
select region_id , 'CN' || SYS_CONNECT_BY_PATH(region_cd, '.') || '#' as "REGION_CD"
from region
connect by prior region_id= p_region_id
start with region_type=2 and region_cd='&&2')
select p.range_id || '=' || v.region_cd || p.brand_id from v, phone_range p
where p.region_id=v.region_id and p.telecom=2;

spool off

spool &1/all_region\.txt
with v as (
select sys_connect_by_path(r.region_cd, '.') || '=' || name as "ID" from region r
connect by prior region_id = p_region_id
start with region_id=1
)
select substr(ID, 2) from v order by id desc;
spool off
exit;

REGION表的定義：
create table REGION
(
region_id         NUMBER(19) not null,
region_type       VARCHAR2(10) not null,
version_num       NUMBER(19) not null,
p_region_id       NUMBER(19),
station_id        VARCHAR2(255),
name              VARCHAR2(40),
code              VARCHAR2(10),
covered           NUMBER(10),
created_by        VARCHAR2(20),
created_date      DATE,
last_updated_by   VARCHAR2(20),
last_updated_date DATE,
region_cd         VARCHAR2(12)
)
;
create index INDX_REGION_PID on REGION (P_REGION_ID)
;
alter table REGION
add primary key (REGION_ID)
using index
tablespace PRODUCT
;
alter table REGION
add constraint FK8FDB1AF4C3BD15E3 foreign key (P_REGION_ID)
references REGION (REGION_ID);

phone_range表的定義：
create table PHONE_RANGE
(
range_id VARCHAR2(7) not null,
shortnum NUMBER,
telecom   NUMBER,
region_id NUMBER,
brand_id NUMBER
)
;
create unique index IND_PHONE_RANGID on PHONE_RANGE (RANGE_ID)
;
alter table PHONE_RANGE
add constraint FK8CA636ECD3F38FA2 foreign key (BRAND_ID)
references BRAND (BRANDID);
alter table PHONE_RANGE
add constraint FK8CA636ECF86DE6F2 foreign key (REGION_ID)
references REGION (REGION_ID);

BRAND表的定義:
create table BRAND
(
brandid   NUMBER(6) not null,
brandname VARCHAR2(20) not null
)
;
alter table BRAND
add primary key (BRANDID)
using index
;