Oracle LOB資料型別的處理

li__hl8888發表於2016-11-07
 Oracle的Blob欄位比較特殊,他比long欄位的效能要好很多,可以用來儲存例如圖片之類的二進位制資料。
寫入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。可選。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/8568259/viewspace-2127908/,如需轉載,請註明出處,否則將追究法律責任。

相關文章