ORACLE LOB大对象流

LOB大对象处理： 主要是用来存储大量数据的数据库字段，最大可以存储4G字节的非结构化数据。 主要介绍字符类型和二进制文件类型LOB数据的存储，单独介绍二进制类型LOB数据的存储。
 
一.  Oracle中的LOB数据类型分类
 1，按存储数据的类型分：
  字符类型：
   CLOB:存储大量 单字节 字符数据。
   NLOB:存储定宽 多字节 字符数据。
  二进制类型：
   BLOB:存储较大无结构的二进制数据。
  二进制文件类型：
   BFILE:将二进制文件存储在数据库外部的操作系统文件中。存放文件路径。
 
 2，按存储方式分：
   存储在内部表空间：
    CLOB，NLOB和BLOB
   指向外部操作系统文件：
    BFILE

 

二，大对象数据的录入
2.1，声明LOB类型列
 
  Create TableSpace JOHN  DataFile '/oracle/oradata/AAA/john.dbf' Size 20m;
 
  Create Table LOBTABLE (
     no Number(4),
     name VarChar2(10),
     resume CLob,
     photo BLob,
     record BFile
    )
  Lob (resume,photo)Store As (
   Tablespace JOHN  --指定存储的表空间
   Chunk 6k  --指定数据块大小
   Disable Storage In Row
  );


 2，插入大对象列
  先插入普通列数据
 
  遇到大对象列时，插入空白构造函数。
   字符型：empty_clob()，empty_nclob()
   二进制型：empty_blob()
   二进制文件类型：BFileName函数指向外部文件。
     BFileName函数：
      BFileName(‘逻辑目录名’，‘文件名’);
      逻辑目录名只能大写，因为数据词典是以大写方式存储。Oracle是区分大小写的。
      在创建时，无需将BFileName函数逻辑目录指向物理路径，使用时才做检查二者是否关联。

   
  将逻辑目录和物理目录关联。(如果是二进制文件类型)
   授予 CREATE ANY DIRECTORY 权限
    Grant  CREATE ANY DIRECTORY  TO 用户名 WITH ADMIN OPTION；
   关联逻辑目录和物理目录
    本地
    Create Directory  逻辑目录名  As  ‘文件的物理目录’；
    网络：
    Create Directory  逻辑目录名  As  ‘//主机名(IP)/共享目录’；
   例子：
    Create Directory  DUMP1 As '/export/home/oracle/dbdump1';
 
插入例子：
INSERT INTO LOBTABLE VALUES   (1,'CAI','CLOB大对象列',EMPTY_BLOB (),BFILENAME ('DUMP1', 'Screenshot.png'));

 

三，大对象数据的读取和操作：DBMS_LOB包
  DBMS_LOB包：包含处理大对象的过程和函数


  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   LOBTABLE 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   LOBTABLE 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   LOBTABLE 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   varC2 FROM   LOBTABLE WHERE   no = 2;
   SELECT   resume INTO   varC3 FROM   LOBTABLE WHERE   no = 3;
   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   LOBTABLE 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   LOBTABLE 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   LOBTABLE 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   LOBTABLE 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   LOBTABLE WHERE   no = 1 FOR UPDATE   ;
   SELECT   resume INTO   vSRC_LOB FROM   LOBTABLE 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;

三， 图片的存储或二进制文件的存储
 1，先插入普通数据，遇到大对象列使用empty_blob()构造空的指针。
  例子：
Create Table BLOBFILE
   (
     no number(8) primary key,
     fname varchar2(30),
     myfile blob
   )
   Lob (myfile) Store As
   (
     Tablespace JOHN
     Chunk 15K
     Disable Storage In Row
   );
插入指针
INSERT INTO BLOBFILE VALUES   (1, 'IMG_0210.JPG', EMPTY_BLOB ());
 
 2，逻辑目录依然用dump1目录
 3，声明一个BLOB类型变量，使用select into 语句让其指向到empty_blob()构造空的指针所指向的存储空间
    SELECT   BLOBFILE INTO   BLOB类型变量 FROM   BLOBFILE WHERE   no = 1 FOR UPDATE;
 
 4，声明一个BFile类型变量，关联逻辑目录和物理目录文件，使用 BFileName() 将其指向到待存储的文件。
    BFile类型变量 := BFileName('MYDIR','IMG_0210.JPG');
 
 5，使用DBMS_LOB.open()方法将BFile类型变量所指向的文件打开
    DBMS_LOB.Open(BFile类型变量);
 
 6，使用DBMS_LOB.loadfromfile()方法将BFile类型变量所指向的文件读入到BLOB类型变量所指向的存储空间
    DBMS_LOB.LoadfromFile(BLOB类型变量,BFile类型变量,DBMS_LOB.getlength(BFile类型变量));
 
 7，使用DBMS_LOB.close()方法将bfile的变量所指向的文件关闭
    DBMS_LOB.Close(BFile类型变量);
 
 8，提交事务
    Commit;
 
 例子;
  Declare
     varB blob;
     varF Bfile;
  Begin
     select myfile into varB from BLOBFILE where no = 1 for update;
     varF := bfileName('DUMP1', 'Screenshot.png');
     DBMS_LOB.open(varF);
    DBMS_LOB.loadfromfile(varB,varF,DBMS_LOB.getlength(varF));
     DBMS_LOB.close(varF);
     commit;
  End;
 
  --查看文件大小
  Declare
     varB blob;
  Begin
     select myfile into varB from BLOBFILE where no = 1;
     DBMS_OUTPUT.PUT_LINE('长度为: '||DBMS_LOB.getlength(varB));
  End;
 
 例子：创建过程存储图片或二进制文件
  Create Or Replace Procedure setBLOB(vFileName varchar2)
  As
     varF bfile;
     varB blob;
     vno number(8);
  Begin
     varF := BFilename('MYDIR',vFileName);
     DBMS_LOB.Open(varF);
     select max(no) into vno from BLOBFILE;
     if vno is null then
       vno := 1;
     else
       vno := vno + 1;
     end if;
     insert into BLOBFILE values(vno,vFileName,empty_blob());
     select myFile into varB from BLOBFILE where no = vno for update;
     DBMS_LOB.loadfromfile(varB,varF,DBMS_LOB.getlength(varF));
     DBMS_LOB.close(varF);
     commit;
  End;
 
  --执行过程
  EXEC setBLOB('Screenshot.png');
 
  --查看文件大小
  Declare
     varB blob;
  Begin
     select myfile into varB from BLOBFILE where no = 2;
     DBMS_output.put_line('长度为: '||DBMS_LOB.getlength(varB));

  End;

###########################

本文由转载自dave大师博客，略做修改

作者：john

转载请注明来自dave大师博客