SPOOL缓冲池导出数据;
1.数据泵虽然功能强大;但有一点非常尴尬;就是它面向的对象仍为Oracle;
导出的文件不能直接导入到其他种类的数据;且导出文件为二进制文件;
也不能直接查看导出内容。为了给下游系统提供数据;或某些时候以表格形式为业务人员提供大量数据;可以采用SPOOL缓冲池技术。
2.SPOOL缓冲池需要在SQLPLUS工具中使用;为了导出格式正确;需要提前设置一些参数
;数据泵导入导出可以在我的博客里面看;
--设置SQLPLUS参数 只在当前SQLPLUS工具中有效
set trimspool on --是否删除每行后的空白字符;建议on
set trimout on --是否允许每行后的空格;建议on
set echo off --是否显示执行命令的内容;建议off
set newpage none --一页中空行的数量;建议无空行
set feedback off --是否显示“返回n行”;建议off
set linesize 200 --每行的字符长度;一定要根据数据行的长度设置;过少自动换行;过长文件过大
set pagesize 0 --每页的间隔;建议无间隔
set heading off --是否打印列标题;建议off
set term off --控制当使用;、;;或START命令执行一个命令文件时;是否显示中间结果;建议off
set timing off --是否显示命令时间;建议off
--启动spool 并设置导出路径和文件名称及类型(常用;.txt或.csv或.dat)
spool D:pathfilename.txt
--设置查询内容
SELECT col1||;|;||col2||;|;||col3||;|;||col4... FROM tb_name;
--结束spool
spool off
我们导出还是以emp表为例;写一个.sql脚本;脚本内容如下;并将其封装。
set trimspool on
set trimout on
set echo off
set newpage none
set feedback off
set linesize 200
set pagesize 0
set heading off
set term off
set timing off
SPOOL D:DIR1EMP.DAT
SELECT EMPNO||;|;||ENAME||;|;||JOB||;|;||MGR||;|;||TO_CHAR(HIREDATE,;YYYY-MM-DD;)||;|;||SAL||;|;||COMM||;|;||DEPTNO FROM EMP;
spool off
==========================================
SPOOL D:DIR1EMP.DAT --导出为EMP.DAT文件;存放在D:DIR87里
我是把脚本放在D盘的dir1文件里;D:DIR1;;这个是实际路径
执行导出命令;导出为一个.DAT文件;调用脚本;
;进入SQL的方法;cmd打开命令行窗口;输入sqlplus / as sysdba 登录数据库;
输入conn scott/scott --连接scott用户;我是在scott用户下执行的;;
SQL>;D:DIR1EMP.SQL
这是执行成功了;如果失败会报错。
然后我们去路径 ;D:DIR1;看一下
我们可以发现EMP.sql就是用来导出数据的数据库脚本;内容与上面的一致;而EMP.DAT就是我们导出的数据文件;其内容如下
这是根据我们的查询语句导出的数据;一会导入的时候可以以这个文件为数据文件导入数据
SQLLDR导入数据
介绍;
SQLLDR是ORACLE另一项非常方便的工具;它常用来接收其它种类数据库导出的文本文件。
它的命令组成中常包括以下几项内容;
数据文件;即接收到的文本文件;常见.dat.txt;
控制文件;一种控制数据如何加载的控制文件;.ctl;
日志文件;记录sqlldr的加载过程、执行情况、报错信息等;.log;
错误文件;记录数据加载过程中因各种问题导入失败的数据;.bad;
SQLLDR导入数据必须的文件是.dat数据文件和.ctl控制文件;而日志文件和错误文件是在执行时创建的
数据文件我们用上面导出的EMP.DAT文件就行;现在就需要编写一个控制文件
语法如下;
--ctl文件配置;
OPTIONS (skip=1,rows=128) --skip决定跳过的行;rows决定每加载多少行提交一次数据
LOAD DATA --数据加载
CHARACTERSET ZHS16GBK --设置编码 ZHS16GBK或 ;UTF8;
INTO TABLE tb_name --目标表
INSERT|APPEND|REPLACE|TRUNCATE --数据加载模式
FIELDS TERMINATED BY ;|; --字段间分隔符设置
OPTIONALLY ENCLOSED BY ;;; --数据括起方式;没有可以忽略
TRAILING NULLCOLS --字段无对应数据时允许为空
( col_1 ;trim(:col_1); ,
col_2 ;trim(:col_2); ,
col_3 ;trim(:col_3); ,
col_4 ;to_date(:col_4,;YYYY-MM-DD;); ,
TS ;to_char(sysdate,;YYYY-MM-DD HH24:MI:SS;) )
--数据加载模式
类似于IMPDP功能;SQLLDR进行数据加载时也提供了不同的加载方式
1;INSERT;直接插入数据;但是要求目标表中不能有数据存在;该方式也是默认方式;--;一般不用;
2;APPEND;增量加载数据;表中原数据保留;将新数据加载到目标表;
3;REPLACE;覆盖旧数据;类似于加载数据前对目标表执行了删除数据操作;DELETE FROM tb_name;
4;TRUNCATE;覆盖旧数据;类似于加载数据前对目标表执行了清空数据操作;TRUNCATE TABLE tb_name; 这种方式是最常用的一种。
编写控制文件;
OPTIONS (skip=0,rows=128)
LOAD DATA
CHARACTERSET ZHS16GBK
INTO TABLE EMP
TRUNCATE
FIELDS TERMINATED BY ;|;
TRAILING NULLCOLS
(EMPNO ; TRIM(:EMPNO );,
ENAME ; TRIM(:ENAME );,
JOB ; TRIM(:JOB );,
MGR ; TRIM(:MGR );,
HIREDATE ; TO_DATE(TRIM(:HIREDATE),;YYYY-MM-DD;); ,
SAL ; TRIM(:SAL );,
COMM ; TRIM(:COMM );,
DEPTNO ; TRIM(:DEPTNO );
)
--将其封装成.CTL文件放到;D:DIR1;路径下
-sqlldr命令;--不需要连接数据库用户;但需要进入安装了数据库的计算机用户
语法;
sqlldr --sqlldr启动
user_name/pwd;ip:1521/db_name --指定用户名、密码、IP和实例名称
data=pathfilename.dat --数据文件路径;可txt、dat、csv等文本文件
control=pathfilename.ctl --控制文件路径
bad=pathfilename.bad --错误文件路径;存储导入失败的数据
log=pathfilename.log --日志文件路径;记录导入过程
errors=0 --允许失败数据量;一般设为0;即一旦出现失败即停止
--在cmd窗口运行以下命令导入数据 (以下命令是一行;不可以分行)
sqlldr SCOTT/scott;192.168.2.72:1521/orcl data=D:DIR1EMP.DAT control=D:DIR1EMP.ctl bad=D:DIR1EMP.bad log=D:DIR1EMP1.log errors=0
执行结果;
运行成功;提交了14条数据
我们再来看看DIR1文件夹里都有什么现在
;里头EMP.DMP和EMP.LOG是我之前用数据泵导出的数据文件和日志文件;
所以我们可以发现EMP.sql是我们编写用来导出EMP.DAT;数据文件;的脚本;而EMP.CTL则是上面编写的控制文件;EMP1.LOG是导入数据时生成的日志文件;因为没有错误;所以没有错误文件。
我们可以看一下导入数据时生成的日志文件
里面就是一些信息和运行的时间。
至此;数据的导出导入就是成功了。
Navicat for MySQL怎么导入数据表?- Navicat for MySQL导入数据表教程攻略
Navicat for MySQL怎么导入数据表?-Navicat for MySQL导入数据表教程