13-4 Lob類型
13.4.1 基本介紹
Oracle和plsql都支持lob(large object) 類型，用來存儲大數(shù)量數(shù)據(jù)，如圖像文件，聲音文件等。Oracle 9i realse2支持存儲最大為4g的數(shù)據(jù)，oracle 10g realse1支持最大8到128萬億字節(jié)的數(shù)據(jù)存儲，依賴于你的db的block size。
在plsql中可以申明的lob類型的變量如下：
類型 描述
BFILE 二進(jìn)制文件,存儲在數(shù)據(jù)庫外的操作系統(tǒng)文件，只讀的。把此文件當(dāng)二進(jìn)制處理。
BLOB 二進(jìn)制大對象。存儲在數(shù)據(jù)庫里的大對象，一般是圖像聲音等文件。
CLOB 字符型大對象。一般存儲大數(shù)量文本信息。存儲單字節(jié)，固定寬度的數(shù)據(jù)。
NCLOB 字節(jié)字符大對象。存儲單字節(jié)大塊，多字節(jié)固定寬度，多字節(jié)變寬度數(shù)據(jù)。
Oracle將lob分類為兩種：
1.存儲在數(shù)據(jù)庫里的，參與數(shù)據(jù)庫的事務(wù)。BLOB,CLOB,NCCLOB。
2.存儲在數(shù)據(jù)庫外的BFILE，不參與數(shù)據(jù)庫的事務(wù)，也就是不能rollback或commit等，它依賴于文件系統(tǒng)的數(shù)據(jù)完整性。
LONG和LONG RAW這兩種數(shù)據(jù)類型也是存儲字符的，但是有系列的問題，不建議使用，這里也就不討論了。
13.4.2 LOB的使用
本部分不討論lob的所有細(xì)節(jié)，只討論lob的基本原理和在plsql中的基本使用，為plsql開發(fā)使用lob提供一個(gè)基礎(chǔ)性指導(dǎo)。
本部分使用的表是：
/**
table script
**/
CREATE TABLE waterfalls (
falls_name VARCHAR2(80),--name
falls_photo BLOB,--照片
falls_directions CLOB,--文字
falls_description NCLOB,--文字
falls_web_page BFILE);--指向外部的html頁面
/
這個(gè)表我們并不需要clob和nclob兩個(gè)，只取一就可以，這里全部定義只是為了演示使用。
1. 理解LOB的Locator
表中的Lob類型的列中存儲的只是存儲指向數(shù)據(jù)庫中實(shí)際存儲lob數(shù)據(jù)的一個(gè)指針。
在plsql中申明了一個(gè)lob類型的變量，然后從數(shù)據(jù)庫中查詢一個(gè)lob類型的值分配給變量，也只是將指針復(fù)制給了它，那么這個(gè)變量也會(huì)指向數(shù)據(jù)庫中實(shí)際存放lob數(shù)據(jù)的地方。如：
--understanding lob locators
DECLARE
photo BLOB;
BEGIN
SELECT falls_photo
INTO photo
FROM waterfalls
WHERE falls_name='Dryer Hose';
見下圖：
Lob工作原理圖解
從上面的圖可以看出，要處理lob數(shù)據(jù)，必須先獲得lob locators。我們可以通過一個(gè)select語句獲取，當(dāng)賦值給lob變量的時(shí)候，它也獲得同樣的lob locators。我們在plsql中處理可以使用dbms_lob包，里面內(nèi)置了很多過程和函數(shù)來讀取和修改我們的lob數(shù)據(jù)。下面給出處理lob數(shù)據(jù)的一般方法。
1. 通過select語句獲取一個(gè)lob locator。
2. 通過調(diào)用dbms_lob.open打開lob。
3. 調(diào)用dbms_lob.getchunksize獲得最佳讀寫lob值。
4. 調(diào)用dbms_lob.getlength獲取lob數(shù)據(jù)的字節(jié)值。
5. 調(diào)用dbms_lob.read獲取lob數(shù)據(jù)。
6. 調(diào)用dbms_lob.close關(guān)閉lob。
2. Empty lob and Null lob
Empty的意思是我們已經(jīng)獲取了一個(gè)lob locator，但是沒有指向任何lob數(shù)據(jù)。Null是定義了一個(gè)變量，但是沒有獲得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;--定義一個(gè)，并且分配值
BEGIN
--刪除一行
DELETE
FROM waterfalls
WHERE falls_name='Munising Falls';
--插入一行通過使用 EMPTY_CLOB( ) to 建立一個(gè)lob locator
INSERT INTO waterfalls
(falls_name,falls_directions)
VALUES ('Munising Falls',EMPTY_CLOB( ));
--獲得lob locator，上面插入的數(shù)據(jù)，因?yàn)槲覀儾迦氲氖且粋€(gè)empty_clob(),那么lob locator不指向任何數(shù)據(jù)，雖然給變量分配了只
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));--結(jié)果為o
END;
注意：
1. 上面例子中的empty_clob()是oracle的內(nèi)置函數(shù)，創(chuàng)建了一個(gè)lob locator。但是我們沒有讓它指向任何數(shù)據(jù)，所以是empty。而且通過select語句給變量directions分配了lob locator，所以不是null,但是length為0，故為empty。
2. 在基本類型中，我們判斷一個(gè)變量是不是有數(shù)據(jù)，只要is null就可以了。但是在lob類型中我們從以上的例子看出來是不正確的。Lob首先必須判斷is null看是否分配lob locator，如果分配了還需要進(jìn)一步檢查length是否為0，看是否是empty，所以完整的是下面這樣：
IF some_clob IS NULL THEN
--如果is null為true表示未分配，肯定沒有數(shù)據(jù)
ELSEIF DBMS_LOB.GETLENGTH(some_clob) = 0 THEN
--分配了length為0，也沒有數(shù)據(jù)
ELSE
--有數(shù)據(jù)
END IF;
3.建立LOB
在上面我們使用empty_clob()建立了一個(gè)空的clob，lob locator只是一個(gè)指針，真正的數(shù)據(jù)是存儲在磁盤中或數(shù)據(jù)庫文件中。我們先建立一個(gè)空的clob，然后我們可以update來讓變量真正指向有數(shù)據(jù)的lob。Empty_clob()可以用來處理clob和nclob。在oracle 8i中可以使用temporary lob達(dá)到同樣的效果。
4.向LOB里寫入數(shù)據(jù)
當(dāng)獲得一個(gè)有效的lob locator之后，就可以使用dbms_lob包的下列procedure向lob中寫入數(shù)據(jù)。
DBMS_LOB.WRITE:允許自動(dòng)寫入數(shù)據(jù)到lob中。
DBMS_LOB.WRITEAPPEND:向lob的末尾寫入數(shù)據(jù)。
--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;
/
在這個(gè)例子里，我們使用了write 和writeappend這兩個(gè)過程來插入數(shù)據(jù)到lob中。因?yàn)殚_始的時(shí)候，我們插入了一個(gè)空的lob locator。要注意一點(diǎn)，我們最后使用了dbms_lob.close方法關(guān)閉lob。這是一個(gè)好的方法，特別是在處理oracle text的時(shí)候，任何oracle text domain和function-based indexes被update是在wirte和writeappend的時(shí)候調(diào)用的，而不是在close的時(shí)候被update的。
我們向lob中寫入數(shù)據(jù)的時(shí)候，沒有必要更新表中的列。因?yàn)樗４娴闹皇且粋€(gè)locator，我們的變量也獲得同樣的locator，當(dāng)我們寫入數(shù)據(jù)去lob的時(shí)候，locator并沒有改變。改變的只是locator指向的物理數(shù)據(jù)。
在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個(gè)字符。Word_wrappend是自動(dòng)換行。
5.從lob中讀取數(shù)據(jù)
步驟：a.通過select查詢獲得lob locator初始化lob變量。2.調(diào)用dbms_lob.read過程讀取lob數(shù)據(jù)。
下面是dbms_lob.read過程的定義，注意參數(shù).
PROCEDURE read(lob_loc IN BLOB, --初始化后的lob變量lob locator
amount IN OUT NOCOPY INTEGER,--讀取的數(shù)量(clob為字符數(shù),blob,bfile是字節(jié)數(shù))
offset IN INTEGER,--開始讀取位置
buffer OUT RAW);--讀到的數(shù)據(jù),raw要顯示用轉(zhuǎn)換函數(shù)，見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);
下面是一個(gè)讀取clob的例子：
--從lob中讀取數(shù)據(jù)
DECLARE
directions CLOB;
directions_1 VARCHAR2(300);
directions_2 VARCHAR2(300);
chars_read_1 BINARY_INTEGER;
chars_read_2 BINARY_INTEGER;
offset INTEGER;
BEGIN
--首先獲得一個(gè)lob locator
SELECT falls_directions
INTO directions
FROM waterfalls
WHERE falls_name='Munising Falls';
--記錄開始讀取位置
offset := 1;
--嘗試讀取229個(gè)字符，chars_read_1將被實(shí)際讀取的字符數(shù)更新
chars_read_1 := 229;
DBMS_LOB.READ(directions, chars_read_1, offset, directions_1);
--當(dāng)讀取229個(gè)字符之后，更新offset，再讀取225個(gè)字符
IF chars_read_1 = 229 THEN
offset := offset + chars_read_1;--offset變?yōu)閛ffset+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;
--顯示讀取的字符數(shù)
DBMS_OUTPUT.PUT_LINE('Characters read = ' ||
TO_CHAR(chars_read_1+chars_read_2));
--顯示結(jié)果
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個(gè)參數(shù)是傳遞要讀取的數(shù)量。對于clob是字符數(shù)，blob和bfile都是字節(jié)數(shù)。它是隨著讀取的數(shù)目自動(dòng)更新的，offset不會(huì)更新。所以分布讀取需要手動(dòng)更新offset，下個(gè)offset是上一個(gè)offset+讀取的數(shù)量。我們可以通過dbms_lob.get_length(lob_locator)獲得這個(gè)lob的長度，結(jié)果clob是字符數(shù)，blob和bfile是字節(jié)數(shù)，然后分布讀取。
13.4.3 使用Bfile
Bfile和clob，nclob，blob是不同的。Bfile是外部的lob類型，其他三個(gè)是oracle內(nèi)部的lob類型，它們至少有三點(diǎn)主要不同的地方：
1. bfile的值是存在操作系統(tǒng)的文件中，而不是數(shù)據(jù)庫中。
2. bfile不參與數(shù)據(jù)庫事務(wù)操作。也就是改變bifle不能commit或rollback。但是改變bfile的locator可以commit或rollback。
3. bfile在plsql和oracle中是只讀的，不允許寫。你必須生成一個(gè)外部的操作系統(tǒng)文件讓bfile locator能夠完全指向它。
在plsql中使用bifle，仍然需要lob locator，只不過是一個(gè)目錄和文件的別名，你可以使用biflename函數(shù)獲得一個(gè)bfile locator。使用create or replace directory [alias] as ‘file locator directory’，你必須具有CREATE ANY DIRECTORY權(quán)限才能使用。如：
CREATE DIRECTORY bfile_data AS 'c:PLSQL BookCh12_Misc_Datatypes';
GRANT READ ON DIRECTORY bfile_data TO gennick; --讀的權(quán)限給這個(gè)用戶。
通過all_directory查找目錄信息。