ORACLE LOB 大对象处理

LOB大对象处理： 主要是用来存储大量数据的数据库字段，最大可以存储4G字节的非结构化数据。 主要介绍字符类型和二进制文件类型LOB数据的存储，单独介绍二进制类型LOB数据的存储。

一、Oracle中的LOB数据类型分类

1、按存储数据的类型分：

  ①字符类型：

   CLOB:存储大量 单字节 字符数据。

   NLOB:存储定宽 多字节 字符数据。

  ②二进制类型：

   BLOB:存储较大无结构的二进制数据。

  ③二进制文件类型：

   BFILE:将二进制文件存储在数据库外部的操作系统文件中。存放文件路径。

2、按存储方式分：

   ①存储在内部表空间：

    CLOB，NLOB和BLOB

   ②指向外部操作系统文件：

    BFILE

 

二、大对象数据的录入

1、声明LOB类型列

  Create TableSpace Dave  DataFile 'D:/Dave/dave.dbf' Size 20m;

  Create Table tLob （

     no Number（4），

     name VarChar2（10），

     resume CLob,

     photo BLob,

     record BFile

    ）

  Lob （resume,photo）Store As （

   Tablespace dave  --指定存储的表空间

   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  MYDIR As ' D:/Dave';

  插入例子：

/* Formatted on 2009-12-24 17:27:00 （QP5 v5.115.810.9015） */

INSERT INTO tlob

  VALUES   （1,

            'Dave',

            'CLOB大对象列',

            EMPTY_BLOB （），

BFILENAME （'MYDIR', 'Dave.jpg'））；

INSERT INTO tlob

  VALUES   （2,

            'Dave',

            'CLOB大对象列',

            EMPTY_BLOB （），

BFILENAME （'MYDIR', 'Dave.jpg'））；

INSERT INTO tlob

  VALUES   （3,

            'Dave',

            'CLOB大对象列',

            EMPTY_BLOB （），

BFILENAME （'MYDIR', 'Dave.jpg'））；

 

三、大对象数据的读取和操作：DBMS_LOB包

  DBMS_LOB包：包含处理大对象的过程和函数

1、读取大对象数据的过程和函数

   ①：DBMS_LOB.Read（）：从LOB数据中读取指定长度数据到缓冲区的过程。

     DBMS_LOB.Read（LOB数据，指定长度，起始位置，

存储返回LOB类型值变量）；

    例子：

/* Formatted on 2009-12-24 17:25:01 （QP5 v5.115.810.9015） */

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数据，指定提取长度，提取起始位置）：

     例子：

/* Formatted on 2009-12-24 17:24:35 （QP5 v5.115.810.9015） */

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数据， 子字符串）；

    例子：

   /* Formatted on 2009-12-24 17:25:39 （QP5 v5.115.810.9015） */

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数据）；

    例子：

/* Formatted on 2009-12-24 17:26:19 （QP5 v5.115.810.9015） */

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数据）；

 例子：

   /* Formatted on 2009-12-24 17:43:38 （QP5 v5.115.810.9015） */

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数据）；

    例子：

/* Formatted on 2009-12-24 17:44:17 （QP5 v5.115.810.9015） */

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数据，指定删除长度， 开始删除位置）；

 例子：

/* Formatted on 2009-12-24 17:44:45 （QP5 v5.115.810.9015） */

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数据，截断长度）；

例子：

/* Formatted on 2009-12-24 17:45:09 （QP5 v5.115.810.9015） */

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开始位置）

    例子：

  /* Formatted on 2009-12-24 17:45:30 （QP5 v5.115.810.9015） */

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;

 

四、图片的存储或二进制文件的存储

1、先插入普通数据，遇到大对象列使用empty_blob（）构造空的指针。

  例子：

/* Formatted on 2009-12-24 18:02:34 （QP5 v5.115.810.9015） */

CREATE TABLESPACE Dave  DATAFILE 'D:/Dave/dave.dbf' SIZE 20M;

Create Table MyLob

   （

     no number（8） primary key,

     fname varchar2（30），

     myfile blob

   ）

   Lob （myfile） Store As

   （

     Tablespace Dave

     Chunk 15K

     Disable Storage In Row

   ）；

INSERT INTO MyLob

  VALUES   （1, 'IMG_0210.JPG', EMPTY_BLOB （））；

 

2、创建逻辑目录MYDIR

  Create Directory  MYDIR As ' D:/Dave';

 

3、声明一个BLOB类型变量，使用select into 语句让其指向到empty_blob（）构造空的指针所指向的存储空间

   /* Formatted on 2009-12-24 18:07:25 （QP5 v5.115.810.9015） */

    SELECT   myfile

      INTO   BLOB类型变量

      FROM   myLob

     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 myLob where no = 1 for update;

     varF := bfileName（'MYDIR','Dave.JPG'）；

     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 myLob 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 myLob;

     if vno is null then

       vno := 1;

     else

       vno := vno + 1;

     end if;

     insert into myLob values（vno,vFileName,empty_blob（））；

     select myFile into varB from myLob where no = vno for update;

     DBMS_LOB.loadfromfile（varB,varF,DBMS_LOB.getlength（varF））；

     DBMS_LOB.close（varF）；

     commit;

  End;

  --执行过程

  EXEC setBLOB（'Dave.JPG'）；

  --查看文件大小

  Declare

     varB blob;

  Begin

     select myfile into varB from myLob where no = 2;

     DBMS_output.put_line（'长度为： '||DBMS_LOB.getlength（varB））；

  End;

（文章来自网络，如有侵权请来信告知，本站将在第一时间删除。）