在oracle中用sql语句不只是可以访问正规的数据表,还可以访问具有一定格式的外部文件(比如CSV文件).具体方法:
 将放在d:\test目录下的两个文件'20061222名词表.txt','参照.txt',映射到两个数据库表中,然后就可以实现用SQL语句访问普通的CSV文件.
create directory dir_test as 'd:\test';

create table tbl_zidian
(
name varchar2(500),english varchar2(1000)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY dir_test
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
badfile 'x1.bad'
discardfile 's1.dis'
logfile 'x1.log'
fields terminated by ','
missing field values are null
)
LOCATION('20061222名词表.txt')
);

create table tbl_zidian_3
(
name varchar2(100)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY dir_test
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
badfile 'x1.bad'
discardfile 's1.dis'
logfile 'x1.log'
fields terminated by ','
missing field values are null
)
LOCATION('参照.txt')  --目录里
);

create table tbl_tt2 as
select distinct ch_name,en_name,ch0_name from (
select b.name ch_name,a.english en_name,a.name ch0_name
from tbl_zidian a,tbl_zidian_3 b
where (a.name like '%'||b.name||'%') or (b.name like '%'||a.name||'%'));

select name,'','' from tbl_zidian_2
where name not in(select ch_name from tbl_tt) order by name