Oracle - UTL_FILE包之詳解

襲冷發表於2018-01-17
Oracle

一、說明
    UTL_FILE是oracle提供的一個標準的工具包，用來讀寫檔案使用。
    
    
二、路徑
    包UTL_FILE用於讀寫作業系統的檔案，前提是首先建立路徑(Directory)並授權。ORACLE目錄的作用就是讓ORACLE資料庫和作業系統之前進行檔案的互動。
    為了建立目錄，必須具有DBA角色或者賦予了CREATE ANY DIRECTORY許可權。如果普通使用者被賦予了CREATE ANY DIRECTORY許可權，那麼使用者就自動具備目錄的READ和WRITE許可權

    建立：CREATE [OR REPLACE] DIRECTORY directory_name AS 'path_name' ;

create or replace directory DB_OUT as '/usr/local/xilen/DB_OUT';

    賦權：GRANT READ[,WRITE] ON DIRECTORY directory_namne TO user_name; 

--路徑授權，新增對路徑讀、寫許可權
grant read,write on directory DB_OUT to xilen;

--utl_file包授權，新增執行許可權
GRANT EXECUTE ON utl_file TO xilen;

    檢視（具有READ和WRITE許可權的目錄）：
-- 限制單列的顯示長度
COL DIRECTORY_PATH format a60;  

-- 查詢路徑
SELECT DIRECTORY_NAME, DIRECTORY_PATH, GRANTEE, PRIVILEGE FROM USER_TAB_PRIVS T, ALL_DIRECTORIES D WHERE T.TABLE_NAME(+)=D.DIRECTORY_NAME;


三、詳解
    1、FILE_TYPE
        該型別是UTL_FILE包中定義的記錄型別，其成員是私有的，不能夠被直接引用。該型別的定義如下：
TYPE file_type IS RECORD(
    id  BINARY_INTEGER,
    datatype BINARY_INTEGER
);

    2、FOPEN
        該函式用於開啟檔案。使用這個函式最多可以開啟50個檔案，語法如下：
UTL_FILE.FOPEN(
    location IN VARCHAR2,    -- 檔案路徑（必須要使用DIRECTORY物件，並且其名稱必須為大寫）
    filename IN VARCHAR2,    -- 檔名
    open_mode IN VARCHAR2,    -- 開啟模式 
    max_linesize IN BINARY_INTEGER    -- 指定檔案每行存放的最大字元數
)
RETURN file_type;    -- 預操作檔案的指標(控制程式碼)
    注：檔案的開啟模式包含'r', 'w', 'a' 'rb', 'wb', 'ab'六種：
        --'r'：讀檔案(文字)，一定要保證有該檔案，不然會報UTL_FILE.INVALID_PATH異常
        --'w'：寫檔案(文字)，沒有該檔案的話會自動新增；有的話會覆蓋
        --'a'：追加檔案(文字)，如果檔案不存在，則會以write模式建立此檔案
        --帶有'b'字尾的為使用byte(位元組)模式，BLOB與VARCHAR2不一樣，BLOB開啟時一定要用帶有'b'字尾的模式


 3、IS_OPEN
      該函式用於確定檔案是否已經被開啟，語法如下：

UTL_FILE.IS_OPEN(
    file IN FILE_TYPE    -- 指定檔案的控制程式碼
)
 RETURN BOOLEAN;    -- 如果已經開啟則返回TRUE，否則返回FALSE
        該函式的示例如下：
DECLARE
    HANDLE UTL_FILE.FILE_TYPE;
BEGIN
    IF NOT UTL_FILE.is_open(HANDLE) THEN
        HANDLE := UTL_FILE.fopen('CZW','DYWT.TXT','R',1000);
    END IF;
    DBMS_OUTPUT.PUT_LINE('D:\DYWT.TXT已經被開啟');
END;

    4、FCLOSE
        該過程用於關閉已經開啟的檔案，語法如下：
UTL_FILE.FCLOSE(FILE IN OUT FILE_TYPE);

    5、FCLOSE_ALL
        該過程用於關閉當前開啟的所有檔案。語法如下：
UTL_FILE.FCLOSE_ALL;

    6、GET_LINE
        該過程用於從已經開啟的檔案中讀取行內容，行內容會被讀取到輸出緩衝區。語法如下：
UTL_FILE.GET_LINE(
  file IN FILE_TYPE,    -- 檔案控制程式碼
  buffer OUT VARCHAR2,    -- 儲存讀取的Buff
  linesize IN NUMBER,    -- 讀取的最大位元組數
  len IN PLS_INTEGER DEFAULT NULL    -- 實際讀取長度  
);
        使用該過程的示例如下：
DECLARE
    HANDLE UTL_FILE.FILE_TYPE;
    BUFFER VARCHAR2(100);
BEGIN
    IF NOT UTL_FILE.is_open(HANDLE) THEN
        HANDLE := UTL_FILE.fopen('CZW','DYWT.TXT','R',1000);
    END IF;
    UTL_FILE.GET_LINE(HANDLE,BUFFER,100);
    DBMS_OUTPUT.PUT_LINE(BUFFER);
    UTL_FILE.fclose(HANDLE);
END;

    7、GET_RAW
        該過程用於從檔案中讀取RAW字串，並調節檔案指標到讀取位置。語法如下：
UTL_FILE.GET_RAW(
  fid IN UTL_FILE.TYPE,    -- 檔案控制程式碼
  r  OUT NOCOPY RAW,    -- 讀取的快取Buff
  len IN PLS_INTEGER DEFAULT NULL    -- 讀取長度
);
        上面所示：fid用於指定檔案的控制程式碼，r用於取得讀取資訊，示例如下：
DECLARE
    HANDLE UTL_FILE.FILE_TYPE;
    BUFFER VARCHAR2(2000);
BEGIN
    IF NOT UTL_FILE.is_open(HANDLE) THEN
        HANDLE := UTL_FILE.fopen('CZW','DYWT.TXT','R',1000);
    END IF;
    UTL_FILE.GET_RAW(HANDLE,BUFFER,1000);
    DBMS_OUTPUT.PUT_LINE(BUFFER);
    UTL_FILE.fclose(HANDLE);
END;

    8、PUT

        該過程用於將緩衝區內容寫入到檔案中。當使用PUT過程的時候，檔案必須以寫方式開啟，在寫入緩衝區之後，如果要結束行，那麼可以使用NEW_LINE過程。語法如下：

UTL_FILE.PUT(
    file IN FILE_TYPE,
    buffer IN VARCHAR2
);
        使用該過程的示例如下： 

DECLARE
    HANDLE UTL_FILE.FILE_TYPE;
    BUFFER VARCHAR2(2000);
BEGIN
    IF NOT UTL_FILE.IS_OPEN(HANDLE) THEN
        HANDLE := UTL_FILE.FOPEN('CZW','NEW.TXT','W',1000);
    END IF;
    BUFFER:='&CONTENT1';
    UTL_FILE.PUT(HANDLE,BUFFER);
    UTL_FILE.NEW_LINE(HANDLE);
    BUFFER:='&CONTENT2';
    UTL_FILE.PUT(HANDLE,BUFFER);
    UTL_FILE.NEW_LINE(HANDLE);
    UTL_FILE.FCLOSE(HANDLE);
END;

    9、PUT_RAW
        該過程用於將RAW緩衝區中的資料寫入檔案中。語法如下：
UTL_FILE.PUT_RAW(
    fid IN UTL_FILE.FILE_TYPE,
    R IN RAW,
    autoflush IN BOOLEAN DEFAULT FALSE
);
        上述,fid用於指定檔案控制程式碼,r用於指定存放RAW資料的緩衝區,autoflush用於指定是否自動重新整理緩衝區資料.該過程的示例如下:
DECLARE
    HANDLE UTL_FILE.FILE_TYPE;
    BUFFER VARCHAR2(2000);
BEGIN
    IF NOT UTL_FILE.IS_OPEN(HANDLE) THEN
        HANDLE := UTL_FILE.FOPEN('CZW','NEW.TXT','W',1000);
    END IF;
    BUFFER:='&CONTENT1';    
    UTL_FILE.PUT_RAW(HANDLE,BUFFER);
    UTL_FILE.NEW_LINE(HANDLE);
    UTL_FILE.FCLOSE(HANDLE);
END;

    10、NEW_LINE
        該過程用於為檔案增加行終止符，語法如下：
UTL_FILE.NEW_LINE(
    file IN FILE_TYPE,
    lines IN NATURAL :=1    -- 指定在檔案中增加的行終止符的個數
);

    11、PUT_LINE

        該過程用於將文字緩衝區內容寫入到檔案中。當使用該過程為檔案追加內容時，會自動在檔案的尾部追加行終止符。

UTL_FILE.PUT_LINE ( 
    file IN FILE_TYPE,
    buffer IN VARCHAR2,
    autoflush IN BOOLEAN DEFAULT FALSE
);

    12、FFLUSH

        該過程用於將資料強制性寫入到檔案中，正常情況下，當給檔案寫入資料的時候，資料會被暫時的放到快取中。過程FFLUSH用於強制將資料寫入到檔案中。語法如下：
UTL_FILE.FFLUSH(file IN FILE_TYPE):


    13、FREMOVE

        該過程用於刪除磁碟檔案。語法如下：
UTL_FILE.FREMOVE(
    location IN VARCHAR2,    -- 指定DIRECTORY，注意，這裡也必須要大寫
    filename IN VARCHAR2    -- 指定要刪除的檔名
);

    14、FCOPY
        該過程用於將原始檔的全部或者部分內容複製到目標檔案中。當使用該過程的時候，如果不設起始行和結束行，則將複製檔案所有的內容。語法如下：
UTL_FILE.FCOPY(
    location IN VARCHAR2,    -- 指定源DIRECTORY路徑物件
    filename IN VARCHAR2,    -- 指定檔名
    dest_dir IN VARCHAR2,    -- 希望複製到的Directory路徑物件
    dest_file IN VARCHAR2,    -- 複製後的檔名
    start_line IN PLS_INTEGER DEFAULT 1,    -- 指定起始行號
    end_line IN PLS_INTEGER DEFALUT NULL    -- 指定結束行號
);

    15、FRENAME
        該過程用於修改已經存在的檔名字，其作用於UNIX的MV命令完全相同，在修改檔名字的時候，通過指定overwrite引數可以覆蓋已經存在的檔案。語法如下：
UTL_FILE.FRENAME(
    location IN VARCHAR2，
    filename IN VARCHAR2，
    dest_dir IN VARCHAR2，
    dest_file IN VARCHAR2，
    overwrite IN BOOLEAN DEFAULT FALSE    -- 是否覆蓋已經存在的檔案
);


四、示例
    匯出表資料示例：
DECLARE
    HANDLE UTL_FILE.FILE_TYPE;
BEGIN
    HANDLE := UTL_FILE.FOPEN('CZW_DIR','DEPT.TXT','W',1000);
    FOR I IN (SELECT T.DEPTNO||','||T.DNAME||','||T.LOC AS MSG FROM SCOTT.DEPT T) LOOP
        UTL_FILE.PUT_LINE(HANDLE,I.MSG);
    END LOOP;
    UTL_FILE.FFLUSH(HANDLE);
    UTL_FILE.FCLOSE(HANDLE);
END;



五、參考

http://blog.csdn.net/bisal/article/details/24667609

http://zhangzhongjie.iteye.com/blog/1903024

http://www.2cto.com/database/201301/181232.html

http://blog.csdn.net/john2522/article/details/8124087



相關文章