一、oracle中的varchar2类型
我们在oracle数据库存储的字符数据一般是用varchar2。varchar2既分pl/sql data types中的变量类型，也分oracle database中的字段类型，不同场景的最大长度不同。
在oracle database中，varchar2 字段类型，最大值为4000；pl/sql中 varchar2 变量类型，最大字节长度为32767。
当 varchar2 容纳不下我们需要存储的信息时，就出来的oracle的大数据类型lob( large object，大型对象)。
二、oarcle中的lob类型
在oracle中，lob（large object，大型对象）类型的字段现在用得越来越多了。因为这种类型的字段，容量大（最多能容纳4gb的数据），且一个表中可以有多个这种类型的字段，很灵活，适用于数据量非常大的业务领域（如图象、档案等）。
lob类型分为blob和clob两种：blob即二进制大型对象（binary large object），适用于存贮非文本的字节流数据（如程序、图象、影音等）。
而clob，即字符型大型对象（character large object），则与字符集相关，适于存贮文本型的数据（如历史档案、大部头著作等）。
三、db中使用clob类型字段
（一）、创建表（使用sql或者直接在pl/sql客户端创建），字段类型clob
-- create table create table temp ( name varchar2(200), age number, temp_clob clob ) tablespace insightdata3_ts pctfree 10 initrans 1 maxtrans 255 storage ( initial 160k next 1m minextents 1 maxextents unlimited );
（二）、增删改查
先看一下使用普通方式操作clob类型：
select t.name, t.temp_clob from temp t; --普通查询 insert into temp t values ('grand.jon', 22, '加入博客园的第一天');
查询因为不是varchar2类型，所以普通查询看不到clob类型的字段内容，结果如下
而普通插入操作也会因为oracle的隐式转换，默认把字符串转换成varchar2类型，一旦字符串内容超过varchar2的最大限度就会报会报ora-01704（字符串太长）错误。
正确操作
--使用pl/sql语法，采取绑定变量的方式解决，而不是直接拼接sql
declare
v_lang clob := '待插入的海量字符串';
v_update clob := '更新的海量字符串';
begin
insert into temp t values ('grand.jon', 22, v_lang); --增加
update temp t set t.temp_clob = v_update where rownum = 1; --修改
select t.name, dbms_lob.substr(t.temp_clob) from temp t; --查询 将clob转成字符类型
delete temp t where rownum = 1; --按列删除
commit;
end;
/
对clob的操作我们在存储过程中基本上使用 dbms_lob 中 substr , append , write 等方法。
dbms_lob 方法总结
dbms_lob.createtemporary(v_sql,true); --创建一个临时clob，用来存储拼接的sql dbms_lob.write(v_sql,'写入信息'); --写入操作 dbms_lob.append(v_sql,','); --拼接clob dbms_lob.substr(v_sql); --截取clob，不传参数就是全部读取 dbms_lob.freetemporary(v_sql); --释放clob
查询结果如下：
四、在存储过程中使用clob类型实例
需求：以开发的存储过程为例，需要循环遍历时间范围拼接sql，将时间日期按列反转（pivot），如果时间太长（1年以上）sql语句（varchar2）就会超出范围报错，这时候就需要使用clob来存储拼接的sql。
procedure p_and_cpt_ratiooth_app_bak2_n(
v_appids in varchar2,
v_type in varchar2,
v_channel in varchar2,
v_table in varchar2,
v_start in varchar2,
v_end in varchar2,
result out mycursor
) is
v_sql clob;
v_sqlwhere varchar2(32767) default '';
v_sqlwhere_channel varchar2(32767) default '';
v_sql_dates clob;
v_sdate date;
v_edate date;
v_table_date varchar2(50);
v_table_type varchar2(50);
v_start_date varchar2(50);
v_end_date varchar2(50);
v_day varchar2(50);
begin
select column_name into v_table_date from user_tab_columns where table_name=''||v_table||'' and column_id=1;
select column_name into v_table_type from user_tab_columns where table_name=''||v_table||'' and column_id=5;
dbms_lob.createtemporary(v_sql,true);--创建一个临时lob
dbms_lob.createtemporary(v_sql_dates,true);--创建一个临时lob
if v_appids is not null then
v_sqlwhere := 'and t.appid in ('||v_appids||')';
end if;
if v_channel is not null then
v_sqlwhere_channel := 'and t.channel = '''||v_channel||'''';
end if;
if v_table_date = 'mon' then
v_start_date := substr(v_start,0,6);
v_end_date := substr(v_end,0,6);
v_sdate := to_date(v_start_date, 'yyyymm');
v_edate := to_date(v_end_date, 'yyyymm');
while (v_sdate <= v_edate) loop
dbms_lob.append(v_sql_dates,to_char(v_sdate, 'yyyymm'));--把临时字符串付给v_str
if v_sdate != v_edate then
dbms_lob.append(v_sql_dates,',');--把临时字符串付给v_str
end if;
v_sdate := add_months(v_sdate,1);
end loop;
else --周和日 类型 都是 day
v_sdate := to_date(v_start, 'yyyymmdd');
v_edate := to_date(v_end, 'yyyymmdd');
v_end_date := v_end;
if substr(v_type,0,1)='d' then
v_start_date := to_char(v_sdate, 'yyyymmdd');
while (v_sdate <= v_edate) loop
dbms_lob.append(v_sql_dates,to_char(v_sdate, 'yyyymmdd'));--把临时字符串付给v_str
if v_sdate != v_edate then
dbms_lob.append(v_sql_dates,',');--把临时字符串付给v_str
end if;
v_sdate := v_sdate+1;
end loop;
elsif substr(v_type,0,1)='w' then
select to_char(v_sdate,'d') into v_day from dual;
if v_day!=2 then
v_sdate:=v_sdate-7;
end if;
v_start_date := to_char(v_sdate, 'yyyymmdd');
while (v_sdate <= v_edate) loop
select to_char(v_sdate,'d') into v_day from dual;
if v_day=2 then
dbms_lob.append(v_sql_dates,to_char(v_sdate, 'yyyymmdd'));--把临时字符串付给v_str
if v_edate-v_sdate >7 then
dbms_lob.append(v_sql_dates,',');--把临时字符串付给v_str
end if;
end if;
v_sdate := v_sdate+1;
end loop;
end if;
end if;
dbms_lob.append(v_sql,'select * from( select *
from '||v_table||' t
where
t.'||v_table_type||' = '''||v_type||'''
and t.'||v_table_date||' >= '''||v_start_date||'''
and t.'||v_table_date||' <= '''||v_end_date||'''
'||v_sqlwhere||'
'||v_sqlwhere_channel||' ) t1
pivot(sum(marketshare)
for '||v_table_date||' in(');
dbms_lob.append(v_sql,v_sql_dates);
dbms_lob.append(v_sql,'))');
dbms_output.put_line(v_sql);
open result for v_sql;
dbms_lob.freetemporary(v_sql);--释放lob
dbms_lob.freetemporary(v_sql_dates);--释放lob
--dbms_output.put_line(v_sqldate);
-- dbms_output.put_line(v_sql_dates);
--记录操作日志及错误日志
end;
五、使用java开发操作clob字段
（一）、原生jdbc处理clob类型
增加，一般会插入一个空的clob到数据库对应的字段,然后锁定该列，用write将待插入字符串写入进去。
重点：这两步操作要放在同一个事务里面。具体增加的方法如下:
public boolean save(article article){
boolean result = true;
connection conn = connectionuntils.getinstance();
string sql = "insert into temp values(?,?,empty_clob())";
//锁住该列，防止并发写入时候该字段同时被多次写入造成错误
string sqlclob = "select temp_clob from temp t where t.name=? for update";
preparedstatement pst =null;
resultset rs = null;
writer writer = null;
try {
conn.setautocommit(false);//设置不自动提交，开启事务
pst = conn.preparestatement(sql);
pst.setstring(1,article.getname());
pst.setstring(2,article.getage());
pst.executeupdate();
pst= conn.preparestatement(sqlclob);
pst.setint(1, article.getid());
rs = pst.executequery();
clob clob = null;
if(rs.next()){
try {
clob = (clob) rs.getclob(1);
writer = clob.getcharacteroutputstream(); //拿到clob的字符输入流
writer.write(article.getcontent());
writer.flush();
writer.close();
} catch (ioexception e) {
e.printstacktrace();
}
}
conn.commit();
} catch (sqlexception e) {
result = false;
try {
conn.rollback();//当commit或者rollback后会自动释放该列的锁定
} catch (sqlexception e1) {
e1.printstacktrace();
}
e.printstacktrace();
} finally {
conn.setautocommit(true);//还原
connectionuntils.close(rs, pst, conn);
}
return result;
}
update操作，update时候主要利用preparedstatement的setclob方法:
public boolean update(string name,string content){
int result = 0;
connection conn = connectionuntils.getinstance();
string sql = "update temp set temp_clob=? where name=?";
preparedstatement pst =null;
try {
clob clob = oracle.sql.clob.createtemporary(conn, false,oracle.sql.clob.duration_session);
clob.setstring(1l, content);
pst = conn.preparestatement(sql);
pst.setclob(1, clob);
pst.setstring(2,name);
result = pst.executeupdate();
} catch (sqlexception e) {
e.printstacktrace();
}finally{
connectionuntils.close(null, pst, conn);
}
if(result==0)
return false;
return true;
}
查询就主要是从结果集resultset中定位到对应的字段后，往外读：
public article select(string name){
article article = new article();
connection conn = connectionuntils.getinstance();
string sql = "select name,age,temp_clog from temp where name = ?";
preparedstatement pst =null;
resultset rs = null;
try {
pst = conn.preparestatement(sql);
pst.setint(1,id);
rs = pst.executequery();
stringbuilder builder = new stringbuilder();
if(rs.next()){
clob clob = rs.getclob("temp_clog");
reader rd = clob.getcharacterstream();
char [] str = new char[12];
while(rd.read(str) != -1) {
builder.append(new string(str));
}
article.setcontent(builder.tostring());
article.setname(rs.getstring("name"));
article.setage(rs.getint("age"));
}
} catch (sqlexception e) {
e.printstacktrace();
} catch (ioexception e) {
e.printstacktrace();
}finally{
connectionuntils.close(rs, pst, conn);
}
return article;
}
（二）、hibernate、mybatis框架操作
这个因为框架都封装集成好了，所以我们使用的时候直接配置变量的类型为clob就可以。
譬如：mybatis
<result column="temp_clob" property="content" jdbctype="clob" typehandler="org.apache.ibatis.type.clobtypehandler"/>
貌似hibernate5内部做了对应的处理，可以直接当string类型一样处理即可。
总结
以上所述是www.887551.com给大家介绍的oracle的clob大数据字段类型操作方法，希望对大家有所帮助