DBMS_LOB的用法

公司的系统有个记录日志的功能，日志的显著特点是：长。。。所以用varchar来存储就不合适了（varchar2最多存储4000字符）。都是用的CLOB类型，下面来学习下CLOB类型的一般用法。

1. EMPTY_CLOB()

the LOB is initialized, but not populated with data.用于清空clob类型

2. dbms_lob.createtemporary(v_clob,true)      --Specifies if LOB should be read into buffer cache or not.

This procedure creates a temporaryBLOB orCLOB and its corresponding index in your default temporary tablespace

3. dbms_lob.getlength(v_clob)

This function gets the length of the specified LOB. The length in bytes or characters is returned.

4. dbms_lob.append(v_clob,'ss');

This procedure appends the contents of a source internalLOB to a destinationLOB. It appends the complete sourceLOB.

5. dbms_lob.writeappend(lob_loc,4,‘assdasd’);

This procedure writes a specified amount of data to the end of an internalLOB. The data is written from thebuffer parameter.

6. dbms_lob.OPEN(v_clob,LOB_READONLY)

This procedure opens a LOB, internal or external, in the indicated mode.ForBLOB andCLOB types, the mode can be either: LOB_READONLY orLOB_READWRITE.

7. dbms_lob.close(v_clob)

This procedure closes a previously opened internal or externalLOB.

8. dbms_lob.substr(v_clob,100)

This function returns amount bytes or characters of a LOB, starting from an absolute offset from the beginning of the LOB.

举例：

declare
  v_clob clob;
  a      VARCHAR2(2000);
begin
dbms_lob.createtemporary(v_clob,true);
dbms_lob.append(v_clob,'ss是');
dbms_output.put_line(dbms_lob.getlength(v_clob));---3，中文字符也当做一个字符
a := dbms_lob.substr(v_clob,5);
dbms_output.put_line(a);--ss是
dbms_lob.writeappend(v_clob,3,'世界纪录');
a := dbms_lob.substr(v_clob,7);
dbms_output.put_line(a);--ss是世界纪
end;