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系列：图片的存储