如果是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 B
WHERE 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.log
sqlplus -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)
)