公司的系统有个记录日志的功能,日志的显著特点是:长。。。所以用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
or CLOB
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 destination LOB
. 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 the buffer
parameter.
6. dbms_lob.OPEN(v_clob,LOB_READONLY)
This procedure opens a LOB
, internal or external, in the indicated mode.ForBLOB
and CLOB
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;