将Oracle表数据保存到Excel工作表中的本地计算机中 [英] Save oracle table data into local machine in excel sheet
本文介绍了将Oracle表数据保存到Excel工作表中的本地计算机中的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我想每天从表中提取数据，并将其保存在Excel工作表中的本地计算机中.
I want to extract data from a table on a daily basis and save it in my local machine in a excel sheet.
推荐答案
我想每天从表中提取数据
I want to extract data from a table on a daily basis
您可以使用 DBMS_SCHEDULER 创建工作和时间表以每天运行.
You could use DBMS_SCHEDULER to create a job and schedule to run daily.
例如，以下作业将每小时运行一次:
For example, the below job will run every hour:
SQL> BEGIN
2 DBMS_SCHEDULER.DROP_JOB (JOB_NAME => 'test_full_job_definition');
3 END;
4 /
PL/SQL procedure successfully completed.
SQL>
SQL> BEGIN
2 DBMS_SCHEDULER.create_job (
3 job_name => 'test_full_job_definition',
4 job_type => 'PLSQL_BLOCK',
5 job_action => 'BEGIN my_job_procedure; END;',
6 start_date => SYSTIMESTAMP,
7 repeat_interval => 'freq=hourly; byminute=0; bysecond=0;',
8 end_date => NULL,
9 enabled => TRUE,
10 comments => 'Job defined entirely by the CREATE JOB procedure.');
11 END;
12 /
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT JOB_NAME, ENABLED FROM DBA_SCHEDULER_JOBS where job_name ='TEST_FULL_JOB_DEFINITION'
2 /
JOB_NAME ENABL
---------------------------------------- -----
TEST_FULL_JOB_DEFINITION TRUE
SQL>
将其保存在本地计算机的Excel工作表中
save it in my local machine in a excel sheet
这是一个常见问题，人们经常在 CSV 文件和 Microsoft Excel 文件之间感到困惑.生成CSV文件就像使用 SQL * Plus 假脱机一样简单.要将输出写入Excel文件，您需要以编程方式进行.
This is a frequently asked question, and people often get confused between a CSV file and a Microsoft Excel file. Generating a CSV file could be as easy as to spool it using SQL*Plus. For writing the output into an Excel file, you need to do it programmatically.
You could use the following code developed by Anton Scheffer:
CREATE OR REPLACE package as_xlsx
is
/**********************************************
**
** Author: Anton Scheffer
** Date: 19-02-2011
** Website: http://technology.amis.nl/blog
** See also: http://technology.amis.nl/blog/?p=10995
**
** Changelog:
** Date: 21-02-2011
** Added Aligment, horizontal, vertical, wrapText
** Date: 06-03-2011
** Added Comments, MergeCells, fixed bug for dependency on NLS-settings
** Date: 16-03-2011
** Added bold and italic fonts
** Date: 22-03-2011
** Fixed issue with timezone's set to a region(name) instead of a offset
** Date: 08-04-2011
** Fixed issue with XML-escaping from text
** Date: 27-05-2011
** Added MIT-license
** Date: 11-08-2011
** Fixed NLS-issue with column width
** Date: 29-09-2011
** Added font color
** Date: 16-10-2011
** fixed bug in add_string
** Date: 26-04-2012
** Fixed set_autofilter (only one autofilter per sheet, added _xlnm._FilterDatabase)
** Added list_validation = drop-down
** Date: 27-08-2013
** Added freeze_pane
**
******************************************************************************
******************************************************************************
Copyright (C) 2011, 2012 by Anton Scheffer
Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in
all copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
THE SOFTWARE.
******************************************************************************
******************************************** */
--
type tp_alignment is record
( vertical varchar2(11)
, horizontal varchar2(16)
, wrapText boolean
);
--
procedure clear_workbook;
--
procedure new_sheet( p_sheetname varchar2 := null );
--
function OraFmt2Excel( p_format varchar2 := null )
return varchar2;
--
function get_numFmt( p_format varchar2 := null )
return pls_integer;
--
function get_font
( p_name varchar2
, p_family pls_integer := 2
, p_fontsize number := 11
, p_theme pls_integer := 1
, p_underline boolean := false
, p_italic boolean := false
, p_bold boolean := false
, p_rgb varchar2 := null -- this is a hex ALPHA Red Green Blue value
)
return pls_integer;
--
function get_fill
( p_patternType varchar2
, p_fgRGB varchar2 := null -- this is a hex ALPHA Red Green Blue value
)
return pls_integer;
--
function get_border
( p_top varchar2 := 'thin'
, p_bottom varchar2 := 'thin'
, p_left varchar2 := 'thin'
, p_right varchar2 := 'thin'
)
/*
none
thin
medium
dashed
dotted
thick
double
hair
mediumDashed
dashDot
mediumDashDot
dashDotDot
mediumDashDotDot
slantDashDot
*/
return pls_integer;
--
function get_alignment
( p_vertical varchar2 := null
, p_horizontal varchar2 := null
, p_wrapText boolean := null
)
/* horizontal
center
centerContinuous
distributed
fill
general
justify
left
right
*/
/* vertical
bottom
center
distributed
justify
top
*/
return tp_alignment;
--
procedure cell
( p_col pls_integer
, p_row pls_integer
, p_value number
, p_numFmtId pls_integer := null
, p_fontId pls_integer := null
, p_fillId pls_integer := null
, p_borderId pls_integer := null
, p_alignment tp_alignment := null
, p_sheet pls_integer := null
);
--
procedure cell
( p_col pls_integer
, p_row pls_integer
, p_value varchar2
, p_numFmtId pls_integer := null
, p_fontId pls_integer := null
, p_fillId pls_integer := null
, p_borderId pls_integer := null
, p_alignment tp_alignment := null
, p_sheet pls_integer := null
);
--
procedure cell
( p_col pls_integer
, p_row pls_integer
, p_value date
, p_numFmtId pls_integer := null
, p_fontId pls_integer := null
, p_fillId pls_integer := null
, p_borderId pls_integer := null
, p_alignment tp_alignment := null
, p_sheet pls_integer := null
);
--
procedure hyperlink
( p_col pls_integer
, p_row pls_integer
, p_url varchar2
, p_value varchar2 := null
, p_sheet pls_integer := null
);
--
procedure comment
( p_col pls_integer
, p_row pls_integer
, p_text varchar2
, p_author varchar2 := null
, p_width pls_integer := 150 -- pixels
, p_height pls_integer := 100 -- pixels
, p_sheet pls_integer := null
);
--
procedure mergecells
( p_tl_col pls_integer -- top left
, p_tl_row pls_integer
, p_br_col pls_integer -- bottom right
, p_br_row pls_integer
, p_sheet pls_integer := null
);
--
procedure list_validation
( p_sqref_col pls_integer
, p_sqref_row pls_integer
, p_tl_col pls_integer -- top left
, p_tl_row pls_integer
, p_br_col pls_integer -- bottom right
, p_br_row pls_integer
, p_style varchar2 := 'stop' -- stop, warning, information
, p_title varchar2 := null
, p_prompt varchar := null
, p_show_error boolean := false
, p_error_title varchar2 := null
, p_error_txt varchar2 := null
, p_sheet pls_integer := null
);
--
procedure list_validation
( p_sqref_col pls_integer
, p_sqref_row pls_integer
, p_defined_name varchar2
, p_style varchar2 := 'stop' -- stop, warning, information
, p_title varchar2 := null
, p_prompt varchar := null
, p_show_error boolean := false
, p_error_title varchar2 := null
, p_error_txt varchar2 := null
, p_sheet pls_integer := null
);
--
procedure defined_name
( p_tl_col pls_integer -- top left
, p_tl_row pls_integer
, p_br_col pls_integer -- bottom right
, p_br_row pls_integer
, p_name varchar2
, p_sheet pls_integer := null
, p_localsheet pls_integer := null
);
--
procedure set_column_width
( p_col pls_integer
, p_width number
, p_sheet pls_integer := null
);
--
procedure set_column
( p_col pls_integer
, p_numFmtId pls_integer := null
, p_fontId pls_integer := null
, p_fillId pls_integer := null
, p_borderId pls_integer := null
, p_alignment tp_alignment := null
, p_sheet pls_integer := null
);
--
procedure set_row
( p_row pls_integer
, p_numFmtId pls_integer := null
, p_fontId pls_integer := null
, p_fillId pls_integer := null
, p_borderId pls_integer := null
, p_alignment tp_alignment := null
, p_sheet pls_integer := null
);
--
procedure freeze_rows
( p_nr_rows pls_integer := 1
, p_sheet pls_integer := null
);
--
procedure freeze_cols
( p_nr_cols pls_integer := 1
, p_sheet pls_integer := null
);
--
procedure freeze_pane
( p_col pls_integer
, p_row pls_integer
, p_sheet pls_integer := null
);
--
procedure set_autofilter
( p_column_start pls_integer := null
, p_column_end pls_integer := null
, p_row_start pls_integer := null
, p_row_end pls_integer := null
, p_sheet pls_integer := null
);
--
function finish
return blob;
--
procedure save
( p_directory varchar2
, p_filename varchar2
);
--
procedure query2sheet
( p_sql varchar2
, p_column_headers boolean := true
, p_directory varchar2 := null
, p_filename varchar2 := null
, p_sheet pls_integer := null
);
注意:这是一个庞大的代码，包含超过90k PL/SQL行代码，因此由于30k个字符的限制，我无法在此处发布整个代码.仅发布重要的过程，功能和软件包详细信息.请点击代码链接以获取完整代码.
Note : It is a huge code with more than 90k PL/SQL lines of code, so I can't post the entire code here due to restriction of 30k characters. Only posting the important procedures, functions and the package details. Please follow the code link to get the entire code.
这篇关于将Oracle表数据保存到Excel工作表中的本地计算机中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
