在oracle中用sql語句不只是可以訪問正規(guī)的數(shù)據(jù)表,還可以訪問具有一定格式的外部文件(比如CSV文件).具體方法:
?將放在d:\test目錄下的兩個(gè)文件'20061222名詞表.txt','參照.txt',映射到兩個(gè)數(shù)據(jù)庫表中,然后就可以實(shí)現(xiàn)用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