代码之家 › 专栏 › 技术社区 › FrustratedWithFormsDesigner

sqlplus-从pl/sql块假脱机到多个文件

spool sqlplus plsql oracle

FrustratedWithFormsDesigner · 技术社区 · 14 年前

我有一个将大量数据返回到csv文件的查询。事实上,Excel打开不了这么多行-行太多了。有办法控制吗 spool 是否在每次处理65000行后假脱机到新文件?理想情况下,我希望将输出按顺序命名,例如 large_data_1.csv , large_data_2.csv , large_data_3.csv 等等…

我可以用 dbms_output 在一个pl/sql块中控制输出多少行,然后如何切换文件,如 滑阀 似乎无法从pl/sql块访问?

(甲骨文10g)

更新:

我没有访问服务器的权限,因此向服务器写入文件可能不起作用。

更新2:

有些字段包含自由格式的文本,包括换行符,因此在写入文件后计算换行符不像在返回数据时计算记录那样容易…

6 回复 | 直到 12 年前

FrustratedWithFormsDesigner 14 年前

有个解决办法,不知道为什么我不早点想到这个…

基本思想是主sqplplus脚本生成一个中间脚本,将输出拆分为多个文件。执行中间脚本将执行多个具有不同范围的查询 rownum ,并为每个查询假脱机到不同的文件。

set termout off
set serveroutput on
set echo off
set feedback off
variable v_rowCount number;
spool intermediate_file.sql
declare
     i number := 0;
     v_fileNum number := 1;
     v_range_start number := 1;
     v_range_end number := 1;
     k_max_rows constant number := 65536;
begin
    dbms_output.enable(10000);
    select count(*) 
    into :v_err_count
    from ...
    /* You don't need to see the details of the query... */

    while i <= :v_err_count loop

          v_range_start := i+1;
          if v_range_start <= :v_err_count then
            i := i+k_max_rows;
            v_range_end := i;

            dbms_output.put_line('set colsep ,  
set pagesize 0
set trimspool on 
set headsep off
set feedback off
set echo off
set termout off
set linesize 4000
spool large_data_file_'||v_fileNum||'.csv
select data_string
from (select rownum rn, data_object
      from 
      /* Details of query omitted */
     )
where rn >= '||v_range_start||' and rn <= '||v_range_end||';
spool off');
          v_fileNum := v_fileNum +1;
         end if;
    end loop;
end;
/
spool off
prompt     executing intermediate file
@intermediate_file.sql;
set serveroutput off

PaulJ 14 年前

尝试使用纯SQL*Plus解决方案…

set pagesize 0
set trimspool on  
set headsep off 
set feedback off
set echo off 
set verify off
set timing off
set linesize 4000

DEFINE rows_per_file = 50


-- Create an sql file that will create the individual result files
SET DEFINE OFF

SPOOL c:\temp\generate_one.sql

PROMPT COLUMN which_dynamic NEW_VALUE dynamic_filename
PROMPT

PROMPT SELECT 'c:\temp\run_#'||TO_CHAR( &1, 'fm000' )||'_result.txt' which_dynamic FROM dual
PROMPT /

PROMPT SPOOL &dynamic_filename

PROMPT SELECT *
PROMPT   FROM ( SELECT a.*, rownum rnum
PROMPT            FROM ( SELECT object_id FROM all_objects ORDER BY object_id ) a
PROMPT           WHERE rownum <= ( &2 * 50 ) )
PROMPT  WHERE rnum >= ( ( &3 - 1 ) * 50 ) + 1
PROMPT /

PROMPT SPOOL OFF

SPOOL OFF

SET DEFINE &


-- Define variable to hold number of rows
-- returned by the query
COLUMN num_rows NEW_VALUE v_num_rows

-- Find out how many rows there are to be
SELECT COUNT(*) num_rows
  FROM ( SELECT LEVEL num_files FROM dual CONNECT BY LEVEL <= 120 );


-- Create a master file with the correct number of sql files
SPOOL c:\temp\run_all.sql

SELECT '@c:\temp\generate_one.sql '||TO_CHAR( num_files )
                                   ||' '||TO_CHAR( num_files )
                                   ||' '||TO_CHAR( num_files ) file_name
  FROM ( SELECT LEVEL num_files 
           FROM dual 
        CONNECT BY LEVEL <= CEIL( &v_num_rows / &rows_per_file ) )
/

SPOOL OFF

-- Now run them all
@c:\temp\run_all.sql

Vincent Malgrat 14 年前

使用 split 在结果文件上。

dcp 14 年前

utl_file 是您要找的包裹。您可以编写一个光标并在行上循环(写出它们),以及 mod(num_rows_written,num_per_file) == 0 是时候开始新文件了。它在pl/sql块中工作良好。

这是参考资料 utl_文件 : http://www.adp-gmbh.ch/ora/plsql/utl_file.html

注: 我在这里假设,把文件写到服务器上是可以的。

dpbradley 14 年前

您是否考虑过在Excel中设置外部数据连接(假设csv文件仅用于Excel)?您可以定义一个Oracle视图来限制返回的行,还可以在查询中添加一些参数,以允许用户进一步限制结果集。(我从来都不知道有人会用Excel中的64K行做什么)。

我觉得这有点像一个黑客,但是你也可以使用utl-mail并生成电子邮件附件给你的用户。附件的大小限制为32K,因此您必须跟踪光标循环中的大小,并在此基础上启动一个新附件。

Jon 12 年前

当您的问题询问如何将大容量的数据分成Excel可以处理的块时,我会询问Excel操作中是否有可以移动到SQL(pl/sql?)中的部分。这样可以减少数据量。最终,它必须减少到对任何人都有意义。数据库是完成这项工作的一个很好的引擎。

当您将数据减少到更可显示的卷,甚至是最终结果时,请将其转储到Excel以进行最终演示。

这不是你一直在寻找的答案,但我认为当你很难完成工作时,最好问你是否使用了正确的工具。