从Oracle导出数据到TXT文件从TXT文件导入数据到Oracle

如果是txt文件的数据导入oracle中，数据量大的话用pl/sql会卡死，那么就要用到sqlload命令。

前两天在做一个不算任务的任务，用shell脚本将数据库中的数据导出到文件，在万能的百度上找了一些资料，自己亲手实践了一番，脚本语言又有长进。从数据库导出数据到TXT文件，完全OK，那么从TXT文本导入到数据库又怎样操作呢？于是又上万能的度娘，又亲手实践了一上午，发现网上的不少例子很好，但是不尽详细，还是要靠自己理解，因此将学习的经验和结果记录如下。

1.Oracle导出到TXT文本

从Oracle导出数据到TXT文本，是很容易的，这里用到spool，通过spool 命令，可以将select 数据库的内容写到文件中，通过在sqlplus设置一些参数，使得按指定方式写到文件中。

常规使用spool方法，将set的一些命令和spool，select等放入.sql脚本中，然后再sqlplus中运行该脚本。

例如以下设置就是常用的模式，只要将文件名和SQL替换成你自己的就可以正常使用。

set line 1000         --设置行的长度
set pagesize 0        --输出不换页
set feedback off      --默认的当一条sql发出的时候，oracle会给一个反馈，比如说创建表的时候，如果成功命令行会返回类似：Table created的反馈,off后不显示反馈
set heading off       --不显示表头信息
set trimspool on      --如果trimspool设置为on，将移除spool文件中的尾部空
set trims on          --去掉空字符
set echo off;　　　　   --显示start启动的脚本中的每个sql命令，缺省为on
set colsep ','         --设置分隔符
--set termout off        --不在屏幕上显示结果
spool db1.txt          --记录数据到db1.txt
select * from UCR_PARAM.STUDENT2_TEMP;  --导出数据语句
spool off              --收集完毕
exit

使用表student2来做测试，student2的表结构如下：

SQL> desc student2;
Name Null? Type
----------------------------------------- -------- ----------------------------
SID NOT NULL NUMBER(10)
SNAME NOT NULL VARCHAR2(20)
SAGE NOT NULL NUMBER
SSEX NOT NULL CHAR(2)
SADDRESS NOT NULL VARCHAR2(20)
SBIRTH DATE
REMARK VARCHAR2(200)
GRADUATION_DATE DATE

表内的数据：REMARK和GRADUATION_DATE字段为null

下面将使用标准shell脚本连接数据库并将数据导出到TXT，通常的步骤是：

1.在shell脚本设置好对应的TXT文件路径及文件名，SQL语句，log路径等。

2.sqlplus连接数据库，这里使用-s简单模式，连接上以后不输出任何内容，这样可以直接使用spool导出结果到文件

3.设置导出的格式，字段，空格等

注意，以下脚本set部分虽然有去空格，但是空格任然存在，最好在结束使用命令将所有空白字符去除

#去除文件开头的空格及中间的空白字符

#去除文件开头的空格及中间的空白字符
sed -i 's/ //g' ${work_path}/stu_${file_date}.txt

完整的 dbout.sh，前面为注释和举例，核心功能从 sqlplus开始

#!/usr/bin/bash#多行注释
:<<BLOCK
set line 1000         --设置行的长度
set pagesize 0        --输出不换页
set feedback off      --默认的当一条sql发出的时候，oracle会给一个反馈，比如说创建表的时候，如果成功命令行会返回类似：Table created的反馈,off后不显示反馈
set heading off       --不显示表头信息
set trimspool on      --如果trimspool设置为on，将移除spool文件中的尾部空
set trims on          --去掉空字符
set echo off;　　　　   --显示start启动的脚本中的每个sql命令，缺省为on
set colsep '|'         --设置分隔符
--set termout off        --不在屏幕上显示结果
spool $filename          --记录数据到db1.txt
$sqlinfo2;  --导出数据语句
spool off              --收集完毕
exit
BLOCK
#可以在这里拼接语句，用|或;,但是逗号,拼接','不行
#逗号拼接可以在后面的sqlplus中设置列分隔符：set colsep ','
sqlinfo="select  B.ORDER_ID||';'||B.PEER_ORDER_ID||';'||B.BUSI_CODE||';'||B.ACCOUNT_CODE||';'||A.TRADE_FEE||';'||A.RECEIPT_FEE||';'||A.RATE_FEE||';'||A.BUSI_CODE||';'||A.PAY_STATUS||';'||to_char(A.CHECK_DATE, 'yyyy-mm-dd') from ucr_cen.UPG_ORDER_PAY_DETAIL A,ucr_cen.I_UPG_ORDER_TMP BWHERE B.MERCHANT_ID = '510915'AND B.PEER_ORDER_ID != '-1'AND A.CHECK_STATUS IN ('0','2','3')AND A.STATE = 'U'AND A.PAY_STATUS = '1'AND A.CHECK_DATE BETWEEN TRUNC(SYSDATE-1) AND TRUNC(SYSDATE)-1/86400 AND A.ORDER_ID = B.ORDER_ID;"#以上为举例和注释，核心功能从这里开始
sqlinfo2="select SID,SNAME,SAGE,to_char(GRADUATION_DATE,'yyyy-mm-dd'),SSEX,SADDRESS,REMARK,to_char(SBIRTH,'yyyy-mm-dd') from UCR_PARAM.STUDENT2;" 
#sqlinfo2="select SID,SNAME,SAGE,to_char(GRADUATION_DATE,'yyyy-mm-dd'),SSEX,SADDRESS,to_char(SBIRTH,'yyyy-mm-dd'),REMARK from UCR_PARAM.STUDENT2;"
#不设置拼接时，使用逗号分隔符 set colsep ',' ，当该列为空时会返回占位的空白字符，一般此种情况用于后续将TXT文本直接导入数据库方便
start_time=`date +%Y%m%d%H%M%S`
work_path=/home/billing/user/zhf/test
DbCon=用户名/密码@所属组
file_date=`date +%Y%m%d`
filename=${work_path}/stu_${file_date}.txt;echo "----------------  start to exec job   $start_time "
echo "----------------  sqlinfo = $sqlinfo2 "
#echo "----------------  start to exec job   $start_time ">>${work_path}/$start_time.log
#echo "----------------  sqlinfo = $sqlinfo2 ">>${work_path}/$start_time.log
echo "----------------  connect to $DbCon and try to exec sql..."
#echo "----------------  connect to $DbCon and try to exec sql...">>${work_path}/$start_time.logsqlplus -S $DbCon<< EndSql
set line 1000
set pagesize 0
set feedback off
set heading off
set trimspool on
set trims on
set echo off
set colsep ','
--set termout off
spool $filename
$sqlinfo2
spool off
exit
EndSql
#去除文件开头的空格及中间的空白字符
sed -i 's/ //g' ${work_path}/stu_${file_date}.txt
end_time=`date +%Y%m%d%H%M%S`
echo "----------------  cmd end , please check te result $end_time"
#echo "----------------  cmd end , please check te result $end_time">>${work_path}/$start_time.log

导出的结果，注意，两个逗号,,分隔的字段没有内容即为null值，此处该两个null字段在中间，不在结尾，后面将看另一种情况：即null字段在结尾的情况下

生成文件暂命名为：stu_20190810.txt

1,lily,24,,M,Shanxi,,1993-01-14
2,kity,17,,WM,Shanxi,,2001-01-14
3,bobo,27,,WM,Shanxi,,1991-01-14
4,anna,29,,WM,Shanxi,,1989-01-14

2.从TXT文本导入Oracle数据表

从TXT文本文件导入数据库其实也简单，格式依然是固定sqlldr的控制文件

我们先新建一张表student3

create table UCR_PARAM.STUDENT3 as select * from UCR_PARAM.STUDENT2 where 1=2;

--将字段GRADUATION_DATE修改为varchar类型，因为该字段为null，在插入时，date转换不接受null，因此用varchar来接收

alter table UCR_PARAM.STUDENT3 modify GRADUATION_DATE varchar2(14)；

student3表结构如下：

打开一个.ctl文件，插入以下内容

vi tb_sqlldr.ctl

load data                 
infile 'stu_20190810.txt' --数据来源文本 
APPEND into table ucr_param.STUDENT3-- 原先的表有数据 就加在后面
--INSERT into table ucr_param.STUDENT3-- 装载空表 如果原先的表有数据 sqlloader会停止 默认值
--REPLACE into table ucr_param.STUDENT3-- 原先的表有数据 原先的数据会全部删除
--TRUNCATE into table ucr_param.STUDENT3-- 指定的内容和replace的相同 会用truncate语句删除现存数据 
fields terminated by "," --4、字段终止于X'09'，是一个制表符(tab) (id,name) --定义对应的字段名称，注意顺序
TRAILING NULLCOLS --如要导入源文件此列内容为空，在导入到数据库表中，此列内容就是null
--读取的TXT字段对应数据库中的字段，注意顺序要与TXT字段顺序一直
(SID	"to_number(:SID)" ,
SNAME 	,
SAGE	"to_number(:SAGE)" ,
GRADUATION_DATE "NVL(:GRADUATION_DATE, '未知')",--"NVL(to_date(:GRADUATION_DATE, 'yyyy-mm-dd'),'NULL')", date为null时插入行不通，date函数不接受null值，因此修改表结构
SSEX	,
--NULLIF (expr1, expr2)，若expr1和expr2相等，返回NULL；不相等，等返回expr1
SADDRESS "NULLIF(:SADDRESS,'NULL')",
--NVL(expr1,expr2)，若expr1为null, 返回expr2; 不为null,返回expr1。  注意：两者类型要一致
REMARK "NVL(:REMARK,'NULL')",
SBIRTH	date"yyyy-mm-dd" ) -- coulmun

注意，此处GRADUATION_DATE和REMARK字段为null，那么这一句就很重要，否则sql loader 读取到null值，插入时会报错

TRAILING NULLCOLS --如要导入源文件此列内容为空，在导入到数据库表中，此列内容就是null

date类型的数据插入时可以使用两种方式插入：

SBIRTH    date"yyyy-mm-dd" 或 SBIRTH    "to_date(:SBIRTH, 'yyyy-mm-dd')"

注意，读取的字段内容为空的，加入该字段判断：

--NVL(expr1,expr2)，若expr1为null, 返回expr2; 不为null,返回expr1。  注意：两者类型要一致
REMARK "NVL(:REMARK,'NULL')"

新建一个shell脚本调用该控制文件，dao.sh

#!/bin/ksh 
#注意，顶行是固定格式
sqlldr userid=数据库名/密码@所属库 control=tb_sqlldr.ctl

调用：dao.sh

结果验证：

另一种情况，导出的TXT文件null字段刚好在行尾

放开dbout.sh的另一种sqlinfo2的情况

生成的TXT文件结果：行尾有逗号

1,lily,24,,M,Shanxi,1993-01-14,
2,kity,17,,WM,Shanxi,2001-01-14,
3,bobo,27,,WM,Shanxi,1991-01-14,
4,anna,29,,WM,Shanxi,1989-01-14,

调整ctl控制文件的字段顺序，也可以导入：

load data                 
infile 'stu_20190810.txt' --数据来源文本 
APPEND into table ucr_param.STUDENT3-- 原先的表有数据 就加在后面
--INSERT into table ucr_param.STUDENT3-- 装载空表 如果原先的表有数据 sqlloader会停止 默认值
--REPLACE into table ucr_param.STUDENT3-- 原先的表有数据 原先的数据会全部删除
--TRUNCATE into table ucr_param.STUDENT3-- 指定的内容和replace的相同 会用truncate语句删除现存数据 
fields terminated by "," --4、字段终止于X'09'，是一个制表符(tab) (id,name) --定义对应的字段名称，注意顺序
TRAILING NULLCOLS --如要导入源文件此列内容为空，在导入到数据库表中，此列内容就是null
(SID    "to_number(:SID)" ,
SNAME   ,
SAGE    "to_number(:SAGE)" ,
GRADUATION_DATE "NVL(:GRADUATION_DATE, '未知')",--"NVL(to_date(:GRADUATION_DATE, 'yyyy-mm-dd'),'NULL')",
SSEX    ,
--NULLIF (expr1, expr2)，若expr1和expr2相等，返回NULL；不相等，等返回expr1
SADDRESS "NULLIF(:SADDRESS,'NULL')",
SBIRTH  "to_date(:SBIRTH, 'yyyy-mm-dd')" ,
--NVL(expr1,expr2)，若expr1为null, 返回expr2; 不为null,返回expr1。  注意：两者类型要一致
REMARK "NVL(:REMARK,'NULL')") -- coulmun

注意，如果表字段数据类型数长字符串，大于255时，在导入的时候需要指定导入字段的最大长度，否则会因为导入字段超过255时超长失败。导入的字段指定长度如下，且要使用char定长类型。

(COLL_HOST char(32),
WARN_INFO       char(2000),
UPDATE_TIME     char(32)
)