ORACLE之 LOB 数据类型

LOB （Large Object）类型

LOB 类型专门用于存储大对象的数据。包括大文本、图形/图形、视频剪切等。最大可以存储 4G 字节的内容。

 

内部 LOB 的数据存储在数据库中，并且支持事务操作（提交、回退、保存点）：
CLOB： 和 ORACLE7 中的 LONG 类型相似，存储单字节字符数据。
NCLOB： 用来存储定宽多字节字符数据。
BLOB： 和 LONG RAW 相似，用来存储无结构的二进制数据。

 

外部 LOB 的数据被保持在操作系统（OS）文件中，不支持事务操作：
BFILE： 它用来允许 ORACLE 对数据库外存储的大型二进制文本进行只读形式的访问。

 

临时 LOB

在编写 PL/SQL 应用程序时，可以使用临时 LOB。临时 LOB 相当于局部变量，与数据库表无关并且只能由当前应用程序建立和使用。
在 SQL 语句中使用临时 LOB 时，它们只能作为输入宿主变量使用。可在  WHERE、VALUES、SET 子句中使用临时 LOB，但不能再 INTO 子句中使用临时 LOB。

 

DBMS_LOB 包

DBMS_LOB 是 oracle 提供专门用于处理 LOB 类型数据的 PL/SQL 包。其中包括常量、过程、函数。

1、常量
dbms_lob 包定义了 6 个常量。常量引用方法为：包名.常量名（e.x: dbms_log.call）
file_readonly CONSTANT BINARY_INTEGER := 0;
lob_readonly CONSTANT BINARY_INTEGER := 0;
log_readwrite CONSTANT BINARY_INTEGER := 1;
lobmaxsize CONSTANT INTEGER := 4294967295;
call CONSTANT PLS_INTEGER := 12;
session CONSTANT PLS_INTEGER := 10;

 

2、过程
APPEND
用于将源 LOB 变量的内容添加到目标 LOB 变量的尾部。该过程不适用于 BFILE 类型。
语法：
DBMS_LOB.APPEND( dest_lob IN OUT NOCOPY BLOB, src_lob in BLOB );
DBMS_LOB.APPEND( dest_lob IN OUT NOCOPY CLOB CHARACTER SET ANY_CS, src_lob IN CLOB CHARACTER SET dest_lob%CHARSET );

CLOSE
用于关闭以及打开的 LOB。
语法：
DBMS_LOB.CLOSE( lob_loc IN OUT NOCOPYT BLOB/CLOB/BFILE );

COPY
用于将源 LOB 变量的部分或全部内容复制到目标 LOB 变量中。该过程不适用于 BFILE 类型。
语法：
DBMS_LOB.COPY
(
 dest_lob IN OUT NOCOPY BLOB/CLOB/NCLOB,
 src_lob IN NOCOPY BLOB/CLOB/NCLOB,
 amount IN INTEGER := 4294967295,
 offset_1 IN INTEGER := 1,
 offset_2 IN INTEGER := 1
);

CREATETEMPORARY
用于建立临时 LOB。该过程不适用于 BFILE 类型。当执行该过程建立临时 LOB 时，oracle 会降该临时 LOB 建立在用户的临时表空间中。
语法：
DBMS_LOB.CREATETEMPORARY
(
 lob_loc IN  OUT BLOB/CLOB/NCLOB,
 cache IN BOOLEAN,
 dur IN PLS_INTEGER := 10
);
lob_loc： 指定 LOB 定位符
cache: 指定是否需要将 LOB 读取到缓冲区
dur： 指定何时清除临时 LOB（10: 会话结束时清除临时 LOB；12: 调用结束时清除临时 LOB）

ERASE
用于删除 LOB 变量的全部内容或部分内容。该过程不适用于 BFILE 类型。
语法：
DBMS_LOB.ERASE
(
 lob_loc IN OUT NOCOPY BLOB/CLOB/NCLOB,
 amount IN OUT NOCOPY INTEGER,
 offset IN INTEGER := 1
);

FILECLOSE
用于关闭已经打开的 BFILE 定位符所指向的 OS 文件。
语法：
DBMS_LOB.FILECLOSE( file_loc IN OUT NOCOPY BFILE );

FILECLOSEALL
用于关闭当前会话已经打开的所有的 BFILE 文件。
语法：
DBMS_LOB.FILECLOSEALL

FILEGETNAME
用于取得 BFILE 定位符所对应的目录别名和文件名。
语法：
DBMS_LOB.FILEGETNAME
(
 file_loc IN BFILE,
 dir_alias OUT VARCHAR2,
 filename OUT VARCHAR2
);
dir_alias： 取得 BFILE 定位符所对应的目录别名
filename： 取得 BFILE 定位符所对应的文件名

FILEOPEN
用于打开 BFILE 所对应的 OS 文件。
语法：
DBMS_LOB.FILEOPEN
(
 file_loc IN OUT NOCOPY BFILE,
 open_mode IN BINARY_INTEGER := file_readonly
);
open_mode： 指定文件的打开模式。OS 文件只能以只读方式打开。

FREETEMPORARY
用于释放在默认临时表空间中的临时 LOB。
语法：
DBMS_LOB.FREETEMPORARY( lob_loc IN OUT NOCOPY BLOB/CLOB/NCLOB );

LOADFROMFILE
用于将 BFILE 的部分或者全部内容复制到目标 LOB 变量（CLOB 或 BLOB）中。当使用该过程将 BFILE 数据装载到 CLOB 中时不会进行字符集转换，因此需要确保 BFILE 数据与数据库具有相同字符集，否则装载后的数据位乱码。
语法：
DBMS_LOB.LOADFROMFILE
(
 dest_lob IN OUT NOCOPY BLOB/CLOB,
 src_file IN BFILE,
 amount IN INTEGER,
 dest_offset IN INTEGER := 1,
 src_offset IN INTEGER := 1
);

LOADBLOBFROMFILE
用于将 BFILE 数据装载到 BLOB 中，并且在装载后可以取得新的偏移位置。
语法：
DBMS_LOB.LOADBLOBFROMFILE
(
 dest_lob IN OUT NOCOPY BLOB,
 src_bfile IN BFILE,
 amount IN INTEGER,
 dest_offset IN OUT INTEGER,
 src_offset IN OUT INTEGER
);
src_bfile： 指定 BFILE 定位符
dest_offset（IN）： 指定目标 LOB 的起始位置
dest_offset（OUT）： 用于取得装载完成后的偏移位置
src_offset（IN）： 指定 BFILE 定位符的起始位置
src_offset（OUT）： 用于取得 BFILE 读取完成后的偏移位置

LOADCLOBFROMFILE
用于将 BFILE 数据装载到 CLOB 中。当使用该过程装载数据到 CLOB 中时，可以指定字符集 ID 号，并进行字符集转换。
语法：
DBMS_LOB.LOADCLOBFROMFILE
(
 dest_lob IN OUT NOCOPY CLOB,
 src_bfile IN BFILE,
 amount IN INTEGER,
 dest_offset IN OUT INTEGER,
 src_offset IN OUT INTEGER,
 src_csid IN NUMBER,
 lang_context IN OUT INTEGER,
 warning OUT INTEGER
);
src_csid： 指定源文件的字符集 ID 号
lang_context（IN）： 指定语言上下文
lang_context（OUT）： 取得先前装载的语言上下文
warning：用于取得警告消息

OPEN
用于在打开 LOB 时指定 LOB 的读写模式。只读（dbms_lob.lob_readonly）、读写（dbms_lob.lob_readwrite）
语法：
DBMS_LOB.OPEN
(
 lob_loc IN OUT NOCOPY BLOB/CLOB/BFILE,
 open_mode IN BINARY_INTEGER
);

READ
用于将 LOB 数据读取到缓冲区中。
语法：
DBMS_LOB.READ
(
 lob_loc IN OUT NOCOPY BLOB/CLOB/BFILE,
 amount IN OUT NOCOPY BINARY_INTEGER,
 offset IN INTEGER,
 buffer OUT RAW/VARCHAR2
);
amount（IN）： 指定要读取的字节个数（BLOB）或字符个数（CLOB）
amount（OUT）： 用于取得实际读取的字节个数或字符个数

TRIM
用于截断 LOB 内容到指定长度。该过程不适用于 BFILE 类型。
语法：
DBMS_LOB.TRIM
(
 lob_loc IN OUT NOCOPY BLOB/CLOB/NCLOB,
 newlen IN INTEGER
);
newlen： 指定截断后的 LOB 长度

WRITE
用于将缓冲区数据写入到 LOB 中的特定位置。该过程不适用于 BFILE 类型。
语法：
DBMS_LOB.WRITE
(
 lob_loc IN OUT NOCOPY BLOB/CLOB,
 amount IN BINARY_INTEGER,
 offset IN INTEGER,
 buffer IN RAW/VARCHAR2
);
buffer： 指定要写入的内容

WRITEAPPEND
用于将缓冲区数据写入到 LOB 尾部。该过程不适用于 BFILE 类型。
语法：
DBMS_LOB.WRITEAPPEND
(
 lob_loc IN OUT NOCOPY BLOB/CLOB/NCLOB,
 amount IN BINARY_INTEGER,
 buffer IN RAW
);

 

3、函数
COMPARE
用于比较两个 LOB 的全部内容或部分内容。该函数只能用于比较同类型的 LOB 变量。如果比较结果相同则返回 0；否则返回一个非 0 的整数。
语法：
DBMS_LOB.COMPARE
(
 lob_1 IN BLOB/CLOB/BFILE,
  lob_2 IN BLOB/CLOB/BFILE,
 amount IN INTEGER := 4294967295,
 offset_1 IN INTEGER := 1,
 offset_2 IN INTEGER := 1
) RETURN INTEGER;
lob_1： 指定第一个 LOB 变量
lob_2： 指定第二个 LOB 变量
amount: 指定字符和数（CLOB）或字节个数（BLOB）
offset_1: 指定第一个 LOB 的起始比较位置
offset_2： 指定第二个 LOB 的起始比较位置

FILEEXISTS
用于确定 BFILE 定位符所指向的 OS 文件是否存在。如果文件存在则返回 1；否则返回 0。
语法：
DBMS_LOB.FILEEXISTS( file_loc IN BFILE ) RETURN INTEGER;

FILEISOPEN
用于确定 BFILE 所对应的 OS 文件是否已经打开。如果文件已经被打开，则返回 1；否则返回 0。
语法：
DBMS_LOB.FILEISOPEN( file_loc IN BFILE ) RETURN INTEGER;

GETCHUNKSIZE
当监理包含 CLOB 列或 BLOB 列的表时，通过指定 CHUNK 参数可以指定操纵 LOB 需要分配的字节数（该值是数据块尺寸的整数倍），如果不指定该参数其默认值为数据块的尺寸。通过使用函数 GETCHUNKSIZE 可以获取 CHUNK 参数所对应的值。
语法：
DBMS_LOB.GETCHUNKSIZE( lob_loc IN BLOB/CLOB/NCLOB )RETURN INTEGER;

GETLENGTH
用于取得 LOB 数据的实际长度。
语法：
DBMS_LOB.GETLENGTH( lob_loc IN BLOB/CLOB/NCLOB ) RETURN INTEGER;

INSTR
用于返回特定样式数据在 LOB 中从某便宜位置开始第 n 次出现时的集体位置。
语法：
DBMS_LOB.INSTR
(
 lob_loc IN BLOB/CLOB/NCLOB/BFILE,
 pattern IN RAW/VARCHAR2,
 offset IN INTEGER := 1,
 nth IN INTEGER := 1
)
RETURN INTEGER;
pattern： 指定要搜索的字符串或二进制数据
offset： 指定搜索的起始位置
nth： 指定第 n 次出现次数

ISOPEN
用于确定 LOB 是否已经被打开。如果 LOB 已经被打开则返回 1；否则返回 0。
语法：
DBMS_LOB.ISOPEN( lob_loc IN BLOB/CLOB/BFILE ) RETURN INTEGER;

ISTEMPORARY
用于确定 LOB 定位符是否为临时 LOB。如果是临时 LOB 则返回 1；否则返回 0。
语法：
DBMS_LOB.ISTEMPORARY( lob_loc IN BLOB/CLOB/NCLOB ) RETURN INTEGER;

SUBSTR
用于返回 LOB 中从指定位置开始的部分内容。
语法：
DBMS_LOB.SUBSTR
(
 lob_loc IN BLOB/CLOB/BFILE,
 amount IN INTEGER := 32767,
 offset IN INTEGER := 1
) RETURN RAW;