最近做項目需要用到一些大對象資料，在網上淘了一陣子，發現了一些比較好的資料，記錄了下來：
主要是用來存儲大量資料的資料庫字段，最大可以存儲4G位元組的非結構化資料。
主要介紹字元類型和二進制檔案類型LOB資料的存儲，單獨介紹二進制類型LOB資料的存儲。
一，Oracle中的LOB資料類型分類
1，按存儲資料的類型分：
①字元類型：
CLOB:存儲大量 單位元組 字元資料。
NLOB:存儲定寬 多位元組 字元資料。
②二進制類型：
BLOB:存儲較大無結構的二進制資料。
③二進制檔案類型：
BFILE:将二進制檔案存儲在資料庫外部的作業系統檔案中。存放檔案路徑。
2，按存儲方式分：
①存儲在内部表空間：
CLOB，NLOB和BLOB
②指向外部作業系統檔案：
BFILE
二，大對象資料的錄入
1，聲明LOB類型列
Create Table tLob (
no Number(4),
name VarChar2(10),
resume CLob,
photo BLob,
record BFile
)
Lob (resume,photo)Store As (
Tablespace ts5_21 --指定存儲的表空間
Chunk 6k --指定資料塊大小
Disable Storage In Row
);
2，插入大對象列
①先插入普通列資料
②遇到大對象列時，插入空白構造函數。
字元型：empty_clob()，empty_nclob()
二進制型：empty_blob()
二進制檔案類型：BFileName函數指向外部檔案。
BFileName函數：
BFileName(‘邏輯目錄名’，‘檔案名’);
邏輯目錄名隻能大寫，因為資料詞典是以大寫方式存儲。Oracle是區分大小寫的。
在建立時，無需将BFileName函數邏輯目錄指向實體路徑，使用時才做檢查二者是否關聯。
例子：
Insert Into tLob Values(1,'Gene',empty_clob(),empty_blob(),bfilename('MYDIR','IMG_0210.JPG'));
③将邏輯目錄和實體目錄關聯。(如果是二進制檔案類型)
授予 CREATE ANY DIRECTORY 權限
Grant CREATE ANY DIRECTORY TO 使用者名 WITH ADMIN OPTION；
關聯邏輯目錄和實體目錄
本地
Create Directory 邏輯目錄名 As ‘檔案的實體目錄’；
網絡：
Create Directory 邏輯目錄名 As ‘\\主機名(IP)\共享目錄’；
例子：
Create Directory MYDIR As 'E:\Oracle';
插入例子：
insert into tlob values(1,'Gene','CLOB大對象列',empty_blob(),bfilename('MYDIR','IMG_0210.JPG'));
三，大對象資料的讀取和操作：DBMS_LOB包
DBMS_LOB包：包含處理大對象的過程和函數
1，讀取大對象資料的過程和函數
①：DBMS_LOB.Read()：從LOB資料中讀取指定長度資料到緩沖區的過程。
DBMS_LOB.Read(LOB資料，指定長度，起始位置，存儲傳回LOB類型值變量)；
例子：
Declare
varC clob;
vRStr varchar2(1000);
ln number(4);
Strt number(4);
Begin
select resume into varC from tlob where no = 1;
ln := DBMS_LOB.GetLength(varC);
Strt := 1;
DBMS_LOB.Read(varC, ln, Strt, vRStr);
DBMS_output.put_line('Return: '||vRStr);
End;
②：DBMS_LOB.SubStr():從LOB資料中提取子字元串的函數。
DBMS_LOB.SubStr(LOB資料，指定提取長度，提取起始位置):
例子：
Declare
varC clob;
vRStr varchar2(1000);
ln number(4);
Strt number(4);
Begin
select resume into varC from tlob where no = 1;
ln := 4;
Strt := 1;
vRStr := DBMS_LOB.SubStr(varC, ln, Strt);
DBMS_output.put_line('結果為: '||vRStr);
End;
③：DBMS_LOB.InStr()：從LOB資料中查找子字元串位置的函數。
DBMS_LOB.InStr(LOB資料, 子字元串);
例子：
Declare
varC clob;
vSubStr varchar2(1000);
vRStr varchar2(1000);
ln number(4);
Begin
select resume into varC from tlob where no = 1;
vSubStr := '大對象';
ln := DBMS_LOB.InStr(varC,vSubStr);
DBMS_output.put_line('位置為: '||ln);
vRStr := DBMS_LOB.SubStr(varC, Length(vSubStr), ln);
DBMS_output.put_line('位置為'||ln||'長度為'||Length(vSubStr)||'的子字元串為：'||vRStr);
End;
④：DBMS_LOB.GetLength()：傳回指定LOB資料的長度的函數。
DBMS_LOB.GetLength(LOB資料)；
⑤：DBMS_LOB.Compare()：比較二個大對象是否相等。傳回數值0為相等，-1為不相等。
DBMS_LOB.Compare(LOB資料，LOB資料)；
例子：
Declare
varC1 clob;
varC2 clob;
varC3 clob;
ln number(4);
Begin
select resume into varC1 from tlob where no = 1;
select resume into varC2 from tlob where no = 2;
select resume into varC3 from tlob where no = 3;
ln := DBMS_LOB.Compare(varC1,varC1);
DBMS_output.put_line('比較的結果為: '||ln);
ln := DBMS_LOB.Compare(varC2,varC3);
DBMS_output.put_line('比較的結果為: '||ln);
End;
2，操作大對象資料的過程
操作會改變資料庫中原有資料，需要加上Updata鎖鎖上指定資料列，修改完後送出事務。
①：DBMS_LOB.Write()：将指定數量的資料寫入LOB的過程。
DBMS_LOB.Write(被寫入LOB, 寫入長度(指寫入LOB資料)，寫入起始位置(指被寫入LOB)，寫入LOB資料);
例子：
Declare
varC clob;
vWStr varchar2(1000);
vStrt number(4);
ln number(4);
Begin
vWStr := 'CLOB';
ln := Length(vWStr);
vStrt := 5;
select resume into varC from tlob where no = 1 FOR UPDATE;
DBMS_LOB.Write(varC, ln, vStrt, vWStr);
DBMS_output.put_line('改寫結果為: '||varC);
Commit;
End;
②：DBMS_LOB.Append()：将指定的LOB資料追加到指定的LOB資料後的過程。
DBMS_LOB.Append(LOB資料，LOB資料)；
例子：
Declare
varC clob;
vAStr varchar2(1000);
Begin
vAStr := '，這是大對象列';
select resume into varC from tlob where no = 1 FOR UPDATE;
DBMS_LOB.Append(varC, vAStr);
commit;
DBMS_output.put_line('追加結果為: '||varC);
End;
③：DBMS_LOB.Erase()：删除LOB資料中指定位置的部分資料的過程；
DBMS_LOB.Erase(LOB資料，指定删除長度, 開始删除位置)；
例子：
Declare
varC clob;
ln number(4);
strt number(4);
Begin
ln := 1;
strt := 5;
select resume into varC from tlob where no = 1 FOR UPDATE;
DBMS_LOB.Erase(varC, ln, strt);
commit;
DBMS_output.put_line('擦除結果為: '||varC);
End;
④：DBMS_LOB.Trim()：截斷LOB資料中從第一位置開始指定長度的部分資料的過程；
DBMS_LOB.Trim(LOB資料，截斷長度)；
例子：
Declare
varC clob;
ln number(4);
Begin
ln := 4;
select resume into varC from tlob where no = 1 FOR UPDATE;
DBMS_LOB.Trim(varC, ln);
COMMIT;
DBMS_output.put_line('截斷結果為: '||varC);
End;
⑤：DBMS_LOB.Copy()：從指定位置開始将源LOB複制到目标LOB；
DBMS_LOB.Copy(目标LOB，源LOB，複制源LOB長度，複制到目标LOB開始位置，複制源LOB開始位置)
例子：
Declare
vDEST_LOB clob;
vSRC_LOB clob;
AMOUNT number;
DEST_OFFSET number;
SRC_OFFSET number;
Begin
select resume into vDEST_LOB from tlob where no = 1 FOR UPDATE;
select resume into vSRC_LOB from tlob where no = 2 ;
AMOUNT := DBMS_LOB.GetLength(vSRC_LOB);
DEST_OFFSET := DBMS_LOB.GetLength(vDEST_LOB)+1;
SRC_OFFSET := 1;
DBMS_LOB.Copy(vDEST_LOB, vSRC_LOB, AMOUNT, DEST_OFFSET, SRC_OFFSET);
DBMS_output.put_line('拷貝結果為: '||vDEST_LOB);
End;
![oracle中的start with connect by用法[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)