Oracle 将数据导出到CSV(Excel)文件的方法 |
您所在的位置：网站首页 › oracle导出200万条数据 › Oracle 将数据导出到CSV(Excel)文件的方法 |
Oracle 将数据导出到CSV(Excel)文件的方法
|
Oracle 快速将数据导出到CSV(Excel)文件的方法及性能
1，我们工作中有需要将线上数据导出到excel给客户分析/查看的情况，如下是方法介绍情况： 工作中有需要将线上数据导出到excel给客户分析/查看的情况，如下是方法介绍情况： 方法1分钟导出的数据量 ---- 适用于utl_file读写文件包300万 大量导出时plsql developer->export query result10万 小量导出时––excel连接数据库1万spool 循环打印5000–excel 最大行数1048576 方案一、利用SQL_TO_CSV导出.csv文件. --.csv逗号分隔值格式文件，可用excel工具打开，显示格式和excel一样… 1. 创建SQL_TO_CSV存储过程如下： 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(1000); 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、创建 directory目录 create or replace directory OUT_PATH as '/expdp_dir';3、拼接出导出的语句 SELECT 'EXEC sql_to_csv(''select * from ' ||T.TABLE_NAME ||''',''OUT_PATH''' || ',''ODS_MDS.' || T.TABLE_NAME ||'.csv'');' FROM user_TABLES T where t.TABLE_NAME='表名'4、执行导出csv的语句 begin EXEC sql_to_csv('select * from A','OUT_PATH','ODS_MDS.A.csv'); end注解：存储过程是通用的，使用者只需复制代码，然后执行即可，在调用存储时传相应的参数即可。 注释：我用的oracle版本是11g的，在执行第四步时报错，如下： 最后改了下调用存储的语句，如下： begin sql_to_csv('select * from A','OUT_PATH','ODS_MDS.A.csv'); end现在刚才那个报错就没了，是另外一个，如图：
如果是新建的用户，记得授dba权限 --创建用户 create user test（用户名） identified by test1（密码） ; --给用户授权 grant dba to test;最后就应该是没问题啦！ 方案二、plsql developer 使用oracle的sql developer导出（这种方法有人验证过，比上面的方法大概快一倍，但是操作简单）（1）在数据库中找到想要导出的表，右键选择导出。去掉勾选的导出DDL，把格式改成csv，选择相应的编码方式，点击下一步：
（2）可以在此处添加where子句 方案三、excel连接数据库导出 （步骤：打开excel->数据->导入数据->第一步选择数据源->ODBC DSN->根据情况输入连接信息–>选表字段等）
方案四、spool 循环打印 （1）新建spool.sql文件 set colsep , set feedback off set heading off set trimout on spool D:\DBoracle\lfc.csv select '"' || user_name || '","' || user_age || '","' || user_card || '","' || user_sex || '","' || user_addres || '","' || user_tel || '"' from lfc_xinxi_tbl; spool off exit（2）sqlplus命令行调用： sqlplus -s 用户名/密码@数据库名 @spool.sql参数说明： set colsep' '; //-域输出分隔符 set newp none //设置查询出来的数据分多少页显示，如果需要连续的数据，中间不要出现空行就把newp设置为none，这样输出的数据行都是连续的，中间没有空行之类的 set echo off; //显示start启动的脚本中的每个sql命令，缺省为on set echo on //设置运行命令是是否显示语句 set feedback on; //设置显示“已选择XX行” set feedback off; //回显本次sql命令处理的记录条数，缺省为on即去掉最后的 "已经选择10000行" set heading off; //输出域标题，缺省为on 设置为off就去掉了select结果的字段名，只显示数据 set pagesize 0; //输出每页行数，缺省为24,为了避免分页，可设定为0。 set linesize 80; //输出一行字符个数，缺省为80 set numwidth 12; //输出number类型域长度，缺省为10 set termout off; //显示脚本中的命令的执行结果，缺省为on set trimout on; //去除标准输出每行的拖尾空格，缺省为off set trimspool on; //去除重定向（spool）输出每行的拖尾空格，缺省为off set serveroutput on; //设置允许显示输出类似dbms_output set timing on; //设置显示“已用时间：XXXX” set autotrace on-; //设置允许对执行的sql进行分析方案五、查询结果处导出相应的格式文件
|
导出来的文件列名会全部在第一列：如下： 
就错误判断，是无法操作文件之类的异常。最后原因：创建的目录没有读写的权限。所以无法进行文件操作，下面需要授权。
【本文地址】
今日新闻 |
推荐新闻 |