0.目录
1.概述
2.生成文本文件
2.1 查询语句生成文本文件
2.2 spool将数据库数据导出成文本文件
2.2.1 语法
2.2.2 设定sqlplus变量
2.2.3 DIY导出数据模板
2.3 windows环境下ociuldr工具生成文本文件
2.3.1 ociuldr导出例子
2.3.2keyword详解
3.sqlldr将文本文件导入到数据库中
3.1 概述
3.2 例1简单
3.3 例2需要转换数据类型
3.4 例3需要跳过某些行
3.5 例4多个txt
4.表导出和导人实例
---------------------------------------------------------------------------------------
概述
oracle数据库中可以通过sql语句将表数据导出成文本文件，也可以通过spool将表导出成文本文件，也可通过ociuldr工具将表数据导出成文本文件；然后通过sqlldr将文本文件导出到已经建好的表中。常用于，生产数据同步到开发环境，或者表数据迁移。在导出文本文件时，尽量将日期
-----------------------------------------------------------------------------------------
2. 生成文本文件
2.1 查询语句生成文本文件
scott>select EMPNO||'||ENAME||'||JOB||'||MGR||'||HIREDATE||'||SAL||'||COMM||'||DEPTNO from emp;
--然后将查询结果粘贴到文本文件中。
2.2 spool将数据库数据导出成文本文件
2.2.1 语法
scott>spool /tmp/emp.txt--打开spool，并指明生成txt文件的路径
scott>select * from emp;--执行sql
scott>spool off--关闭spool
cd /tmp
cat emp.txt
SCOTT>select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
。。。。。。
14 rows selected.
SCOTT>spool off
--可以看到第一个spool之后，到关闭spool之间所有的查询语句和查询结果都会显示到文本文件中。
2.2.2 设定sqlplus的变量
***有时候我们想去美化或者格式化文本文件的内容，需要去设定sqlplus的变量
scott>show all--查询当前会话sqlplus的变量
可以直接输入set去限定当前会话的sqlplus变量
如：默认的sqlplus登录后提示符为sql>,
sql>set sqlprompt "_user>" 提示符就变为当前登录用户，如：scott>
常见和常用的有：
set echo off;--不显示脚本中正在执行的SQL语句
set term off;--不将结果显示在屏幕上，直接导出到文件中
set heading off;--不输出列名
set feedback off; --默认行数大于6行会显示 xx rows selected.
set linesize 100;--每行100个字符
set pagesize 800;--每页800行，默认24
set colsep |;--设定列之间的分隔符
set verify off; --用于绑定变量是不显示old new信息
SCOTT>select * from emp where EMPNO=7369;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------
7369 SMITH CLERK 7902 17-DEC-80 800 20
--绑定变量
SCOTT>select * from emp where EMPNO=&EMPNO;
Enter value for EMPNO: 7369
old 1: select * from emp where EMPNO=&EMPNO
new 1: select * from emp where EMPNO=7369
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------
7369 SMITH CLERK 7902 17-DEC-80 800 20
2.2.3 导出数据模板
cd /tmp
vi spool.sql
--20161222 for spool生成文本文件
set echo off;
set heading off;
set feedback off;
set pagesize 0;
set colsep ,;
set verify off;
spool &1;
select * from &2;
spool off
scott>@/tmp/spool.sql /tmp/emp.txt emp--导出成txt文件
scott>@/tmp/spool.sql /tmp/emp.csv emp--导出成CSV文件
2.3 windows环境下ociuldr工具生成文本文件（ociuldr.exe需自己下载）
2.3.1 ociuldr导出例子
ociuldr 不是内部工具，需要bat所在文件夹里面有ociuldr.exe，oracle客户端配置正常
例1：
步1：下载ociuldr.exe
步2：新建文件，并重命名为out.bat，在out.bat中输入：
ociuldr user=scott/tiger@orcl query="select * from emp" field=# head=YES batch=2 file="test_%%d.txt"
（在命令行中直接输入也可）
步3：双击out.bat
注：bat文件和ociuldr一定要在同一文件夹
例2：
步1：下载ociuldr.exe
步2：新建文件，并重命名为out.bat，在out.bat中输入：
oociuldr.exe userscott/tiger@orcl sql=.\emp.sql field="|"
file=.\emp.txt log=.\emp.log
步3：生成emp.sql文件，内容为查询的语句，不要有最后的分号
步4：双击out.bat
注：bat文件和ociuldr一定要在同一文件夹，sql文件不需要，只有保障路径正确
2.3.2keyword详解
User：指定目标数据库的用户名、密码和tnsname;
Sql : 设置所要执行的sql文件；
Query：设置所要执行的sql语句；
Field：设置每个field间的分隔字符串；
Record：设置每个record间的分隔字符串；
Rows： 指定输出多少行以后打印一条日志，可以更好的观察进度（默认是1000000）
File：设置导出的数据的文件名（如：sqluldrData.txt）；
Log：设置生成的log文件；
Text：设置输出的文件类型；
Charset：设置输出的字符集；
Ncharset: 设置输出的字符集；
支持按照不同的批量导出数据，这通过一个参数batch来实现
默认一个batch是50万条记录，如果不指定
batch为2就表示100万条记录换一个文件
默认这个选项值是0，就是指不生成多个文件。
field 分隔符
head 是否打印，默认为NO
可以尝试使用ociuldr导出的控制文件将数据加载到数据库中
-------------------------------------------------------------------------------------------
3.sqlldr 将文本文件导入到数据库中
3.1 概述
sqlldr为oracle自带的工具，用于将文本文件导入到数据库中。
[root@oracle@zsh bin]#cd /u01/app/oracle/product/11.2.0/dbhome_1/bin
[root@oracle@zsh bin]# ll sql*
-rwxr-x--x 1 oracle oinstall 1362228 Nov 28 23:37 sqlldr
-rwxr-x--- 1 oracle oinstall 0 Aug 13 2009 sqlldrO
-rwxr-x--x 1 oracle oinstall 6889 Nov 28 23:37 sqlplus
sqlldr命令很简单：sqlldr scott/tiger control=/tmp/emp2.ctl log=emp2.log
sqlldr相关的文件：（1）文本文件，需要导入的数据
（2）ctl控制文件,指明导入的文本，指明导入到的表信息，导入方式
（3）log日志文件，记录导入过程
（4）错误数据文件.bad及失败文件.dsc(可以不指明)
sqlldr的控制文件
*.ctl格式为：
load
infile "d://test.txt" 外部数据文件
infile "d://test1.txt" 可指定多个数据文件 (通过多行 infile 语句实现)
append into table test 向表中追加数据
fields terminated by "," 外部文件的数据以“，”分隔
trailing nullcols 表中的字段没有对应的值时填充空值
(
id integer external, integer external 表示插入的数据是string，如果只保留 integer，表示插入的数据是二进制
name "upper(:name)", 将插入的值转换为大写
con ":id||:name", 表中CON列的值是ID和NAME的组合值
dt date"yyyy-mm-dd" 插入日期型数据
)
在append的位置还可以用以下列表中的一个值：
insert 向表中插入值，但要求表开始时为空
replace delete表中的数据，然后插入新值
append 向表中追加数据
truncate trunctate表，然后插入新值
也可以按照字符的位置来导入数据
test.txt的数据如下
11,add,,2007-07-8
12,bd ,,2008-07-8
13,fcd,,2009-07-8
（id position(1:2),
name position(4:6),
dt date"yyyy-mm-dd" position(9:17)--待验证
）
3.2 例1简单
C:\Users\wzj>sqlldr scott/tiger control=d:/dept.ctl
dept.ctl的内容如下：
load
infile "d://test.txt"
append into table tt
fields terminated by ","
trailing nullcols
(
id integer external,
name "upper(:name)",
con ":id||:name",
dt date"yyyy-mm-dd"
)
test.txt的数据如下
1,a,,2007-07-8
2,b,,2008-07-8
3,c,,2009-07-8
SQL> desc tt ;
名称 是否为空? 类型
----------------------
ID VARCHAR2(10)
NAME VARCHAR2(20)
CON VARCHAR2(30)
DT DATE
3.3 例2需要转换数据类型
ID CONSTANT "100"
DT "TRUNC(SYSDATE)"
BEGTIME date 'yyyy-mm-dd hh34:mi:ss' ,
ENDTIME date 'yyyy-mm-dd hh34:mi:ss' ,
ETL_TIME TIMESTAMP(6) 'yyyy-mm-dd hh34:mi:ss.ff6' ,
3.4 例3需要跳过某些行
--一开始就跳 SKIP 5
payment.txt
BEG
1 || 1020100 || 02 || 20120828001 || CTIJ65002412000007 || 375409794 || 01 || 10000 ||
2 || 1020100 || 02 || 20120828001 || CTIJ65002412000006 || 375409794 || 01 || 10001 ||
3 || 1020100 || 02 || 20120828001 || CTIJ65002412000008 || 375409795 || 01 || 10002 ||
END
Load data
infile 'payment.txt'
Append into table tmp_cx_autoagree_ids
when (01) <> 'BEG' and (01) <> 'END'
--when (1:3)<>'BEG' AND (1:3)<>'END'
fields terminated by ' || '
trailing nullcols
3.5 例4多个txt
--yugu_00000000000001.txt 结构如下：
保单号 |被保人×××|分公司|中支|险种|责任|责任子码|期次|频率|金额|渠道|是否生存金|是否预派发|来源系统|给付日期|
010111300053960|372928xxxxxx225626|00000000000001|00000000000103|28950100|200|00|1|2|804.0|31|1|1|APP0001|2015/01/21|
010111300053960|372928xxxxxxxx25626|00000000000001|00000000000103|28950100|200|00|2|2|804.0|31|1|1|APP0001|2016/01/21|
--表bq_prepay_detail_yugu的结构如下：
YEARMTH VARCHAR2(10) Y 应付日期
ORGCODE1 VARCHAR2(24) Y 分公司
ORGCODE2 VARCHAR2(24) Y 中支
POLICYNO VARCHAR2(20) Y 保单号
CLASSCODE VARCHAR2(8) Y 险种
SALEATTR VARCHAR2(10) Y 渠道
DELCODE VARCHAR2(3) Y 给付类型
DELNUM NUMBER(20) Y 期次
TYPENO VARCHAR2(2) Y 责任子码
GUEST_ID VARCHAR2(100) Y 客户ID
JFTYPE VARCHAR2(20) Y
AMT NUMBER(20,2) Y 给付金额
ORGCODE3 VARCHAR2(24) Y 支公司
ISSCJ VARCHAR2(2) Y 是否生存金给付
ISPF VARCHAR2(2) Y 是否未来派发
--控制文件写法： bq_prepay_detail_yugu1446.ctl
load data
infile 'yugu_00000000000001.txt'
infile 'yugu_00000000000002.txt'
infile 'yugu_00000000000003.txt'
infile 'yugu_00000000000004.txt'
infile 'yugu_00000000000005.txt'
infile 'yugu_00000000000006.txt'
infile 'yugu_00000000000007.txt'
infile 'yugu_00000000000008.txt'
infile 'yugu_00000000000009.txt'
infile 'yugu_00000000000010.txt'
infile 'yugu_00000000000011.txt'
infile 'yugu_00000000000012.txt'
infile 'yugu_00000000000013.txt'
infile 'yugu_00000000000014.txt'
infile 'yugu_00000000000015.txt'
infile 'yugu_00000000000016.txt'
infile 'yugu_00000000000017.txt'
infile 'yugu_00000000000018.txt'
infile 'yugu_00000000000019.txt'
infile 'yugu_00000000000020.txt'
infile 'yugu_00000000000021.txt'
infile 'yugu_00000000000022.txt'
infile 'yugu_00000000000023.txt'
infile 'yugu_00000000000024.txt'
infile 'yugu_00000000000025.txt'
infile 'yugu_00000000000026.txt'
infile 'yugu_00000000000027.txt'
infile 'yugu_00000000000028.txt'
infile 'yugu_00000000000029.txt'
infile 'yugu_00000000000030.txt'
infile 'yugu_00000000000031.txt'
infile 'yugu_00000000000032.txt'
truncate into TABLE bq_prepay_detail_yugu
Fields terminated by X'7c'
TRAILING NULLCOLS
(
POLICYNO,
GUEST_ID,
ORGCODE1,
ORGCODE2,
CLASSCODE,
DELCODE,
TYPENO,
DELNUM,
JFTYPE,
AMT,
SALEATTR,
ISSCJ,
ISPF,
orgcode3,
YEARMTH "replace (:YEARMTH,'/','')"
)
命令：
sqlldr bqtj/bqstatistics@FT CONTROL=bq_prepay_detail_yugu1446.ctl LOG=1446.LOG rows=1000 direct=y
4.表导出和导人实例
create table PAETL.C02_RELPAYRC
(
SNO INTEGER,
TYPEID INTEGER,
CON_ID INTEGER,
POLICYNO VARCHAR2(20),
CLASSCODE VARCHAR2(8),
CHECK_ID INTEGER,
CHECKNO VARCHAR2(40),
AMT NUMBER(20,4),
CURRENCY CHAR(3),
GENDATE DATE,
DELFRM CHAR(2),
PAYCODE CHAR(1),
MOVEWHY VARCHAR2(120),
DELCODE CHAR(3),
TYPENO CHAR(2),
REGDATE DATE,
AGENT_ID INTEGER,
AGENTNO VARCHAR2(20),
OPER_ID INTEGER,
OPERNO VARCHAR2(20),
DEPTNO VARCHAR2(20),
DOCPRO VARCHAR2(20),
DOCNO VARCHAR2(20),
PERSON_ID INTEGER,
PID VARCHAR2(40),
IDTYPE CHAR(2),
PAYTIME INTEGER,
BEGTIME DATE,
ENDTIME DATE,
BRANCH CHAR(14),
ETL_TIME TIMESTAMP(6),
SRC_SYS VARCHAR2(20),
DEL_DATE CHAR(8),
REG_CODE VARCHAR2(20),
DEL_TYPE INTEGER,
SOUR_SYS VARCHAR2(20),
FGSNO CHAR(3),
EMPNO CHAR(8),
SECKEY VARCHAR2(20),
LOAD_TIME TIMESTAMP(6),
CONTNO CHAR(8),
SALE_MODE CHAR(4),
THE_THRDPRT CHAR(4),
CSRNO VARCHAR2(20),
JOB CHAR(8),
BUSI_BRANCH CHAR(14),
INSRNC_RATE NUMBER(7,4),
POLICY_NO_TYPE VARCHAR2(1),
FIN_PROC_TYPE INTEGER,
SETNO VARCHAR2(20)
)
--导成txt的bat
cd D:\wgw\Tool
ociuldr.exe user=paetl/paetl0203@LIFEPF_ODB query=" SELECT * FROM C02_RELPAYRC T WHERE T.REGDATE>=to_date('20141001','yyyymmdd') " field="|" file=.\C02_RELPAYRC.txt log=.\C02_RELPAYRC.log
exit
/
sqlldr "paetl/*IK<9ol."@FT CONTROL=.\c02.CTL LOG=.\c02.LOG rows=30000 direct=y
c02.CTL内容：
load data
infile 'C02_RELPAYRC.txt'
truncate into TABLE "C02_RELPAYRC"
Fields terminated by "|"
TRAILING NULLCOLS
(
SNO ,
TYPEID ,
CON_ID ,
POLICYNO ,
CLASSCODE ,
CHECK_ID ,
CHECKNO ,
AMT ,
CURRENCY ,
GENDATE date 'yyyy-mm-dd hh34:mi:ss' ,
DELFRM ,
PAYCODE ,
MOVEWHY ,
DELCODE ,
TYPENO ,
REGDATE date 'yyyy-mm-dd hh34:mi:ss' ,
AGENT_ID ,
AGENTNO ,
OPER_ID ,
OPERNO ,
DEPTNO ,
DOCPRO ,
DOCNO ,
PERSON_ID ,
PID ,
IDTYPE ,
PAYTIME ,
BEGTIME date 'yyyy-mm-dd hh34:mi:ss' ,
ENDTIME date 'yyyy-mm-dd hh34:mi:ss' ,
BRANCH ,
ETL_TIME TIMESTAMP(6) 'yyyy-mm-dd hh34:mi:ss.ff6' ,
SRC_SYS ,
DEL_DATE ,
REG_CODE ,
DEL_TYPE ,
SOUR_SYS ,
FGSNO ,
EMPNO ,
SECKEY ,
LOAD_TIME TIMESTAMP(6) 'yyyy-mm-dd hh34:mi:ss.ff6' ,
CONTNO ,
SALE_MODE ,
THE_THRDPRT ,
CSRNO ,
JOB ,
BUSI_BRANCH ,
INSRNC_RATE ,
POLICY_NO_TYPE ,
FIN_PROC_TYPE ,
SETNO
)
