Oracle - LOB（大对象数据类型）

最新推荐文章于 2021-11-10 21:53:42 发布

袭冷

最新推荐文章于 2021-11-10 21:53:42 发布

阅读量8.2k

点赞数 1

CC 4.0 BY-SA版权

分类专栏： DB 文章标签： oracle lob bfile

本文链接：https://blog.youkuaiyun.com/guoxilen/article/details/72991976

DB 专栏收录该内容

52 篇文章

订阅专栏

本文介绍了Oracle数据库中的LOB（大对象）数据类型，包括BLOB、CLOB、NCLOB和BFILE。LOB用于存储大量二进制和文本数据，其中BFILE存储在操作系统文件中。文章还详细阐述了LOB的内部机制、创建方法，以及Temporary LOB的特点和创建方式，强调了 Temporary LOB 不支持事务管理和一致性读取等特性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、简介

在ORACLE数据库中，LOB（Large Objects(大对象)）是用来存储大量的二进制和文本数据的一种数据类型。在 Oracle8i 中开始提供 LOB 字段，在 Oracle10g 前最多只能存储 4G 数据，从 Oracle10g 开始，可以存储 8-128T 的数据，取决于你的数据库的块大小。

LOB又分为两种类型：

内部LOB类型：将数据以字节流的形式存储在数据库的内部。因而，内部LOB的许多操作都可以参与事务，也可以像处理普通数据一样对其进行备份和恢复操作。如：

BLOB：二进制大对象。定义一个 BLOB 变量，用于存储大量的二进制数据(如图像、视频、音频等)，该二进制大对象存储于数据库中。

CLOB：字符型大对象（单字节字符数据）。定义一个 CLOB 变量，用于存储超长的文本数据，该字符型大对象存储于数据库中。

NCLOB：字符型大对象（多字节国家字符数据）。定义一个 NCLOB 变量，用于存储超长的文本数据，该字符型大对象存储于数据库中。

外部LOB类型：在数据库内仅存储数据在操作系统中的位置信息，而数据的实体以外部文件的形式存在于操作系统的文件系统中。因而，该类型所表示的数据是只读的，不参与事务。如：

BFILE：二进制文件。定义一个 BFILE 变量，它指向操作系统的一个文件，Oracle 会将其视为二进制数据进行处理。

二、内部LOB（BLOB/CLOB/NCLOB）

在 Oracle 中，存储在 LOB 列中的不是实际的数据，而是该LOB字段的定位器，即指向 LOB 数据在数据库中实际存储的位置（不是内存中）的指针。而要对某一 LOB 的值进行访问和维护操作，首先得获取到这个 LOB 指针，然后使用 DBMS_LOB 包中提供的函数和过程处理实际的 LOB 数据。

1、创建

通过系统函数 EMPTY_CLOB()，得到一个空的 LOB 指针，但这个指针在此时不指向磁盘中的任何位置，也没有任何 LOB 数据。它可用于初始化 LOB 变量，或者在 INSERT 或 UPDATE 语句中用于将LOB列进行初始化(根据表的定义确定存储的位置），但不填充数据，如果需要使用该初始化后的 LOB，需要再一次从表中查询它出来。

INSERT INTO lob_demo (lob_id, lob_content) VALUES (100,EMPTY_CLOB());

2、写入

没有指向数据库实际存储位置的 LOB 不能被写入；更新 LOB 时必须要用for update 锁定

DECLARE

    lob_object CLOB;
    amount BINARY_INTEGER;
    offset INTEGER;
    content_str VARCHAR2(100);

BEGIN

    --插入一行新的记录来创建一个新的 LOB 定位器(没有指向数据库实际存储位置的 LOB 不能被写入)
    INSERT INTO lob_demo (lob_id, lob_content) VALUES (100, EMPTY_CLOB());

    --检索出上面创建的 LOB 的定位器(更新 LOB 必须要用 for update 锁定)
    SELECT lob_content INTO lob_object FROM lob_demo WHERE lob_id=100 for update;

    content_str := 'Follow I-75 across the Mackinac Bridge.';
    amount := LENGTH(content_str); -- 要写入的字符数
    offset := 1; -- LOB 中开始写入的位置

    DBMS_LOB.OPEN(lob_object, DBMS_LOB.LOB_READWRITE); -- 打开 LOB（不是严格必须的）
    DBMS_LOB.WRITE(lob_object, amount, offset, content_str);  -- 开始写入
    DBMS_LOB.CLOSE(lob_object);  -- 关闭

END;

3、读取

DECLARE

    lob_object CLOB;
    content_str VARCHAR2(300);
    read_amount BINARY_INTEGER;
    offset INTEGER;

BEGIN

    --检索先前插入的 LOB 定位器
    SELECT lob_content INTO lob_object FROM lob_demo WHERE lob_id =100;

    offset := 1;
    read_amount := 200;

    DBMS_LOB.READ(lob_demo, read_amount, offset, content_str);

    IF read_amount = 200 THEN   ----如果实际读了200个字符，更新偏移量，继续尝试读取.
        DBMS_OUTPUT.PUT_LINE(content_str);
        
        offset := offset + read_amount;
        DBMS_LOB.READ(lob_demo, read_amount, offset, content_str);
        DBMS_OUTPUT.PUT_LINE(content_str);

    ELSE   --如果实际没读够200个，则表示已经读取完了
        DBMS_OUTPUT.PUT_LINE(content_str);
    END IF;

END;

三、外部LOB（BFILE）

在 PL/SQL 中操作 BFILE，其实也是操作 LOB 指针。只是对于 BFILE 的指针来说，它指向的 BFILE 数据在数据库外。所以，一个 BFILE 列的两行，可以存储指向同一个文件的 BFILE 指针。

和BLOB，CLOB，NCLOB三种大字段类型相比，BFILE 有以下三点不同：

1) BFILE 的数据是存储在操作系统文件中的，而不是在数据库中

2) BFILE 数据不参与事务处理，也就是说，BFILE 数据的改变不能被提交和回滚（但 BFILE 指针的改变是可以提交或回滚的）

3) 从 PL/SQL 中，只能读取 BFILE 数据，而不能写入。必须得在数据库外先创建 BFILE 文件，再创建 BFILE 指针

1、创建

BFILE 指针由目录和文件名组成，将这两部分信息作为参数传入 BFILENAME 函数，该函数会返回一个 BFILE 指针

一个 BFILE 指针只是简单地将目录和文件名联合在一块，而实际的目录和文件甚至可以不存在。所以，你可以创建一个目录，它实际指向的路径并不存在；再使用这个目录创建一个 BFILE 指针

-- 第一步：        
create directory bfile_data as '/usr/local/bfiledata';  --创建一个目录


-- 第二步：
DECLARE

    piece_bfile BFILE;

BEGIN

    piece_bfile := BFILENAME('BFILE_DATA','test.jpg'); --创建一个BFILE指针
    INSERT INTO bfile_demo (bfile_id, bifle_content) VALUES (100, piece_bfile);   --保存到表中
    
END;

2、读取

DECLARE

    bfile_object BFILE;
    piece RAW(60);
    amount BINARY_INTEGER := 60;
    offset INTEGER := 1;

BEGIN

    SELECT bifle_content INTO bfile_object FROM bfile_demo WHERE bfile_id = 100;

    -- 打开这个指针，读取60个字节，然后关闭它
    DBMS_LOB.OPEN(bfile_object);  
    DBMS_LOB.READ(bfile_object, amount, 1, piece);
    DBMS_LOB.CLOSE(bfile_object);

    --16进制显示结果
    DBMS_OUTPUT.PUT_LINE(RAWTOHEX(piece));

END;

3、转换

BFILE 提供了一种从数据库中访问文件系统中数据的方法，同时 ORACLE 也提供了从 BFILE 到 LOB 的方法：DBMS_LOB.LOADCLOBFROMBFILE和DBMS_LOB.LOADBLOBFROMBFILE

DECLARE

    piece_bfile BFILE := BFILENAME('BFILE_DATA','test.jpg'); --创建一个BFILE指针
    photo_blob BLOB;

BEGIN

    SELECT bifle_content INTO photo_blob FROM bfile_demo WHERE bfile_id = 101;

    DBMS_LOB.OPEN(photo_blob, DBMS_LOB.LOB_READWRITE);
    DBMS_LOB.OPEN(piece_bfile);

    DBMS_LOB.LOADBLOBFROMFILE(photo_blob, piece_bfile, DBMS_LOB.LOBMAXSIZE, 1, 1);

    DBMS_LOB.CLOSE(photo_blob);
    DBMS_LOB.CLOSE(piece_bfile);
 
END;

四、Temporary LOBs

1、说明

Temporary LOB，它跟我们先前介绍的 LOB 不一样，先前的可以称之为 Persistent LOB。有时候，我们需要 Temporary LOB，就像其它类型的本地变量一样，在会话中使用，而不用持久化到数据库中。

Temporary LOB 的默认生存期是其所在的会话的生存期，但可以被手工释放。使用 Temporary LOB，不会产生 REDO 和 UNDO 信息，所以能提供更好的性能。

和 Persistent LOB 一样，Temporary LOB 数据也是保存在数据库中的，不要以为它是在内存中存在的。但 Temporary LOB 数据并不跟任何表的列相关联，而是保存在该会话所占用的临时表空间中。所以使用 Temporary LOB，你得保证临时表空间足够存储它。

2、创建

有两种创建方法，一种是使用系统过程 DBMS_LOB.CREATETEMPORARY 显式创建，一种是直接赋值隐式创建。

显式创建：

DBMS_LOB.CREATETEMPORARY (
        -- 指定LOB定位符
    lob_loc IN OUT NOCOPY [ BLOB | CLOB CHARACTER SET ANY_CS ],  
        -- 是否将LOB读取到缓冲区
    cache IN BOOLEAN,   
        -- 指定何时清除临时LOB（DBMS_LOB.SESSION(10)：会话结束时。DBMS_LOB.CALL(12)：调用结束时）
    dur IN PLS_INTEGER := DBMS_LOB.SESSION  
);

隐式创建：

DECLARE

temp_clob CLOB;
temp_blob BLOB;

BEGIN

    --给 NULL 的 LOB 变量赋值
    temp_clob := 'www.baidu.com';
    temp_blob := HEXTORAW('7A');

END;

3、释放

使用系统过程 DBMS_LOB.FREETEMPORARY 释放 Temporary LOB

在 PL/SQL 中，若已被释放掉的 Temporary LOB 指针被赋值给另一个 LOB 指针，则这个指针也被释放

系统函数 DBMS_LOB.ISTEMPORARY 可以判断一个 LOB 指针是否指向 Temporary LOB 数据

-- 返回值：1 表示入参为 Temporary LOB 指针；0 表示入参是 persistent LOB 指针        
DBMS_LOB.ISTEMPORARY (lob_loc IN [ BLOB | CLOB CHARACTER SET ANY_CS ]) RETURN INTEGER;

4、管理

1) 和 persistent LOB 不同，Temporary LOB 不支持事务管理、一致性读取、回滚等等，所以你应该注意以下几点：

* 当处理 Temporary LOB 发生错误时，你必须释放该 LOB，然后重新进行该处理

* 由于不支持一致性读取和回滚，所以你不能将多个 LOB 指针都指向同一个 Temporary LOB 数据

* 若一个用户想要修改一个 Temporary LOB 数据，但该数据与另一个 LOB 指针关联，则会发生该 Temporary LOB 数据的拷贝（deep copy）。那么指向该 LOB 数据的其它指针将会看到不同的数据。为了降低 deep copy，当将 Temporary LOB 作为参数传递时，使用 NOCOPY 线索；

* 通过调用 DBMS_LOB.COPY 过程，可以将 Temporary LOB 数据持久化到数据库中

* 在一个会话中，Temporary LOB 指针是唯一的。不能将一个指针从一个会话传递到另一个会话，使得相关联的 LOB 数据在另一个会话中可用

2) 从 Oracle9i Database Release 1 开始，可以通过视图 v$temporary_lobs 来查看每个会话中有多少被缓存的和未被缓存的 LOBs。还可以通过数据字典 dba_segments 来查看每个会话中 LOB 占用的空间。

五、参考

http://blog.youkuaiyun.com/cunxiyuan108/article/details/6422387