oracle使用存储过程将表数据以excel格式导出

虽然目前pl/sql developer等数据库客户端软件都支持将表数据以excel格式导出，但是假如数据量大，必要等客户端加载表数据等待很久。而且，可能会遇到定时以excel格式导出数据的要求。因此我自己写了一个使用存储过程将表数据以excel格式导出的存储过程。

• 服务端新建目录
  create directory DIR_EXCEL as 'D:\DIR_EXCEL';
  
• 新建存储过程
  

1. create or replace procedure pr_export_to_excel(p_table_name      varchar2,
2.                                                p_where_predicate varchar2 default null) is
3.   /*
4.   propose:根据表名和where条件生成excel
5.   p_where_predicate:where条件语句
6.   */
7.   out_file          utl_file.file_type; --定义一个文件类型变量
8.   str1              varchar2(20000); --定义一个字符串变量，用于存储表1的字段名
9.   str1_chr          varchar2(30000);
10.   l_sql             varchar2(20000);
11.   l_where_predicate varchar2(30000) default 'where ' || p_where_predicate;
13. begin
15.   if p_where_predicate is null then
16.     l_where_predicate := null;
17.   end if;
18.   --查询表1的字段名，用制表符分隔，并赋值给str1
19.   select listagg(column_name, chr(9)) within group(order by column_id)
20.     into str1
21.     from user_tab_columns
22.    where table_name = upper(p_table_name);
24.   --查询表1的字段名，用制表符分隔，并赋值给str1_chr
25.   select listagg(case
26.                    when t.DATA_TYPE = 'DATE' OR t.DATA_TYPE LIKE 'TIMESTAMP%' THEN
27.                     'to_char(f_cur.' || column_name || ',''YYYYMMDD HH24:MI:SS'')'
28.                    else
29.                     'f_cur.' || column_name
30.                  END,
31.                  '||chr(9)||') within group(order by column_id)
32.     into str1_chr
33.     from user_tab_columns t
34.    where table_name = upper(p_table_name);
36.   l_sql := '
37.   declare
38.     out_file utl_file.file_type; --定义一个文件类型变量
39.   BEGIN
40.     --打开一个文件，指定目录对象、文件名和写入模式
41.     out_file := utl_file.fopen('' DIR_EXCEL '',
42.                                ''' || p_table_name ||
43.            '.xls '',
44.                                '' W '',
45.                                32767);
46.     utl_file.put_line(out_file,
47.                       ''' || str1 ||
48.            '''); --写入字段名，换行
49.     for f_cur in (select *
50.                     from ' || p_table_name || ' t ' ||
51.            l_where_predicate || ') loop
52.       utl_file.put_line(out_file, ' || str1_chr || ');
53.     end loop;
54.     utl_file.fclose(out_file);
55.   exception
56.     when others then
57.       utl_file.fclose(out_file); --关闭文件，防止异常关闭
58.       dbms_output.put_line(SQLERRM);
59.       dbms_output.put_line(dbms_utility.format_error_backtrace);
60.       raise; --抛出异常信息
61.   end;
62.   ';
63.   dbms_output.put_line(l_sql);
64.   --dbms_output.put_line(l_sql);
65.   execute immediate l_sql;
66. exception
67.   when others then
68.     utl_file.fclose(out_file); --关闭文件，防止异常关闭
69.     dbms_output.put_line(SQLERRM);
70.     dbms_output.put_line(dbms_utility.format_error_backtrace);
71.     raise; --抛出异常信息
72. end pr_export_to_excel;

复制代码

3.调用存储过程
call pr_export_to_excel('TEST','NAME='''123''');
4.去目录'D:\DIR_EXCEL'取出TEST.xls文件

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。