自动提取数据-Oracle SQL Developer [英] Automatically extracting data - Oracle SQL Developer

问题描述

我通过SQL Developer与Oracle数据库建立了连接，我想编写一个查询，该查询返回每月的数据集，然后将该数据提取到带分隔符的文本文件中.我知道该怎么做就好了，我想知道是否有一种方法可以编写脚本来运行查询并逐年提取一年的数据.这样，我就可以启动脚本，每当脚本完成时，我就会拥有12个文本文件，每个月一个.

I have a connection to an Oracle database via SQL Developer and I want to write a query that returns a monthly set of data and then extract that data to a delimited text file. I know how to do that just fine, what I am wondering is if there is a way to write a script to run the query and extract the data month by month for a year. That way I would kick off the script and whenever it all finishes I would have 12 text files, one for each month.

我可以手动完成操作，但是其中包含大量数据，我希望它可以在一夜之间运行.这样做的原因是，如果我们不尝试一次导入所有数据，将使用数据的应用程序将运行得更快.我什至不知道是否有可能，但是如果可以的话，有人可以指出我正确的方向吗?

I could do it manually but it is a lot of data and I would like to have it run overnight. The reason for doing it this way is the application we would be using the data with would run faster if we did not try to import all of that data at once. I don't even know if it is possible but if so can someone point me in the right direction?

谢谢.

推荐答案

首先编写参数化脚本:

define the_year=&1
define the_mon=&2

set lines etc
select * from the_table
where trunc(the_date , 'MM' ) = to_date ( '&the_year&the_mon', 'YYYYMM' )

spool extract_&the_year&the_mon.csv

/

spool off

然后是包装脚本:

@the_script 2014 01
@the_script 2014 02
.
.
.
@the_script 2014 12

您会变得很聪明并生成包装器:

You can get clever(ish) and generate the wrapper:

sppol the_wrapper.sql
select '@the_script ' || to_char ( ADD_MONTHS ( trunc(sysdate,'YYYY' ), rn-1 ), 'YYYY MM' )
from ( select rownum rn from dual connect by level < 13 );
spool off

别忘了设置选项以使生成的脚本可运行(例如，设置验证关闭，反馈关闭等).

DOn't forget the set options to make the generated script runnable (e.g. set verify off, set feedback off, etc).

这篇关于自动提取数据-Oracle SQL Developer的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！