ORACLE LOB 大对象处理

xhuiting

于 2023-09-25 16:05:24 发布

阅读量354

点赞数

文章标签： oracle 数据库

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

一. Oracle中的LOB数据类型分类
1，按存储数据的类型分：
①字符类型：
   CLOB:存储大量单字节字符数据。
   NLOB:存储定宽多字节字符数据。
②二进制类型：
   BLOB:存储较大无结构的二进制数据。
③二进制文件类型：
   BFILE:将二进制文件存储在数据库外部的操作系统文件中。存放文件路径。

2，按存储方式分：
   ①存储在内部表空间：
    CLOB，NLOB和BLOB
   ②指向外部操作系统文件：
    BFILE

二，大对象数据的录入
2.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;

测试：

--drop TableSpace Testtest INCLUDING CONTENTS AND DATAFILES; -- 先删除表，再删除表空间
Create TableSpace TestAha  DataFile 'E:/Aha/test.dbf' Size 200m;

-- drop table MyLobTest;
Create Table MyLobTest
   (
     no number(8) primary key,
     fname varchar2(30),
     myfile blob
   )
   Lob (myfile) Store As
   (
     Tablespace TestAha
     Chunk 15K
     Disable Storage In Row
   );
-- truncate table MyLobTest;
INSERT INTO MyLobTest
  VALUES   (1, '1.zip', EMPTY_BLOB ());	 
	
	--创建逻辑目录
-- 	drop Directory  MYDIRAha;
	Create Directory  MYDIRAHA As 'E:/Aha';
	
	Declare
     varB blob;
     varF Bfile;
  Begin
		-- 声明一个BLOB类型变量
     select myfile into varB from MyLobTest where no = 1 for update;
		 -- 声明一个BFile类型变量，关联逻辑目录和物理目录文件，使用BFileName() 将其指向到待存储的文件。
     varF := bfileName('MYDIRAHA','1.zip');
		 -- 打开文件
     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 MyLobTest where no = 1;
     DBMS_OUTPUT.PUT_LINE('长度为: '||DBMS_LOB.getlength(varB));
  End;
	
	select * from MyLobTest;