oracle 增加表字段_[O记]Oracle数据导出

    应测试同学的需求，想将表中数据导出成csv格式，如果用pl/sql developer公交导出，总是在文件的第一列加上了序号，看看能不能有更好的方式可以导出，于是在网上搜刮到一个使用procedure方式实现的脚本，记录下来，已备后查：

导出成csv格式

1.创建procedure

CREATE OR REPLACE PROCEDURE SQL_TO_CSV

(

 P_QUERY IN VARCHAR2, -- PLSQL文

 P_DIR IN VARCHAR2, -- 导出的文件放置目录

 P_FILENAME IN VARCHAR2 -- CSV名

 )

 IS

  L_OUTPUT UTL_FILE.FILE_TYPE;

  L_THECURSOR INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;

  L_COLUMNVALUE VARCHAR2(4000);

  L_STATUS INTEGER;

  L_COLCNT NUMBER := 0;

  L_SEPARATOR VARCHAR2(1);

  L_DESCTBL DBMS_SQL.DESC_TAB;

  P_MAX_LINESIZE NUMBER := 32000;

BEGIN

  --OPEN FILE

  L_OUTPUT := UTL_FILE.FOPEN(P_DIR, P_FILENAME, 'W', P_MAX_LINESIZE);

  --DEFINE DATE FORMAT

  EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''YYYY-MM-DD HH24:MI:SS''';

  --OPEN CURSOR

  DBMS_SQL.PARSE(L_THECURSOR, P_QUERY, DBMS_SQL.NATIVE);

  DBMS_SQL.DESCRIBE_COLUMNS(L_THECURSOR, L_COLCNT, L_DESCTBL);

  --DUMP TABLE COLUMN NAME

  FOR I IN 1 .. L_COLCNT LOOP

    UTL_FILE.PUT(L_OUTPUT,L_SEPARATOR || '"' || L_DESCTBL(I).COL_NAME || '"'); --输出表字段

    DBMS_SQL.DEFINE_COLUMN(L_THECURSOR, I, L_COLUMNVALUE, 4000);

    L_SEPARATOR := ',';

  END LOOP;

  UTL_FILE.NEW_LINE(L_OUTPUT); --输出表字段

  --EXECUTE THE QUERY STATEMENT

  L_STATUS := DBMS_SQL.EXECUTE(L_THECURSOR);

  --DUMP TABLE COLUMN VALUE

  WHILE (DBMS_SQL.FETCH_ROWS(L_THECURSOR) > 0) LOOP

    L_SEPARATOR := '';

    FOR I IN 1 .. L_COLCNT LOOP

      DBMS_SQL.COLUMN_VALUE(L_THECURSOR, I, L_COLUMNVALUE);

      UTL_FILE.PUT(L_OUTPUT,

                  L_SEPARATOR || '"' ||

                  TRIM(BOTH ' ' FROM REPLACE(L_COLUMNVALUE, '"', '""')) || '"');

      L_SEPARATOR := ',';

    END LOOP;

    UTL_FILE.NEW_LINE(L_OUTPUT);

  END LOOP;

  --CLOSE CURSOR

  DBMS_SQL.CLOSE_CURSOR(L_THECURSOR);

  --CLOSE FILE

  UTL_FILE.FCLOSE(L_OUTPUT);

EXCEPTION

  WHEN OTHERS THEN

    RAISE;

END;

/

2.创建目录

create directory dump_csv_dir as '/home/oracle/dump';

grant read,write on directory dump_csv_dir to scott;

3.生成批量导出脚本

SELECT 'EXEC sql_to_csv(''select * from ' ||T.TABLE_NAME ||''',''DATA_PUMP_DIR''' || ',''MONITOR.' || T.TABLE_NAME || '.csv'');' FROM user_TABLES T;

4.执行导出脚本

EXEC sql_to_csv('select * from T1','DATA_PUMP_DIR','MONITOR.T1.csv');

导出成excel格式

set linesize 200

set term off verify off feedback off pagesize 999 

set markup html on entmap ON spool on preformat off

spool test_tables.xls

@get_tables.sql

spool off

exit

--get_tables.sql

select * from all_objects where rownum<=1000;