LOB（7）— 操作

从 Oracle9i Database Release 1 开始， LOB 可以不通过系统包来实现一些自身的操作：

 

* 大部分以 VARCHAR2 做参数的 SQL 函数和 PL/SQL 函数中，都支持 CLOB；

* 在 PL/SQL 中（而不是 SQL ），LOB 可以使用关系运算符（>, <, =, ...）；

* 你可以将 CLOB 数据赋值给 VARCHAR2 类型的变量（反之亦然），也可以在 SELECT 语句中将 CLOB 数据赋值给 VARCHAR2 变量（反之亦然）。因为 PL/SQL 会隐式转换 CLOB 与 VARCHAR2。

 

当然，这样使用 LOB 有很多的限制。在 Oracle10g Database Release 2 的官方文档《Application Developer's GuideLarge Objects》的第九章“SQL Semantics and LOBs”中对此有详细介绍。

 

1. SQL 语句导致的 Temporary LOB

 

有时候，SQL 语句会导致 Temporary LOB 的产生。比如下面的这两个SQL：

 

DECLARE    directions CLOB; BEGIN    SELECT UPPER(falls_directions)    INTO directions    FROM waterfalls    WHERE falls_name = 'Munising Falls'; END;

DECLARE    directions CLOB; BEGIN    SELECT falls_directions    INTO directions    FROM waterfalls    WHERE falls_name = 'Munising Falls'; END;

 

右边的这个是普通的 Persistent LOB，而左边这个增加了一个函数 UPPER，变为了 Temporary LOB。WHY？我们已经知道列 fall_directions 对应的 CLOB 数据存储在磁盘中，不可能将磁盘中的这些数据转换为大写形式；Oracle 也不可能将这些大写的数据放到内存中，因为内存空间很可能不够，而且 CLOB 指针是指向磁盘而不是内存的。那么，唯一的选择就是在临时表空间中创建一个新的 Temporary LOB 了。UPPER 函数将原来的 Persistent LOB 数据从原来的位置拷贝到 Temporary CLOB 中

，在拷贝的过程中将数据变成大写的形式。最后这个 SELECT  返回指向临时表空间中的 Temporary LOB 数据的指针，而不是原来的 LOB 数据。所以，你需要注意：

 

* 不能用返回的这个 LOB 指针来更新 LOB 数据，因为它已经不再指向原来的 LOB 数据了，而是指向临时表空间的 Temporary LOB 数据。

* 创建 Temporary LOB 时，会占用磁盘空间和 CPU。后面我们会继续讨论这个话题。

 

不止是 UPPER 函数，大部分入参为 VARCHAR2 返回值也为 VARCHAR2 的函数，只要能应用在 CLOB 上，就会产生 Temporary CLOB。返回 CLOB 的表达式也是一样。要记住返回的 Temporary LOB 指针已经不再指向原来的 LOB 数据了，所以不要试图使用它来更新原来的 LOB 数据。

 

2. SQL 语句中 Temporary LOB 的性能

 

“LOB”中的“L”的意思是“Large”从 Oracle10g 开始，最大可达到128T。所以如果在 SQL 语句中产生 LOB 数据，你应该仔细考虑它的执行过程，以及由此所带来的性能问题。我们看一个最简单的 SQL：

 

SELECT falls_name
FROM waterfalls
WHERE INSTR(UPPER(falls_directions),'MACKINAC BRIDGE') <> 0;

 

该 SQL 从表 waterfalls 中查询数据，返回字段 falls_directions 中包含 'MACKINAC BRIDGE' 的数据。我们考虑一下它的执行过程：

 

（1）在临时表空间中分配空间；

（2）将字段 falls_directions 中的 CLOB 数据拷贝到临时表空间中，并在拷贝的过程中将每个字符大写，形成新的 Temporary CLOB 数据；

（3）在临时表空间中的每个 Temporary CLOB 数据中查找是否包含字符串 'MACKINAC BRIDGE' ；

（4）返回结果。

 

如果每个 falls_directions 数据都很大，比如说1G，那么想想步骤 2 和步骤 3 中所消耗的 CPU 和磁盘空间吧。在你的数据库中，这个简单的 SQL 能否执行呢？它可能会使你的 DBA 火冒三丈。

 

Oracle 推荐每个 LOB 数据大小在 100kb 以内的，可以使用 Temporary LOB。不过我感觉不要完全相信这个数据，还是要在自己的应用系统中实际测试一下。在使用 LOB 数据时，一定要注意 Temporary LOB 所带来的开销。

 

Oracle Text and SQL Semantics

If you need to execute queries that look at uppercase versions of CLOB values, and you need to do so efficiently, Oracle Text may hold the solution. For example, you might reasonably expect to write a query such as the following some day:

    SELECT falls_name
    FROM waterfalls
    WHERE INSTR(UPPER(falls_directions), 'MACKINAC BRIDGE') <> 0;

If falls_directions is a CLOB column, this query may not be all that efficient. However, if you are using Oracle Text, you can define a case-insensitive Oracle Text index on that CLOB column, and then use the CONTAINS predicate to efficiently evaluate the query:

    SELECT falls_name
    FROM waterfalls
    WHERE CONTAINS(falls_directions,'mackinac bridge') > 0;

For more information on CONTAINS and case-insensitive indexes using Oracle Text, see Oracle's Text Application Developer's Guide.

3. LOB 转换函数

 

Oracle 提供了一些转换函数，在你操作 LOB 数据时可能用得着：

Function	Description
TO_CLOB (character_data)	Converts character data into a CLOB. The input to TO_CLOB can be any of the following character types: VARCHAR2, NVARCHAR2, CHAR, NCHAR, CLOB, and NCLOB. If necessary (for example, if the input is NVARCHAR2), input data is converted from the national character set into the database character set.
TO_BLOB(raw_data)	Similar to TO_CLOB, but converts RAW or LONG RAW data into a BLOB.
TO_NCLOB (character_data)	Does the same as TO_CLOB, except that the result is an NCLOB using the national character set.
TO_LOB (long_data)	Accepts either LONG or LONG RAW data as input, and converts that data to a CLOB or a BLOB, respectively. TO_LOB may be invoked only from the select list of a subquery in an INSERT ... SELECT ... FROM statement.
TO_RAW	Takes a BLOB as input and returns the BLOB's data as a RAW value.

The TO_LOB function is designed specifically to enable one-time conversion of LONG and LONG RAW columns into CLOB and BLOB columns, because LONG and LONG RAW are now considered obsolete. The TO_CLOB and TO_NCLOB functions provide a convenient mechanism for converting character large object data between the database and national language character sets.