Oracle DBMS_LOB 大对象操作

适用版本：Oracle Database 9i / 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

DBMS_LOB 包操作 LOB（大对象）数据[1]：

类型	说明
CLOB	字符大对象
NCLOB	国家字符集大对象
BLOB	二进制大对象
BFILE	外部文件

2. 创建包含 LOB 的表

CREATE TABLE docs (
  id NUMBER PRIMARY KEY,
  title VARCHAR2(100),
  content CLOB,
  image BLOB,
  file_ref BFILE
);

-- 默认 IN ROW
CREATE TABLE docs2 (
  id NUMBER,
  content CLOB
) LOB(content) STORE AS SECUREFILE (
  ENABLE STORAGE IN ROW
  COMPRESS HIGH
  DEDUPLICATE
  CACHE
);

3. 写入 LOB

3.1 INSERT

INSERT INTO docs (id, title, content) 
VALUES (1, 'Test', 'Hello World');

-- 空 LOB
INSERT INTO docs (id, content) 
VALUES (1, EMPTY_CLOB());

3.2 DBMS_LOB.WRITE

DECLARE
  v_lob CLOB;
BEGIN
  INSERT INTO docs (id, content) VALUES (1, EMPTY_CLOB()) RETURNING content INTO v_lob;
  
  DBMS_LOB.WRITE(v_lob, 11, 1, 'Hello World');
  -- 长度 11，偏移 1，内容
END;

3.3 DBMS_LOB.WRITEAPPEND

DECLARE
  v_lob CLOB;
BEGIN
  SELECT content INTO v_lob FROM docs WHERE id = 1 FOR UPDATE;
  
  DBMS_LOB.WRITEAPPEND(v_lob, 6, ' Hello');
END;

3.4 APPEND

DECLARE
  v_src CLOB;
  v_dst CLOB;
BEGIN
  SELECT content INTO v_dst FROM docs WHERE id = 1 FOR UPDATE;
  SELECT content INTO v_src FROM docs WHERE id = 2;
  
  DBMS_LOB.APPEND(v_dst, v_src);
END;

4. 读取 LOB

4.1 DBMS_LOB.READ

DECLARE
  v_lob CLOB;
  v_buf VARCHAR2(32767);
  v_len NUMBER;
  v_amount NUMBER;
BEGIN
  SELECT content INTO v_lob FROM docs WHERE id = 1;
  
  v_len := DBMS_LOB.GETLENGTH(v_lob);
  v_amount := LEAST(v_len, 32767);
  
  DBMS_LOB.READ(v_lob, v_amount, 1, v_buf);
  DBMS_OUTPUT.PUT_LINE(v_buf);
END;

4.2 直接 SELECT

-- 小 LOB 可直接读
SELECT content FROM docs WHERE id = 1;

4.3 DBMS_LOB.SUBSTR

SELECT DBMS_LOB.SUBSTR(content, 100, 1) FROM docs WHERE id = 1;
-- 取前 100 字符

4.4 DBMS_LOB.INSTR

SELECT DBMS_LOB.INSTR(content, 'Oracle') FROM docs WHERE id = 1;
-- 返回位置

5. LOB 操作

5.1 长度

SELECT DBMS_LOB.GETLENGTH(content) FROM docs WHERE id = 1;

5.2 截断

DECLARE
  v_lob CLOB;
BEGIN
  SELECT content INTO v_lob FROM docs WHERE id = 1 FOR UPDATE;
  DBMS_LOB.TRIM(v_lob, 100);  -- 保留前 100
END;

5.3 截取

DECLARE
  v_src CLOB;
  v_dst CLOB;
BEGIN
  DBMS_LOB.CREATETEMPORARY(v_dst, TRUE);
  SELECT content INTO v_src FROM docs WHERE id = 1;
  
  DBMS_LOB.COPY(v_dst, v_src, 100, 1, 1);
  -- 目标，源，长度，目标偏移，源偏移
END;

5.4 ERASE

DECLARE
  v_lob CLOB;
BEGIN
  SELECT content INTO v_lob FROM docs WHERE id = 1 FOR UPDATE;
  DBMS_LOB.ERASE(v_lob, 50, 10);  -- 从位置 10 擦除 50
END;

6. BFILE 操作

6.1 创建目录

CREATE DIRECTORY file_dir AS '/u01/files';

6.2 插入 BFILE

INSERT INTO docs (id, file_ref) 
VALUES (1, BFILENAME('FILE_DIR', 'doc.pdf'));

6.3 读取

DECLARE
  v_bfile BFILE;
  v_amount NUMBER;
BEGIN
  SELECT file_ref INTO v_bfile FROM docs WHERE id = 1;
  
  DBMS_LOB.FILEOPEN(v_bfile, DBMS_LOB.FILE_READONLY);
  v_amount := DBMS_LOB.GETLENGTH(v_bfile);
  DBMS_OUTPUT.PUT_LINE('Size: ' || v_amount);
  DBMS_LOB.FILECLOSE(v_bfile);
END;

6.4 BFILE → BLOB

DECLARE
  v_bfile BFILE;
  v_blob BLOB;
  v_amount NUMBER;
  v_dest_offset NUMBER := 1;
  v_src_offset NUMBER := 1;
BEGIN
  SELECT file_ref INTO v_bfile FROM docs WHERE id = 1;
  SELECT image INTO v_blob FROM docs WHERE id = 1 FOR UPDATE;
  
  DBMS_LOB.FILEOPEN(v_bfile);
  v_amount := DBMS_LOB.GETLENGTH(v_bfile);
  
  DBMS_LOB.LOADFROMFILE(v_blob, v_bfile, v_amount, v_dest_offset, v_src_offset);
  
  DBMS_LOB.FILECLOSE(v_bfile);
END;

7. 临时 LOB

7.1 创建

DECLARE
  v_lob CLOB;
BEGIN
  DBMS_LOB.CREATETEMPORARY(v_lob, TRUE);
  -- TRUE: 事务结束自动释放
  
  DBMS_LOB.WRITEAPPEND(v_lob, 5, 'Hello');
  
  DBMS_LOB.FREETEMPORARY(v_lob);
END;

7.2 临时 LOB 释放

-- 显式释放
DBMS_LOB.FREETEMPORARY(v_lob);

-- 自动释放（会话结束）

8. SECUREFILES（11g+）

8.1 特性

压缩
去重
加密
高性能

8.2 创建

CREATE TABLE docs (
  id NUMBER,
  content CLOB
) LOB(content) STORE AS SECUREFILE (
  COMPRESS HIGH
  DEDUPLICATE
  ENCRYPT
  CACHE
);

8.3 参数

参数	说明
COMPRESS	压缩（HIGH/MEDIUM/LOW）
DEDUPLICATE	去重
ENCRYPT	加密
CACHE	缓存

9. 性能优化

9.1 IN ROW

-- 小 LOB 存在行内
LOB(content) STORE AS SECUREFILE (
  ENABLE STORAGE IN ROW
)

9.2 CACHE

-- 频繁访问
LOB(content) STORE AS SECUREFILE (
  CACHE
)

9.3 批量操作

-- 使用 DBMS_LOB.APPEND 替代多次 WRITE

10. 常见坑与排错

10.1 ORA-22920: 未锁定

-- 必须加 FOR UPDATE
SELECT content INTO v_lob FROM docs WHERE id = 1 FOR UPDATE;

10.2 ORA-21560: 偏移无效

-- 偏移从 1 开始
-- 不能为 0 或负

10.3 ORA-22922: 不存在 LOB 值

-- LOB 为 NULL
-- 使用 EMPTY_CLOB() 初始化

10.4 临时 LOB 内存

-- 检查
SELECT * FROM v$temporary_lobs;
-- 释放
DBMS_LOB.FREETEMPORARY(v_lob);

11. 最佳实践

SECUREFILES：11g+ 推荐
IN ROW 小 LOB：性能
CACHE 频繁访问：性能
批量操作：性能
FOR UPDATE 锁定：避免错误
释放临时 LOB：内存
压缩节省空间：存储
加密敏感数据：安全
BFILE 外部大文件：节省
定期检查空间：监控

12. 参考资料

[1] Oracle Database PL/SQL Packages and Types Reference 19c, “DBMS_LOB” https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_LOB.html