13-4 Lob類型
13.4.1 基本介紹
Oracle和plsql都支持lob(large object) 類型，用來存儲大數量數據，如圖像文件，聲音文件等。Oracle 9i realse2支持存儲最大為4g的數據，oracle 10g realse1支持最大8到128萬億字節的數據存儲，依賴于你的db的block size。
在plsql中可以申明的lob類型的變量如下：
類型 描述
BFILE 二進制文件,存儲在數據庫外的操作系統文件，只讀的。把此文件當二進制處理。
BLOB 二進制大對象。存儲在數據庫里的大對象，一般是圖像聲音等文件。
CLOB 字符型大對象。一般存儲大數量文本信息。存儲單字節，固定寬度的數據。
NCLOB 字節字符大對象。存儲單字節大塊，多字節固定寬度，多字節變寬度數據。
Oracle將lob分類為兩種：
1.存儲在數據庫里的，參與數據庫的事務。BLOB,CLOB,NCCLOB。
2.存儲在數據庫外的BFILE，不參與數據庫的事務，也就是不能rollback或commit等，它依賴于文件系統的數據完整性。
LONG和LONG RAW這兩種數據類型也是存儲字符的，但是有系列的問題，不建議使用，這里也就不討論了。
13.4.2 LOB的使用
本部分不討論lob的所有細節，只討論lob的基本原理和在plsql中的基本使用，為plsql開發使用lob提供一個基礎性指導。
本部分使用的表是：
/**
table script
**/
CREATE TABLE waterfalls (
falls_name VARCHAR2(80),--name
falls_photo BLOB,--照片
falls_directions CLOB,--文字
falls_description NCLOB,--文字
falls_web_page BFILE);--指向外部的html頁面
/
這個表我們并不需要clob和nclob兩個，只取一就可以，這里全部定義只是為了演示使用。
1. 理解LOB的Locator
表中的Lob類型的列中存儲的只是存儲指向數據庫中實際存儲lob數據的一個指針。
在plsql中申明了一個lob類型的變量，然后從數據庫中查詢一個lob類型的值分配給變量，也只是將指針復制給了它，那么這個變量也會指向數據庫中實際存放lob數據的地方。如：
--understanding lob locators
DECLARE
photo BLOB;
BEGIN
SELECT falls_photo
INTO photo
FROM waterfalls
WHERE falls_name='Dryer Hose';
見下圖：
Lob工作原理圖解
從上面的圖可以看出，要處理lob數據，必須先獲得lob locators。我們可以通過一個select語句獲取，當賦值給lob變量的時候，它也獲得同樣的lob locators。我們在plsql中處理可以使用dbms_lob包，里面內置了很多過程和函數來讀取和修改我們的lob數據。下面給出處理lob數據的一般方法。
1. 通過select語句獲取一個lob locator。
2. 通過調用dbms_lob.open打開lob。
3. 調用dbms_lob.getchunksize獲得最佳讀寫lob值。
4. 調用dbms_lob.getlength獲取lob數據的字節值。
5. 調用dbms_lob.read獲取lob數據。
6. 調用dbms_lob.close關閉lob。
2. Empty lob and Null lob
Empty的意思是我們已經獲取了一個lob locator，但是沒有指向任何lob數據。Null是定義了一個變量，但是沒有獲得lob locator。對lob類型的處理和其他類型不一樣。如下面的例子：
/* null lob example*/
declare
directions clob;--定義了，但是沒有分配值，為null
begin
if directions is null then
dbms_output.put_line('directions is null');
else
dbms_output.put_line('directions is not null');
end if;
end;
/
DECLARE
directions CLOB;--定義一個，并且分配值
BEGIN
--刪除一行
DELETE
FROM waterfalls
WHERE falls_name='Munising Falls';
--插入一行通過使用 EMPTY_CLOB( ) to 建立一個lob locator
INSERT INTO waterfalls
(falls_name,falls_directions)
VALUES ('Munising Falls',EMPTY_CLOB( ));
--獲得lob locator，上面插入的數據，因為我們插入的是一個empty_clob(),那么lob locator不指向任何數據，雖然給變量分配了只
SELECT falls_directions
INTO directions
FROM waterfalls
WHERE falls_name='Munising Falls';
IF directions IS NULL THEN
DBMS_OUTPUT.PUT_LINE('directions is NULL');
ELSE
DBMS_OUTPUT.PUT_LINE('directions is not NULL');--打印此句
END IF;
DBMS_OUTPUT.PUT_LINE('Length = '
|| DBMS_LOB.GETLENGTH(directions));--結果為o
END;
注意：
1. 上面例子中的empty_clob()是oracle的內置函數，創建了一個lob locator。但是我們沒有讓它指向任何數據，所以是empty。而且通過select語句給變量directions分配了lob locator，所以不是null,但是length為0，故為empty。
2. 在基本類型中，我們判斷一個變量是不是有數據，只要is null就可以了。但是在lob類型中我們從以上的例子看出來是不正確的。Lob首先必須判斷is null看是否分配lob locator，如果分配了還需要進一步檢查length是否為0，看是否是empty，所以完整的是下面這樣：
IF some_clob IS NULL THEN
--如果is null為true表示未分配，肯定沒有數據
ELSEIF DBMS_LOB.GETLENGTH(some_clob) = 0 THEN
--分配了length為0，也沒有數據
ELSE
--有數據
END IF;
3.建立LOB
在上面我們使用empty_clob()建立了一個空的clob，lob locator只是一個指針，真正的數據是存儲在磁盤中或數據庫文件中。我們先建立一個空的clob，然后我們可以update來讓變量真正指向有數據的lob。Empty_clob()可以用來處理clob和nclob。在oracle 8i中可以使用temporary lob達到同樣的效果。
4.向LOB里寫入數據
當獲得一個有效的lob locator之后，就可以使用dbms_lob包的下列procedure向lob中寫入數據。
DBMS_LOB.WRITE:允許自動寫入數據到lob中。
DBMS_LOB.WRITEAPPEND:向lob的末尾寫入數據。
--write lob
DECLARE
directions CLOB;
amount BINARY_INTEGER;
offset INTEGER;
first_direction VARCHAR2(100);
more_directions VARCHAR2(500);
BEGIN
--Delete any existing rows for 'Munising Falls' so that this
--example can be executed multiple times
DELETE
FROM waterfalls
WHERE falls_name='Munising Falls';
--Insert a new row using EMPTY_CLOB( ) to create a LOB locator
INSERT INTO waterfalls
(falls_name,falls_directions)
VALUES ('Munising Falls',EMPTY_CLOB( ));
--Retrieve the LOB locator created by the previous INSERT statement
SELECT falls_directions
INTO directions
FROM waterfalls
WHERE falls_name='Munising Falls';
--Open the LOB; not strictly necessary, but best to open/close LOBs.
DBMS_LOB.OPEN(directions, DBMS_LOB.LOB_READWRITE);
--Use DBMS_LOB.WRITE to begin
first_direction := 'Follow I-75 across the Mackinac Bridge.';
amount := LENGTH(first_direction); --number of characters to write
offset := 1; --begin writing to the first character of the CLOB
DBMS_LOB.WRITE(directions, amount, offset, first_direction);
--Add some more directions using DBMS_LOB.WRITEAPPEND
more_directions := ' Take US-2 west from St. Ignace to Blaney Park.'
|| ' Turn north on M-77 and drive to Seney.'
|| ' From Seney, take M-28 west to Munising.';
DBMS_LOB.WRITEAPPEND(directions,
LENGTH(more_directions), more_directions);
--Add yet more directions
more_directions := ' In front of the paper mill, turn right on H-58.'
|| ' Follow H-58 to Washington Street. Veer left onto'
|| ' Washington Street. You''ll find the Munising'
|| ' Falls visitor center across from the hospital at'
|| ' the point where Washington Street becomes'
|| ' Sand Point Road.';
DBMS_LOB.WRITEAPPEND(directions,
LENGTH(more_directions), more_directions);
--Close the LOB, and we are done.
DBMS_LOB.CLOSE(directions);
END;
/
在這個例子里，我們使用了write 和writeappend這兩個過程來插入數據到lob中。因為開始的時候，我們插入了一個空的lob locator。要注意一點，我們最后使用了dbms_lob.close方法關閉lob。這是一個好的方法，特別是在處理oracle text的時候，任何oracle text domain和function-based indexes被update是在wirte和writeappend的時候調用的，而不是在close的時候被update的。
我們向lob中寫入數據的時候，沒有必要更新表中的列。因為它保存的只是一個locator，我們的變量也獲得同樣的locator，當我們寫入數據去lob的時候，locator并沒有改變。改變的只是locator指向的物理數據。
在sqlplus中顯示上面的例子：
SQL> SET LONG 2000
SQL> COLUMN falls_directions WORD_WRAPPED FORMAT A70
SQL> SELECT falls_directions
2 FROM waterfalls
3 WHERE falls_name='Munising Falls';
其中set long 2000是顯示2000個字符。Word_wrappend是自動換行。
5.從lob中讀取數據
步驟：a.通過select查詢獲得lob locator初始化lob變量。2.調用dbms_lob.read過程讀取lob數據。
下面是dbms_lob.read過程的定義，注意參數.
PROCEDURE read(lob_loc IN BLOB, --初始化后的lob變量lob locator
amount IN OUT NOCOPY INTEGER,--讀取的數量(clob為字符數,blob,bfile是字節數)
offset IN INTEGER,--開始讀取位置
buffer OUT RAW);--讀到的數據,raw要顯示用轉換函數，見bfile
PROCEDURE read(lob_loc IN CLOB CHARACTER SET ANY_CS,
amount IN OUT NOCOPY INTEGER,
offset IN INTEGER,
buffer OUT VARCHAR2 CHARACTER SET lob_loc%CHARSET);
PROCEDURE read(file_loc IN BFILE,
amount IN OUT NOCOPY INTEGER,
offset IN INTEGER,
buffer OUT RAW);
下面是一個讀取clob的例子：
--從lob中讀取數據
DECLARE
directions CLOB;
directions_1 VARCHAR2(300);
directions_2 VARCHAR2(300);
chars_read_1 BINARY_INTEGER;
chars_read_2 BINARY_INTEGER;
offset INTEGER;
BEGIN
--首先獲得一個lob locator
SELECT falls_directions
INTO directions
FROM waterfalls
WHERE falls_name='Munising Falls';
--記錄開始讀取位置
offset := 1;
--嘗試讀取229個字符，chars_read_1將被實際讀取的字符數更新
chars_read_1 := 229;
DBMS_LOB.READ(directions, chars_read_1, offset, directions_1);
--當讀取229個字符之后，更新offset，再讀取225個字符
IF chars_read_1 = 229 THEN
offset := offset + chars_read_1;--offset變為offset+chars_read_1，也就是從300開始
chars_read_2 := 255;
DBMS_LOB.READ(directions, chars_read_2, offset, directions_2);
ELSE
chars_read_2 := 0;--否則后面不在讀取
directions_2 := '';
END IF;
--顯示讀取的字符數
DBMS_OUTPUT.PUT_LINE('Characters read = ' ||
TO_CHAR(chars_read_1+chars_read_2));
--顯示結果
DBMS_OUTPUT.PUT_LINE(directions_1);
dbms_output.put_line(length(directions_1));
DBMS_OUTPUT.PUT_LINE(directions_2);
dbms_output.put_line(length(directions_2));
END;
/
Dbms_lob.read的第2個參數是傳遞要讀取的數量。對于clob是字符數，blob和bfile都是字節數。它是隨著讀取的數目自動更新的，offset不會更新。所以分布讀取需要手動更新offset，下個offset是上一個offset+讀取的數量。我們可以通過dbms_lob.get_length(lob_locator)獲得這個lob的長度，結果clob是字符數，blob和bfile是字節數，然后分布讀取。
13.4.3 使用Bfile
Bfile和clob，nclob，blob是不同的。Bfile是外部的lob類型，其他三個是oracle內部的lob類型，它們至少有三點主要不同的地方：
1. bfile的值是存在操作系統的文件中，而不是數據庫中。
2. bfile不參與數據庫事務操作。也就是改變bifle不能commit或rollback。但是改變bfile的locator可以commit或rollback。
3. bfile在plsql和oracle中是只讀的，不允許寫。你必須生成一個外部的操作系統文件讓bfile locator能夠完全指向它。
在plsql中使用bifle，仍然需要lob locator，只不過是一個目錄和文件的別名，你可以使用biflename函數獲得一個bfile locator。使用create or replace directory [alias] as ‘file locator directory’，你必須具有CREATE ANY DIRECTORY權限才能使用。如：
CREATE DIRECTORY bfile_data AS 'c:PLSQL BookCh12_Misc_Datatypes';
GRANT READ ON DIRECTORY bfile_data TO gennick; --讀的權限給這個用戶。
通過all_directory查找目錄信息。