LOB大物件處理：主要是用來儲存大量資料的資料庫欄位，最大可以儲存4G位元組的非結構化資料。
主要介紹字元型別和二進位制檔案型別LOB資料的儲存，單獨介紹二進位制型別LOB資料的儲存。

一. Oracle中的LOB資料型別分類
1，按儲存資料的型別分：
①字元型別：
   CLOB:儲存大量單位元組字元資料。
   NLOB:儲存定寬多位元組字元資料。
②二進位制型別：
   BLOB:儲存較大無結構的二進位制資料。
③二進位制檔案型別：
   BFILE:將二進位制檔案儲存在資料庫外部的作業系統檔案中。存放檔案路徑。

2，按儲存方式分：
   ①儲存在內部表空間：
    CLOB，NLOB和BLOB
   ②指向外部作業系統檔案：
    BFILE

二，大物件資料的錄入
2.1，宣告LOB型別列

Create TableSpace Dave DataFile 'D:/Dave/dave.dbf' Size 20m;

Create Table tLob (
     no Number(4),
     name VarChar2(10),
     resume CLob,
     photo BLob,
     record BFile
    )
Lob (resume,photo)Store As (
   Tablespace dave --指定儲存的表空間
   Chunk 6k --指定資料塊大小
   Disable Storage In Row
);

2，插入大物件列
①先插入普通列資料

②遇到大物件列時，插入空白建構函式。
   字元型：empty_clob()，empty_nclob()
   二進位制型：empty_blob()
   二進位制檔案型別：BFileName函式指向外部檔案。
     BFileName函式：
      BFileName(‘邏輯目錄名’，‘檔名’);
      邏輯目錄名只能大寫，因為資料詞典是以大寫方式儲存。Oracle是區分大小寫的。
      在建立時，無需將BFileName函式邏輯目錄指向物理路徑，使用時才做檢查二者是否關聯。

③將邏輯目錄和物理目錄關聯。(如果是二進位制檔案型別)
   授予 CREATE ANY DIRECTORY 許可權
    Grant CREATE ANY DIRECTORY TO 使用者名稱 WITH ADMIN OPTION；
   關聯邏輯目錄和物理目錄
    本地
    Create Directory 邏輯目錄名 As ‘檔案的物理目錄’；
    網路：
    Create Directory 邏輯目錄名 As ‘//主機名(IP)/共享目錄’；
   例子：
    Create Directory MYDIR As ' D:/Dave';

插入例子：
/* Formatted on 2009-12-24 17:27:00 (QP5 v5.115.810.9015) */

INSERT INTO tlob

VALUES (1,

'Dave',

'CLOB大物件列',

EMPTY_BLOB (),

BFILENAME ('MYDIR', 'Dave.jpg'));

INSERT INTO tlob

VALUES (2,

'Dave',

'CLOB大物件列',

EMPTY_BLOB (),

BFILENAME ('MYDIR', 'Dave.jpg'));

INSERT INTO tlob

VALUES (3,

'Dave',

'CLOB大物件列',

EMPTY_BLOB (),

BFILENAME ('MYDIR', 'Dave.jpg'));

三，大物件資料的讀取和操作：DBMS_LOB包
DBMS_LOB包：包含處理大物件的過程和函式

1，讀取大物件資料的過程和函式
①：DBMS_LOB.Read()：從LOB資料中讀取指定長度資料到緩衝區的過程。
DBMS_LOB.Read(LOB資料，指定長度，起始位置，

儲存返回LOB型別值變數)；
例子：

/* Formatted on 2009-12-24 17:25:01 (QP5 v5.115.810.9015) */

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資料，指定提取長度，提取起始位置):

例子：
/* Formatted on 2009-12-24 17:24:35 (QP5 v5.115.810.9015) */

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資料, 子字串);

例子：
/* Formatted on 2009-12-24 17:25:39 (QP5 v5.115.810.9015) */

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資料)；

例子：
/* Formatted on 2009-12-24 17:26:19 (QP5 v5.115.810.9015) */

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資料);

例子：
/* Formatted on 2009-12-24 17:43:38 (QP5 v5.115.810.9015) */

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資料)；
    例子：
/* Formatted on 2009-12-24 17:44:17 (QP5 v5.115.810.9015) */

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資料，指定刪除長度, 開始刪除位置)；

例子：
/* Formatted on 2009-12-24 17:44:45 (QP5 v5.115.810.9015) */

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資料，截斷長度)；

例子：
/* Formatted on 2009-12-24 17:45:09 (QP5 v5.115.810.9015) */

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開始位置)

例子：
/* Formatted on 2009-12-24 17:45:30 (QP5 v5.115.810.9015) */

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;

三，圖片的儲存或二進位制檔案的儲存

1，先插入普通資料，遇到大物件列使用empty_blob()構造空的指標。
例子：

/* Formatted on 2009-12-24 18:02:34 (QP5 v5.115.810.9015) */
CREATE TABLESPACE Dave DATAFILE 'D:/Dave/dave.dbf' SIZE 20M;

Create Table MyLob

   (
     no number(8) primary key,
     fname varchar2(30),
     myfile blob
   )
   Lob (myfile) Store As
   (
     Tablespace Dave

     Chunk 15K
     Disable Storage In Row
   );

INSERT INTO MyLob

VALUES   (1, 'IMG_0210.JPG', EMPTY_BLOB ());

2，建立邏輯目錄MYDIR
Create Directory MYDIR As ' D:/Dave';

3，宣告一個BLOB型別變數，使用select into 語句讓其指向到empty_blob()構造空的指標所指向的儲存空間
   /* Formatted on 2009-12-24 18:07:25 (QP5 v5.115.810.9015) */
    SELECT   myfile

INTO BLOB型別變數
FROM myLob

     WHERE   no = 1
FOR UPDATE;

4，宣告一個BFile型別變數，關聯邏輯目錄和物理目錄檔案，使用 BFileName() 將其指向到待儲存的檔案。
    BFile型別變數 := BFileName('MYDIR','IMG_0210.JPG');

5，使用DBMS_LOB.open()方法將BFile型別變數所指向的檔案開啟
    DBMS_LOB.Open(BFile型別變數);

6，使用DBMS_LOB.loadfromfile()方法將BFile型別變數所指向的檔案讀入到BLOB型別變數所指向的儲存空間
    DBMS_LOB.LoadfromFile(BLOB型別變數,BFile型別變數,DBMS_LOB.getlength(BFile型別變數));

7，使用DBMS_LOB.close()方法將bfile的變數所指向的檔案關閉
    DBMS_LOB.Close(BFile型別變數);

8，提交事務
    Commit;

例子;
Declare
     varB blob;
     varF Bfile;
Begin
     select myfile into varB from myLob where no = 1 for update;
     varF := bfileName('MYDIR','Dave.JPG');
     DBMS_LOB.open(varF);
    DBMS_LOB.loadfromfile(varB,varF,DBMS_LOB.getlength(varF));
     DBMS_LOB.close(varF);
     commit;
End;

--檢視檔案大小
Declare
     varB blob;
Begin
     select myfile into varB from myLob where no = 1;
     DBMS_OUTPUT.PUT_LINE('長度為: '||DBMS_LOB.getlength(varB));
End;

例子：建立過程儲存圖片或二進位制檔案
Create Or Replace Procedure setBLOB(vFileName varchar2)
As
     varF bfile;
     varB blob;
     vno number(8);
Begin
     varF := BFilename('MYDIR',vFileName);
     DBMS_LOB.Open(varF);
     select max(no) into vno from myLob;
     if vno is null then
       vno := 1;
     else
       vno := vno + 1;
     end if;
     insert into myLob values(vno,vFileName,empty_blob());
     select myFile into varB from myLob where no = vno for update;
     DBMS_LOB.loadfromfile(varB,varF,DBMS_LOB.getlength(varF));
     DBMS_LOB.close(varF);
     commit;
End;

--執行過程
EXEC setBLOB('Dave.JPG');

--檢視檔案大小
Declare
     varB blob;
Begin
     select myfile into varB from myLob where no = 2;
     DBMS_output.put_line('長度為: '||DBMS_LOB.getlength(varB));
End;

http://blog.csdn.net/tianlesoftware/article/details/5070981

《轉》ORACLE LOB 大物件處理

相關文章