LOB（4）— 读写

1. 写入

 

当获取到一个可用的 LOB 指针后，就可以通过该指针写入 LOB 数据了。有两种写入数据的系统函数：

 

* DBMS_LOB.WRITE ：将数据随机地写入 LOB 中。

 

* DBMS_LOB.WRITEAPPEND ：从 LOB 的最后开始写入数据。

 

下面我们将原来的例子稍微扩展一下，打开一个  CLOB 指针后，先使用 DBMS_LOB.WRITE 写入一些数据，再使用 DBMS_LOB.WRITEAPPEND 写入一些数据：

 

DECLARE
   directions CLOB;
   amount BINARY_INTEGER;
   offset INTEGER;
   first_direction VARCHAR2(100);
   more_directions VARCHAR2(500);
BEGIN
   --Delete any existing rows for 'Munising Falls' so that this
   --example can be executed multiple times
   DELETE
     FROM waterfalls
    WHERE falls_name='Munising Falls';

 

   --Insert a new row using EMPTY_CLOB(  ) to create a LOB locator
   INSERT INTO waterfalls
             (falls_name,falls_directions)
      VALUES ('Munising Falls',EMPTY_CLOB(  ));

 

   --Retrieve the LOB locator created by the previous INSERT statement
   SELECT falls_directions
     INTO directions
     FROM waterfalls
    WHERE falls_name='Munising Falls';

 

   --Open the LOB; not strictly necessary, but best to open/close LOBs.
   DBMS_LOB.OPEN(directions, DBMS_LOB.LOB_READWRITE);

 

   --Use DBMS_LOB.WRITE to begin
   first_direction := 'Follow I-75 across the Mackinac Bridge.';
   amount := LENGTH(first_direction);  --number of characters to write
   offset := 1; --begin writing to the first character of the CLOB
   DBMS_LOB.WRITE(directions, amount, offset, first_direction);

 

   --Add some more directions using DBMS_LOB.WRITEAPPEND
   more_directions := ' Take US-2 west from St. Ignace to Blaney Park.'
                   || ' Turn north on M-77 and drive to Seney.'
                   || ' From Seney, take M-28 west to Munising.';
   DBMS_LOB.WRITEAPPEND(directions,
                        LENGTH(more_directions), more_directions);

 

   --Add yet more directions
   more_directions := ' In front of the paper mill, turn right on H-58.'
                   || ' Follow H-58 to Washington Street. Veer left onto'
                   || ' Washington Street. You''ll find the Munising'
                   || ' Falls visitor center across from the hospital at'
                   || ' the point where Washington Street becomes'
                   || ' Sand Point Road.';
   DBMS_LOB.WRITEAPPEND(directions,
                        LENGTH(more_directions), more_directions);

 

   --Close the LOB, and we are done.
   DBMS_LOB.CLOSE(directions);
END;

 

写入 LOB 数据，就这么简单，获取到这个 LOB 指针，然后通过 DBMS_LOB.WRITE 或 DBMS_LOB.WRITEAPPEND 来写入数据，最后关闭该指针。

 

这里并不需要使用 UPDATE 来更新列 falls_directions，因为这个 LOB 指针并没有发生变化，我们只是将数据写入它所指向的位置。

 

在代码中，没有写 COMMIT，你可以根据需要来提交或回滚数据：

 

SQL> set long 10000

SQL> select FALLS_DIRECTIONS from waterfalls;

 

FALLS_DIRECTIONS
--------------------------------------------------------------------------------
Follow I-75 across the Mackinac Bridge. Take US-2 west from St. Ignace to Blaney
 Park. Turn north on M-77 and drive to Seney. From Seney, take M-28 west to Muni
sing. In front of the paper mill, turn right on H-58. Follow H-58 to Washington
Street. Veer left onto Washington Street. You'll find the Munising Falls visitor
 center across from the hospital at the point where Washington Street becomes Sa
nd Point Road.

 

SQL> commit;

 

提交完成。

 

 

2. 读取

 

使用系统函数 DBMS_LOB.READ( ) 来读取 LOB 中的数据，当然，首先要得到这个 LOB 指针。

 

比如读取 CLOB 数据，应该指定字符串的偏移量（offset），从指定的偏移量的位置开始读取数据。CLOB 的第一个字符的偏移量是1；

也需要指定读取的字符串长度。如果这个 CLOB 数据太大，应该多次读取数据。

 

对于 BLOB 数据，也是这样处理，唯一的区别就是它是按字节存储的。

 

下面我们把刚才保存的数据读取出来：

 

DECLARE
   directions CLOB;
   directions_1 VARCHAR2(300);
   directions_2 VARCHAR2(300);
   chars_read_1 BINARY_INTEGER;
   chars_read_2 BINARY_INTEGER;
   offset INTEGER;
BEGIN
   --Retrieve the LOB locator inserted previously
   SELECT falls_directions
     INTO directions
     FROM waterfalls
    WHERE falls_name='Munising Falls';

 

   --Begin reading with the first character
   offset := 1;

 

   --Attempt to read 229 characters of directions, chars_read_1 will
   --be updated with the actual number of characters read
   chars_read_1 := 229;
   DBMS_LOB.READ(directions, chars_read_1, offset, directions_1);

 

   --If we read 229 characters, update the offset and try to
   --read 255 more.
   IF chars_read_1 = 229 THEN
      offset := offset + chars_read_1;
      chars_read_2 := 255;
      DBMS_LOB.READ(directions, chars_read_2, offset, directions_2);
   ELSE
      chars_read_2 := 0;
      directions_2 := '';
   END IF;

 

   --Display the total number of characters read
   DBMS_OUTPUT.PUT_LINE('Characters read = ' || TO_CHAR(chars_read_1+chars_read_2));

 

   --Display the directions
   DBMS_OUTPUT.PUT_LINE(directions_1);
   DBMS_OUTPUT.PUT_LINE(directions_2);
END;

 

输出结果如下：

 

Characters read = 414
Follow I-75 across the Mackinac Bridge. Take US-2 west from St. Ignace to Blaney
Park. Turn north on M-77 and drive to Seney. From Seney, take M-28 west to
Munising. In front of the paper mill, turn right on H-58. Follow H-58 to
Washington Street. Veer left onto Washington Street. You'll find the Munising
Falls visitor center across from the hospital at the point where Washington
Street becomes Sand Point Road.

 

DBMS_LOB.READ 中的第二个参数 chars_read_1，是 IN OUT 参数。调用时按照该参数指定的长度来读取数据，读取完毕后，将其更新为实际读取的字符（字节）长度。当读取后，该参数的值比你原来的值小，则说明已经读取到 LOB 的末尾了。而 DBMS_LOB.READ 中的第三个参数——偏移量——却并没有被更新，这让我们使用起来有点不方便，当多次读取时，你得手工修改每次读取的偏移量的位置了。

 

所以，第二个参数不能用常量，而必须声明一个变量作为第二个参数。否则，会出现类似这样的错误：

 

   DBMS_LOB.READ(waterfall, 60, 1, piece);
                            *
ERROR 位于第 15 行:
ORA-06550: 第 15 行, 第 29 列:
PLS-00363: 表达式 '60' 不能用作赋值目标
ORA-06550: 第 15 行, 第 4 列:
PL/SQL: Statement ignored

 

你也可以使用系统函数 DBMS_LOB.GET_LENGTH( lob_locator ) 来获取 LOB 数据的长度。