dbms_lob包(一）

    最近在工作中因数据较大，用到了CLOB字段，oracle提供了专门处理LOB字段的包 DBMS_LOB。我的理解：oracle中的Package类似于面向对象语言中的Class，而Package中的函数及过程类似于CLASS中的方法，只不过函数是有返回值，而过程是void型无返回值的。

    现以CLOB为例将该包用到的几个过程总结并记录如下:

 

先说明一些参数，

dest_lob: 需写入的CLOB型参数

amount:   写入dest_lob时字符长度

offset:   偏移量，从第offset个位置开始写入字符

source:   写入dest_lob的字符串

 

--获取dest_lob的字符长度

dbms_lob.getlength(dest_lob);

 

dbms_lob.createtemporary(dest_lob,TRUE);

没完全明白它的作用是什么，oracle文档解释为：This procedure creates a temporary BLOB or CLOB and its corresponding index in your default temporary tablespace.因为在调试过程中发现这样的写法：dest_lob := null;似乎不起作用，我一般用它来清空dest_lob，另外还有一个用来清空LOB字段的方法，那就是empty_clob()，updat t set col= empty_clob(); 但当它用来清空变量 dest_lob := empty_clob(); 仍然也不起作用。不得已只好用dbms_lob.createtemporary(dest_lob,TRUE);来清空数据。在定义dest_lob变量后还不能直接身其写入数据，必须调用dbms_lob.createtemporary(dest_lob,TRUE);进行初始化，否则会提示错误：ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275（请高人指点:) ）

 

--从source中取amount个字符写入到dest_lob,原dest_lob被覆盖

DBMS_LOB.WRITE(dest_lob,amount,offset,source);

SQL> DECLARE
  2 v_clob CLOB;
  3      v_amount INT;
  4      v_offset INT;
  5      v_char VARCHAR2(100);
  6 BEGIN
  7     v_char := 'Chinese中国人';
  8     v_offset := 1;
  9     v_amount := LENGTH(v_char);
 10
 11 dbms_lob.createtemporary(v_clob,TRUE);
 12     dbms_lob.write(v_clob,v_amount,v_offset,v_char);
 13     dbms_output.put_line(v_clob||' 字符长度：'||dbms_lob.getlength(v_clob)||' 字节长度：'||LENGTHB(to_char(v_clob)));
 14
 15 END;
 16 /

Chinese中国人 字符长度：10 字节长度：13

--将source追加到dest_lob之后

DBMS_LOB.APPEND(dest_lob,source);

SQL> DECLARE
  2 v_clob CLOB;
  3      v_amount INT;
  4      v_offset INT;
  5      v_char VARCHAR2(100);
  6 BEGIN
  7     v_char := 'Chinese中国人';
  8     v_offset := 1;
  9     v_amount := LENGTH(v_char);
 10
 11     dbms_lob.createtemporary(v_clob,TRUE);
 12     FOR i IN 1..2 LOOP
 13         dbms_lob.append(v_clob,v_char);
 14         dbms_output.put_line(v_clob||' 字符长度：'||dbms_lob.getlength(v_clob)||' 字节长度：'||LENGTHB(to_char(v_clob)));
 15 END LOOP;
 16 END;
 17 /

Chinese中国人 字符长度：10 字节长度：13
Chinese中国人Chinese中国人 字符长度：20 字节长度：26

--从source中取amount个字符追加到dest_lob结尾

dbms_lob.writeappend(dest_lob,amount,source);

SQL> DECLARE
  2 v_clob CLOB;
  3      v_amount INT;
  4      v_offset INT;
  5      v_char VARCHAR2(100);
  6 BEGIN
  7     v_char := 'Chinese中国人';
  8     v_offset := 1;
  9     v_amount := 7;
 10
 11     dbms_lob.createtemporary(v_clob,TRUE);
 12     FOR i IN 1..2 LOOP
 13         dbms_lob.writeappend(v_clob,v_amount,v_char);
 14         dbms_output.put_line(v_clob||' 字符长度：'||dbms_lob.getlength(v_clob)||' 字节长度：'||LENGTHB(to_char(v_clob)));
 15 END LOOP;
 16 END;
 17 /

Chinese 字符长度：7 字节长度：7
ChineseChinese 字符长度：14 字节长度：14

dbms_lob.append与dbms_lob.writeappend的区别在于，writeappend多了一个amount参数，可以设置一次写入字符数，而append只能将源字符串一次全部写入。

 

最后说明下offset的作用。从下面的示例中可以看出，写入dest_lob时是从第offset个位置写入的，最后dest_lob长度 = 字符串长度 + (offset-1)

SQL> DECLARE
  2 v_clob CLOB;
  3 v_amount INT;
  4 v_offset INT;
  5 v_char VARCHAR2(100);
  6 BEGIN
  7 v_char := 'Chinese中国人';
  8 v_offset := 2;
  9 v_amount := 7;
 10
 11 dbms_lob.createtemporary(v_clob,TRUE);
 12 dbms_lob.write(v_clob,v_amount,v_offset,v_char);
 13 dbms_output.put_line(v_clob||' 字符长度：'||dbms_lob.getlength(v_clob)||' 字节长度：'||LENGTHB(to_char(v_clob)));
 14
 15 END;
 16 /

 Chinese 字符长度：8 字节长度：8


SQL>
SQL> DECLARE
  2 v_clob CLOB;
  3 v_amount INT;
  4 v_offset INT;
  5 v_char VARCHAR2(100);
  6 BEGIN
  7 v_char := 'Chinese中国人';
  8 v_offset := 5;
  9 v_amount := 10;
 10
 11 dbms_lob.createtemporary(v_clob,TRUE);
 12 dbms_lob.write(v_clob,v_amount,v_offset,v_char);
 13 dbms_output.put_line(v_clob||' 字符长度：'||dbms_lob.getlength(v_clob)||' 字节长度：'||LENGTHB(to_char(v_clob)));
 14
 15 END;
 16 /

    Chinese中国人 字符长度：14 字节长度：17

-- The End --