Oracle - LOB（大物件資料型別）

襲冷發表於2018-01-17
原文網址 : https://blog.csdn.net/guoxilen/article/details/72991976
Oracle物件資料型別

一、簡介

    在ORACLE資料庫中，LOB（Large Objects(大物件)）是用來儲存大量的二進位制和文字資料的一種資料型別。在 Oracle8i 中開始提供 LOB 欄位，在 Oracle10g 前最多隻能儲存 4G 資料，從 Oracle10g 開始，可以儲存 8-128T 的資料，取決於你的資料庫的塊大小。

    LOB又分為兩種型別：
    
        內部LOB型別：將資料以位元組流的形式儲存在資料庫的內部。因而，內部LOB的許多操作都可以參與事務，也可以像處理普通資料一樣對其進行備份和恢復操作。如：

            BLOB：二進位制大物件。定義一個 BLOB 變數，用於儲存大量的二進位制資料(如影象、視訊、音訊等)，該二進位制大物件儲存於資料庫中。

            CLOB：字元型大物件（單位元組字元資料）。定義一個 CLOB 變數，用於儲存超長的文字資料，該字元型大物件儲存於資料庫中。

            NCLOB：字元型大物件（多位元組國家字元資料）。定義一個 NCLOB 變數，用於儲存超長的文字資料，該字元型大物件儲存於資料庫中。


        外部LOB型別：在資料庫內僅儲存資料在作業系統中的位置資訊，而資料的實體以外部檔案的形式存在於作業系統的檔案系統中。因而，該型別所表示的資料是隻讀的，不參與事務。如：

            BFILE：二進位制檔案。定義一個 BFILE 變數，它指向作業系統的一個檔案，Oracle 會將其視為二進位制資料進行處理。


二、內部LOB（BLOB/CLOB/NCLOB）

    在 Oracle 中，儲存在 LOB 列中的不是實際的資料，而是該LOB欄位的定位器，即指向 LOB 資料在資料庫中實際儲存的位置（不是記憶體中）的指標。而要對某一 LOB 的值進行訪問和維護操作，首先得獲取到這個 LOB 指標，然後使用 DBMS_LOB 包中提供的函式和過程處理實際的 LOB 資料。


    1、建立

        通過系統函式 EMPTY_CLOB()，得到一個空的 LOB 指標，但這個指標在此時不指向磁碟中的任何位置，也沒有任何 LOB 資料。它可用於初始化 LOB 變數，或者在 INSERT 或 UPDATE 語句中用於將LOB列進行初始化(根據表的定義確定儲存的位置），但不填充資料，如果需要使用該初始化後的 LOB，需要再一次從表中查詢它出來。

INSERT INTO lob_demo (lob_id, lob_content) VALUES (100,EMPTY_CLOB());
    2、寫入

        沒有指向資料庫實際儲存位置的 LOB 不能被寫入；更新 LOB 時必須要用for update 鎖定

DECLARE

    lob_object CLOB;
    amount BINARY_INTEGER;
    offset INTEGER;
    content_str VARCHAR2(100);

BEGIN

    --插入一行新的記錄來建立一個新的 LOB 定位器(沒有指向資料庫實際儲存位置的 LOB 不能被寫入)
    INSERT INTO lob_demo (lob_id, lob_content) VALUES (100, EMPTY_CLOB());

    --檢索出上面建立的 LOB 的定位器(更新 LOB 必須要用 for update 鎖定)
    SELECT lob_content INTO lob_object FROM lob_demo WHERE lob_id=100 for update;

    content_str := 'Follow I-75 across the Mackinac Bridge.';
    amount := LENGTH(content_str); -- 要寫入的字元數
    offset := 1; -- LOB 中開始寫入的位置

    DBMS_LOB.OPEN(lob_object, DBMS_LOB.LOB_READWRITE); -- 開啟 LOB（不是嚴格必須的）
    DBMS_LOB.WRITE(lob_object, amount, offset, content_str);  -- 開始寫入
    DBMS_LOB.CLOSE(lob_object);  -- 關閉

END;
    3、讀取 

DECLARE

    lob_object CLOB;
    content_str VARCHAR2(300);
    read_amount BINARY_INTEGER;
    offset INTEGER;

BEGIN

    --檢索先前插入的 LOB 定位器
    SELECT lob_content INTO lob_object FROM lob_demo WHERE lob_id =100;

    offset := 1;
    read_amount := 200;

    DBMS_LOB.READ(lob_demo, read_amount, offset, content_str);

    IF read_amount = 200 THEN   ----如果實際讀了200個字元，更新偏移量，繼續嘗試讀取.
        DBMS_OUTPUT.PUT_LINE(content_str);
        
        offset := offset + read_amount;
        DBMS_LOB.READ(lob_demo, read_amount, offset, content_str);
        DBMS_OUTPUT.PUT_LINE(content_str);

    ELSE   --如果實際沒讀夠200個，則表示已經讀取完了
        DBMS_OUTPUT.PUT_LINE(content_str);
    END IF;

END;

三、外部LOB（BFILE）

    在 PL/SQL 中操作 BFILE，其實也是操作 LOB 指標。只是對於 BFILE 的指標來說，它指向的 BFILE 資料在資料庫外。所以，一個 BFILE 列的兩行，可以儲存指向同一個檔案的 BFILE 指標。

    和BLOB，CLOB，NCLOB三種大欄位型別相比，BFILE 有以下三點不同：

        1) BFILE 的資料是儲存在作業系統檔案中的，而不是在資料庫中

        2) BFILE 資料不參與事務處理，也就是說，BFILE 資料的改變不能被提交和回滾（但 BFILE 指標的改變是可以提交或回滾的）

        3) 從 PL/SQL 中，只能讀取 BFILE 資料，而不能寫入。必須得在資料庫外先建立 BFILE 檔案，再建立 BFILE 指標

    1、建立

        BFILE 指標由目錄和檔名組成，將這兩部分資訊作為引數傳入 BFILENAME 函式，該函式會返回一個 BFILE 指標

        一個 BFILE 指標只是簡單地將目錄和檔名聯合在一塊，而實際的目錄和檔案甚至可以不存在。所以，你可以建立一個目錄，它實際指向的路徑並不存在；再使用這個目錄建立一個 BFILE 指標

-- 第一步：        
create directory bfile_data as '/usr/local/bfiledata';  --建立一個目錄


-- 第二步：
DECLARE

    piece_bfile BFILE;

BEGIN

    piece_bfile := BFILENAME('BFILE_DATA','test.jpg'); --建立一個BFILE指標
    INSERT INTO bfile_demo (bfile_id, bifle_content) VALUES (100, piece_bfile);   --儲存到表中
    
END;
    2、讀取 

DECLARE

    bfile_object BFILE;
    piece RAW(60);
    amount BINARY_INTEGER := 60;
    offset INTEGER := 1;

BEGIN

    SELECT bifle_content INTO bfile_object FROM bfile_demo WHERE bfile_id = 100;

    -- 開啟這個指標，讀取60個位元組，然後關閉它
    DBMS_LOB.OPEN(bfile_object);  
    DBMS_LOB.READ(bfile_object, amount, 1, piece);
    DBMS_LOB.CLOSE(bfile_object);

    --16進位制顯示結果
    DBMS_OUTPUT.PUT_LINE(RAWTOHEX(piece));

END;
    3、轉換

        BFILE 提供了一種從資料庫中訪問檔案系統中資料的方法，同時 ORACLE 也提供了從 BFILE 到 LOB 的方法：DBMS_LOB.LOADCLOBFROMBFILE和DBMS_LOB.LOADBLOBFROMBFILE

DECLARE

    piece_bfile BFILE := BFILENAME('BFILE_DATA','test.jpg'); --建立一個BFILE指標
    photo_blob BLOB;

BEGIN

    SELECT bifle_content INTO photo_blob FROM bfile_demo WHERE bfile_id = 101;

    DBMS_LOB.OPEN(photo_blob, DBMS_LOB.LOB_READWRITE);
    DBMS_LOB.OPEN(piece_bfile);

    DBMS_LOB.LOADBLOBFROMFILE(photo_blob, piece_bfile, DBMS_LOB.LOBMAXSIZE, 1, 1);

    DBMS_LOB.CLOSE(photo_blob);
    DBMS_LOB.CLOSE(piece_bfile);
 
END;

四、Temporary LOBs

    1、說明

        Temporary LOB，它跟我們先前介紹的 LOB 不一樣，先前的可以稱之為 Persistent LOB。有時候，我們需要 Temporary LOB，就像其它型別的本地變數一樣，在會話中使用，而不用持久化到資料庫中。

        Temporary LOB 的預設生存期是其所在的會話的生存期，但可以被手工釋放。使用 Temporary LOB，不會產生 REDO 和 UNDO 資訊，所以能提供更好的效能。

        和 Persistent LOB 一樣，Temporary LOB 資料也是儲存在資料庫中的，不要以為它是在記憶體中存在的。但 Temporary LOB 資料並不跟任何表的列相關聯，而是儲存在該會話所佔用的臨時表空間中。所以使用 Temporary LOB，你得保證臨時表空間足夠儲存它。

    2、建立

        有兩種建立方法，一種是使用系統過程 DBMS_LOB.CREATETEMPORARY 顯式建立，一種是直接賦值隱式建立。

        顯式建立：

DBMS_LOB.CREATETEMPORARY (
        -- 指定LOB定位符
    lob_loc IN OUT NOCOPY [ BLOB | CLOB CHARACTER SET ANY_CS ],  
        -- 是否將LOB讀取到緩衝區
    cache IN BOOLEAN,   
        -- 指定何時清除臨時LOB（DBMS_LOB.SESSION(10)：會話結束時。DBMS_LOB.CALL(12)：呼叫結束時）
    dur IN PLS_INTEGER := DBMS_LOB.SESSION  
);
        隱式建立：
DECLARE

temp_clob CLOB;
temp_blob BLOB;

BEGIN

    --給 NULL 的 LOB 變數賦值
    temp_clob := 'www.baidu.com';
    temp_blob := HEXTORAW('7A');

END;
    3、釋放

        使用系統過程 DBMS_LOB.FREETEMPORARY 釋放 Temporary LOB

        在 PL/SQL 中，若已被釋放掉的 Temporary LOB 指標被賦值給另一個 LOB 指標，則這個指標也被釋放

        系統函式 DBMS_LOB.ISTEMPORARY 可以判斷一個 LOB 指標是否指向 Temporary LOB 資料

-- 返回值：1 表示入參為 Temporary LOB 指標；0 表示入參是 persistent LOB 指標        
DBMS_LOB.ISTEMPORARY (lob_loc IN [ BLOB | CLOB CHARACTER SET ANY_CS ]) RETURN INTEGER;
    4、管理

        1) 和 persistent LOB 不同，Temporary LOB 不支援事務管理、一致性讀取、回滾等等，所以你應該注意以下幾點：

            * 當處理 Temporary LOB 發生錯誤時，你必須釋放該 LOB，然後重新進行該處理

            * 由於不支援一致性讀取和回滾，所以你不能將多個 LOB 指標都指向同一個 Temporary LOB 資料

            * 若一個使用者想要修改一個 Temporary LOB 資料，但該資料與另一個 LOB 指標關聯，則會發生該 Temporary LOB 資料的拷貝（deep copy）。那麼指向該 LOB 資料的其它指標將會看到不同的資料。為了降低 deep copy，當將 Temporary LOB 作為引數傳遞時，使用 NOCOPY 線索；

            * 通過呼叫 DBMS_LOB.COPY 過程，可以將 Temporary LOB 資料持久化到資料庫中

            * 在一個會話中，Temporary LOB 指標是唯一的。不能將一個指標從一個會話傳遞到另一個會話，使得相關聯的 LOB 資料在另一個會話中可用

        2) 從 Oracle9i Database Release 1 開始，可以通過檢視 v$temporary_lobs 來檢視每個會話中有多少被快取的和未被快取的 LOBs。還可以通過資料字典 dba_segments 來檢視每個會話中 LOB 佔用的空間。

五、參考

http://blog.csdn.net/cunxiyuan108/article/details/6422387



相關文章