使用utl_file將oracle數據庫中數據寫入excel文件
2024-08-29 13:44:52
供稿：網友
最近做的一個項目，其中有一塊的功能是將數據導出為Excel文件。我使用了Oracle的utl_file程序包。
主要實現思路：
1、聲明一個紀錄，用來存儲導出的數據；
2、使用游標取數據到紀錄中；
3、使用utl_file將紀錄中的數據寫入excel文件；
4、循環執行步驟2和3，完成數據的導出。
做的過程中主要碰到的問題：
1、excle文件中寫數據如何寫入下一列；
使用TAB字符完成excel中橫向跳格，excel中TAB字符表示單元格的結尾，其中使用了chr()函數，
應用舉例如下：
select U.USER_NAMEchr(9),U.ACCOUNTchr(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_idchr(9), --***chr(9)是TAB字符，保證數據輸出到EXCEL時能自動換到下一列***--
job_titlechr(9),
min_salarychr(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）
這是我第一個blog，可能內容不是很有技術含量的說，但總希望記錄一下自己的歷程，分享大家的經驗。
MSN:lintaowen@hotmail.com
