0.目录
1.概述
2.2.1 语法
2.2.2 设定sqlplus变量
2.2.3 DIY导出数据模板
2.3.1 ociuldr导出例子
2.3.2keyword详解
3.1 概述
3.2 例1简单
3.3 例2需要转换数据类型
3.4 例3需要跳过某些行
3.5 例4多个txt
4.表导出和导人实例
---------------------------------------------------------------------------------------
概述
oracle数据库中可以通过sql语句将表数据导出成文本文件，也可以通过spool将表导出成文本文件，也可通过ociuldr工具将表数据导出成文本文件；然后通过sqlldr将文本文件导出到已经建好的表中。常用于，生产数据同步到开发环境，或者表数据迁移。在导出文本文件时，尽量将日期
-----------------------------------------------------------------------------------------
scott>selectEMPNO||'||ENAME||'||JOB||'||MGR||'||HIREDATE||'||SAL||'||COMM||'||DEPTNO from emp;
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变量
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
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
例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详解
Query：设置所要执行的sql语句；
Field：设置每个field间的分隔字符串；
Record：设置每个record间的分隔字符串；
Rows： 指定输出多少行以后打印一条日志，可以更好的观察进度（默认是1000000）
File：设置导出的数据的文件名（如：sqluldrData.txt）；
Charset：设置输出的字符集；
Ncharset: 设置输出的字符集；
支持按照不同的批量导出数据，这通过一个参数batch来实现
默认一个batch是50万条记录，如果不指定
batch为2就表示100万条记录换一个文件
field 分隔符
head 是否打印标题，默认为NO
可以尝试使用ociuldr导出的控制文件将数据加载到数据库中
-------------------------------------------------------------------------------------------
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
（2）ctl控制文件,指明导入的文本，指明导入到的表信息，导入方式
（3）log日志文件，记录导入过程
*.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 hh24:mi:ss',
ENDTIME date 'yyyy-mm-dd hh24:mi:ss',
ETL_TIME TIMESTAMP(6) 'yyyy-mm-dd hh24: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,
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 hh24:mi:ss',
DELFRM,
PAYCODE,
MOVEWHY,
DELCODE,
TYPENO,
REGDATE date 'yyyy-mm-dd hh24:mi:ss',
AGENT_ID,
AGENTNO,
OPER_ID,
OPERNO,
DEPTNO,
DOCPRO,
DOCNO,
PERSON_ID,
PID,
IDTYPE,
PAYTIME,
BEGTIME date 'yyyy-mm-dd hh24:mi:ss',
ENDTIME date 'yyyy-mm-dd hh24:mi:ss',
BRANCH,
ETL_TIME TIMESTAMP(6) 'yyyy-mm-dd hh24:mi:ss.ff6',
SRC_SYS,
DEL_DATE,
REG_CODE,
DEL_TYPE,
SOUR_SYS,
FGSNO,
EMPNO,
SECKEY,
LOAD_TIME TIMESTAMP(6) 'yyyy-mm-dd hh24:mi:ss.ff6',
CONTNO,
SALE_MODE,
THE_THRDPRT,
CSRNO,
JOB,
BUSI_BRANCH,
INSRNC_RATE,
POLICY_NO_TYPE,
FIN_PROC_TYPE,
SETNO
)
原文链接：https://www.f2er.com/oracle/211170.html