Oracle中的LOB操作（轉）

二，大物件資料的錄入
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;

帶有LOB欄位的表空間的移動