Oracle - UTL_FILE包之BLOB匯入和匯出

一、將檔案匯入到BLOB欄位中示例

CREATE OR REPLACE PROCEDURE SET_FILE_BY_BLOB(UPDATE_TABLE_NAME VARCHAR2, MARK_COLUMN_NAME VARCHAR2, MARK_COLUMN_VALUE VARCHAR2, 
    MARK_TYPE_ISNUM NUMBER, BLOB_COLUMN_NAME VARCHAR2, LOCAL_FILE_NAME VARCHAR2) IS
    
  /******************************************
   *引數描述：
   *  UPDATE_TABLE_NAME：匯入的表名(字元型)
   *  MARK_COLUMN_NAME：主鍵的欄位名(字元型)
   *  MARK_COLUMN_VALUE：主鍵欄位的值(字元型)
   *  MARK_TYPE_ISNUM：主鍵是否數值(0是1否，數值型)
   *  BLOB_COLUMN_NAME：檔案儲存的欄位名(字元型)
   *  LOCAL_FILE_NAME：本地的檔名(字元型)
   *樣例：
   *  exec EPAYMENT.SET_FILE_BY_BLOB('t_audit_channel_file', 'audit_id', '756', 0, 'audit_file', 'test.zip');
  ******************************************/
  
  
    UTL_FILE_OBJ     UTL_FILE.FILE_TYPE;       -- 要讀取檔案的控制程式碼
    READ_MAX_SIZE    BINARY_INTEGER := 32767;  -- 一次讀取的最大長度
    UTL_BUFF_RAW     RAW(32767);               -- 檔案讀取的快取
    UTL_BLOB_OBJ     BLOB;                     -- BLOB欄位的指標
    LOB_CURR_POS     BINARY_INTEGER := 1;      -- 當前讀取位置
    READ_CUUR_SIZE   BINARY_INTEGER := 32767;  -- 當前讀取到的長度
    DYN_STR_SQL      VARCHAR(250);             -- 執行的動態SQL
   
    BEGIN
        DBMS_OUTPUT.PUT_LINE('INFO：Start...');
        
        IF MARK_TYPE_ISNUM = 0 THEN
            DYN_STR_SQL := 'SELECT ' || BLOB_COLUMN_NAME || ' FROM ' || UPDATE_TABLE_NAME || ' WHERE ' || MARK_COLUMN_NAME || ' = ' || MARK_COLUMN_VALUE || ' FOR UPDATE ';
        ELSE
            DYN_STR_SQL := 'SELECT ' || BLOB_COLUMN_NAME || ' FROM ' || UPDATE_TABLE_NAME || ' WHERE ' || MARK_COLUMN_NAME || ' = ''' || MARK_COLUMN_VALUE || '''' || ' FOR UPDATE ';
        END IF; 
        
        DBMS_OUTPUT.PUT_LINE('DEBUG：' || DYN_STR_SQL);
        -- 執行動態SQL，並將值賦給變數
        EXECUTE IMMEDIATE DYN_STR_SQL INTO UTL_BLOB_OBJ; -- 將目標欄位鎖住，並且將目標欄位賦值給UTL_BLOB_OBJ，以待後續的操作
      
        DBMS_LOB.OPEN(UTL_BLOB_OBJ, DBMS_LOB.LOB_READWRITE);
        DBMS_LOB.TRIM(UTL_BLOB_OBJ,0);  -- 幹掉所有的內容
       
        UTL_FILE_OBJ := UTL_FILE.FOPEN('DB_OUT', LOCAL_FILE_NAME, 'RB', READ_MAX_SIZE); --檔案超過4000要使用迴圈或者RAW(最大32767)
        
        LOOP
            BEGIN
                UTL_FILE.GET_RAW(UTL_FILE_OBJ, UTL_BUFF_RAW, READ_MAX_SIZE);
               
                READ_CUUR_SIZE := DBMS_LOB.GETLENGTH(UTL_BUFF_RAW);
                DBMS_LOB.WRITE(UTL_BLOB_OBJ, READ_CUUR_SIZE, LOB_CURR_POS, UTL_BUFF_RAW);
                LOB_CURR_POS := READ_CUUR_SIZE + LOB_CURR_POS;
                DBMS_OUTPUT.PUT_LINE('DEBUG: Reading(Cuur='||READ_CUUR_SIZE||' | Total='|| LOB_CURR_POS||')...');
            EXCEPTION
                WHEN NO_DATA_FOUND THEN
                    EXIT;
            END;
        END LOOP;
        
        DBMS_LOB.CLOSE(UTL_BLOB_OBJ);
        UTL_FILE.FCLOSE(UTL_FILE_OBJ);
        
        COMMIT;
        DBMS_OUTPUT.PUT_LINE('INFO：End...');
        
        EXCEPTION
            WHEN OTHERS THEN
                IF UTL_FILE.IS_OPEN(UTL_FILE_OBJ) THEN
                    UTL_FILE.FCLOSE(UTL_FILE_OBJ);
                    
                END IF;  
                ROLLBACK;
                DBMS_OUTPUT.PUT_LINE('ERR：' || SQLERRM);
  
  END SET_FILE_BY_BLOB;

二、將BLOB欄位中的檔案匯出示例：

CREATE OR REPLACE PROCEDURE GET_FILE_BY_BLOB(QUERY_TABLE_NAME VARCHAR2, MARK_COLUMN_NAME VARCHAR2, MARK_COLUMN_VALUE VARCHAR2, 
    MARK_TYPE_ISNUM NUMBER, BLOB_COLUMN_NAME VARCHAR2, FILE_NAME_COLUMN VARCHAR2) IS                                                                    
  /******************************************
   *引數描述：
   *  QUERY_TABLE_NAME：查詢的表名(字元型)
   *  MARK_COLUMN_NAME：主鍵的欄位名(字元型)
   *  MARK_COLUMN_VALUE：主鍵欄位的值(字元型)
   *  MARK_TYPE_ISNUM：主鍵是否數值(0是1否，數值型)
   *  BLOB_COLUMN_NAME：檔案儲存的欄位名(字元型)
   *  FILE_NAME_COLUMN：檔名對應的欄位名(字元型)
   *樣例：
   *  exec EPAYMENT.GET_FILE_BY_BLOB('t_audit_channel_file', 'audit_id', '756', 0, 'audit_file', 'file_name');  
  ******************************************/
  
  
    UTL_FILE_OBJ     UTL_FILE.FILE_TYPE;       -- 預生成檔案的控制程式碼
    UTL_BUFF_RAW     RAW(32767);               -- 快取讀取的到BLOB(以位元組為單位，作為資料庫列最大2000，作為變數最大32767位元組)
    READ_MAX_SIZE    BINARY_INTEGER := 32767;  -- 一次讀取的長度
    UTL_CURR_POS     INTEGER := 1;             -- 當前讀取位置
    UTL_BLOB_OBJ     BLOB;                     -- BLOB的內容
    UTL_BLOB_LEN     INTEGER;                  -- BLOB的總長
    GEN_FILE_NAME    VARCHAR2(150);            -- 預生成的檔名
    DYN_STR_SQL      VARCHAR(250);             -- 執行的動態SQL
  
    BEGIN
    
        DBMS_OUTPUT.PUT_LINE('INFO：Start...');

        IF MARK_TYPE_ISNUM = 0 THEN
            DYN_STR_SQL := 'SELECT ' || FILE_NAME_COLUMN || ' , ' || BLOB_COLUMN_NAME || ' FROM ' || QUERY_TABLE_NAME || ' WHERE ' || MARK_COLUMN_NAME || ' = ' || MARK_COLUMN_VALUE;
        ELSE
            DYN_STR_SQL := 'SELECT ' || FILE_NAME_COLUMN || ' , ' || BLOB_COLUMN_NAME || ' FROM ' || QUERY_TABLE_NAME || ' WHERE ' || MARK_COLUMN_NAME || ' = ''' || MARK_COLUMN_VALUE || '''';
        END IF;
  
    DBMS_OUTPUT.PUT_LINE('DEBUG：' || DYN_STR_SQL);
    -- 執行動態SQL，並將值賦給變數
    EXECUTE IMMEDIATE DYN_STR_SQL INTO GEN_FILE_NAME, UTL_BLOB_OBJ;
    DBMS_OUTPUT.PUT_LINE('DEBUG：'|| GEN_FILE_NAME);
  
    DBMS_LOB.OPEN(UTL_BLOB_OBJ, DBMS_LOB.LOB_READONLY);
    UTL_BLOB_LEN  := DBMS_LOB.GETLENGTH(UTL_BLOB_OBJ);
     
    UTL_FILE_OBJ  := UTL_FILE.FOPEN('DB_OUT', GEN_FILE_NAME, 'WB', READ_MAX_SIZE);
  
    WHILE UTL_CURR_POS < UTL_BLOB_LEN LOOP
        --  DBMS_LOB.READ(LOB資料，指定長度，起始位置，儲存返回LOB型別值變數)；
        --  第二個引數呼叫時按照該引數指定的長度來讀取資料，讀取完畢後，將其更新為實際讀取的字元（位元組）長度。當讀取後，該引數的值比你原來的值小，則說明已經讀取到 LOB 的末尾了
        DBMS_LOB.READ(UTL_BLOB_OBJ, READ_MAX_SIZE, UTL_CURR_POS, UTL_BUFF_RAW);
        --  UTL_FILE.PUT_RAW(檔案控制程式碼，快取，是否自動FLUSH)
        UTL_FILE.PUT_RAW(UTL_FILE_OBJ, UTL_BUFF_RAW, TRUE);
        UTL_CURR_POS := UTL_CURR_POS + READ_MAX_SIZE;
        DBMS_OUTPUT.PUT_LINE('DEBUG: Reading(Cuur='||UTL_CURR_POS||' | Total='|| UTL_BLOB_LEN||')...');
    END LOOP;

    DBMS_LOB.CLOSE(UTL_BLOB_OBJ);
    UTL_FILE.FCLOSE(UTL_FILE_OBJ);
    DBMS_OUTPUT.PUT_LINE('INFO：End...');

    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('ERR: No data.');
        WHEN OTHERS THEN
            IF UTL_FILE.IS_OPEN(UTL_FILE_OBJ) THEN
                UTL_FILE.FCLOSE(UTL_FILE_OBJ);
                RAISE;
            END IF;
            DBMS_OUTPUT.PUT_LINE('ERR：' || SQLERRM);
END;