Oracle LOB資料型別的處理

寫入Blob欄位和寫入其它型別欄位的方式非常不同，因為Blob自身有一個cursor，你必須使用cursor對blob進行操作，因而你在寫入Blob之前，必須獲得cursor才能進行寫入，那麼如何獲得Blob的cursor呢？

這需要你先插入一個empty的blob，這將建立一個blob的cursor，然後你再把這個empty的blob的cursor用select查詢出來，這樣透過兩步操作，你就獲得了blob的cursor，可以真正的寫入blob資料了。

舉例如下：

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;

關於使用LOB資料型別的效能問題：

Clob/blob實現是比較複雜的，這裡只提提幾個和效能相關的點，當然能不用lob儘量不用：
a、  一個lob欄位包括lobindex和lobsegment
b、  Lob預設可以存放在表中（表欄位），條件是：
     1.它的大小小於4kb
     2.並且在定義的時候沒有使用(disable storage inrow)字句（預設是enable）
     當lob大於4kb的時候它會被存放到lobsegment中
c、  當lob存放在表中的時候，它可以被快取，對於它的操作效率遠遠高於儲存在lobsegment中的lob（不用lobindex）
d、  儲存在lobsegment中的lob預設不在緩衝區快取，對於lob的讀寫都是物理IO，代價非常高，所以對於大於4kb的lob欄位千萬不要頻繁更新，效率非常低
e、  儲存在lobsegment中的lob可以在定義的時候指定使用cache（預設是nocache），這對於中等大小的lob（比如幾k～幾十k）很有效果，減少物理IO

使用其他資料型別需要考慮的效能問題：

1、 Char
定長格式字串，在資料庫中儲存時不足位數填補空格，不建議使用，會帶來不必要的麻煩
a、  字串比較的時候，如果不注意（char不足位補空格）會帶來錯誤
b、  字串比較的時候，如果用trim函式，這樣該欄位上的索引就失效（有時候會帶來嚴重效能問題）
c、  浪費儲存空間
 
2、 Varchar2/Varchar
不定長格式字串，對於4000位元組以內的字串，建議都用該型別
a、  網上有說char比varchar2效能好，但是如果你有興趣做測試的話，會發現沒有區別（如果發生行遷移，可以透過pctfree來調整）
b、  充分利用儲存空間
 
3、 Long/long raw
Oracle已經廢棄，只是為了向下相容保留著，應該全部升級到lob
Long型別有很多限制
a、  表中只能有一列long型別
b、  Long型別不支援分散式事務
c、  太多的查詢不能在long上使用了
 
4、 Number
定義Number的方法：Number(p,s)
其中p，s都是可選的：
a、  p代表精度，預設為38
b、  s代表小數位數，取值範圍-84~127，預設取值要看是否指定了p，如果制定了p，預設s為0，如果沒有指定p，預設取最大值。
幾個例子：
a、  Number(5,0)=Number(5) 取值範圍99999～-99999
b、  Number(5,2) 取值範圍999.99～-999.99
注意：其中的整數位數只有3位，小數位數有2位，按照如下方法計算：
      整數位數<=p-s
      小數位數<=s
如果插入123.555儲存在資料庫中變成123.56 (在小數的第三位上四捨五入)，如果插入999.999，資料庫就要拋錯。
c、  Number(5,-2) 取值範圍9999900～-9999900 （整數位數<=p-s，沒有小數位數）
如果插入9999949儲存在資料庫中變成9999900（在整數的第二位上四捨五入），如果插入9999950，資料庫就要拋錯。
其他的數值型別都是number的衍生，底層都是number，比如integer/int完全對映到number(38)
效能相關：number是一種軟實現的型別，如果需要對number做複雜的運算，建議先用cast內建函式轉換number為浮點數型別
另外需要注意的一點是：number是變長型別，在計算表儲存空間的時候要切記
 
5、 Date
Date型別是一個7位元組的定長資料型別，沒啥好說的，一個例子：效能a>b>c
a、Where date_colum>=to_date(’01-jan-2007’,’dd-mon-yyyy’)
   and date_colum< DIV>

b、Where trunc(date_colum,’y’)=to_date(’01-jan-2007’,’dd-mon-yyyy’)
c、Where to_char(date_colum,’yyyy’)=’2007’
 
6、 Timestamp/timestamp with time zone/timestamp with local time zone
和date類似，只不過它另外支援小數秒和時區。語法Timestamp(n)，n指定秒的小數位數，取值範圍0～9。可選。