Oracle系列：LOB大對象處理

Oracle系列：LOB大對象處理

 主要是用來儲存大量資料的資料庫欄位，最大可以儲存4G位元組的非結構化資料。
 主要介紹字元類型和二進位檔案類型LOB資料的儲存，單獨介紹二進位類型LOB資料的儲存。
 
一，Oracle中的LOB資料類型分類
 1，按儲存資料的類型分：
  ①字元類型：
   CLOB:儲存大量 單位元組 字元資料。
   NLOB:儲存定寬 多位元組 字元資料。
  ②二進位類型：
   BLOB:儲存較大無結構的位元據。
  ③二進位檔案類型：
   BFILE:將二進位檔案儲存在資料庫外部的作業系統檔案中。存放檔案路徑。
 
 2，按儲存方式分：
   ①儲存在內部資料表空間：
    CLOB，NLOB和BLOB
   ②指向外部作業系統檔案：
    BFILE

二，大對象資料的錄入
 1，聲明LOB類型列
  /*
   conn scott/tiger;
   Create TableSpace ts5_21
      DataFile 'E:/Oracle/ts5_21.dbf'
   Size 5m;
  */
  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包：包含處理大對象的過程和函數
  /*
  insert into tlob values(1,'Gene','CLOB大對象列',empty_blob(),bfilename('MYDIR','IMG_0210.JPG'));
  insert into tlob values(2,'Jack','CLOB大對象列',empty_blob(),bfilename('MYDIR','IMG_0210.JPG'));
  insert into tlob values(3,'Mary','大對象列CLOB',empty_blob(),bfilename('MYDIR','IMG_0210.JPG'));
  */
  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;
   
 如有問題或指正請聯絡 ：qfs_v@qq.com 謝謝！

下一文:Oracle系列：圖片的儲存