将oracle库中表导出为外部文本并指定分隔符

opo

方法一：使用spool进行保存

• SQL> spool emp.txt
• SQL> select empno||','||ename||','||job||','||mgr||','||sal from emp;
• EMPNO||','||ENAME||','||JOB||','||MGR||','||SAL
• ----------------------------------------------------------------------------------------------------
• 7369,SMITH,CLERK,7902,800
• 7499,ALLEN,SALESMAN,7698,1600
• 7521,WARD,SALESMAN,7698,1250
• 7566,JONES,MANAGER,7839,2975
• 7654,MARTIN,SALESMAN,7698,1250
• 7698,BLAKE,MANAGER,7839,2850
• 7782,CLARK,MANAGER,7839,2450
• 7788,SCOTT,ANALYST,7566,3000
• 7839,KING,PRESIDENT,,5000
• 7844,TURNER,SALESMAN,7698,1500
• 7876,ADAMS,CLERK,7788,1100
• 7900,JAMES,CLERK,7698,950
• 7902,FORD,ANALYST,7566,3000
• 7934,MILLER,CLERK,7782,1300
• 14 rows selected.
• SQL> spool off;
  

自己写了简单脚本进行导出指定表到外部文本：

• #!/bin/bash
• ##jxp   
• ##导出指定表结构
• tname=$1
• sqlplus -S scott/tiger <<EOF
• spool tname1.txt
• desc $tname
• spool off
• exit
• EOF
• ##截取表内字段名
• sed -n '3,$p' tname1.txt| awk '{print$1}' |sed '$d' >tname2.txt
• ##使用abcd.sed把字段名进行列转行输出并使用'||'作为分隔符传入tname_1
• tname_1=`sed -f abcd.sed tname2.txt`
• #echo $tname_1
• ##传入表名，在sqlplus中进行选择数据
• new_tname=$tname
• sqlplus -S scott/tiger <<EOF
• set pagesize 0;
• set linesize 300;
• set feedback off;
• alter session set nls_date_format='YYYY-MM-DD hh24:mi:ss'
• spool $new_tname.txt
• select $tname_1  from  $new_tname;
• spool off
• exit;
• EOF
• ##删除产生的临时文件
• rm -rf tname*.txt
• #
  

需要修改的可能是登陆数据库的用户名了。其中脚本中用到了abcd.sed文件，下面列出此文件内容和作用：

• {
• N
• s/\n/\|\|\'||\'\|\|/g
• }
• {
• N
• s/\n/\|\|\'||\'\|\|/g
• }
• {
• N
• s/\n/\|\|\'||\'\|\|/g
• }
• {
• N
• s/\n/\|\|\'||\'\|\|/g
• }
• {
• N
• s/\n/\|\|\'||\'\|\|/g
• }
• {
• N
• s/\n/\|\|\'||\'\|\|/g
• }
  

此处指列出abcd.sed（abcd是随便起的名字）的简单几行，如果导出的表的字段较多的话，需要补充abcd.sed的内容，直接复制粘贴以上内容就Ok。
abcd.sed的主要作用就是把需要导出表的字段进行合并使用“||”连接为一行。分隔符“||”可以自己指定其他的符号替代。

脚本的使用：

把exp_table.sh和abcd.sed放到一个目录下直接运行sh脚本，后面跟用户下的表名

• [oracle@jxpred test_dir]$ nohup ./exp_table.sh dept
• [oracle@jxpred test_dir]$ cat dept.txt  
• 10||ACCOUNTING||NEW YORK
• 20||RESEARCH||DALLAS
• 30||SALES||CHICAGO
• 40||OPERATIONS||BOSTON
  

方法二：使用oracle中的UTL_FILE

conn sys/oracle as sysdba

--在初始化参数文件中加入下面一行

utl_file_dir=d:\bk

• alter system set utl_file_dir='/home/oracle/exp' scope=spfile;
  

这个需要重启数据库，比较严重哦。

--其中\n为换行

--%s为替代字符，将来会被后面的1到5个参数替代，默认值为NULL

--NEW_LINE 过程建立一个新的空行

• -------------------案例：将DEPT表的数据导入到文本中-----------------------------
• declare
• v_filehandle UTL_FILE.FILE_TYPE;
• begin
• v_filehandle:=utl_file.fopen('/home/oracle/exp','dept.txt','w');
• UTL_FILE.PUTF (v_filehandle,'表DEPT的文本数据，导出时间为：%s\n', SYSDATE);
• UTL_FILE.NEW_LINE (v_filehandle);
• for i in(select * from scott.dept) loop
• UTL_FILE.PUTF (v_filehandle, '%s ,%s, %s\n',i.deptno,i.dname,i.loc);
• end loop;
• UTL_FILE.FCLOSE (v_filehandle);
• end;
• /
  

此方法需要将表的所用的字段都写到UTL_FILE.PUTF后 ：

• UTL_FILE.PUTF (out_file, '%s||%s||%s||%s||%s||',i.TYPEID,i.SYSTEMID,i.SOURCEID,i.SOURCENAME,i.DESTINATIONID);
• UTL_FILE.PUTF (out_file, '%s||%s||%s||%s||%s||',i.DESTINATIONNAME,i.REMARK,i.OPRTR,i.OPRT_TIME,i.SORT_NO);
• UTL_FILE.PUTF (out_file, '%s\n',i.PARENTID);
  

而且每个UTL_FILE.PUTF中最多只能有5个%s替代符。多的字段需要换行继续写UTL_FILE.PUTF内容。此处使用的是“||”作为 分隔符，可以替换其他分隔符。

以上是oracle较常用的两种导出表到文本的方法。

FXMAR

我是个凑数的。。。

ywg

生活***好玩，因为生活老***玩我!

suncool

看尽天下A片，心中自然无码～

小雪崩

勿以坑小而不灌，勿以坑大而灌之。

踏雪寻梅

长得真有创意，活得真有勇气!

a13698822086

我抢、我抢、我抢沙发~