SQL Loader
a.固定格式加载数据
1、建表
create table user21(userid number,
username varchar2(10),
userage number,
usermail varchar2(20));
select * from user21;
2、建excel，写数据，列数和表对应，保存为.csv
3、建ctl文件，保存为.ctl
load data
infile 'D:\appdata\dataport\user21.csv'
insert into table user21
fields terminated by ','
(userid,username,userage,usermail)
4、cmd-sqlldr
C:\Documents and Settings\Administrator>sqlldr userid=dump_test/dump_test control=D:\appdata\dataport\user21.ctl
csv中的数据就被插入表中
数据导出到CSV
1、在目录D:\appdata\dataport中建call.sql文件
conn dump_test/dump_test
set echo off
set term off
set line 1000 pages 0
set feedback off
set heading off
set trimspool on
spool D:\appdata\dataport\ldr_test.csv
select a.userid||',"'||a.username||'",'||a.userage||',"'||a.usermail||'"' from user21 a;
spool off
set trimspool off
set heading on
set feedback on
set term on
set echo on
exit
a.固定格式加载数据
1、建表
create table user21(userid number,
username varchar2(10),
userage number,
usermail varchar2(20));
select * from user21;
2、建excel，写数据，列数和表对应，保存为.csv
3、建ctl文件，保存为.ctl
load data
infile 'D:\appdata\dataport\user21.csv'
insert into table user21
fields terminated by ','
(userid,username,userage,usermail)
4、cmd-sqlldr
C:\Documents and Settings\Administrator>sqlldr userid=dump_test/dump_test control=D:\appdata\dataport\user21.ctl
csv中的数据就被插入表中
数据导出到CSV
1、在目录D:\appdata\dataport中建call.sql文件
conn dump_test/dump_test
set echo off
set term off
set line 1000 pages 0
set feedback off
set heading off
set trimspool on
spool D:\appdata\dataport\ldr_test.csv
select a.userid||',"'||a.username||'",'||a.userage||',"'||a.usermail||'"' from user21 a;
spool off
set trimspool off
set heading on
set feedback on
set term on
set echo on
exit
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/12961536/viewspace-769563/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
