oracle使用儲存過程將表資料以excel格式匯出

追求完美9196發表於2024-07-24
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檔案

相關文章