#DBMS_LOB

DBMS_LOB包提供了一组内置的函数,用于LOB类型(或可与LOB进行隐式转换的类型)相关的运算。

# COMPARE

DBMS_LOB.COMPARE (
   LOB_1            IN BLOB/CLOB/CHAR/VARCHAR/NCLOB/NCHAR/NVARCHAR/RAW,
   LOB_2            IN BLOB/CLOB/CHAR/VARCHAR/NCLOB/NCHAR/NVARCHAR/RAW,
   AMOUNT           IN BIGINT DEFAULT MAX(LENGTH(LOB_1), LENGTH(LOB_2)),
   OFFSET_1         IN BIGINT DEFAULT 1,
   OFFSET_2         IN BIGINT DEFAULT 1)
  RETURN INTEGER;

COMPARE函数用于比较两个LOB数据的大小,返回一个值为0、-1或者1的INTEGER类型数据。

参数 用途
LOB_1 用于比较的第一个LOB数据,类型可以为BLOB/CLOB/CHAR/VARCHAR/NCLOB/NCHAR/NVARCHAR/RAW。
LOB_2 用于比较的第二个LOB数据,类型可以为BLOB/CLOB/CHAR/VARCHAR/NCLOB/NCHAR/NVARCHAR/RAW。
AMOUNT 用于比较的数据长度,函数从偏移位置开始获取该长度的字节数(对于BLOB/RAW)或字符数(对于CLOB/CHAR/VARCHAR/NCLOB/NCHAR/NVARCHAR)进行比较。缺省取LOB_1与LOB_2数据长度的较大值。
OFFSET_1 LOB_1用于比较的偏移量。缺省为1,即从第1个字节(对于BLOB/RAW)或字符(对于CLOB/CHAR/VARCHAR/NCLOB/NCHAR/NVARCHAR)开始获取AMOUNT长度的数据进行比较。
OFFSET_2 LOB_2用于比较的偏移量。缺省为1,即从第1个字节(对于BLOB/RAW)或字符(对于CLOB/CHAR/VARCHAR/NCLOB/NCHAR/NVARCHAR)开始获取AMOUNT长度的数据进行比较。

本函数的主要规则为:

  • 如果LOB_1和LOB_2在OFFSET和AMOUNT参数指定的范围内完全匹配,函数返回0;如果LOB_1小于LOB_2,函数返回-1;如果LOB_1大于LOB_2,函数返回1。
  • 当LOB_1和LOB_2中的一个为BLOB/RAW类型,而另一个为CLOB/CHAR/VARCHAR/NCLOB/NCHAR/NVARCHAR类型时,函数返回错误。
  • 当LOB_1和LOB_2中的一个为CLOB/CHAR/VARCHAR类型,而另一个为NCLOB/NCHAR/NVARCHAR类型时,函数会将CLOB/CHAR/VARCHAR类型的字符串转码为国家字符集形态,再和NCLOB/NCHAR/NVARCHAR类型进行比较。
  • 出现如下情况之一时,函数返回NULL:
    • AMOUNT < 1
    • OFFSET_1 < 1
    • OFFSET_2 < 1
    • 任一个参数输入了NULL
  • AMOUNT、OFFSET_1、OFFSET_2可以为数值型或字符型,函数将隐式转换为BIGINT,并对小数做截断处理。
  • NCLOB/NCHAR/NVARCHAR按国家字符集UTF-16进行编码,固定按2B一个字符进行计算。

示例

CREATE TABLE clob_compare(C1 CLOB, C2 CLOB);
INSERT INTO clob_compare VALUES('124SD', '12332');
--对C1、C2进行完全比较
SELECT DBMS_LOB.COMPARE(C1, C2) compare_res FROM clob_compare;
 COMPARE_RES 
------------ 
           1
--比较C1、C2的前两个字符
SELECT DBMS_LOB.COMPARE(C1, C2, 2) compare_res FROM clob_compare;
 COMPARE_RES 
------------ 
           0
--比较C1的第2、3位字符和C2的第5位字符
SELECT DBMS_LOB.COMPARE(C1, C2, 2, 2, 5) compare_res FROM clob_compare;
 COMPARE_RES 
------------ 
           1

CREATE TABLE raw_compare(C1 RAW(10), C2 RAW(10));
INSERT INTO raw_compare VALUES('AB', 'A234');
--对C1、C2进行完全比较
SELECT DBMS_LOB.COMPARE(C1, C2) compare_res FROM raw_compare;
 COMPARE_RES 
------------ 
           -1
--比较C1、C2的第一个字节
SELECT DBMS_LOB.COMPARE(C1, C2, 1) compare_res FROM raw_compare;
 COMPARE_RES 
------------ 
           1
--C1未取到数据,而C2获取到第2个字节的比较
SELECT DBMS_LOB.COMPARE(C1, C2, 2, 2, 2) compare_res FROM raw_compare;
 COMPARE_RES 
------------ 
          -1

# GET_LENGTH

DBMS_LOB.GET_LENGTH (
   LOB_LOC    IN  BLOB/CLOB/CHAR/VARCHAR/NCLOB/NCHAR/NVARCHAR/RAW) 
RETURN BIGINT;

GET_LENGTH函数用于获取指定LOB数据的长度,返回一个BIGINT类型的数值。

参数 用途
LOB_LOC 输入参数,类型可以为BLOB/CLOB/CHAR/VARCHAR/NCLOB/NCHAR/NVARCHAR/RAW。

本函数的主要规则为:

  • 输入参数为NULL时,函数返回NULL。
  • 输入参数为BLOB/RAW类型时,函数返回的是按字节的长度;输入参数为CLOB/CHAR/VARCHAR/NCLOB/NCHAR/NVARCHAR时,函数返回的是按字符的长度。
  • NCLOB/NCHAR/NVARCHAR按国家字符集UTF-16进行编码,固定按2B一个字符进行计算。

示例

CREATE TABLE test_get_length(C1 CLOB, C2 BLOB);
INSERT INTO test_get_length VALUES('1U8WQHSD', 'AF1234');
SELECT DBMS_LOB.GET_LENGTH(C1) cl_len,
	   DBMS_LOB.GET_LENGTH(C2) c2_len
FROM test_get_length;
               CL_LEN                C2_LEN 
--------------------- --------------------- 
                    8                     3

# GETLENGTH

同GET_LENGTH。

# SUB_STR

DBMS_LOB.SUB_STR (
   LOB_LOC     IN    BLOB/CLOB/CHAR/VARCHAR/NCLOB/NCHAR/NVARCHAR/RAW,
   AMOUNT      IN    INTEGER DEFAULT 32000,
   OFFSET      IN    BIGINT DEFAULT 1)
RETURN RAW/VARCHAR2;

SUB_STR函数用于读取给定LOB数据的子串,返回RAW或者VARCHAR2类型的数据。

参数 用途
LOB_LOC 要读取的LOB数据,类型可以为BLOB/CLOB/CHAR/VARCHAR/NCLOB/NCHAR/NVARCHAR/RAW。
AMOUNT 要读取的字节数(对于BLOB/RAW)或字符数(对于CLOB/CHAR/VARCHAR/NCLOB/NCHAR/NVARCHAR),该值范围为[1, 32000],缺省为32000。 特例情况,因为NCLOB/NCHAR/NVARCHAR按国家字符集UTF-16进行编码,固定按2B一个字符进行计算,该值范围在[16000, 32000],实际按16000最大字符数处理。
OFFSET 偏移量。缺省为1,即从第1个字节(对于BLOB/RAW)或字符(对于CLOB/CHAR/VARCHAR/NCLOB/NCHAR/NVARCHAR)开始读取AMOUNT长度的数据。

本函数的主要规则为:

  • 函数对LOB_LOC读取从OFFSET开始的AMOUNT长度的字串,单位为字节(对于BLOB/RAW)或字符(对于CLOB/CHAR/VARCHAR/NCLOB/NCHAR/NVARCHAR)。
  • 当LOB_LOC为BLOB/RAW类型时,函数返回RAW类型;当LOB_LOC为CLOB/CHAR/VARCHAR类型时,函数返回VARCHAR2类型。当LOB_LOC为NCLOB/NCHAR/NVARCHAR类型时,函数返回NVARCHAR类型。
  • 出现如下情况之一时,函数返回NULL:
    • AMOUNT < 1
    • AMOUNT > 32000
    • OFFSET < 1
    • 任一个参数输入了NULL

示例

CREATE TABLE test_sub_str(C1 CLOB, C2 BLOB);
INSERT INTO test_sub_str VALUES('1U8WQHSD', 'AF1234');
--读取全部的C1和C2
SELECT DBMS_LOB.SUB_STR(C1) sub_c1,
	   DBMS_LOB.SUB_STR(C2) sub_c2 
FROM test_sub_str;
SUB_C1             SUB_C2          
------------------ ------------------
1U8WQHSD           AF1234                                                        

--从C1/C2的第3个字符/字节开始读取2个字符/字节
SELECT DBMS_LOB.SUB_STR(C1, 2, 3) sub_c1,
	   DBMS_LOB.SUB_STR(C2, 2, 3) sub_c2
FROM test_sub_str;
SUB_C1             SUB_C2          
------------------ ------------------
8W                 34    

# SUBSTR

同SUB_STR。

pdf-btn 下载文档
copy-btn 复制链接