oracle中把表导出为txt文件方法，oracletxt

oracle中把表导出为txt文件方法，oracletxt

      本篇列举了几种把oracle 表中的数据导出成txt文件的方法，目前只列举了三种方式，如果后续发现更好的方法会持续添加进来。

1.plsqldev 里面有一个选项可以把表以execl格式到时

2.使用spool

sqlplus / as sysdba

set linesize 1000
set pagesize 0
set echo off
set termout off
set heading off
set feedback off
SET trims ON
set term off
SET trimspool ON
SET trimout ON
spool '/archlog/exp/test.txt';
select OWNER||' , '||SEGMENT_NAME||' , '||PARTITION_NAME||' , ' from dba_segments where rownum<100;
spool off;

#输出的test.txt文件头尾要编辑下
#set term off 只有在使用.sql脚本文件时才起作用，如上虽然指定了 set term off但是还是会把结果输出，也就是说set term off设置只对sql脚本有用

3.使用UTL_FILE程序包

##UTL_FILE.FOPEN第一个参数为文件路径，不能直接指定绝对路径，需要建立directory，然后指定我们建立的directory
sqlplus / as sysdba
create directory MY_DIR as '/home/oracle/';
grant read,write on directory dir_dump to HR;##也可以直接建立一个public directory

CREATE OR REPLACE PROCEDURE test
 IS
testjiao_handle UTL_FILE.file_type;
BEGIN
  test_handle := UTL_FILE.FOPEN('MY_DIR','test.txt','w');
    FOR x IN (SELECT * FROM TESTJIAO) LOOP
      UTL_FILE.PUT_LINE(test_handle,x.ID || ',' || x.RQ ||',');
    END LOOP;
      UTL_FILE.FCLOSE(test_handle);
EXCEPTION WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,2000));
END;
/

分几个步骤
1，创建输出路径，比如你要在c盘test目录下输出，你就先要建立好这个test路径

2，sqlplus下以sysdba登录，执行以下语句

3，create or replace directory TMP as 'c:\test';

4，grant read,write on directory TMP to 你要生成文件的用户;

5，alter system set utl_file_dir='c:\test' scope=spfile;
以上步骤执行完，需要重启数据库

6，最重要的一步开始了，创建存储过程
create or replace PROCEDURE SP_OUTPUTisfile_handle utl_file.file_type;Write_content VARCHAR2(1024);Write_file_name VARCHAR2(50);v_id int;v_form varchar2(10);cursor cur_sp_outisselect id,form from a;beginopen cur_sp_out;loop fetch cur_sp_out into v_id,v_form; exit when cur_sp_out%notfound; write_file_name := to_char(SYSDATE,'YYYYMMDD')||'.txt'; file_handle := utl_file.fopen('TMP',write_file_name,'a'); write_content := v_id||' '||v_form; --write file IF utl_file.is_open(file_handle) THEN utl_file.put_line(file_handle,write_content); END IF; --close file utl_file.fclose(file_handle); end loop; close cur_sp_out;end;
我建立了一个跟你一样的测试表，数据如下
create table a(id int,form varchar2(10));insert into a values (1,'你好');insert into a values (2,'你很好');
然后执行存储过程
begin SP_OUTPUT;end;
执行完毕后，你在c盘test路径下会发现已经有新文件生成

文件内容

存储过程的话，如果表名和字段名跟你一致的话，你无须修改


 

上周做系统演示时需要手工制造一些文本数据充当数据源，想偷偷懒就用了Toad工具直接查询后导出成csv格式。今天正好有点时间，整理了一下思路，用sql去实现文本数据的生成。写了大致框架，有空时再来完善吧。 步骤如下： 新建一个sql脚本 d:\czrk.sql,代码如下： SET echo off SET feedback off SET newpage none SET pagesize 50000 SET linesize 20000 SET verify off SET pagesize 0 SET term off SET trims ON SET heading off SET trimspool ON SET trimout ON SET timing off SET verify off SET colsep | spool d:\czrk.txt SELECT sfzh || ',' || xm || ',' || xb || ',' || csrq || ',' || mz|| ',' || xzqh|| ',' || jzdz FROM m_czrk WHERE rownum<=10000; spool off sqlplus连接上数据库,执行脚本 sqlplus dc/dc@mydb; sqlplus>@d:\czrk.sql 这样就在d盘下生成了一万条数据了。