ORACLE LOB 大对象处理

转载

tianlesoftware 2022-09-02 09:16:20

文章标签 oracle 存储 insert 数据库 table 文章分类 云平台云计算

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