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

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

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

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文件