oracle使用存储过程将表数据以excel格式导出
虽然目前pl/sql developer等数据库客户端软件都支持将表数据以excel格式导出,但是如果数据量大,需要等客户端加载表数据等待很久。而且,可能会遇到定时以excel格式导出数据的要求。因此我自己写了一个使用存储过程将表数据以excel格式导出的存储过程。
- 服务端新建目录
create directory DIR_EXCEL as 'D:\DIR_EXCEL';
- 新建存储过程
create or replace procedure pr_export_to_excel(p_table_name varchar2,
p_where_predicate varchar2 default null) is
/*
propose:根据表名和where条件生成excel
p_where_predicate:where条件语句
*/
out_file utl_file.file_type; --定义一个文件类型变量
str1 varchar2(20000); --定义一个字符串变量,用于存储表1的字段名
str1_chr varchar2(30000);
l_sql varchar2(20000);
l_where_predicate varchar2(30000) default 'where ' || p_where_predicate;
begin
if p_where_predicate is null then
l_where_predicate := null;
end if;
--查询表1的字段名,用制表符分隔,并赋值给str1
select listagg(column_name, chr(9)) within group(order by column_id)
into str1
from user_tab_columns
where table_name = upper(p_table_name);
--查询表1的字段名,用制表符分隔,并赋值给str1_chr
select listagg(case
when t.DATA_TYPE = 'DATE' OR t.DATA_TYPE LIKE 'TIMESTAMP%' THEN
'to_char(f_cur.' || column_name || ',''YYYYMMDD HH24:MI:SS'')'
else
'f_cur.' || column_name
END,
'||chr(9)||') within group(order by column_id)
into str1_chr
from user_tab_columns t
where table_name = upper(p_table_name);
l_sql := '
declare
out_file utl_file.file_type; --定义一个文件类型变量
BEGIN
--打开一个文件,指定目录对象、文件名和写入模式
out_file := utl_file.fopen('' DIR_EXCEL '',
''' || p_table_name ||
'.xls '',
'' W '',
32767);
utl_file.put_line(out_file,
''' || str1 ||
'''); --写入字段名,换行
for f_cur in (select *
from ' || p_table_name || ' t ' ||
l_where_predicate || ') loop
utl_file.put_line(out_file, ' || str1_chr || ');
end loop;
utl_file.fclose(out_file);
exception
when others then
utl_file.fclose(out_file); --关闭文件,防止异常关闭
dbms_output.put_line(SQLERRM);
dbms_output.put_line(dbms_utility.format_error_backtrace);
raise; --抛出异常信息
end;
';
dbms_output.put_line(l_sql);
--dbms_output.put_line(l_sql);
execute immediate l_sql;
exception
when others then
utl_file.fclose(out_file); --关闭文件,防止异常关闭
dbms_output.put_line(SQLERRM);
dbms_output.put_line(dbms_utility.format_error_backtrace);
raise; --抛出异常信息
end pr_export_to_excel;
3.调用存储过程
call pr_export_to_excel('TEST','NAME='''123''');
4.去目录'D:\DIR_EXCEL'取出TEST.xls文件
热门相关:最强神话帝皇 大金主,你别假正经了 国民女神:重生王牌千金 功夫圣医 九阳剑圣