將oracle資料庫中資料寫入excel檔案

主要實現思路：
    1、聲明一個紀錄，用來儲存匯出的資料；
    2、使用遊標取資料到紀錄中；
    3、使用utl_file將紀錄中的資料寫入excel檔案；
    4、迴圈執行步驟2和3，完成資料的匯出。
    做的過程中主要遇到的問題：
    1、excle檔案中寫資料如何寫入下一列；
          使用TAB字元完成excel中橫向跳格，excel中TAB字元表示儲存格的結尾，其中使用了chr()函數，
應用舉例如下：
         select U.USER_NAME||chr(9),U.ACCOUNT||chr(9) from USER U
                                            例句1
          例句1作為遊標的主體，取出的資料每項都包含一個TAB字元，使用utl_file.put()往excel檔案中
寫資料時會自動跳格
    2、聲明的紀錄中各項的類型問題
           這個問題的產生主要是在類型的強轉化時產生。如例句1種的U.ACCOUNT為number型時，
添加||chr(9)時oracle會對進行強轉化；當然這裡的轉化不會有問題（number轉化為varchar2），
但是當這種強轉換還是會出現問題的，如聲明的紀錄裡某個屬性聲明為使用者自己定義的一種類型，
oracle無法進行轉換，則會報錯。解決的辦法很簡單，可以將紀錄的屬性都聲明為varchar類型，但是要注意長度。
    3、匯出檔案儲存體路徑問題
       utl_file在寫檔案時，檔案的儲存路徑必須在oracle初始化參數utl_file_dir中設定，
需重起服務才會生效。後來經過查資料發現可以先建立一個directory，在預存程序中使用它。
建立directory的語句：
create or replace directory FILEPATH as 'path' "；
例句2（註：path為隱藏檔的路徑，如c:\Temp）

    以下是我簡單做的處理hr.jobs表資料的預存程序：
      
CREATE OR REPLACE PROCEDURE SP_JOBS_DATA_OUT(
      p_file_name IN VARCHAR2           --***處理檔案名稱，需包含副檔名(xls用於寫excel檔案)***--
      ) as
     
   --***定義並聲明儲存交通資產資訊的紀錄***--
   --***record_define start***--
    TYPE job_record_type is RECORD(
    job_id hr.jobs.job_id%TYPE,
    job_title hr.jobs.job_title%TYPE,
    min_salary varchar2(30)
    );
    job_rec job_record_type;
   --***record_define end***--
  
  
    --***定義擷取job資訊的遊標***--
   --***cursor_define start***--
    CURSOR c_jobs IS
     select
        job_id||chr(9),      --***chr(9)是TAB字元，保證資料輸出到EXCEL時能自動換到下一列***--
        job_title||chr(9),
        min_salary||chr(9)
    FROM
        hr.jobs;
   --***cursor_define end***--
   
    l_file utl_file.file_type;      --***處理檔案操作的控制代碼***--
 
   
BEGIN
     l_file :=utl_file.fopen('FILEPATH',p_file_name,'w');    --FILEPATH是先於匯出前使用者建立的儲存匯出檔案的路徑
     utl_file.put_line(l_file,'jobs表匯出資料');
  
   OPEN c_jobs;
     LOOP 
     FETCH  c_jobs INTO   
            job_rec.job_id  ,
            job_rec.job_title ,
            job_rec.min_salary ;
         EXIT WHEN c_jobs%NOTFOUND;
            utl_file.put(l_file,job_rec.job_id );            --***資料寫入excle檔案中***--
            utl_file.put(l_file,job_rec.job_title);
            utl_file.put_line(l_file,job_rec.min_salary);
               
     END LOOP;
   CLOSE  c_jobs;
     utl_file.fflush(l_file);
     utl_file.fclose(l_file);
  
   EXCEPTION
     WHEN others THEN
      IF utl_file.is_open(l_file) THEN
         utl_file.fclose(l_file);
       
      END IF;
END;
例句3（註：我的oracle版本為9.2）

DECLARE
        FILE_ID TEXT_IO.FILE_TYPE;
        FILE_NAME VARCHAR2(200);
        FILE_CODE  VARCHAR2(100) :='BOM'||TO_CHAR(SYSDATE,'HHMISS');
        p_file_path varchar2(200);
  ln_count number;

BEGIN
        FILE_NAME := '/prod/applprod/prodora/iAS/Apache/Apache/htdocs/lc_cust/'||FILE_CODE||'.xls';
        FILE_ID := TEXT_IO.FOPEN(FILE_NAME, 'w');

  TEXT_IO.PUT(FILE_ID,convert('父項料號','ZHT16BIG5','UTF8'));
  Text_IO.PUT(FILE_ID,chr(9));  
  TEXT_IO.PUT(FILE_ID,convert('子項料號','ZHT16BIG5','UTF8'));
  Text_IO.PUT(FILE_ID,chr(9));  
  TEXT_IO.PUT(FILE_ID,convert('子項料名','ZHT16BIG5','UTF8'));
  Text_IO.PUT(FILE_ID,chr(9));
  TEXT_IO.PUT(FILE_ID,convert('單位','ZHT16BIG5','UTF8'));
  Text_IO.PUT(FILE_ID,chr(9));
        TEXT_IO.PUT(FILE_ID,convert('單位用量','ZHT16BIG5','UTF8'));
        Text_IO.PUT(FILE_ID,chr(9));
        TEXT_IO.PUT(FILE_ID,convert('利用率','ZHT16BIG5','UTF8'));
        Text_IO.PUT(FILE_ID,chr(9));
        TEXT_IO.PUT(FILE_ID,convert('供給型態','ZHT16BIG5','UTF8'));
        Text_IO.PUT(FILE_ID,chr(9));
        TEXT_IO.PUT(FILE_ID,convert('供給倉庫','ZHT16BIG5','UTF8'));
        Text_IO.PUT(FILE_ID,chr(9));
        TEXT_IO.PUT(FILE_ID,convert('供給儲位','ZHT16BIG5','UTF8'));
        Text_IO.PUT(FILE_ID,chr(9));
        TEXT_IO.PUT(FILE_ID,convert('分類','ZHT16BIG5','UTF8'));        

       

        GO_BLOCK('CHECK_BOM_COMPONENT_PT');
        FIRST_RECORD;

  LOOP
        Text_IO.PUT(FILE_ID,chr(13));
        Text_IO.PUT(FILE_ID,convert(NVL(:CHECK_BOM_COMPONENT_PT.ASSEMBLY_SEGMENT,' '),'ZHT16BIG5','UTF8'));
                 Text_IO.PUT(FILE_ID,chr(9));
                 Text_IO.PUT(FILE_ID,convert(NVL(:CHECK_BOM_COMPONENT_PT.COMPONENT_SEGMENT,' '),'ZHT16BIG5','UTF8'));
                 Text_IO.PUT(FILE_ID,chr(9));
                 Text_IO.PUT(FILE_ID,convert(NVL(:CHECK_BOM_COMPONENT_PT.COMPONENT_DESCRIPTION,' '),'ZHT16BIG5','UTF8'));
                 Text_IO.PUT(FILE_ID,chr(9));
                 Text_IO.PUT(FILE_ID,convert(NVL(:CHECK_BOM_COMPONENT_PT.PRIMARY_UOM_CODE,' '),'ZHT16BIG5','UTF8'));
                 Text_IO.PUT(FILE_ID,chr(9));
                 Text_IO.PUT(FILE_ID,convert(NVL(:CHECK_BOM_COMPONENT_PT.COMPONENT_QUANTITY,0),'ZHT16BIG5','UTF8'));
                 Text_IO.PUT(FILE_ID,chr(9));
                 Text_IO.PUT(FILE_ID,convert(NVL(:CHECK_BOM_COMPONENT_PT.COMPONENT_YIELD_FACTOR,''),'ZHT16BIG5','UTF8'));
                 Text_IO.PUT(FILE_ID,chr(9));
                 Text_IO.PUT(FILE_ID,convert(NVL(:CHECK_BOM_COMPONENT_PT.MEANING,''),'ZHT16BIG5','UTF8'));
                 Text_IO.PUT(FILE_ID,chr(9));
                 Text_IO.PUT(FILE_ID,convert(NVL(:CHECK_BOM_COMPONENT_PT.SUBINVENTORY_CODE,''),'ZHT16BIG5','UTF8'));
                 Text_IO.PUT(FILE_ID,chr(9));
                 Text_IO.PUT(FILE_ID,convert(NVL(:CHECK_BOM_COMPONENT_PT.INVENTORY_LOCATOR_SEGMENT,''),'ZHT16BIG5','UTF8'));
                 Text_IO.PUT(FILE_ID,chr(9));
                 Text_IO.PUT(FILE_ID,convert(NVL(:CHECK_BOM_COMPONENT_PT.CATEGORY_SEGMENT,''),'ZHT16BIG5','UTF8'));                                         
                
                 NEXT_RECORD;
        EXIT WHEN :SYSTEM.CURRENT_VALUE IS NULL;
  END LOOP;

  TEXT_IO.FCLOSE(FILE_ID);
  p_file_path :='http://erp.lacquercraft.com:8000/lc_cust/'||FILE_CODE||'.xls';
  Web.Show_Document(p_file_path, '_BLANK');
END;

20111213

 

 

if l_attachtype = 'XLS' then
      l_mimetype := 'application/vnd.ms-excel';
  end if;
  if l_attachtype = 'DOC' then
      l_mimetype := 'application/vnd.ms-word';
  end if;
  if l_attachtype = 'PDF' then
      l_mimetype := 'application/pdf';
  end if;
  if l_attachtype in ('HTML','HTM') then
      l_mimetype := 'text/html';
  end if;

Chr(9)    Tab